VBA Global Variable in Excel — Public Variables, Scope, and Why They Reset

TL;DR — Put Public gTaxRate As Double at the top of a standard module, above every procedure, and every procedure in the workbook can read and change it. That is the easy half. The hard half is that a global remembers its value only as long as the VBA project stays loaded, and the project is reset more often than people expect: an End statement, the Reset button, an unhandled error the user ends, an edit in the VBA editor, closing the workbook. After a reset every global is back to 0, "" or Nothing, with no warning. So never read a global you have not just checked: reach it through a function that rebuilds it when it is empty, and store anything that must outlive the session in the workbook itself.

' Module1 - at the very top, above any Sub or Function
Option Explicit
Public gTaxRate As Double            ' visible to every module in this project

Sub SetUp()
    gTaxRate = 0.19
End Sub

Sub UseIt()
    Debug.Print gTaxRate             ' 0.19 - until the project resets, then 0
End Sub

This is the first of a three-part cluster on where a value lives. Every variable has a scope, who can see it, and a lifetime, how long it remembers. A global is the widest of both: the whole project, until the project resets. A Static variable keeps the long lifetime but shrinks the scope to one procedure. An Optional parameter shrinks both to a single call, handed in by the caller. My rule for the whole cluster: pick the narrowest one that works.

What you'll learn

The mental model: scope is who can see a value, lifetime is how long it lasts
How to declare a global, and Public vs Global vs Dim at the top of a module
What resets a global, and why the value disappears without an error
The self-healing getter that makes a reset harmless
The local Dim that silently hides your global, and the ambiguous-name error
Public variables in sheet, ThisWorkbook and UserForm modules
How to keep a value after the workbook closes

The mental model: scope and lifetime are two different questions

Where you declare a variable answers two questions at once. Scope is who can see it. Lifetime is how long it keeps its value. VBA has three levels:

Declared	Keyword	Scope	Lifetime
Inside a `Sub` or `Function`	`Dim`	that procedure only	until the procedure ends
Top of a module	`Dim` or `Private`	every procedure in that module	until the project resets
Top of a standard module	`Public` (or `Global`)	every procedure in the project	until the project resets

The Dim guide covers the first row. This article is about the last one, and the key fact is in the last column: module-level and public variables do not live until you close Excel, and they do not die when the procedure that set them ends. They live exactly as long as the project's state, and that state is more fragile than it looks.

Declaring a global: Public, Global, and where the line goes

A global must sit in the declarations section of a standard module: after Option Explicit, before the first procedure. Put the same line inside a Sub and VBA refuses it, because Public is not allowed inside a procedure.

Option Explicit

Public gUserName As String           ' project-wide
Global gRunCount As Long             ' the same thing, older keyword
Private mCache As Object             ' module-wide only
Dim mLastRow As Long                 ' also module-wide only

Sub Demo()
    ' Public x As Long here would be a compile error
End Sub

Global is the older spelling of Public. It works only in standard modules and adds nothing, so use Public. At module level, Dim and Private mean the same thing; prefer Private, because it says what you mean. A prefix like g for globals and m for module-level variables costs nothing and prevents the bug in a later section.

If the value never changes, it is not a variable at all. Use Public Const VAT_RATE As Double = 0.19, which the Const guide shows cannot drift and cannot be reset.

The rule that bites: a global lives only as long as the project

Here is the failure everyone meets. A macro sets gTaxRate on the first click, a second button uses it, and it works all morning. After lunch the second button calculates with 0. Nothing raised an error. The project was reset in between, and a reset sets every module-level and public variable back to its empty value:

An End statement anywhere runs, which the End guide shows wipes all state at once.
You press Reset (the square button) in the VBA editor, or choose Run > Reset.
An unhandled run-time error appears and the user clicks End in the dialog.
You edit code in the VBA editor: changing a declaration or adding a module can force a recompile that resets the project. In break mode Excel asks first; otherwise it simply happens.
The workbook that holds the code is closed.

What does not reset it: the procedure that set the value finishing normally, an error that your own On Error handler catches, or Exit Sub. That asymmetry is why the bug looks random. It appears on the day someone hits an unhandled error, or on your own machine right after you edit the code.

The fix: reach a global through a self-healing getter

Do not try to prevent resets; you cannot. Make them harmless instead. Keep the variable Private and expose a function that rebuilds it whenever it is empty:

Option Explicit
Private mRates As Object             ' Scripting.Dictionary, built on demand

Public Function Rates() As Object
    If mRates Is Nothing Then
        Dim r As Range
        Set mRates = CreateObject("Scripting.Dictionary")
        For Each r In ThisWorkbook.Worksheets("Rates").Range("A2:A50")
            If Len(r.Value) > 0 Then mRates(r.Value) = r.Offset(0, 1).Value
        Next r
    End If
    Set Rates = mRates
