[VBA] How to Share Variables Between Modules: Using Global Variables

2025年12月26日

Background

When organizing Excel VBA code into multiple modules, I encountered a situation where I wanted to use a variable obtained in Module 1 for processing in Module 2.

Specifically, I used the Call statement to run a subroutine in another module, and I wanted to use the value assigned there in the original calling process.

After investigating and testing, I solved this by using Global Variables.

The Code That Didn’t Work

Here is the example I tried first, which failed.

' Module1
Sub MySub()
    Dim myVar As String
    myVar = "Hello, World!"
End Sub

' Module2
Sub MyOtherSub()
    Call MySub
    MsgBox myVar
End Sub

In this case, myVar defined inside MySub is a local variable. It is destroyed the moment MySub finishes running. Therefore, when MyOtherSub tries to reference myVar, it is not recognized, and an error occurs (or it is empty).

Solution: Use Global Variables

The solution was to declare the variable outside the subroutine using the Public keyword. By doing this, the variable becomes accessible from all modules.

' Module1
' Define a global variable
Public myVar As String

Sub MySub()
    myVar = "Hello, World!"
End Sub

' Module2
Sub MyOtherSub()
    Call MySub
    MsgBox myVar
End Sub

By doing this, the value assigned in MySub is retained and can be used in MyOtherSub without any issues.

What is a Global Variable?

A Global Variable is a variable declared outside of any Sub or Function using the Public keyword within a module.

This allows the variable to be referenced and modified from all procedures, functions, and modules in the project.
Global variables must generally be declared in a Standard Module. They may not function as intended if declared in Class Modules or Sheet Modules.

Important Notes

Management: While global variables are very convenient, they can lead to unintended overwrites and bugs because they can be changed from anywhere. Use them with care.
Naming: Since the scope is wide, I recommend establishing strict naming conventions to avoid duplicate names or conflicts.

Summary

If you want to share variables across modules in VBA, using Global Variables is the easiest and most reliable method.

Define the variable as Public at the top of a Standard Module.
You can access that value from any subroutine.

This is a very effective technique when you want to use a value obtained in a subroutine called by a Call statement in another module. Please try using it in your VBA projects.