How do I clear a static variable in Excel VBA?

Question

I have a static variable defined in a Sub:

Private Sub assignVars()
' Use this function to assign default values

    Static isSet As Integer

    If isSet <> 1 Then
        ' do something

        isSet = 1
    End If

End Sub

I have made some changes to my code and want to reset the static variable "isSet". Is there any easy way to do this without closing Excel and opening it up again?

Answer 1

The easiest way to do this is to execute the End statement in the immediate window.

However, this will destroy all stored state - i.e. all your module level variables, all static variables in all procedures, etc. And it's abrupt; Unload and Terminate events don't fire, etc.:

http://msdn.microsoft.com/en-us/library/gg251671.aspx

(I edited the stuff below after I re-read your question...)

To cause a loss of state in just the one routine, you could manually comment out the declaration of isSet and then restore it. There is a setting you can make in the VBE under the Tools...Options menu, General tab, that will cause you to be notified when this kind of state loss happens. (It doesn't alert for an invocation of End, though, presumably because you shouldn't need any warning in that case.)

You didn't ask for this, but if you want to be able to reset the one static variable in the one procedure without editing any code, you'll have to do something kludgy like this:

Public Sub assignVars(Optional reset As Boolean)
    Static isSet As Integer

    If reset Then
        isSet = 0

        Exit Sub
    End If

    If isSet <> 1 Then
        isSet = 1
    End If
End Sub

Notice that I had to make your routine Public so that you can call it from the immediate window with a parameter of True when you want the reset.

If it's the case that you need some state that is accessible from outside of your procedure, in this case for the purposes of being able to manually reset it, you might consider a module-level variable rather than a Static procedure-level one. Then your routine can stay Private, there is no dirtying of its interface for reset purposes, and you can mess with the module-level variable all you want manually.

Answer 2

Uhh.... How about just clearing the variable to whatever you want it to be? Its default value is zero:

isSet = 0

The VBA editor also lets you stop ("End") the execution of a macro and restart it ("Run").