6

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?

Improve this question
2
  • 2
    You would need to if (i = 0) then i = 5 Commented Oct 17, 2014 at 11:56
  • @AlexK. This is the closest thing to a complete answer I think. If you post it as an answer, I'll probably accept it. The only problem I can see is if a negative initial value is used, when it reaches 0 it'll bounce back to the initial again. Commented Oct 17, 2014 at 12:20

4 Answers 4

Reset to default
10

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Great answer. I mulled over the Variant bit and think I have a suggestion. An additional Static initialized as Boolean would bypass the issue I noted in the comment on the question without needing to change the type to Variant. You can use: If Not initalized Then: i = 5: initialized = True: End If
The Microsoft KB article shows their recommended way of doing this (also by using an additional variable): support.microsoft.com/en-us/kb/119737 Personally I like the If Not initialized as suggested by @Kai better. The default value of a Boolean is False, and it looks more logical to me than If IsEmpty(dummy)
3

I had the same issue in VB6, where it's exactly the same, and I like the Microsoft recommendation most:

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
Improve this answer

1 Comment

I mercifully don't have to touch VBA these days but every time one of my old questions gets a new answer I'm reminded what a masterpiece of design VBA is. Static variables are supported but only so long as you only ever start at the default value or include a ton of additional boilerplate as recommended by Microsoft themselves.
2

I solved it as follows using a static boolean to indicate if you are entering the function for the first time. This logic should work for other situation as well, i think

    Private Sub Validate_Date(TB as MSForms.TextBox)
         Static Previous_Value as Date
         Static Not_First_Time as Boolean
         if Not_First_Time = False Then 
               Previous_Value = Now
               Not_First_Time = True
         endif
         if IsDate(TB.Value) = False then TB.Value = Previous_Value
         Previous_Value = TB.Value
     End sub
Improve this answer

Comments

0

The use of a Boolean to flag something as already initialized functions correctly in normal use but has an unexpected side effect when using the debugger. bIsInitialized is NOT reset to False when the VBA projuect is re-compiled. When the initialization code (or the constants the code uses) is changed changed, the thing being initialized will not be re-initialized while using the debugger.

One work-around is to set a breakpoint at the statement "If (Not bIsInitialized) Then" and add bIsInitialized as a watch variable. When the breakpoint is reached the first time, Click on the value and change it to false, remove the breakpoint and use F5 to continue. There may be a better work-around that uses something that is reliably reset by recompiling the project, but since the documentation for VBA says that the Boolean would be re-initialed after going out of context and all code stopping, there's no way to know if that behavior was version dependent. Not reinitializing the block of memory associated with the routine appears to be a performance optimization.

Static bIsInitialized as Boolean

Static something_time_consuming_to_initialize

If (Not bIsInitialized) Then

initialize something_time_consuming_to_initialize

bIsInitialized = True

End If
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.