30 Advanced Excel Tips for 2026

Excel Champs / VBA / Global Variable
By Puneet Gogia (Microsoft MVP)

Declare and Use VBA Global Variable in Excel (Public Variable)

Declare the variable with Public at the top of a standard module, above every procedure: Public gTotal As Long. Every procedure in every module of the workbook can then read and change it. Global gTotal As Long does the same; it’s the older keyword and works only in standard modules.

Use a standard module (Insert → Module), not a sheet or ThisWorkbook module. A Public variable declared in Sheet1‘s module becomes a property of that sheet, so other modules have to refer to it as Sheet1.gTotal.

For a value only one module needs, use Private or Dim at the top of that module instead. A short prefix such as g shows at a glance which variables are global.

The value lasts only while the project is running in memory. It’s kept between macro runs, but it resets to zero or an empty string after an End statement, when you click End on an error message or Reset in the Visual Basic Editor, and when the workbook closes. To keep a value after closing, store it in a cell or a defined name.

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” is available everywhere. So, if you have a global variable in “Module1” and now you want to use it in “Module2”, you can use it.

That means that global variables are different from the normal variables which you declare within a 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 steps below for this:

  1. First, you need to type the keyword “Global” which helps VBA to identify that this variable is global.
  2. After that, declare the name of the variable which is the same process we follow in declaring all the variables.
  3. Next, type “as” to get the list of data types.
  4. 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.
global-variable-in-vba

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 three sub procedures, we have used it with 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 Sub

And 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 (Module1 and Module2).

Now, this global variable is declared in 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 a different value.

Learn VBA in 1 Hour – If you want to kickstart your VBA learning, check out this free course that can help you learn the basics in the most effective way possible. This will help you to have a solid foundation about how VBA works.

Important Notes

  1. When you are declaring one or more global variables, you need to declare them at the top of the module, before writing any procedure, as you can see in the above example.
  2. Or, you can also have a specific module to declare all the global variables in it.
  3. You can also use the keyword “Public” to declare a global variable.
  4. Declare global variables in a standard module (Insert → Module). If you declare a Public variable in a sheet, ThisWorkbook or UserForm module, it belongs to that object, and you need to use the object name with it in other modules, like Sheet1.iCol.

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 levels of scope that you can give to a variable.

  1. Procedure Level Variables: You can only use a variable in the same procedure where you declare it (with Dim inside the procedure).
  2. Module Level Variables (Private): Makes a variable accessible from all the procedures (Module Level Availability) in a module. You declare it with Private (or Dim) at the top of the module.
  3. 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 of a three-floor building. I mostly sit in 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 in 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.

declared-the-variable-icon

Think of this module as a building where you have different floors (Procedures) and as you have already declared the variable at the start of the module, you can use it in 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 a Public/Global variable.

How Long a Global Variable Keeps Its Value

A procedure-level variable loses its value as soon as the procedure ends. But a global variable keeps its value after the procedure ends, so you can set it in one macro and read it in another. Run the first macro below and then the second one, and you will see 10 in the message box.

Global iCol As Integer

Sub setValue()
iCol = 10
End Sub

Sub showValue()
MsgBox iCol
End Sub

The value stays there while the workbook is open, but VBA clears it (back to 0 for a number and an empty string for text) when:

  • You click the Reset button in the Visual Basic Editor.
  • The code runs an End statement.
  • A runtime error stops the code and you click End.
  • You close the workbook.
  • You make some kinds of changes to the code, like adding or deleting a module-level declaration.

So, if your code depends on a global variable having a value, check it before you use it, or set it again at the start of your main macro.

Declare a Global Constant

If the value never changes, like a tax rate or a sheet name, declare it as a public constant instead of a variable. You can use it in all the modules in the same way, and no code can change it by mistake.

Public Const TAX_RATE As Double = 0.18

Sub showTax()
MsgBox "Tax: " & Range("A1").Value * TAX_RATE
End Sub

Common Mistakes with Global Variables

Declaring the same name again inside a procedure: If you use Dim iCol inside a procedure, VBA uses that local variable in that procedure and ignores the global one. So, the value you set there never reaches the other procedures.

Global iCol As Integer

Sub one()
Dim iCol As Integer ' this hides the global iCol
iCol = 10 ' changes only the local iCol
End Sub

Declaring it below a procedure: The declaration has to sit at the top of the module, above the first procedure. Otherwise, VBA shows a compile error.

Using too many global variables: Any procedure can change a global variable, which makes it hard to find where a wrong value came from. Use them only for values that many procedures really share, and pass the rest to procedures as arguments.

Frequently Asked Questions

What is the difference between Global and Public in VBA?

In a standard module, they work the same way: both make a variable available to all the procedures in all the modules. Public is the keyword you see in Microsoft’s documentation today, and it also works in class, sheet and UserForm modules, so it’s the better habit.

Why does my global variable lose its value?

The VBA project was reset. That happens when you click Reset, when the code runs End, when an error stops the code and you click End, and when you close the workbook. Set the value again at the start of the macro that needs it.

Can I use a global variable in a UserForm?

Yes. A global variable declared in a standard module can be used in the UserForm’s code, and in sheet and ThisWorkbook code too, without any prefix.

Can another workbook use my global variable?

Public variables are visible to other projects as well, but only if that workbook adds a reference to your project (Tools → References). If you want to keep them inside your own workbook, add Option Private Module at the top of the module.

Puneet Gogia

Written by

Puneet Gogia

Microsoft MVP — Excel · Founder, Excel Champs

Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.

3M+ readers helped
1,000+ tutorials written
Since 2015 teaching Excel
Author Photo

My name is Puneet and I am the founder of ExcelChamps.com. I have been awarded Microsoft MVP for Excel for my contribution to the Excel Community through this blog, YouTube Channel, and LinkedIn.