Life of Variables

Sometimes you want to retain the value of a variable in Excel VBA when a procedure ends. You can achieve this by using the Static keyword.

1. Place a command button on your worksheet and add the following code lines:



2. Result when you click the command button on the sheet:



3. Result when you click another time:



Explanation: Excel VBA destroys the variable when the procedure ends. Each time you click the command button on the sheet, Excel VBA creates the variable x again, adds the value 1 to it, and displays the result.

4. Now replace the keyword Dim with the keyword Static.



5. Result when you click the command button on the sheet:



6. Result when you click another time:



Conclusion: static variables retain their values, even when a procedure ends.

Note: static variables will be destroyed when you click the Reset (Stop) button or when you close your workbook..