In Excel VBA, a global variable is a value that procedures throughout the same VBA project can access. Declare it with Public at the top of a standard module, outside any Sub or Function; for example, Public RunCount As Long. Use global variables for shared, temporary macro state—not as a substitute for worksheet data or for values that must survive closing the workbook.
What “global” means in Excel VBA
VBA variables have scope, which determines where code can refer to them, and lifetime, which determines how long their stored value remains available. These are related but different ideas. A variable can be visible to several procedures without being permanent, and a variable hidden inside one procedure can still retain its value between calls if it is declared with Static.
In VBA, “global variable” commonly refers to a variable declared Public in a standard module. Its scope is the VBA project: code in other standard modules and workbook or worksheet modules in that same project can refer to it by name. It is not automatically shared with every open workbook, another VBA project, or an Excel formula. A variable declared in one workbook’s project does not become a universal Excel-wide setting.
VBA also accepts the older Global declaration in certain contexts, but Public is the usual, clearer choice for new code. It explicitly communicates that the declaration is accessible outside its module.
How to declare and use one
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, then put declarations at the top of the new module, before any procedures. On a Mac, open the editor from Developer > Visual Basic; the keyboard shortcut can vary with the keyboard and function-key settings. The VBA editor also has an Insert > Module command.
This example keeps a count and a short status message available to procedures in the same workbook project:
Option Explicit
' Public declarations belong at the top of a standard module.
Public RunCount As Long
Public StatusMessage As String
Public Sub StartSession()
RunCount = 0
StatusMessage = "Ready"
End Sub
Public Sub RecordRun()
RunCount = RunCount + 1
StatusMessage = "Runs: " & CStr(RunCount)
End Sub
Public Function GetRunCount() As Long
GetRunCount = RunCount
End FunctionRun StartSession once to initialize the values, then run RecordRun to increase the count. Another procedure in the project can read RunCount or StatusMessage directly, or call GetRunCount. If a macro relies on a global value, make sure the code that sets it runs first: a newly created numeric variable initially contains zero, while a newly created string variable contains an empty string.
Save a macro-enabled workbook as .xlsm if it needs to retain its VBA code. Save a copy before running a macro that changes workbook contents; VBA macros cannot be undone with Ctrl+Z on Windows (or Command+Z on Mac). This sample only changes variables, but the same caution matters when adapting it to edit cells or files.
Choosing between Public, Private, Dim, and Static
The declaration keyword and where it appears determine the access level. Choose the narrowest scope that lets the required code work; fewer widely accessible variables usually make a project easier to understand and maintain.
Diminside a Sub or Function creates a local variable. Only that procedure can use it, and its value normally lasts only while that procedure is running.Staticinside a procedure creates a local variable whose value is retained between calls while the VBA project remains running. Other procedures still cannot refer to it directly.Privateat the top of a standard module makes a module-level variable. Procedures in that module can use it, but procedures in other modules cannot access it by its variable name.Publicat the top of a standard module makes a project-level variable available to code elsewhere in the same VBA project.
For example, if a helper procedure and its callers all live in one module, a module-level Private variable may be sufficient. Making it Public just in case gives the whole project access without providing a benefit. A procedure’s local variables are usually the best default for values needed only during one calculation.
Where declarations belong—and where they do not
For a project-wide variable, place the declaration in a standard module, not inside a Sub or Function. A declaration placed inside a procedure is local to that procedure. Declarations at the top of a worksheet, ThisWorkbook, or class module have different module and object context; they are not the straightforward project-wide variable pattern described above. In class and object modules, public members belong to an instance of that class or object, so using them generally involves an object reference.
Keep module-level declarations in the declarations section, before the first procedure. Use Option Explicit at the top of each module to require variables to be declared. It helps catch misspellings that would otherwise create unintended variables, a particularly useful safeguard when shared variables are referenced across multiple modules.
VBA is not case-sensitive, but consistent names help readers distinguish shared state from local values. A descriptive name such as CurrentImportRow is more informative than a generic name such as x. Add a brief comment when the variable’s purpose or initialization timing might not be obvious.
How long a global variable lasts
A public variable is stored in memory while the VBA project is running; it is not written into the workbook merely because it is public. Its value is generally available to later macro calls until the project is reset or the workbook closes. Closing and reopening a workbook starts with fresh in-memory variable values, so an initialized value such as RunCount returns to its default unless code sets it again.
Values can also be cleared when the VBA project resets. For example, stopping or resetting code in the Visual Basic Editor, certain unhandled errors, or using VBA’s End statement can reset execution state and module-level variables. The exact event depends on what happened, so code should not assume that a global value is safely preserved after an error or manual reset. Avoid using End as routine cleanup; structured exits and error handling make program flow more predictable.
If a value must survive closing Excel, store it somewhere persistent: for example, in a worksheet cell, a defined name, a text file, or another suitable data store. A global variable is useful for temporary state during a session, not as durable storage.
When global variables are useful—and when they cause trouble
A shared variable can be practical when several related procedures need the same temporary state, such as an import routine’s current status or a setting chosen once at the start of a multi-step macro. It can prevent passing the same value through a long chain of procedure calls. Keep the use narrow, initialize it deliberately, and document which procedure sets it.
The trade-off is hidden dependency. A procedure that reads RunCount does not reveal from its argument list that it depends on another procedure having initialized that variable. If macros run in an unexpected order, or an error interrupts the setup, the procedure may behave incorrectly. Shared state also makes testing and debugging harder because the result can depend on what ran earlier.
Common safeguards include:
- Initialize shared variables in a clearly named startup or setup procedure.
- Use a local variable when the value is only needed in one procedure.
- Pass a value as a procedure argument when that makes the dependency clearer.
- Use a constant for a value that should not change during execution.
- Check that a value is initialized before using it, especially after error handling or a project reset.
Alternatives for settings, worksheet values, and functions
For an unchanging value, a constant is clearer than a variable. Declare a constant in a standard module with Public Const if code throughout the project needs it, or Private Const if it is used only in that module. A constant cannot be reassigned while the code runs, which prevents accidental changes to values such as a fixed label or a defined conversion factor.
For data a user should be able to inspect or edit, use worksheet cells or a defined name rather than relying only on memory. That makes the value visible in the workbook and allows it to be saved with the file. A global variable can temporarily hold a value read from a cell, but it does not automatically stay synchronized if the cell changes.
For a calculation, passing inputs to a Function and returning a result is often easier to reason about than reading or changing global state. Excel worksheet formulas cannot refer directly to a VBA variable. Although a VBA user-defined function can be used in a worksheet when macros are enabled, using hidden global state as an input to that function is unreliable: Excel’s calculation engine may not know that the state changed and therefore may not recalculate the cell when expected.
Common problems and practical checks
If VBA reports “Variable not defined,” check spelling, whether the declaration is in the correct module, and whether a Private declaration is being referenced from outside its module. If code compiles but produces an unexpected value, verify that the initialization procedure ran and that no project reset cleared the value. Adding Option Explicit and compiling the project can help find declaration and naming mistakes.
If a macro works in one workbook but not another, check which VBA project contains the declaration. Public variables are scoped to their project, not to every workbook open in Excel. To share information across workbook projects, use an explicit design such as passing arguments through an exposed procedure or storing the value in a suitable shared location; do not assume identical public variable names refer to the same memory.
Macros also need to be permitted to run. If a workbook opens with its macros disabled, its VBA procedures will not run as expected. Follow your organization’s macro-security policies rather than lowering security settings broadly. A global variable does not bypass macro security or make a workbook’s code run automatically.
Frequently Asked Questions
How do I make a variable global in Excel VBA?
In a standard module, declare it outside all procedures with Public, followed by its name and data type—for example, Public CurrentRow As Long. Then code elsewhere in the same VBA project can refer to it.
Can a global VBA variable be used in every open workbook?
No. A public variable belongs to the VBA project where it is declared. Another workbook has a separate project, so sharing a value requires an explicit method such as passing it through code or storing it in a shared location.
Does a global variable keep its value after I close Excel?
No. A public variable is held in memory, not automatically saved into the workbook. Store the value in a worksheet or another persistent location if it must be available after closing and reopening Excel.
What is the difference between Public and Private in VBA?
A module-level Public variable can be accessed by code throughout its VBA project. A module-level Private variable is accessible only to procedures in the module where it is declared.
Can an Excel formula read a VBA global variable?
Not directly. Worksheet formulas do not reference VBA variables. A VBA user-defined function can return a value to a cell, but a function that depends on changing global state may not recalculate when that state changes.