3

I'm not sure if this was asked before, but I couldn't find it. Suppose I have a procedure with a local variable inside it. Normally, that variable is destroyed after the function finishes running. But in some cases, I'd like for it to persist, like in this example:

Function myFunction()
   Dim runCount As Integer

   runCount = runCount +1

   debug.print "This function is now running for the " & runCount & " time."
End Function

In this example, the code wouldn't work, because the runCount would be reset each time. Of course, the simplest solution would be to declare a global variable instead, but in some cases, I want to avoid that for the sake of simplicity, encapsulation or other reasons.

So, is there any way to have the local variable persist after the procedure has finished running?

braX
  • 11,506
  • 5
  • 20
  • 33
J R
  • 227
  • 3
  • 8
  • make it a public variable and declare it at the top of the module. do not re declare inside the sub. – Scott Craner Mar 21 '18 at 13:42
  • 6
    You can also do `Static runCount As Integer` – CallumDA Mar 21 '18 at 13:45
  • 2
    Yes, but doing that will also make it accessible from outside the function. I want it accessible only on the inside. – J R Mar 21 '18 at 13:47
  • 3
    This is not a duplicate of "How to declare a *global* variable", he specifically asks how to persist a *local* variable. As @CallumDA says, this can be done by declaring it as `Static` inside the function – Joe Mar 21 '18 at 13:47

2 Answers2

8

Use the Static keyword to declare your local variable, instead of Dim, and the variable's content will outlive a call to the procedure it's declared in.

e.g. this will work as intended:

Function myFunction()
   Static runCount As Integer

   runCount = runCount + 1

   debug.print "This function is now running for the " & runCount & " time."
End Function

Using Static locals is arguably preferable to declaring module-scope variables, when the variable only makes sense in a local scope or is only used in one procedure.

Note that module-scope does not equate global scope. This variable is accessible anywhere in the module it's declared in, but not outside of it:

Option Explicit
Private foo As Long

Use the Private (or Dim, but I prefer to keep Dim for declaring locals) or Public keyword to declare module-scope variables. The Global keyword is deprecated, and does exactly the same thing as Public.

As Kostas K. correctly points out, VBA also supports Static members.

See this signature:

Function myFunction()

Is implicitly a Public member. This would be explicit:

Public Function myFunction()

VBA supports adding the Static modifier at the procedure level, so you can do this:

Public Static Function myFunction()

And now you have a Public function where every local variable is implicitly Static. This is too much implicit, easily bug-prone stuff going on for my own personal taste, so I would avoid it. But it's probably good to know it's there if you need it.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Happy to hear about how this answer can be improved, @downvoter. – Mathieu Guindon Mar 21 '18 at 15:19
  • 2
    Worth mentioning in my opinion that by moving the `Static` keyword to the method declaration, all local variables (passed to the function and within the body of the method) preserve their values. – Kostas K. Mar 21 '18 at 15:50
  • 3
    @KostasK. good point - I tend to try and avoid `Static` locals in the first place though... IMO a `Static` procedure is an evil thing, makes locals *implicitly* static (and you probably know how much I "love" the implicit stuff VBA does!), so... I'll upvote your comment and leave it out of my answer =) – Mathieu Guindon Mar 21 '18 at 15:56
  • Mathieu, thank you very much, this is exactly what I've been looking for. But could you please elaborate on that last comment? How does using Static make locals implicitly static, and why is that bad? – J R Mar 21 '18 at 16:04
  • 2
    @JR it's essentially a language feature: if the `Static` modifier is applied to a procedure, then every local variable in that procedure is implicitly `Static`, so you have local `Dim foo As Long` that's static even if it doesn't explicitly says so. IMO that's bug-prone, since static locals, as you now know, retain their value between procedure calls. One last note, your `Function` is also implicitly returning a `Variant`. You'll want to declare an `As [Type]` return type, and assign its return value `myFunction = result` so that the caller can use that return value. – Mathieu Guindon Mar 21 '18 at 16:17
-4

Do not use Static (imo).
Use Private on module level instead of is more preferable.
But much more preferable will be to pass counter to function ByRef.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
user6698332
  • 407
  • 3
  • 14
  • 3
    Can you explain why a `Public` global is better than a `Static`? That seems to go against the prevailing common wisdom of the age. – FreeMan Mar 21 '18 at 14:56
  • 1
    For what it's worth, I don't like `Static` much either, because it interferes with my constant context-switching between VBA and .NET, where `static` means something entirely different - but see I didn't bring it up, because it's unsubstantiated opinion and besides, sometimes it *is* the right tool for the job. As I said in my own answer, it's *arguably* preferable to declaring at module scope. I would warmly recommend you [edit] your answer to back up your claims with *some* arguments. I don't see the point of exposing a formerly local variable outside the module it's in. Why `Public`? – Mathieu Guindon Mar 21 '18 at 15:11
  • @FreeMan as you minus me, so I prefere to listen to you about prevalation Static on Global. However, there is two main reason (applying to this case). The first is that author have a very low skill, cause he don't know about Static. The second is that Static on procedure level is subject to loss for eyes among other code. Concerning to 1-st reason - it is very important. And 3-rd. There are tohns of discussions on Static vs. Global. You are free to do rtfm and to make your own imo. Do not forget - I mark it as imo. And mark as preferable p.3. – user6698332 Mar 21 '18 at 15:16
  • @Mathieu Guindon thank you. Public is typo. Private on module level, of course... – user6698332 Mar 21 '18 at 15:21
  • Then you need to [edit] your answer to fix it, and back up your claim about passing a `ByRef` parameter, especially given OP's goal to count invokes, with a `ByRef` counter the value lives outside the function and is no longer guaranteed to fulfill its purpose - it's now all in the hands of the caller. – Mathieu Guindon Mar 21 '18 at 15:25
  • Thank you once more. However, it was very easy to detect typo cause the phrase 'Public on module level' itself is absurd :) In case given OP's there are no risks it will be changed somewhere. As I wrote - there are no reasons to assume sophisticated options for calling this function from different places. And do not have to jump above question skill itself. – user6698332 Mar 21 '18 at 15:39
  • 2
    Ironically, @user6698332, I didn't downvote your answer. Also, the OP _explicitly stated_ in his question, "Of course, the simplest solution would be to declare a global variable instead, but in some cases, I want to avoid that for the sake of simplicity, encapsulation or other reasons." By answering the question with "You should do what you just said you didn't want to do" without giving a good reason (beyond "my opinion") doesn't make for a particularly good answer. Unfortunately, when prompted to try to make your answer better, you only got defensive, and will probably now leave in a huff. – FreeMan Mar 21 '18 at 15:43
  • @FreeMan, I repeat in 3-rd. Global was a typo. I do not know what came over me :) I apologize to everyone who was misled by my typo. 'imo' and explicity marked as preferable variant with ByRef, they clearly say that I am not inclined to holiwars on on the empty place and on very simple question. . – user6698332 Mar 21 '18 at 15:53