End Function

Sub PriceOrder()
    Debug.Print Rates()("EUR")       ' works after any reset - the getter reloads
End Sub

Every caller asks Rates() instead of reading mRates directly. If a reset emptied it, the first caller after the reset pays the cost of reloading and nobody sees a wrong answer. The same shape works for a simple value: test whether it is 0 or "", and reload it from its source. The Dictionary guide covers the cache object itself.

The silent trap: a local Dim with the same name

The second classic bug has no reset in it at all:

' Module1
Public gTotal As Double

Sub AddSales()
    Dim gTotal As Double             ' a NEW local variable that hides the global
    gTotal = gTotal + 100            ' changes the local copy only
End Sub

Sub ShowTotal()
    MsgBox gTotal                    ' still 0
End Sub

A procedure-level Dim with the same name as a global shadows it: inside AddSales, the name gTotal means the local one. VBA accepts this without complaint, and Option Explicit cannot help, because both variables are declared. The fix is the naming habit: globals start with g, and you never Dim a g name inside a procedure.

The opposite collision does raise an error. If Module1 and Module2 both declare Public Total, a third module that writes Total gets Ambiguous name detected at compile time. Qualify it as Module1.Total, or better, keep one owner for each global.

Public in a sheet, ThisWorkbook or UserForm module

Public at the top of a sheet module, ThisWorkbook or a UserForm does not create a global. It creates a property of that object, so other modules must name the object to reach it:

' In the Sheet1 code module
Public Threshold As Double

' In Module1
Sub Check()
    Sheet1.Threshold = 500           ' qualified - this works
    ' Threshold = 500                ' Variable not defined (with Option Explicit)
End Sub

These modules are object modules, so a few things are not allowed as Public members there: arrays, constants, fixed-length strings and user-defined types all raise a compile error. A UserForm adds a lifetime of its own: UserForm1.SomeValue is lost the moment the form is unloaded, which the UserForm guide shows with Unload Me vs Me.Hide. For a true project-wide variable, use a standard module.

Keeping a value after the workbook closes

No variable survives closing the workbook. If a value must be there next week, the last run date, a user choice, a counter, write it into the file:

Sub RememberLastRun()
    ThisWorkbook.Worksheets("Settings").Range("B2").Value = Now
End Sub

Function LastRun() As Date
    LastRun = ThisWorkbook.Worksheets("Settings").Range("B2").Value
End Function

A settings sheet set to very hidden, as in the Hide Sheet guide, keeps it out of the way. A hidden named range works too. Either way, the file is the memory and the variable is just a fast copy of it, which is the same idea as the getter above.

The judgment call: globals for settings and caches, arguments for data

Globals are not evil; they are just shared, and shared state is hard to reason about. Any procedure can change a global, so when it holds the wrong value you have to search the whole project for who wrote it. My rule: use a global for things the whole project reads and almost nothing writes, such as settings loaded once and caches behind a getter. When one procedure needs to hand data to another, pass it as an argument, with ByVal or ByRef chosen on purpose. If only one procedure needs to remember something between calls, a Static variable gives it that memory without sharing it.

How ExcelMaster helps

Global-variable bugs rarely look like global-variable bugs. They look like a button that worked this morning and now calculates with zero, or a total that never changes because a local copy took the update.

ExcelMaster lets you describe what the macros should do, such as "load the rates once and use them in every pricing macro". It writes the shared state behind a getter that survives resets, keeps data moving through arguments, and finds the local Dim that is hiding a global in existing code.

Frequently asked questions

How do I declare a global variable in VBA?

Write Public followed by the name and type, such as Public gTaxRate As Double, at the top of a standard module, before the first Sub or Function. Every procedure in the project can then read and change it. Declaring it inside a procedure is a compile error.

What is the difference between Public and Global in VBA?

Nothing that matters. Global is the older keyword and works only in standard modules; Public does the same job and also works in class, sheet and UserForm modules. Use Public.

Why does my VBA global variable lose its value?

The VBA project was reset. An End statement, the Reset button in the editor, an unhandled error where the user clicks End, some code edits, and closing the workbook all clear every module-level and public variable. Reach the value through a function that reloads it when it is empty.

How do I keep a variable's value after closing the workbook?

Store it in the workbook, not in a variable: a cell on a hidden settings sheet, a hidden defined name, or a document property. Read it back when the workbook opens, for example in the Workbook_Open event.

Can a UserForm use a global variable?

Yes. Declare the variable as Public in a standard module and the form's code can read and change it. A Public variable declared inside the UserForm's own module belongs to the form and is lost when the form is unloaded.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-10-01.