USE CAUTION! Any time you declare a variable as public it is important to understand all the consequences and how they behave.
In order to make a variable public it has to have the keyword ‘Public’ rather than ‘dim’. It also has to be declared in a module before the first procedure declaration. It does not matter which module, but has to be a module. It cant be a class module or in the UserForm Code.
Public variables are used because the value is retained in any procedure in the VBA Project. Even when the procedure or macro is done running, the value in the variable is still retained. The only way a public variable loses its value is 1) excel is closed. 2) the procedure is halted by an ‘End’ statement or the End button is bushed during a debug error. Note: ‘End’ is not the same as ‘End Sub’. 3) the reset button is pushed.
The below example is a useful way to use Public Variables. A user will want to open and close a Userform with inputs and keep the multi page control on the same page where the user left off. The multipage to show is stored in a public variable we assigned as multipageStartup. If multipageStartup were declared with dim keyword, then the userform will always open with page1 showing, which is not what we want.
Below is a bad example of how to use public variables. We want a macro that writes 22 on cells A1 to A20. It will work perfectly the first time. But the second time, it wont work because irow will be stored with value greater than 20 so the loop will never run.
Coding with Visual Basic 