4

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
Improve this question
4
  • 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? Commented Jul 15, 2019 at 6:28
  • You Code looks Correct. I tried and I can access the variables in other Modules. Check if they are assigned the Values correctly. Commented Jul 15, 2019 at 6:51
  • 1
    DeclareGlobalVariables should be a sub and not function, you also need to execute it, in order to initialize the global variables. Commented Jul 15, 2019 at 7:11
  • 1
    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. Commented Jul 15, 2019 at 7:21

1 Answer 1

Reset to default
5

  1. Make sure you put your golobal variable in a module and not in worksheet scope, to make it accessible in other modules.

  2. Your Function should be a Sub because it does not return anything.

  3. 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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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?
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.
@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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.