Initializing a static variable in VBA with non-default value

Question

Static variables in VBA are simple enough:

Public Sub foo()
    Static i As Integer
    i = i + 1
    Debug.Print i
End Sub

outputs (when called multiple times):

1
2
3
...

The problem is, VBA does not support initializing a variable on the same line as the declaration (not counting using : to put two lines on one):

Public Sub foo()
    Dim i As Integer = 5 'won't compile!

    Dim j As Integer
    j = 5 'we have to do this instead
End Sub

This clashes with static variables:

Public Sub foo()
    Static i As Integer 'we can't put an initial value here...
    i = 5 'so this is how we'd usually initialize it, but...
    i = i + 1
    Debug.Print i
End Sub

You can probably see what happens - The very first thing the variable does every time foo is called is set itself back to 5. Output:

6
6
6
...

How can you initialize a static variable in VBA to a value other than its default? Or is this just VBA dropping the ball?

Answer 1

One way to do this if you want to keep the static semantics and not switch to a global is to sniff the default value and then set the initial condition:

Static i As Integer
if (i = 0) then i = 5

Safer alternative would perhaps be

Static i As Variant
if isempty(i) then i = 5

Or

Public Sub foo(optional init as boolean = false)
    Static i As Integer
    if init then
      i = 5
      exit sub
    endif

You could probably also create a class with a default property and use class_initialize but that's probably a bit over-fussy.