Global variables - where do you place them?

[CRayF]

OK I’m still trying to get a handle on the VBA methodology,

I’ve now created some “global†subroutines that are not in Module3 and are
called by the worksheet modules…

I have more than one Sub() in the module3. Many share the same variables
“Dim srcProgramDataInputWs As Worksheet†as an example…
Where do I define Global variables that can be used throughout anywhere in
the module… What about a variable that can be used anywhere in any worksheet?
This is what I have in one of my modules:

Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
'
'------------------------------------------------------------------------
' Re-Build Program Summary Template
'------------------------------------------------------------------------

'------- Main File Names used for this WorkBOOK ---------------
'--*** Use Program Input Worksheet as a Master for changes ***
'--------------------------------------------------------------
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")
'--------------------------------------------------------------
'------- Set Variables to Workbook Names ---------------
'--*** Use Program Input Worksheet as a Master for changes ***
'--------------------------------------------------------------

If ActiveSheet.Name = srcProgramSummaryTemplateWs.Name Then
MsgBox "Can't run from Template"
Range("N3").Select
Exit Sub
End If
....more

 

[Jim Thomlinson]

Globals are declared outside of procedures and functions at teh top of the
code window. When they are created you are best off to declare the scope of
them at the same time. Something like

Option Explicit

Public MyProjectLevel as String
Private MyModuleLevel as String

Sub Test()
....

As a rule always try to use the smallest possible scope that you can and
avoid Globals like they were evil. Every time you have a global you create
what can become a debugging nightmare. If during run time a global is not the
value that you anticipate that it should be there is almost know way of
knowing which procedure modified it last. Globals have their place but it is
very limited. If you are using globals to avoid the hassel of passing
variables I would say that you are heading down a very dangerous path.

 

[Tom Ogilvy]

Dim srcProgramDataInputWs As Worksheet

is declared within you sub so it is local to that sub - not global

Global variable are declared at the top of a general module before any
procedure

Public SrcProgramDataInputWs as Worksheet


However, if you want to use the global version in your sub, you would have
to remove the declaration in the sub.

 

[Rowan]

And to add the replies above you can use a variable in more than one
procedure without declaring it as global if you pass it from one to the
other. eg

Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
Dim srcProgramDataInputWs As Worksheet
Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Call MyMacro(srcProgramDataInputWs)
End Sub

Sub MyMacro(srcProgramDataInputWs as Worksheet)
msgbox srcProgramDataInputWs.name
end sub

Regards
Rowan

 

[Jim Thomlinson]

Good point. One thing to note here is that having both a global declaration
and the local declaration within the procedure are perfectly legal syntax and
will not cause a compile error (In this case you would probably get a run
time error as your object would be nothing). Where you have declared both a
Global and a Local varaible the local supercedes the global and is the
varaible that is used in that procedure.

 

[CRayF]

Good to know, I think it best to stay away from them until I understand more.
Still new new... So, to understand passing better...

In one example I'm using in the Worksheet module I call:
ImportNewDataFile False (or)
ImportNewDataFile
to the subroutine below. If I wanted to pass it 2 variables and the newlu
added one as a "filename" not Boolean, how would my SUB() statement look?

------------------------------------
Sub ImportNewDataFile(Optional Confirm As Boolean = True)
'
'------------------------------------------------------------------------
' Import Data File
'------------------------------------------------------------------------
'------- Main File Names used for this WorkBOOK ---------------
'--*** Use Program Input Worksheet as a Master for changes ***
'--------------------------------------------------------------
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryTemplateWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Dim srcBettingTemplateWs As Worksheet
Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcBettingTemplateWs = Sheets("@TempleteBetting")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")
'--------------------------------------------------------------
'------- Set Variables to Workbook Names ---------------
'--*** Use Program Input Worksheet as a Master for changes ***
'--------------------------------------------------------------

If ActiveSheet.Name = srcProgramSummaryTemplateWs.Name Then
MsgBox "Can't run from Template"
Range("N3").Select
Exit Sub
End If

If Confirm Then 'If changes were made parm should be set to TRUE

x = MsgBox("You've made changes." & Chr(10) & _
"If you continue loading a NEW 'R A C E S U M M A R Y'," & Chr(10)
& _
"This will [CLEAR] any changes you've made to this currently loaded:
" _
& Range("G1").Value & " for " & Range("E1").Value & " Worksheet", _
Buttons:=vbOKCancel)
If x = vbCancel Then
Range("N3").Select
Exit Sub
End If
End If

