If you have several Visual Basic modules in your workbook and want to use the same variable in all the modules in the workbook, declare it with the word Public or Global instead of Dim. This declaration must be at the very top of your module, above any macros or functions you have, but below option statements like Option Explicit. For example:
Public GLB_TestVariable As Integer
This allows GLB_TestVariable to be used in other modules. Note that using GLB as part of the variable name helps to identify it as a global variable. Although not required, such a naming approach will insure that you know when you are working with a global variable. Public variables will retain their values after a macro runs, just like a Static variable. However, they will be reset if the Visual Basic code is edited, an End statement is used to halt a macro, or the workbook is closed.
Please note that if a variable in a macro is declared with the same name as a global variable, references to this variable in that macro will refer to the local or macro declared variable instead of the global variable. Such a situation may cause errors that are difficult to debug.