Very Basic Scope/Structure Question

[JPARKHURST]

First, I'm a little embarassed to ask this, and any tutorial which actually covers this woudl be wonderful. I have looked, but haven't found.

I am new to VBA and am attempting to get the environment. Here are some of my questions:

I am VERY confused as to how to set my global variables. What I've read "Just delcare them outside of a function or sub at the top of your VBA file. What I see: 10 files across 6 worksheets, with drop down seletors which ALL have declarations. What I have tried: Declaring in the ThisWorkbook file, declaring in the 'unclaimed' area at the top of my Module, Creating a Workbook_Open module (at this time, accessed with a button to check)...

I want to delcare global variables which outline the document I am using so I can reference these instead of typing the references directly.



Public Sub Workbook_Open()
Public wbSchedule As Workbook
Public wsShortages As Worksheet
Public wsWorkingDispatch As Worksheet
Public wsDashboard As Worksheet
Public wsInstructions As Worksheet
Public wsJobDispatch As Worksheet
Public wsNotes As Worksheet

Set wbSchedule = ThisWorkbook
Set wsShortages = wbSchedule.Worksheets("Shortages")
'MsgBox ("wsShortages" & wsShortages.Name)
Set wsWorkingDispatch = wbSchedule.Worksheets("Working_Dispatch")
'MsgBox ("wsWorkingDispatch" & wsWorkingDispatch.Name)
Set wsDashboard = wbSchedule.Worksheets("Dashboard")
'MsgBox ("wsDashboard" & wsDashboard.Name)
Set wsInstructions = wbSchedule.Worksheets("Instructions")
'MsgBox ("wsInstructions" & wsInstructions.Name)
Set wsJobDispatch = wbSchedule.Worksheets("Job_Dispatch")
'MsgBox ("wsJobDispatch" & wsJobDispatch.Name)
Set wsNotes = wbSchedule.Worksheets("Notes")
'MsgBox ("wsNotes" & wsNotes.Name)
End Sub

This obviously works when I click my "document open" button I am using to simulate opening the document.

However, when I call it in a sub, I'm getting an undeclared variable.

Priavate Sub btnClearWorkingDispatch_Click() 'MsgBox ("wsWorkingDispatch" & wsWorkingDispatch.Name)
Call ClearWorksheet(wsWorkingDispatch, "D")
End Sub

Any help on where I can look, or is it a "dummy, put your variables here".... any advice is very much appreciated.

TIA,

Jon

 

[JPARKHURST]

...

Priavate Sub btnClearWorkingDispatch_Click() 'MsgBox ("wsWorkingDispatch" & wsWorkingDispatch.Name)
Call ClearWorksheet(wsWorkingDispatch, "D")
End Sub

I did, at least, spell private correctly in my vba, but simply mis-typed it here.

 

[Fluff]

Your declarations need to go at the top a standard module & removed from the Workbook_open event.

 

[JPARKHURST]

Thanks, Fluff.

Thank you so much. I began doing soem testing, these were the questions i initially had on reading your post.

I have to run, just wanted to post the thank you and perhaps talk with you ab it more, but it will be a bit before i am back on this project. Thank you, again, Fluff, for all your help, this is a very rushed project and I am constantly interrupted, so it makes learning very difficult. Promise I'm not lazy.

I have added the declarations to the top - where is best to assign values to global variables?

Can I do that in the workbook_open? I want a generic procedure which assigns these - I *think* know .........

it doesn't matter, does it? I just need to check and see if they have been set, then set them in a module, correct?

 

[Fluff]

where is best to assign values to global variables?

Without knowing your exact needs, the workbook_open event is probably best, that way they will be available to all macros in that workbook.

 

[JPARKHURST]

I really appreciate the tips. It's been very difficult in the time i have available for this to find and read proper tutorials on this. Your help is invaluable, thanks for donating your time.

 

[Fluff]

Glad to help & thanks for the feedback.