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

Building on the answer of @David, this is how to use Dim and Public and their differences (in a Modul, named Modul1 write the following and run TestMe):

Dim a           As String
Public b        As String
Private c       As String
Global d        As String

Private Sub TestA()
'Whatever is not declared in TestMe, would take its value from here for the print.
'^-If it is declared, the value would be attached to the public/private/dim/glb above.    
    a = 11
    b = 22
    c = 33
    d = 44        
End Sub

Private Sub TestMe()

    Dim a       As String
    'Dim b       As String
    'Dim c       As String
    Dim d       As String

    a = 1
    b = 2
    c = 3
    d = 4

    TestA

    Debug.Print a; vbTab; Modul1.a
    Debug.Print "----------------"
    Debug.Print b; vbTab; Modul1.b
    Debug.Print "----------------"
    Debug.Print c; vbTab; Modul1.c
    Debug.Print "----------------"
    Debug.Print d; vbTab; Modul1.d

End Sub

This is what you get:

1    11
----------------
22    22
----------------
33    33
----------------
4    44

Answer 2

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

Answer 3

No - the variable should be declared as Public in a normal module.

Answer 4

It can't be called out, as you are calling a Private Sub.

In order to have variables which can be changed Publically, try outside of any Sub:

Public wBk As Workbook 
Public var As String

This would declare the variables. To modify the values, you need to set them in a public Subroutine:

Public Sub myPublicVar()
Set wBk = Workbooks("Workbook1.xlsm")
Set var = "Whatever you like"
End Sub

If then you want your module, or subroutine to include the variables, you would do the following

Sub myOtherSub()
Call myPublicVar
MsgBox var
End Sub

Hope this helps!

Answer 5

' Public variable
Public a as String

' Local variable
Public sub hello()
    Dim a as String
End sub

The first a is public and you can use where you want and the second variable is local and the only site you can use is into the function hello(). So both variable are diferent.