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.

Answer 2

Although this question was answered over four years ago by @Gary's Student, there's a subtle nuance worth mentioning, since the solution can depend on the data type of myvar.

First of all, as you've noted in the question, Public Static myvar as Integer doesn't work, because Static is only allowed inside a sub or function.

As noted in the comments to the OP by @Patrick Lepelletier, you can easily get around this by declaring a Constant instead (assuming you don't need to change it dynamically): Public Const myvar as Integer = 999. (Or possibly Private Const myvar...)

Another option is to declare myvar as a function instead of a variable or constant, effectively turning it into a pseudo-constant:

Private Function myvar() as Integer
     Static intMyvar as Integer 
     intMyvar = 999
     myvar = intMyvar
End function

In this simple example where myvar is an integer, the pseudo-constant approach is obviously unnecessary and adds the overhead of a function call. Simply declaring a Constant does the job. However, using a constant only works if the value is static and not an object. It will not work if myvar is an object, for example a Range. In that case, using pseudo-constants might be useful:

Private Function myvar() as Range
    Set myvar = Range("A1")
End Function

Another advantage is that you can use code inside the function to check for certain conditions and assign different values to myvar accordingly.

The pseudo-constant approach can also be combined with naming worksheet ranges: If cell A1 is a named range, say MyRange, then you could write:

Dim strMyString as String
strMyString = "MyRange"
Private Function myvar() as Range
    Set myvar = Range(strMyString)
End Function

Now it is possible to move around the content of cell A1 without breaking the code, since the named range follows along if you cut and paste the cell. I find this approach useful in the design stage, when things tend to move around a lot in a worksheet.

Pseudo-constants also help avoiding some problems usually associated with global (or module-level) variables that might be an issue in larger projects.