Usually, the global variable loses its value after restarting the Excel Workbook. As a result, the users have to reconstruct the variable.
The entire process can consume a ton of time and lead to ruthless suffering. Fortunately, there are multiple methods to resolve the issue and save the variable’s value for a lifetime.
In this article, I will explain how and why the global variables lose their value and how you can solve this annoying issue.
Without further discussion, let’s begin!
How the VBA Global Variable Loses Value in Excel
In Visual Basic for Application (VBA), a global variable loses value when you close the Excel Workbook or shut down the Excel app.
Consider the following process to better understand how the global variable loses its value.
- Launch Microsoft Excel on your PC.
- Open VBA by navigating to Developer >> Visual Basic. If the Developer option is missing, navigate to File > Options > Customize Ribbon, check the Developer option, and click OK.
- Bring up a new VBA Module by clicking on Insert >> Module.
- Copy and paste the following code in the new Module.
Public mn_globalVar As Integer
Sub SetValueOfVariable()
mn_globalVar = 20
End Sub
Sub GlobalVariableValue()
MsgBox "The value of the variable is: " & mn_globalVar
End Sub- Keep the mouse cursor in the first Sub and click the Run button.
- Place the mouse cursor in the second Sub and click Run.
You will get a popup message saying the variable value is 20. However, if you exit the Workbook and relaunch it, you will notice that the variable value is 0, meaning the value is lost.
Why Does the Global Variable Lose Value in Excel?
The above discussion clarifies how the global variable loses its value for closing the Excel Workbook. Besides, some other causes lead to the same issue.
The following are the possible causes for VBA Global Variable’s value loss:
| Reasons | Descriptions |
|---|---|
| An End Statement is Terminating the Process | When an unnecessary End statement halts the runtime process, the variables may lose their value. Moreover, if you assign the value using one module and access it via another, you will lose it. |
| Closing the Excel Workbook | If you close the Workbook, the variables will lose their value. Moreover, the variable loses its scope (the variable's lifetime in the program) and value when all code execution is completed. |
| Error in the Code | Another common reason for this issue is an error in the code. For instance, if the variable code is not declared with Public, it will lose the value. You must declare it outside the Subroutine. Similarly, VBA can reset the value when the lifetime of a variable has expired. Primarily, the static variables can hold the value where the non-static variable values are destroyed after running the code. |
Now that you know the details and possible causes of the missing value issue, move down to the next section to resolve the problem.
How to Fix VBA Global Variable Loses Value in Excel
To fix the VBA global variables losing value issue of MS Excel, you can save the value to another Excel Sheet or text document. If you store the value in Sheet2 or a text file using the Module, you won’t lose the value even after relaunching the Excel Workbook.
When you add the variable’s value in a form module, you may lose the value when UNLOAD the form. In such scenarios, you should replace the Unload Me with Me.Hide.
It will store the value for a lifetime. However, If it doesn’t help, consider the following methods to save the variable’s value from losing.
Here are the methods to fix the value loss issue of VBA global variables in MS Excel:
1. Save the Value in a Text File
To resolve the Public variables losing their values in Microsoft Excel, you can store the value in a .txt file and recall it when necessary.
To do so, navigate to Insert >> Module and paste the following code into the VBA Module.
Public mn_globalVar As Integer
Sub ModifyAndSaveGlobalVariable()
' Modify the global variable
mn_globalVar = 20
' Save the global variable to a text file
Dim mn_fso As Object
Set mn_fso = CreateObject("Scripting.FileSystemObject")
Dim filePath As String
filePath = ThisWorkbook.Path & "\globalVarValue.txt"
Dim file As Object
Set file = mn_fso.CreateTextFile(filePath)
file.Write mn_globalVar
file.Close
MsgBox "Global variable modified and saved."
End Sub
Sub RetrieveGlobalVariableFromFile()
' Retrieve the global variable from the text file
Dim mn_fso As Object
Set mn_fso = CreateObject("Scripting.FileSystemObject")
Dim filePath As String
filePath = ThisWorkbook.Path & "\globalVarValue.txt"
If mn_fso.FileExists(filePath) Then
' Open the text file and read the value
Dim file As Object
Set file = mn_fso.OpenTextFile(filePath)
Dim valueFromFile As Integer
valueFromFile = file.ReadLine
file.Close
' Update the global variable with the retrieved value
mn_globalVar = valueFromFile
MsgBox "Global variable retrieved from the file."
Else
MsgBox "Value file not found."
End If
MsgBox "The value of the variable is: " & mn_globalVar
End SubPlace your mouse cursor into the first Subroutine (ModifyAndSaveGlobalVariable) and click Run. It will modify the variable and save the value to a text file to a similar address as the Excel Workbook.
Now place the cursor into the second Subroutine (RetrieveGlobalVariableFromFile) and click Run. It will recall the global variable’s value and display it with a popup message.
2. Store the Value in Another Sheet
Alternatively, you can store the value in another Excel Sheet and access the value whenever you need.
To do so, open the VBA Module by performing the above steps and paste the following code.
Public mn_globalVar As Integer
Sub StoringValueToSheet()
' Modify the global variable
mn_globalVar = 20
' Store the global variable in a worksheet cell
ThisWorkbook.Worksheets("Sheet2").Range("A1").Value = mn_globalVar
MsgBox "Value stored in Sheet2 A1."
End Sub
Sub ExtractingValueFromSheet()
' Extract the global variable from the worksheet cell
mn_globalVar = ThisWorkbook.Worksheets("Sheet2").Range("A1").Value
MsgBox "The value of the Variable is: " & mn_globalVar
End SubSimilar to the first method, run the StoringValueToSheet Subroutine first. It will store the value in Sheet2 and cell A1.
Place the cursor on the second Subroutine to recall the variable value and click Run. It will bring up the value from Sheet2 and show it with a popup window.
Wrap Up
The VBA Public variable loses its value for multiple reasons, such as closing the Workbook, an error in the code, or an irrelevant End Statement.
To resolve the issue, store the variable’s value in another Excel Sheet or a text document. Also, ensure no unnecessary End Statement is terminating the code execution.
If you have further queries regarding this topic, leave a comment below.
