Life of Variables in Excel VBA

 

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:

Dim Statement in Excel VBA

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

Dim Statement Result

3. Result when you click another time:

Dim Statement Result

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.

Static Keyword in Excel VBA

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

Static Keyword Result

6. Result when you click another time:

Static Keyword Result

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.