...code

End Sub

 

[CRayF]

So would these variables be a GOOD example as ones that could be set as Global:
They are used by many of the Sub() and I don’t see if efficient to declare
them all over the place. They will always be the same and easier to define in
one location?

Dim srcProgramDataInputWs As Worksheet
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

and if so then I define them as

Public SrcProgramDataInputWs as Worksheet

And can I assign them a value at the same time?

 

[CRayF]

In this example:
Sub ReBuildProgramSummary(Optional Confirm As Boolean = True)
Dim srcProgramDataInputWs As Worksheet
Set srcProgramSummaryTemplateWs = Sheets("@TemplateProgramSummary")
Call MyMacro(srcProgramDataInputWs)
End Sub

Sub MyMacro(srcProgramDataInputWs as Worksheet)
'code
end sub

Are the names assigned to the receiving sub routine in the order they are
given? (or is I had written Sub MyMacro(srcXXXDataInputWs as Worksheet)
Would that inherit the value of sending calls firs tparm
(srcProgramDataInputWs) or would this fail?

 

[Rowan]

The receiving routine assigns the arguments in the order they are give,
try this example.

Sub ReBuildProgramSummary()
Dim srcProgramDataInputWs As Worksheet
Dim srcProgramSummaryWs As Worksheet
Set srcProgramSummaryWs = Sheets("ProgramSummary")
Set srcProgramDataInputWs = Sheets("ProgramDataInput")
Call MyMacro(srcProgramDataInputWs, "other val", srcProgramSummaryWs)
End Sub

Sub MyMacro(InptSht As Worksheet, Val1 As String, SummSht As Worksheet)
MsgBox InptSht.Name
MsgBox Val1
MsgBox SummSht.Name
End Sub

Regards
Rowan

 

[Chip Pearson]

You can't assign a value to a public variable outside of a
procedure. You'd need an initialization procedure to assign
initial values or (for object variables) test whether they are
Nothing before using them in code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

and just to add - if it were appropriate to assign a value in the
declaration and that value would not change, then maybe what you actually
want is a public Constant.

 

[CRayF]

Kewl... So far so good, lastly, where can I find a listing of how I sould
declare my variables?

I have no idea how to choose... So far I've seen As worksheet, As String,
As Boolean, As Variant, As Integer, etc...

 

[Rowan]

Search for "Data Type Summary" in the excel VBA help. This shows the
storage size and allowable range for each data type.

With object variables it can get a bit trickier to know the correct
declaration. While working it out you could do a test by declaring your
variable as a variant. The first time you use the variable excel will
assign it to the correct object and then you can use the TypeName
function to find out what that is. You can then fix your declaration.

Sub tester()
Dim mysht As Variant
Set mysht = ActiveSheet
MsgBox TypeName(mysht)
End Sub

Regards
Rowan

 

[CRayF]

Very nice. Thank you.

Search for "Data Type Summary" in the excel VBA help. This shows the
storage size and allowable range for each data type.

With object variables it can get a bit trickier to know the correct
declaration. While working it out you could do a test by declaring your
variable as a variant. The first time you use the variable excel will
assign it to the correct object and then you can use the TypeName
function to find out what that is. You can then fix your declaration.

Sub tester()
Dim mysht As Variant
Set mysht = ActiveSheet
MsgBox TypeName(mysht)
End Sub

Regards
Rowan

 

[CRayF]

So, I have a few variables that will not dynamically change and may be used
in many places. So I think this seems appropriate for a Global Variable. Is
this what’s recommended?

public srcProgramDataInputWs As Constant
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

If I were to place these in one location, where is the best place for this?

Would I pick my Module3 where I’ve already placed a few sub routines? And if
so, simply place them above the SUB() statement? Or will I need to place them
somewhere special to get initialized to be used by all?
I’m not too familiar with the Project Menu yet…

 

[Tom Ogilvy]

A constant isn't a variable.

See Declaring Constants in VBA help


Public Const conAge As Integer = 34

 

[CRayF]

I've have a better understanding of the variables now but I'm still not
understanding where they go?
I have 3 variables that look like this in any modules:

Dim srcProgramDataInputWs As Worksheet
Set srcProgramDataInputWs = Sheets("ProgramDataInput")

I would like to place/maintain them in one place that as soon as the
Workbook is loaded they are populated. Then I'd like to remove all "Dim" AND
"SET" commands from all the separate modules but have the variable available.

What should the code look like for this one variable and it's set command,
AND what module is there a "best" module this should go in?