Excel VBA Global Constant (2 Convenient Ways)

In this article, we have explored how to declare Excel VBA global constant. We have demonstrated 2 convenient ways to declare global constants. Then, we explored Excel VBA global variables. Lastly, we talked about global variables losing value.

We use Excel VBA global constants to provide meaningful names to fixed values and ensure consistency throughout the modules of the workbook.

What is Global Constant in Excel?

In Excel VBA, a global constant is such a constant that is declared at the beginning of the VBA module and the constant remains the same in the module or in all of the modules within the workbook. You can use global constants in all procedures of a module or in all procedures of all modules. You should use global constants when you have values that remain unchanged, and you need to access them from different procedures or modules.

How to Declare Excel VBA Global Constant: 2 Convenient Ways

In this section, we will show you 2 different ways to declare Excel VBA global constant. In the dataset, we have student IDs, names and CGPAs of multiple students. We will use the values of this dataset as global constants.

1. Declaration in Module

In this method, we will show you how to 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.

• First, you have to launch the VBA Macro Editor from your workbook to write the VBA code.
• Paste the following code in your VBA Macro Editor.
• Press the Run button or F5 key 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

• You will see a message with the name and ID of a student.

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

• Attach the following code in your VBA Macro Editor.
• Press the Run button or F5 key to run the code.

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

• You will see a message with the name and CGPA of a student.

2. Public Declaration

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

• Paste the following code in your VBA Macro Editor.
• Press the Run button or F5 key 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

• You will see the ID and name of a student.

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.
• Press the Run button or F5 key to run the code.

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

• You will see the name and CGPA of a student.

How to Declare Excel VBA Global Variable

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

1. Declaration in Module

In this method, we will show you how to declare a global variable in VBA, 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.
• Press the Run button or F5 key 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

• You will see a message with the name and ID of a student.

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

• Attach the following code in your VBA Macro Editor.
• Press the Run button or F5 key 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

• You will see a message with the name and CGPA of a student.

2. Public Declaration

In this method, we will show you how to 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.

• Paste the following code in your VBA Macro Editor.
• Press the Run button or F5 key 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

• You will see the ID and name of a student.

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.
• Press the Run button or F5 key 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

• You will see the name and CGPA of a student.

Excel VBA Global Variable Loses Value

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

• Paste the following code in your VBA Macro Editor.
• Press the Run button or F5 key 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

• You will see the value of the highest CGPA.

• Attach the following code in your VBA Macro Editor.
• Press the Run button or F5 key to run the code.

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

• You will see the value of the highest CGPA.

Things to Remember

There are a few things to remember while working with Excel VBA global constants.

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

Frequently Asked Questions

1. Can I change the value of a global constant during runtime?
No, you cannot change the value of a global constant during the runtime of the code.

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

3. Can I use global constants to define arrays?
No, global constants cannot be used to declare arrays directly. However, you can use the global constant as a string. Then, in the subroutine you can use the VBA SPLIT function to convert the string into an array.

Download Practice Workbook

You can download this practice workbook while going through the article.

Conclusion

In this article, we have explored 2 efficient ways to declare Excel VBA global constant. If you have any questions regarding this essay, don’t hesitate to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.