VBA Static Variable in Excel — Memory That Survives End Sub (and Remembers Too Much)

TL;DR — Write Static instead of Dim inside a procedure and the variable keeps its value when the procedure ends: Static clicks As Long counts every call, not just this one. It is a private memory: nobody outside the procedure can see it, but it lives as long as a global does, until the VBA project resets. That is its strength and its trap. A Static never forgets on its own, so it also remembers a flag that got stuck when an error skipped the line that cleared it, and a cache built from data that has since changed. Use Static when one procedure owns the memory; the moment another procedure needs to read or reset it, move it to a module-level Private.

Sub CountClicks()
    Static clicks As Long            ' kept between calls
    clicks = clicks + 1
    Application.StatusBar = "Clicked " & clicks & " times"
End Sub

This is the second part of a three-part cluster on where a value lives. A global variable is visible to the whole project and lives until the project resets. A Static variable keeps that lifetime but is visible to one procedure only. An Optional parameter lasts a single call. The rule across all three: pick the narrowest scope that does the job.

What you'll learn

The mental model: Static is local scope with module lifetime
Static vs Dim vs a module-level Private, side by side
Four jobs Static does well: counters, toggles, re-entry guards, caches
The two ways a Static remembers too much: stuck flags and stale caches
Why every cell that calls a worksheet function shares one Static
Static Sub, and when to promote a Static out of its procedure

The mental model: local scope, module lifetime

A Dim inside a procedure is a scratch pad: it is created when the procedure starts and thrown away when it ends, so the next call starts from zero. A Static inside a procedure is a drawer that only this procedure has a key to. The procedure can leave something in it, end, and find it there on the next call.

Declaration	Who can see it	How long it keeps its value
`Dim n As Long` inside a procedure	that procedure	until the procedure ends
`Static n As Long` inside a procedure	that procedure	until the project resets
`Private n As Long` at the top of a module	every procedure in the module	until the project resets
`Public n As Long` at the top of a standard module	the whole project	until the project resets

The second row is the only one that combines a narrow scope with a long life. "Until the project resets" means the same thing it does for a global: End, the Reset button, an unhandled error the user ends, some code edits, or closing the workbook, all covered in the global variable guide.

Declaring a Static, and the first-call value

Static is only allowed inside a procedure, in place of Dim:

Function NextInvoiceNo() As Long
    Static lastNo As Long            ' 0 on the very first call
    lastNo = lastNo + 1
    NextInvoiceNo = lastNo
End Function

VBA has no initializer, so Static lastNo As Long = 1000 is a syntax error. On the first call a Static holds the empty value for its type, 0, "", Empty or Nothing, and you set a real starting value yourself. When 0 could be a real value, track the first call with a second Static:

Function NextInvoiceNo() As Long
    Static lastNo As Long, started As Boolean
    If Not started Then
        lastNo = ThisWorkbook.Worksheets("Settings").Range("B3").Value
        started = True
    End If
    lastNo = lastNo + 1
    NextInvoiceNo = lastNo
End Function

Notice what that version admits: the Static is a fast copy, and the real number lives in the workbook. A counter that must survive closing the file has to be written back there, because no variable outlives the project.

Four jobs Static does well

A toggle. One button that switches something on and off needs to know its last state:

Sub ToggleGridlines()
    Static isOff As Boolean
    isOff = Not isOff
    ActiveWindow.DisplayGridlines = Not isOff
End Sub

A counter or a running total that belongs to one routine, like the invoice number above.

A re-entry guard for an event handler that changes the sheet it is watching. Writing a cell inside Worksheet_Change fires Worksheet_Change again; a Static flag stops the loop:

Private Sub Worksheet_Change(ByVal Target As Range)
    Static busy As Boolean
    If busy Then Exit Sub
    busy = True
    On Error GoTo Done
    Target.Offset(0, 1).Value = Now  ' would re-trigger this event
Done:
    busy = False
End Sub

A cache for a lookup that is slow to build and needed by one procedure only:

Function RegionOf(ByVal code As String) As String
    Static map As Object
    If map Is Nothing Then
        Dim r As Range
        Set map = CreateObject("Scripting.Dictionary")
        For Each r In ThisWorkbook.Worksheets("Regions").Range("A2:A500")
            map(r.Value) = r.Offset(0, 1).Value
        Next r
    End If
    If map.Exists(code) Then RegionOf = map(code)
End Function

