Excel VBA: Declare Global Variable and Assign Value

It appears to be quite easy to declare a variable. However, in order to have excellent practical experience, we must comprehend the range of those factors. In the Sub-procedure, we frequently specify the variables for each macro. Nevertheless, by defining a single variable, we may utilize it in every macro in that module as well as the other modules in the active VBA project. You will learn how in VBA you can declare global variable(s) and assign value in Excel in this piece of writing.

What Are VBA Global or Public Variables?

Global variables are Excel VBA variable types that have been defined before the module’s first macro runs. A variable becomes a global variable when it is defined with either the “Public” or “Global” keyword. It is capable of being used in functions, Sub-procedures, modules, and declarations sections.

For each new module or distinct sub-procedure, you must define the same variable when using a local variable. The pre-defined variable is therefore given a fresh space each time. Additionally, it makes maintenance work laborious. These issues were avoided by the introduction of the Global variable. It generally defines below the Option Explicit statement.

Code:

Option Explicit
Global txt As String
Sub global_level_variable_1()
txt = "Exceldemy"
MsgBox txt
End Sub

For your convenience, to run VBA code you need to open code writing window in Excel.

Excel VBA Declare Global Variable and Assign Value: 2 Ways

We are going to introduce two different approaches regarding Global or Public variable declaration and assigning of value in VBA. At first, we intend to discuss the value assigning while using VBA macro declaring a global variable. After that, we will learn to reference values from an Excel cell along with the Public variable declaration.

1. Declare Global Variable and Assign Value in VBA

There are numerous advantages to using Global variables. Step by step we will address the issues regarding the use of variables and its remedy.

Procedure Level Variable

As you can see in the image, we declared variable typing veteran Dim in the 1st sub-procedure of Module 1. But the declaration of that variable didn’t work for the 2nd sub-procedure of the same module; rather it shows Compile error: Variable not defined.

Module Level Variable

Using veteran Dim, you can declare a variable under the Option Explicit statement representing the module-level variable. Module level variable allows executing all the sub-procedure of a single module.

As you observe in the image, we obtain Excelden once we run the VBA code of the 2nd sub-procedure.

Global Level Variable

Unlike the Module-level variable, if we want to use a variable that works for all the modules of a workbook, we must write Global or Public instead of widely used Dim under the Option Explicit statement. Thus we get Excelden once we run the VBA code of the 2nd sub-procedure of Module 1. Therefore, we don’t need to type Dim separately in a separate sub-procedure.

We are likely to execute a new code in Module 2 where we didn’t declare the variable in the sub-procedure. However, the VBA code works and outputs Softeko because we declared txt as a Global variable that works for all the modules in a workbook.

2. Declare Public Variable in VBA and Assign Value from Excel Cell

Not only assigning value but calling from an Excel cell can be done while declaring Global or Public variables. Calling value from the B1 cell and executing the VBA code of the 2nd sub-procedure of Module 1, we achieve Excelden in a pop-up message box.

Similarly, by calling the value from the B2 cell and executing the VBA code of Module 2, we obtain Softeko through a pop-up message box.

Frequently Asked Questions(FAQ)

1. Where do I declare global variables in VBA?

Using the keyword Public or Global instead of Dim under the Options explicit statement, we can declare the global level variable. It is usable in any sub-procedure, module, or function in a workbook.

Example: Global a As Integer

2. How is a value assigned to a global variable?

You can define any type of variable such as integer, string, date, decimal, etc. by assigning value in VBA code or calling from an Excel cell while using global-level variables.

For instance, Global a as String, thus we obtain string value for a.

3. How do I assign a cell value to a Variable in VBA?

By referencing a cell from an Excel cell while declaring Global or Public variables, we can assign a cell value. Let’s say, A1= Apple. Writing txt=Range(“A1”) and msgbox txt declaring the txt variable as global, we obtain Apple in a message pop-up box.

📄 Important Notes

The value of a global or Public variable remains the same throughout all the Sub-procedure, Modules, functions, etc. once the Excel VBA Macro has executed with it.
In order to define global variables in VBA and have all the variables, it is preferable to maintain a specific module.
The VBA macro code can only be reset by hitting the stop button, which is the only means to reset the value of the variables.

📝 Takeaways from This Article

Difference among Procedure level, Module level, and Global level variables.
Importance and Usability of Global level variable.
Declaration approaches of Global variables.
Assigning values through input in VBA code or calling from an Excel cell.

Download Practice Workbook

To practice, please download the Excel Workbook file from the link below.

Declare Global Variable and Assign Value.xlsm

Conclusion

We concisely expound on 2 different ways for VBA to declare global variables and assign value. I hope you enjoyed your learning and will be declaring global or public variables along with assigning values in Excel VBA. Any suggestions including queries are appreciated. Don’t hesitate to leave your thoughts in the comment section.

Get FREE Advanced Excel Exercises with Solutions!