Initialising global variables in VBA

Question

In Excel 2003, how can I declare global variables and initialise them only once, ie when the workbook is opened?

I have some parameters that are used by a few macros: paths to input files, basically. At the moment, my code looks like this:

global path1, path2 as string

sub initPaths
    path1 = 'path\to\file1'
    path2 = 'path\to\file2'
end sub

Then, whenever I need to use file1 or file2 in a subroutine or function, I insert a call to initPaths. But this seems rather inelegant; I'd like to be able to set the paths only once rather than repeatedly.

Answer 1

From your example, it looks like what you want are constants, not global variables.

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

If you really do need to use code to determine the values, but only 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

The nice thing here is that if your code ever gets reset, the values simply get re-initialized again next time you access them via their respective functions.

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