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, Jul 15 '19 at 06:28
You Code looks Correct. I tried and I can access the variables in other Modules. Check if they are assigned the Values correctly. — Mikku, Jul 15 '19 at 06:51
DeclareGlobalVariables should be a sub and not function, you also need to execute it, in order to initialize the global variables. — h2so4, Jul 15 '19 at 07:11
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, Jul 15 '19 at 07:21

Pᴇʜ · Accepted Answer · 2019-07-30T06:11:26.843

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

Thank you PEH, appreciate it. From testing, it appears you have to run or call the sub to initialize it. This seems a bit clunky given other languages I am used to. If I have a bunch of functions that will use StartYear and BaseYear and I don't want to keep feeding these values in as parameters, what would be the best way to initializing the variables? It seems inefficient to Call DeclareGlobalVariables() in each function. Is it best to try to call this sub when the EXCEL workbook is opened? — brb, Jul 29 '19 at 23:37
Well, every function/procedure that uses the global variables need to check if they were set. If you initialize them when the workbook is opened, they are initialized until the workbook is closed, but if anywhere anything goes wrong and the code stops unexpectedly then the global variable can end up non-initialized (beeing `0`). So you should check in every procedure/function if the variable is initialized (eg. check if it is not `0` because if you rely on it beeing initialized or beeing a valid user input then you could end up with a fail of the procedure. — Pᴇʜ, Jul 30 '19 at 06:08
@brb I changed the procedure above into a function to show what I mean. This would be a safe way to not rely on any assumptions, about the value or state of your global variables. — Pᴇʜ, Jul 30 '19 at 06:11

How to set global variables in VBA

1 Answers1