Public Static variable in excel vba

Question

Is it possible to have a static variable declared in one procedure, and use this variable in several different procedures using Excel VBA?

i.e.

Public myvar as integer

Sub SetVar()
   static myvar as integer
   myvar=999
end sub

sub Usevar()
    dim newvar as integer
    newvar=myvar*0.5
end sub

I need myvar to be seen by other procedures, and not change or get "lost". The code above works if myvar is not declared as a static variable, but more code then the variable is "lost". If the static declaration is used, myvar is not seen by the usevar procedure. And "Public Static myvar as integer" is not accepted by VBA.

Thanks for your help

Zeus

Answer 1

Try this by calling MAIN() :

Public myvar As Integer

Sub MAIN()
    Call SetVar
    Call UseVar
End Sub

Sub SetVar()
    myvar = 999
End Sub

Sub UseVar()
    Dim newvar As Variant
    newvar = myvar * 0.5
    MsgBox newvar
End Sub

If you declare an item Static , its value will be preserved within the procedure or sub.
If you declare the item Public , its value will be preserved and it will be visible to other procedures as well.