Public Variables in VBA

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.

End Button
Reset Button

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.

Code in the Userform class
Code in the Module level
Multi Page shown where user left off

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.

Bad use of Public variable

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s