CODEWITHSUNDEEP
vbastatic
kmccoy
kmccoy

Reputation: 2861

How do I clear a static variable in Excel VBA?

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?

Upvotes: 6

Views: 26590

Answers (4)

Keith Brown
Keith Brown

Reputation: 1

Here's an approach using a break-point and the immediate window.

  1. Write the following routine, and run it a few times to load up the static variable N.

    Sub sub_with_static_var()

    Static N As Long:     N = N + 1

Debug.Print N

    End Sub

  2. Set a break-point in the Sub, and run it again, stopping at the break-point.

  3. Enter N = 0 in the Immediate Window, then drag the "Next Line to Run" arrow to the End Sub line and execute to completion.

If you now run this sub, it will print 1 (i.e. N was 0 and got incremented as usual).

This should meet your requirement, and does not affect the way you write your code. (If your sub has arguments, you may have to invoke it via the Immediate Window with some dummy arguments in order to apply this method.)

Upvotes: 0

Matt
Matt

Reputation: 11

Hi I don't know if anyone else may want to write their own "search function", but I found that I was wanting to gather up several words from within one cell, comma-deliminated, and then search within the strings using the built in "InStr" function. Anyways, I was wanting to do this for all words, so I had to clear out the gathered word after each use of InStr. In order to do this, I set the string to Empty.

Upvotes: 1

jtolle
jtolle

Reputation: 7113

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.

Upvotes: 3

Cody Gray
Cody Gray

Reputation: 244772

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").

Upvotes: 1

Related Questions