In VBA, when you declare a variable as a global variable you can use it in any procedure in any module. As the name suggests you can use it globally means its availability is everywhere.
So if you have a global variable in the “module1” and now you want to use it in the module2, you can use it.
Declare a Global Variable in VBA
- First, you need to use the keyword “Global”
- After that, declare the name of the variable.
- Next, type “as” to get the list of data types.
- In the end, select the data type that you want to declare for the variable.
- When you are declaring a global variable, you need to declare it before writing any procedure into the module as you can see in the above example.
- You can also use the keyword “Public” to declare a variable as global.
Understand Scope of Variables
But to have a clear understanding of a global variable you need to understand the scope of the variables. There are three ways of defining scopes to variables:
- Procedure-Level: You can only use a variable in the same procedure where you declare it.
- Module-Level (Private): Makes a variable accessible from all the procedures in a module.
- Global Level (Public): Makes a variable accessible from all the procedures in all the modules.
Let me share something from the real world with you to make you understand this. I work from a co-working space and the place where I sit is on the first floor in the three-floor building, I mostly sit on the same seat every day.
When you declare a variable with the procedure-level scope you can only use it in the same procedure.
Just like I sit on the same seat on the same floor every day. But let me tell you more: Even though I sit on the first floor, I can use any seat on other floors too.
And in the below snapshot, we have declared the variable “iCon” at the top of the module using the keyword Private before starting any procedure and I have used it in all three codes.
Think of this module as a building where you have different floors (Procedures) and as you have already declared the variable at the starting of the module you can use any of the procedures in that module. It is called private module-level scope.
Now here is the last thing: My office has branches in different cities all over the country and if I go there, I can go to those offices and use any of the seats if I want. So, if you have different modules in your workbook, you can use a variable in all those modules by declaring it as Public/Global.
⇠ Back to What is VBA in Excel
Helpful Links – Developer Tab – Visual Basic Editor – Run a Macro – Personal Macro Workbook – Excel Macro Recorder – VBA Interview Questions – VBA Codes