What is VBA Global Variable in Excel?
In Excel VBA, when you declare a variable as a global variable you can use it in any procedure in any module. As the name suggests, a variable declared using “Global” 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.
That means that the global variables are different from normal variables which you declare within the sub procedure. (You can also say that it is an all level variable.)
Declare a VBA Global Variable (Simple Steps)
Using Global Variables is simple in Excel VBA. You can use the below mentioned steps for this:
- First, you need to type the keyword “Global” which helps VBA to identify the that this variable is global.
- After that, declare the name of the variable which is the same process we do in declaring all the all the variables.
- Next, type “as” to get the list of data types.
- In the end, select the data type that you want to specify for the variable. Or, you can type integer, as we have done in our example.
As you can see in the above example, the variable “iCol” has been declared as an integer before starting any procedure. And after that in all the three sub procedures, we have used it with the different values. But in none of the procedures, we have declared it again.
Option Explicit
Global iCol As Integer
Sub one()
iCol = 10
End Sub
Sub two()
iCol = 8
End Sub
Sub three()
iCol = 12
End SubAnd you can also use the word Public instead of Global. See the example below:
Option Explicit
Public iCol As Integer
Sub one()
iCol = 10
End Sub
Sub two()
iCol = 8
End Sub
Sub three()
iCol = 12
End Sub
And when you declare a global variable, you can use it in all the modules which you have in a workbook. In the above example, you can see that we have two modules, (Module 1 and module 2).
Now this global variable is declared in the Module1 where we have three sub procedures using it. But in the second module, we have one more sub procedure which is using the same variable but with the different value.
Learn VBA in 1 Hour – If you want to kickstart you VBA learning, check out free course that can help you learn the basic in the most effective way possible. This will help you to have a solid foundation about how VBA works.
Important Notes
- When you are declaring one or more global variables, you need to declare them before writing any procedure into the module as you can see in the above example.
- Or, you can also have a specific module to declare all the global variables in it.
- You can also use the keyword “Public” to declare a global variable.
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 in sub procedures.
- Procedure Level Variables: You can only use a variable in the same procedure where you declare it.
- Module Level Variables (Private): Makes a variable accessible from all the procedures (Module Level Availability) in a module.
- Global Level Variables (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 Variable.