Excel VBA Global variable

Question

Can variable declared in the Private Sub Workbook_Open of the ThisWorkbook Excel Object be accessed by another method in another module? I want to declare and assign a variable at the start of my code that can be changed by any module using it. This change should be reflected in the variable when the next method calls it.

I have a sub in a module that assigns value to the public variable. I require this value set by module1 to be accessible to that of module2

Answer 1

A global variable needs to have Public accessibility, and be declared at module-scope in a standard module (.bas).

Option Explicit
Public Foo As Long ' global variable

The problem with global variables is that they can be read and written to by anything anywhere in the code: global state easily leads to unmaintainable spaghetti code and should be avoided whenever possible.

There are a number of alternatives, notably using parameters:

Option Explicit

Public Sub SomeEntryPoint()
    Dim foo As Long ' local variable
    DoSomething foo
    MsgBox foo 'prints 42
End Sub

'this procedure could be in any module, public.
Private Sub DoSomething(ByRef foo As Long)
    foo = 42 'byref assignment; caller will receive the updated value
End Sub

Another alternative, if the variable needs to be written by the module that declares it, but needs to be read from somewhere else, is to use properties:

Option Explicit
Private foo As Long ' private field

Public Sub DoSomething()
    'do stuff...
    foo = 42
    '...
End Sub

Public Property Get SomeFoo() As Long
    SomeFoo = foo
End Property

Now code in that module can write to foo as needed, and other modules can only read foo through the SomeFoo property - assuming the field and property are defined in Module1:

Debug.Print Module1.SomeFoo 'gets the value of the encapsulated private field