VBA Public Variables

What is Public Variables in VBA?

VBA public variables are those variables that are declared at the module level and are available from any function or procedure within the module as well as from other modules within the same project. They have a larger scope compared to local variables, whose access is restricted to the process in which they are declared.

Let us look at an example where we will see how to declare a public variable in VBA. First, we define a variable called “itemCount” as a public variable in a module and assign it the data type Integer. To ensure that itemCount can be accessed from several areas within the project, we utilize the “Public” keyword.

Write the first subroutine, “AddItem,” after the public variable has been created. It will increase the value of “itemCount” by one each time it is invoked.

Similarly, write another subroutine called “DisplayItemCount.” Here, we use the MsgBox function to display the most recent itemCount tally in a message box.

The following message box will show up when we run this macro. “The current item count is: X”; ‘X’ represents the current value of itemCount. It will go up each time the “AddItem” function is called. This is because ‘itemcount’ is declared as a public variable and can be accessed within the second sub-procedure as well.

How to Declare Public Variables in VBA?

When we set up subprocedures in VBA, we frequently end up declaring variables inside of them, which means we are repeatedly typing out the same declarations and data types. Instead, we’ll look at a more effective method that removes this duplication by using public variables.

#1 – Module Level Variables

A module’s variables can be made public or private. Any process in any module can access public variables. Private variables, on the other hand, are only accessible by procedures within that module.

A variable is typically considered private if it is declared in the declarations section of a module using only the “Dim” statement. On the other hand, you can explicitly state that a variable is private by preceding it with the Private keyword.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

#2 – Declare Variables use them Publicly

Examples

Example #1 – Basic Usage

In this example, we will see the declaration and use of a public integer variable named myVar, where Procedure1 assigns it a value of 10, and Procedure2 displays its value in a message box.

• Step 1: Here, we declare a VBA public variable named “myVar” of type Integer. The Public keyword indicates that this variable can be accessed and modified from any part of the VBA project.

• Step 2: In this step, we define a VBA sub-procedure named Procedure1. Here, we assign the myVar variable a value of 10.

• Step 3: In this step, we define another VBA Sub procedure named Procedure2. Inside this procedure, we use the MsgBox function to display the value of the “myVar” variable. When this procedure is executed, it will show a message box with the value of myVar, which is 10.

• Step 4: Now, save the VBA macro and run the code. It sets the value of the Public integer variable myVar to 10 in Procedure1 and then displays it in a message box in Procedure2.

Note: The order in which you perform the steps in matters. You may be receiving a message box with the value 0, which indicates that Procedure1 wasn’t run before Procedure2. The order of execution matters when using VBA.

Take the following actions to make sure the message box displays the number 10:

• To set the value of myVar to 10, execute Procedure 1.
• Run Procedure 2 after that to see myVar’s value displayed.
• Procedure2 will return 0 if it is executed before Procedure1 because myVar does not yet have a value assigned to it.

Here is the full code:

Public myVar As Integer

Sub Procedure1()
myVar = 10
End Sub

Sub Procedure2()
MsgBox myVar
End Sub

Example #2 – Using VBA Public Variables Across Modules

This example will show how to use the public integer variable globalVar, which is declared in one module of a VBA project and accessible and used in another module. Please refer to the Excel Workbook “VBA Public Variables,” specifically Modules 3 and 4.

• Step 1: Declare the Public Variable in the new module. In this case, it is Module 3.

A public variable of type Integer named globalVar is declared in Module 3. It can be accessed from other modules in the same VBA project because it has been declared public.

• Step 2: Procedure1 is a VBA sub-procedure that we define in Module 4. We set the “globalVar” variable, defined in Module 3, to 5 here. The value of globalVar is set to 5 by this line of VBA code.

• Step 3: We define Procedure2 in Module 5.  Here, we print the output in ‘globalVar’ to a message box.

• Step 4: Save the module and run the code. It sets the value of the previously declared global variable globalVar to 5 in Procedure1, and Procedure2 displays a message box with the value of globalVar, which is 5.

Note: To make sure that the global variable globalVar is correctly set to 5, as was indicated in the previous example, Procedure 1 must be completed before Procedure 2.

Here is the full code:

'Module 3
Public globalVar As Integer

'Module 4
Sub Procedure1()
globalVar = 5
End Sub

Sub Procedure2()
MsgBox globalVar
End Sub

Example #3 – User Form

We’ll demonstrate in this example how to initialise and set the UserForm’s userName public string variable in the code. A TextBox control (TextBox1) is filled, and the value “John” is set to userName with the code during the UserForm initialization.

• Step 1: In the new module, we declare a public variable named “userName” of type String. It will be used to store a user’s name and can be accessed from various parts of the VBA project.

• Step 2: Now, click on Insert and select “UserForm.”

• Step 3: Double-click on the UserForm to open its code window.

You can create a private sub-procedure called UserForm_Initialize within the userform’s code window.

Since this code is a part of the initialization procedure, it is run automatically when a UserForm is loaded or initialized.

• Step 4: In this step, the string value “John” is assigned to the variable userName.

• Step 5: This step will assign the value stored in the userName variable to TextBox1, a TextBox control. As a result, during UserForm initialization, TextBox1 will show the content “John.”

• Step 6: Save the macro and click on Run. When you run this code, it initializes a UserForm by setting the TextBox1 value to “John” in the UserForm_Initialize event.

Here is the full code:

'Module 5
Public userName As String

'UserForm code
Private Sub UserForm_Initialize()
userName = "John"
TextBox1.Value = userName
End Sub

Important Things to Note

• Make sure you set initial values for the VBA public variables before using them in different modules or procedures.
• Use descriptive and understandable names for your VBA public variables.
• Assigning the appropriate data types to VBA public variables is essential when declaring them. By doing this, you can prevent errors caused by mismatched or incorrect data types.
• Take caution when modifying the values of VBA public variables, as you can do so from multiple locations within your code. Use error handling in VBA carefully and think about using encapsulation—a technique that bundles the data with the methods that use it—to ensure the security and accuracy of your data.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Public Variables. Here we learn what are Public Variables in Excel VBA code & how to declare them, with examples & points to note. You can learn more from the following articles –

• VBA Variable Types
• VBA Global Variables
• VBA Wait Function