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.
Table of Contents
- Public variables can be accessed from various procedures and modules due to their project-level scope within a single VBA project.
- Use the “Public” keyword, followed by the variable name and data type to declare the VBA public variable.
- When working with public variables across modules, proper sequencing of procedures is essential to guarantee that they have the expected values during execution.
- You can set up a user form’s initial state and values using the initialization code that UserForms in VBA can have in the UserForm_Initialize event.
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
To declare the variables, we use the following procedure.
- Open Excel and press ALT + F11 to open the VBA editor.
- Insert a new module by clicking on “Insert” in the menu and selecting “Module.”
- After adding a new module, you can begin setting up your public variables.
You can access these variables at any point in your VBA project. You must use the “Public” keyword, the variable name, and the data type in order to declare a public variable.
Public myVariable As Integer
Public anotherVariable As String
Two public variables, one of type String and the other of type Integer, are defined in this example. You can use and alter these variables from different places in your VBA code, such as different modules and procedures inside the same project.
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
myVar = 10
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:
Public globalVar As Integer
globalVar = 5
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:
Public userName As String
Private Sub UserForm_Initialize()
userName = "John"
TextBox1.Value = userName
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)
If you declare VBA public variables incorrectly or have logical errors in your code, they might not function. Make sure there are no typos or scope-related issues, and that you have declared them at the module level.
Whereas private variables are only accessible within the procedure in which they are declared, VBA public variables have the scope of the project and can be accessed from any module.
VBA Public variables can be declared within a module and accessed from the user form’s code. You can utilize a public variable that you set to a user’s input in a user form elsewhere in your VBA project.
Simply use the appropriate procedure or function in your VBA code to assign a new value to a public variable to reset its value. For instance, myVar = 0 will cause myVar’s value to be reset to 0.
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 –