Initializing a static variable in VBA with a non-standard value

Question

Static variables in VBA are quite simple:

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

(when called several times):

 1 2 3 ... 

The problem is that VBA does not support initializing a variable on the same line as the declaration (not counting with : 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 is a collision 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 is happening. The very first thing that a variable does each time foo called returns to 5. Exit:

 6 6 6 ... 

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

Answer 1

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

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

A safer alternative might 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 

Perhaps you can also create a class with a default property and use class_initialize , but this is probably a bit overloaded.

Answer 2

I had the same problem in VB6 where it is exactly the same and I like the Microsoft recommendation more:

 Sub initstatic () Static Dummy, V, I As Integer, S As String ' The code in the following if statement will be executed only once: If IsEmpty(Dummy) Then ' Initialize the dummy variant, so that IsEmpty returns FALSE for ' subsequent calls. Dummy = 0 ' Initialize the other static variables. V = "Great" I = 7 S = "Visual Basic" End If Print "V="; V, "I="; I, "S="; S ' Static variables will retain their values over calls when changed. V = 7 I = I + 7 S = Right$(S, 5) End Sub