Excel VBA Global Constant (2 Convenient Ways)

In this article, we will demonstrate 2 convenient ways to declare global constants in Excel VBA. We’ll also show how to declare VBA global variables, and discuss why they sometimes lose value.

What is a Global Constant in Excel?

In Excel VBA, a global constant is a constant that is declared at the beginning of the VBA module, remains the same in the module or in all modules in the workbook, and can be used in all procedures of a module or all modules in the workbook. Global constants are very useful when we have values that remain unchanged, and need to access them from different procedures or modules.

How to Declare Excel VBA Global Constants: 2 Convenient Ways

In the dataset below, we have the IDs, Names and CGPAs of multiple students. We will use the values of this dataset as global constants.

Method 1 – Declaration in a Module

In this method, we will declare a global constant in VBA that runs in all subroutines or procedures of a particular module. We will use the Const keyword while declaring the constants.

Steps:

To write the VBA code, we need to launch the VBA Macro Editor in the workbook where the code will be applied.

• Press Alt + 11 to open the VBA Editor.
• Click Insert then Module.
• Paste the following code in the VBA Macro Editor that opens.
• Click the Run button or press F5 to run the code.

Option Explicit
'constant declaration in module
Const ID1 As Integer = 1618
Const Name1 As String = "Wendy"
Const CGPA1 As Double = 3.96
Sub Constant_Module_declaration1()
    'show constants
    MsgBox "Student ID " & ID1 & " is " & Name1
End Sub

A message with the name and ID of a student will pop up.

Now, these three global constants (ID1, Name1, and CGPA1) can be accessed from all procedures of this module.

• Attach the following code in the VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Sub Constant_Module_declaration2()
    'show constants
    MsgBox "CGPA of " & Name1 & " is " & CGPA1
End Sub

A message with the name and CGPA of a student is returned.

Method 2 – Public Declaration

In this method, we will declare a global constant in VBA that can be accessed from all modules within the workbook. We will use the Public and Const keywords in the VBA code.

• Paste the code below in your VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Option Explicit
'constant public declaration
Public Const ID2 As Integer = 1615
Public Const Name2 As String = "Daniel"
Public Const CGPA2 As Double = 3.76
Sub Constant_Public_declaration1()
    'show constants
    MsgBox "Student ID " & ID2 & " is " & Name2
End Sub

The ID and name of a student are returned in a message box.

Now these three global constants (ID2, Name2, and CGPA2) can be accessed from all modules of this workbook.

• Paste the following code into a new VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Sub Constant_Public_declaration2()
    'show public constants
    MsgBox "CGPA of " & Name2 & " is " & CGPA2
End Sub

A message box containing the name and CGPA of a student pops up.

How to Declare Excel VBA Global Variables

In this section we will demonstrate how to declare global variables in Excel VBA.

Method 1 – Declaration in a Module

First, we will declare a global variable that runs in all subroutines or procedures in a particular module. We will declare the global variables at the beginning of the VBA code.

• Paste the following code in your VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Option Explicit
'variable declaration in module
Dim ID3 As Integer
Dim Name3 As String
Dim CGPA3 As Double
Sub Variable_Module_declaration1()
    'set variables
    ID3 = ActiveSheet.Range("B12")
    Name3 = ActiveSheet.Range("C12")
    'show variables
    MsgBox "Student ID " & ID3 & " is " & Name3
End Sub

A message with the name and ID of a student is displayed.

Now these three global variables (ID3, Name3, and CGPA3) can be accessed from all procedures of this module.

• Enter the following code in your VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Sub Variable_Module_declaration2()
    'set variables
    Name3 = ActiveSheet.Range("C13")
    CGPA3 = ActiveSheet.Range("D13")
    'show variables
    MsgBox "CGPA of " & Name3 & " is " & CGPA3
End Sub

A message with the name and CGPA of a student is displayed.

Method 2 – Public Declaration

In this method, we will declare a global variable in VBA that can be accessed from all modules within the workbook. We will use the Public keyword in the VBA code.

• Insert the following code in your VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Option Explicit
'variable public declaration
Public ID4 As Integer
Public Name4 As String
Public CGPA4 As Double
Sub Variable_Public_declaration1()
    'set variables
    ID4 = ActiveSheet.Range("B6")
    Name4 = ActiveSheet.Range("C6")
    'show variables
    MsgBox "Student ID " & ID4 & " is " & Name4
End Sub

A message with the ID and name of a student is displayed.

Now these three global variables (ID4, Name4, and CGPA4) can be accessed from all modules of this workbook.

• Paste the following code into a new VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Sub Variable_Public_declaration2()
    'set public variables
    Name4 = ActiveSheet.Range("C14")
    CGPA4 = ActiveSheet.Range("D14")
    'show variables
    MsgBox "CGPA of " & Name4 & " is " & CGPA4
End Sub

A message with the name and CGPA of a student is displayed.

Excel VBA Global Variable Loses Value

Sometimes the global variable may not store a value or the value may be reset. This generally happens when there are multiple variables with the same name in different subroutines.

• Paste the following code in your VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Option Explicit
'global variable declaration
Dim GlobVar As Double
Sub Value_Loss1()
    'local variable declaration
    Dim GlobVar As Double
    'this will modify the local variable, not the global one
    GlobVar = 3.96
    'show result
    MsgBox "Highest CGPA is " & GlobVar
End Sub

The value of the highest CGPA is returned.

• Insert the following code in your VBA Macro Editor.
• Click the Run button or press F5 to run the code.

Sub Value_Loss2()
    'this will display the global variable value
    MsgBox "Highest CGPA is " & GlobVar
End Sub

A message with the value of the highest CGPA is returned.

Things to Remember

• Unlike for variables, the value of a global constant must be set at the beginning of your code.
• Once a global constant is declared, its value cannot be changed. However, global constants may be overwritten by procedure level constants.
• It is good practice to maintain a particular module in VBA in which to declare global constants.

Frequently Asked Questions

1. Can I change the value of a global constant during runtime?
No.

2. What data types can I use for global constants?
Various data types such as Integer, Long, Double, String, Boolean, Date, etc.

3. Can I use global constants to define arrays?
Not directly. However, we can use a global constant as a string, then in the subroutine use the VBA SPLIT function to convert the string into an array.

Download Practice Workbook