VBA · Variables
Variable scope
Dim inside a procedure is local; Private or Public at the top of a module is module-level or global.
When to use it
Where a variable is visible. Dim inside a procedure is local to it; Private at the top of a module is shared by that module; Public at the top of a standard module is global.
The code
- Code
Private counter As Long ' module level Public Const APP As String = "Model"
Worked examples
Local
Sub A() Dim x As Long ' only in A End Sub → x exists while A runs
The default.
Module level
Private counter As Long ' top of module → Shared by every procedure in the module
Module state.
Global
Public Const APP As String = "Model" → Visible from every module
Global settings.
Worth knowing
- Prefer local variables and pass values as arguments; globals make bugs hard to trace.
- Module-level variables keep their value until the workbook closes or code is reset.
- Public variables in a sheet module are accessed as Sheet1.name.
Where it goes wrong
- Two modules with the same Public name conflict.
- Pressing Stop or editing code resets module-level variables.
Related
Learn the moves here — or let Wauvel run them on your numbers.
$99/mo, everything included. Free for 14 days, no card.
One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.