VBA Public Variable

Excel VBA Public Variable

Variable is an important part of a programmers life. Every time a programmer codes he creates some set of variables. A variable can be considered as a voucher which we frequently use on online shopping sites such as Flipkart, Amazon, etc. that voucher allows you to have a certain discount on certain products predefine. Same is the case with a variable. A variable can grammatically be defined as a placeholder which reserves some memory in the system with certain name. That memory space can then be utilized anytime with the same name.

A VBA variable is considered as a public variable in two circumstances. When –

• It can be used along with all the codes within a module.
• It can be used along all within all the codes for different modules.

In layman terms, a variable is considered as a public variable when it can be used in any code and in any module. In this article, we are about to see, how a variable can be defined. How it can be defined publicly within VBA.

Before starting the concept of VBA Public Variable, we need to understand that the variables which are used in one sub-procedure can’t be used in the other sub-procedure (that is where we started to feel the need of having a public variable which can be used across any module, any sub-procedure).

Suppose we create a sub-procedure in a VBA and define a new variable there using Dim statement.

In this code, var1 is the variable defined under sub procedure Ex_Var.

Now, I will define a new sub-procedure and irony is, I can’t use previously defined var1 in this sub-procedure. See the screenshot below, where I am trying to assign value 10 to var1 under different sub-procedure. See the error message as well after we run the code.

By declaring variables under sub-procedures, we restrict them to be used by that sub-procedure only. It is a major drawback of declaring a variable within a sub-procedure.

This leads to the new theory, which includes the variable declaration outside the sub-procedures. This will help the programmer to use the defined variables widely with different subprocedures.

How to Declare Public Variables in VBA?

Let us look at a few examples to declare public variables in VBA.

Example #1 – Defining a Variable Publicly Within a Module

Suppose we want to define a variable which can be accessed publicly across all the codes within a single module. We can do that. Follow the steps below:

Step 1: Open your Visual Basic Editor (VBE) by hitting Alt + F11 or clicking the Visual Basic button under Developers tab in Excel. Select Insert tab, navigate to Modules and click on it. It will add a new module within the VBE to work on.

Step 2: Within the newly created module, add Option Explicit command at the start of your VBA code (before creating any variable). Option Explicit helps you if there is any typo while using the defined variable. If the used variable is not defined in the system, this option throws an error message saying the variable is not defined.

Code:

Option Explicit

Step 3: Use a Dim statement to create a new variable named my_name with data type as String.

Code:

Option Explicit

Dim my_name As String

If you could pay attention, we have defined this variable explicitly. Meaning, it is not a part of specific sub-procedure. Which automatically makes this variable available for all the codes within the module created (Module1). This is called as defining a variable publicly within a module. This variable can be used anywhere across the multiple sub-procedures within the same module.

Suppose the code given below, I have assigned value to variable my_name as “Lalit Salunkhe”.

Code:

Option Explicit

Dim my_name As String

Sub Ex_1()

my_name = "Lalit Salunkhe"
MsgBox "My name is: " & my_name

End Sub

If I run this code, I will see a message box as shown in the screenshot below:

We can also use this variable in different sub-proc within the same module. See the screenshot given below:

Code:

Option Explicit

Dim my_name As String

Sub Ex_1()

my_name = "Lalit Salunkhe"
MsgBox "My name is: " & my_name

End Sub

Sub Ex_2()

Dim my_age As Integer
my_name = "Lalit Salunkhe"
my_age = 28
MsgBox "My name is: " & my_name & "; My age is: " & my_age

End Sub

See the Ex_2 sub-proc. It has a new variable defined my_age. I have used my_name to add my name and my_age to add the current age of mine. You can see my_name is still accessible within the second sub-procedure. Once I run this code, we can get the output as a message box shown below:

This is how we can define a variable publicly within a module.

Example #2 – Define a Variable Publicly Across all the Modules

In order to achieve this feat, you need to use the keywords as Public or Global instead of Dim which sets the variable as public for all the module wherever you work across.

Step 1: Create a new module. Go to Insert tab, click on Module to add a new module under VBE.

Step 2: Add Option Explicit within the module.

Code:

Option Explicit

Step 3: Define a new variable named var1 and var2 both as a type integer. But this time using Public statement instead of Dim (which we used previously).

Code:

Option Explicit

Public var1 As Integer
Public var2 As Integer

Step 4: Now, these two variables can be used across multiple modules if created under the same VBE. See the example given below:

Code:

Option Explicit

Public var1 As Integer
Public var2 As Integer

Sub Ex_3()

var1 = 10
var2 = 100
MsgBox "Value for var1 is: " & var1 & Chr(13) & "Value for var2 is: " & var2

End Sub

We have assigned the values to both of the variables using a MsgBox feature, trying to display values for those two variables.

Step 5: Run the code by pressing F5 key or by clicking on Play Button, you can see an output as shown below:

Step 6: We will use the same variables under Module1 which we have used in the first example and try to figure out whether the variables are accessible there or not.

Code:

Option Explicit

Dim my_name As String

Sub Ex_1()

my_name = "Lalit Salunkhe"
MsgBox "My name is: " & my_name

End Sub

Sub Ex_2()

Dim my_age As Integer
my_name = "Lalit Salunkhe"
my_age = 28
MsgBox "My name is: " & my_name & "; My age is: " & my_age

End Sub

Sub Ex_public()

var1 = 1000
var2 = 999
MsgBox "Value for var1 is: " & var1 & Chr(13) & "Value for var2 is: " & var2

End Sub

If you could see the screenshot above, we have used the same set of variables defined under Module2 and have values like 1000 and 999 respectively. We are using MsgBox function to present the values of these two variables respectively. Run the code and you can see the output as below:

This is how a variable can be used publicly within the module and across the modules.

Things to Remember

• It is always preferred to define the variables explicitly in VBA. So that they are not limited to the sub-procedure they are defined within.
• You can assign public variable in two ways: one within a module using conventional Dim statement and second across all the modules present in VBE using either of the Public or Global statement.

Recommended Articles

This is a guide to VBA Public Variable. Here we discuss how to declare Public Variable in Excel using VBA code in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Global Variables
2. One Variable Data Table in Excel
3. VBA FileSystemObject (FSO)
4. Two Variable Data Table in Excel