The If map Is Nothing test does double duty: it builds the cache on the first call, and rebuilds it after a project reset has cleared it. That is the self-healing pattern from the global variable guide, with the cache kept private to the one function that uses it.

The rule that bites: a Static never forgets on its own

Every Static bug is the same bug: the value outlived the situation it was valid for.

The stuck flag. Take the re-entry guard above and delete the On Error GoTo Done line. Now any error after busy = True, a protected cell or a type mismatch, ends the handler before busy = False runs. The flag stays True, and from then on the first line exits every time: the event handler is silently dead until the project resets. The user sees a sheet that stopped updating, with no error message. A guard flag must be cleared on every exit path, which is why the error handler jumps to the line that clears it. The EnableEvents guide covers the other common guard, with the same rule.

The stale cache. RegionOf builds its map once. If someone adds a region to the sheet afterwards, the function keeps answering from the old map, all day, because nothing tells it the data changed. And nothing outside the function can tell it, because the Static is invisible from outside. That is the real limit of Static: you cannot reset it from another procedure.

The UDF surprise: every cell shares one Static

A Static belongs to the procedure, not to the place it was called from. For a worksheet function that means every cell that calls it shares one variable:

Function CallCount() As Long
    Static n As Long
    n = n + 1
    CallCount = n
End Function
' =CallCount() in A1, A2 and A3 does not give 1, 1, 1 -
' it gives three different numbers, in calculation order

Excel decides the calculation order, and it can recalculate any cell at any time, so a UDF whose answer depends on a Static gives results that change on their own. The Function guide explains why a worksheet function should depend only on its arguments. A Static cache inside a UDF, like the RegionOf map, is fine, because it changes how fast the answer comes back and not what the answer is. A Static counter or running total inside a UDF is a bug.

Static Sub, and Static in a UserForm

Putting Static before the procedure makes every local variable in it static:

Static Sub Tally()
    Dim total As Double              ' static too, because of Static Sub
    total = total + 1
End Sub

It exists, but avoid it. A reader sees Dim and assumes a fresh variable, and the one keyword at the top that says otherwise is easy to miss. Mark the individual variables that need memory.

Static variables in a UserForm's procedures last only as long as the form stays loaded. After Unload, the next Show starts with fresh values, the same lifetime as the form's other variables in the UserForm guide.

The judgment call: Static until a second procedure needs it

Static is the narrowest way to give a procedure memory, and narrow is good: nothing else can change the value by accident, and the declaration sits right next to the code that uses it. My rule: use Static when exactly one procedure reads and writes the memory, and nothing else ever needs to clear it. As soon as a second procedure has to see it, or you need a "refresh" button that empties a cache, move the variable to the top of the module as Private, and add a small procedure that resets it:

Private mMap As Object

Sub ResetRegionCache()
    Set mMap = Nothing               ' the next lookup rebuilds it
End Sub

That keeps the scope as narrow as the new need allows, and avoids the jump straight to a Public global.

How ExcelMaster helps

Static bugs look like ghosts: an event handler that stops firing for no visible reason, a lookup that ignores the row you just added, a UDF whose numbers shift each time the sheet recalculates.

ExcelMaster lets you describe what the macro should remember, such as "number each new invoice from the last one used". It chooses the right home for that memory, a Static, a module-level variable with a reset, or a cell in the workbook, and adds the error handling that keeps a guard flag from getting stuck.

Frequently asked questions

What does Static mean in VBA?

Static declares a variable inside a procedure that keeps its value after the procedure ends. The next call sees the value the last call left. Only that procedure can see the variable, and the value is lost when the VBA project resets or the workbook closes.

What is the difference between Static and Dim in VBA?

A Dim variable inside a procedure starts empty on every call and disappears when the procedure ends. A Static variable starts empty only on the first call and keeps its value between calls. Both are visible only inside their procedure.

How do I keep a variable's value between macro runs?

Declare it with Static inside the procedure if only that procedure needs it, or as Private at the top of the module if several procedures do. Both keep the value until the project resets. To keep it after the workbook closes, store it in a cell or a defined name.

Can I give a Static variable a starting value?

Not in the declaration: VBA has no initializers, so Static n As Long = 5 is a syntax error. The variable starts as 0, "", Empty or Nothing. Set a starting value on the first call, using a second Static Boolean to know whether the first call has happened.

How do I reset a Static variable?

Only the procedure that declares it can change it, so give that procedure a way to reset it, or move the variable to module level as Private and add a small reset procedure. Resetting the whole VBA project also clears it, along with every other module-level and public variable.

Tested in

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