Static Functions and Subs
After fifteen years of writing VBA, I'm still learning new features of the language. Even if they're not particularly useful features...
I've used Static variables in the past–local variables that retain their value between calls–but did you know that entire Functions and Subs can also be marked as Static?
In the case of a Static Function or Static Sub, every local variable in the routine behaves as if it were declared with the Static keyword individually. Check out the following note from the Static Statement documentation:
The Static statement and the Static keyword are similar, but used for different effects. If you declare a procedure by using the Static keyword (as in Static Sub CountSales ()), the storage space for all local variables within the procedure is allocated once, and the value of the variables is preserved for the entire time the program is running. For nonstatic procedures, storage space for variables is allocated each time the procedure is called and released when the procedure is exited. The Static statement is used to declare specific variables within nonstatic procedures to preserve their value for as long as the program is running.Honestly, I rarely use the Static keyword for individual variables. I'm struggling to see when I would want to use this feature for an entire Function or Sub. That said, it is important to know that it's possible. You never know when you might come across some code in the wild that uses this feature...
Tip o' the Hat to Jan Karel Pieterse
Credit for this juicy tidbit of information goes to Jan Karel Pieterse, an Excel MVP and font of VBA knowledge and trivia. I first learned this was an option from one of his LinkedIn posts:
You learn something new every day!
External references
Image by Lars Nissen from Pixabay
