How to set global variables in VBA

Question

I would like to define a global variable in VBA in one module and use this in other VBA modules.

I am trying to follow: How do I declare a global variable in VBA?

I have created a new module called "GlobalVariables", and first declared the Public variables and then set their value within a function (trying to do this in open code causes an error). My code is below.

But the Global variable StartYear does not seem to be available into other VBA modules. What am I doing wrong?

Option Explicit

Public StartYear As Integer
Public BaseYear As Integer

Function DeclareGlobalVariables()
    StartYear = ActiveWorkbook.Worksheets("RunModel").Range("StartYear").Value
    BaseYear = ActiveWorkbook.Worksheets("RunModel").Range("BaseYear").Value
End Function

The code seems correct to me. Do you really mean you cannot use the variable in other modules, or does it just not have the value you expect? — KekuSemau
You Code looks Correct. I tried and I can access the variables in other Modules. Check if they are assigned the Values correctly. — Mikku
DeclareGlobalVariables should be a sub and not function, you also need to execute it, in order to initialize the global variables. — h2so4
Irrelevant to you question, but don't use Integer variables. This datatype may not be able to hold numeric values you are using causing an overflow error. — JvdV

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-07-15T07:52:50

Make sure you put your golobal variable in a module and not in worksheet scope, to make it accessible in other modules.
Your Function should be a Sub because it does not return anything.
Your code will error if your cells eg. contain text (strings). Never trust a user's input. Always validate!

So I suggest the following

Module 1

Option Explicit

Public StartYear As Long
Public BaseYear As Long

Public Function InitializeGlobalVariables() As Boolean
    InitializeGlobalVariables = True
    With ActiveWorkbook.Worksheets("RunModel").Range("StartYear")
        If IsYear(.Value) Then
            StartYear = CLng(.Value)
        Else
            InitializeGlobalVariables = False
            MsgBox "StartYear needs to be a number"
        End If
    End With

    With ActiveWorkbook.Worksheets("RunModel").Range("BaseYear")
        If IsYear(.Value) Then
            BaseYear = CLng(.Value)
        Else
            InitializeGlobalVariables = False
            MsgBox "BaseYear needs to be a number"
        End If
    End With
End Function

'validate if the input value is a valid year
Private Function IsYear(ByVal InputValue As Variant) As Boolean
    If IsNumeric(InputValue) Then
        If CLng(InputValue) = InputValue And _
           InputValue > 0 And InputValue < 9999 Then 'integer not decimal AND 4 digit year
            IsYear = True
        End If
    End If
End Function

And you can access the variables in any other module like:

Module 2

Option Explicit

Public Sub TestOutput()
    'before using the variables test if they are initialized (not 0)
    If StartYear = 0 Or BaseYear = 0 Then 
        'they are not initalized so initalize them (and at the same time check if it was successful)
        If InitializeGlobalVariables = False Then 
            'the function returns FALSE if the initialization process failed so we need to cancel this procedure or we use not initilized variables!
             MsgBox "Variables were not intitialized. Trying to initialize them failed too. I cannot proceed."
             Exit Sub
        End If
    End If

    Debug.Print StartYear
    Debug.Print BaseYear
End Sub

How to set global variables in VBA

1 Answers