How long do vars keep their values in VBA?

Hello all,

I noticed that sometimes if I stop a macro midway (completely stop, not just pause), and then re-run it from the beginning, my variables will still hold the value they were last set to right before i stopped the macro. But sometimes, this doesn't happen - the variables reset.

I'm wondering what defines how long the variable's value are held for? I'm considering writing a macro that can hold variables so that even after the macro is finished, the user can re-run and the variables will still be available. This is useful mostly for text boxes in userforms that will probably have the same values the next time the user runs, but the user can still change them if he wants.


Thanks
Joe

Hello Joe,

The lifetime of a variable is determined by where it is declared. Variables declared in the body of a Sub or Function hold their value only as long as the Sub or Function is active. However, declaring a variable as Static within a Sub or Function will retain its value between calls. Variables declared as Public at the top of a standard Module hold their values for as long as the project is active.

Variable retain their values until they go out of scope or are explicitly changed (e.g., Erase for an array).

Procedure-level variables (those declared in a Sub or Function) go out of scope when the procedure exits, unless they are declared as Static.

Module-level and global (Public) variables go out of scope when anybody executes an End statement or you hit the Reset button.