XL2000: Module-Level Variables Are Reset to Their Default Values

When you run a Microsoft Visual Basic for Applications macro (Sub procedure) for the second time from a workbook, both the public and private module-level variables in your macro are reset to their default settings.

This problem occurs when you do the following:

1. You declare one or more variables using the Dim, Private, or Public keyword in the declaration section of a standard module.
   
   -and-
2. You write a Sub procedure in the same module and use the module-level variables.
   
   -and-
3. You click the Design Mode button on the Standard toolbar of the Visual Basic Editor, or on the Control Toolbox in Excel.
   
   -and-
4. You run the macro a second time.

To work around this problem, do not go into design mode until you no longer need to run your code.

For more information about understanding the scope and visibility of variables, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type understanding scope and visibility in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the lifetime of variables, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type understanding the lifetime of variables in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about how to declare a variable, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type declaring variables in the Office Assistant or the Answer Wizard, and then click Search to view the topic.