PDA

View Full Version : Clear out static variables



rhythm
06-19-2012, 05:57 AM
I wrote this code to give a prompt to start a process but whenever I run the macro within the same instance of excel, it never prompts more than once. To remedy this I have to close out of excel entirely and re-run the macro -- is there a way around that? Thanks!


Private Function TimePrompt() As String
Static ans As String
If ans = vbNullString Then
ans = InputBox("Please specify the time period of this report:")
End If
TimePrompt = ans
End Function

mikerickson
06-19-2012, 07:09 AM
You could add a clearing argument

Private Function TimePrompt(Optional Reset as Boolean) As String
Static ans As String
If (ans = vbNullString) Or Reset Then
ans = InputBox("Please specify the time period of this report:", default:=ans)
End If
TimePrompt = ans
End Function

Then
MsgBox TimePrompt() will ask iff it hasn't been answered
MsgBox TimePrompt(True) will always ask.

mikerickson
06-19-2012, 07:54 AM
Alternalty,
Sub ClearAllVariableValue()
End
End Sub

Bob Phillips
06-19-2012, 08:09 AM
Why not use a non-static variable?