Initializing Global Variables in VBA

Question

In Excel 2003, how can I declare global variables and initialize them only once, i.e. when is the workbook open?

I have some parameters that are used by several macros: paths to the input files, basically. At the moment, my code is as follows:

global path1, path2 as string sub initPaths path1 = 'path\to\file1' path2 = 'path\to\file2' end sub 

Then, when I need to use file1 or file2 in a routine or function, I insert an initPaths call. But it seems rather inelegant; I would like to be able to set paths only once, not several times.

Answer 1

In your example, this looks like what you want - these are constants, not global variables.

 Public Const PATH1 = "path\to\file1" Public Const PATH2 = "path\to\file2" 

If you really need to use the code to determine the values, but just want to initialize them once, you can use lazy-initialization ...

 Private mstrPath1 As String Private mstrPath2 As String Public Function Path1() As String if mstrPath1 = vbNullString Then ' Initialize mstrPath1 value here. End If Path1 = mstrPath1 End Function Public Function Path2() As String if mstrPath2 = vbNullString Then ' Initialize mstrPath2 value here. End If Path2 = mstrPath2 End Function 

It's nice that if your code ever gets reset, the values ​​are simply initialized again the next time they are accessed through their respective functions.

Note that global variables should be avoided as much as possible, and you should always choose private global variables over public variables where possible. Use global and public globals if necessary, but only where necessary.