18

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

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Zeus
  • 1,496
  • 2
  • 24
  • 53
  • 2
    A Global variable is always Static, in that it keeps its value unless code execution is halted. If that's why you want a Static variable, then use a Global. – Tim Williams Feb 16 '14 at 02:21
  • Tim, thanks for editing my post and your answer. All I want to do is initialize several collections in several procedures in one module, and then use these collections in several procedures in another module. Declaring them as Public has helped, but I now have Runtime error 91 object variable or with block variable not set, and I don't know why. The code seems to run ok until code execution is halted or the code changed. Is there anyway to make this more robust like using a set statement? – Zeus Feb 17 '14 at 02:58
  • I got around the problem by initializing the collections in the procedure which uses them each time it ran, but this seems like a bad solution. – Zeus Feb 17 '14 at 03:02
  • If you don't like your solution then why not post it as an update and ask for suggestions? – Tim Williams Feb 17 '14 at 06:50
  • 2
    `Public Const myvar as integer = 999` – Patrick Lepelletier Nov 22 '14 at 12:31

3 Answers3

10

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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    Thanks Gary's student. Your solution works but if I change the code i get a run time 91 error saying object variable or with block variable not set. I'm not sure what it means. Do I need to set the collection in usevar()? The setvar and usevar are also in different modules. – Zeus Feb 17 '14 at 02:25
  • by changing code I mean changing the line location of the procedure - just moving it above a ' statement – Zeus Feb 17 '14 at 02:34
  • Is there a more efficient way of initializing collections than initializing them in each procedure? – Zeus Feb 17 '14 at 02:51
  • **Dim** ing myvar at the very top of a module should make it available to all subs and functions in that module. Making it **Public** should make it available to other modules as well........I am not sure if event code can "see" it as well. – Gary's Student Feb 17 '14 at 03:13
  • Thanks, the problem seems to be when the code changes (still in development) or has an error and all the variables are reset. So maybe some type of error check in the usevar procedure? If the collection is null then refresh? – Zeus Feb 17 '14 at 03:48
  • @Zeus - the error is caused by the fact that transitioning between some of the 16 or so different compilation states that occur in VBA will clear all variables across the entire "project" I get around this by calling through "wrapper" properties that check initialisation state. – Roger Willcocks May 17 '17 at 21:08
8

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.

Egalth
  • 962
  • 9
  • 22
1

The key is to use 2 variables. In the code below, myvar is public but not static. stvar is static but not public. Its scope is only within Main(). By assigning myvar=stvar and stvar=myvar, it effectively creates a variable that is both public and static. The value is preserved.

Public myvar As String
Sub Main() 'in module 1
Static stvar As String
myvar = stvar
toInput
stvar = myvar
End Sub

Sub toInput() 'in module2
myvar = InputBox("enter something", "Input", myvar)
End Sub
On Chang
  • 11
  • 2