You should declare constants in your VBA project when you need a value defined and you know the value won’t change. I often find myself using VBA constants when working with scientific equations. To declare constants, you use the VBA Const and Public Const statements. Recall that you typically declare variables using Dim statements, instead.
Unlike variables, you can’t change the value of a VBA constant once it’s declared. This tutorial will show you how to declare constants and will explain the difference in scope between a constant declared using a Const statement and one declared using a Public Const statement.
- Declaring Constants with VBA
- Common Errors with Constants
Declaring Constants with VBA
There are 3 ways to declare a constant in VBA. Each method has a different scope. The first method only makes the constant available in the procedure (subroutine or function) where it’s declared. These are called procedure level constants.
The second method lets you access the constant from any procedure within a single module. That’s why these constants are called module level constants.
The final method makes the constant available in all modules and procedures in your entire workbook. Constants with this highest level of scope are called project level constants.
Let’s explore these three options now.
Procedure Level Constants with VBA Const
Procedure level constants in VBA are declared inside your procedure using the
Const keyword. The general syntax is
Const Name as Type = Value where
Sub Force_Gravity() Const Gravity As Double = 9.80665 'm/s^2, standard acceleration of gravity constant Dim Force As Double Dim Mass As Double 'Newton's 2nd Law: F=ma Mass = 100 'kg Force = Mass * Gravity Debug.Print Force & " Newtons" End Sub
In this example, we declare a constant,
Const declaration. It’s inside the procedure. By declaring the constant inside our
Make powerful spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
If you try to reference the
Sub Color1() Const color As String = "Blue" Debug.Print color End Sub Sub Color2() Const color As String = "Green" Debug.Print color End Sub
Even though both constants are named
Module Level Constants with VBA Const
The syntax for a module level constant is the same as a procedure level constant, but the placement of the declaration changes. Module level constants are accessible in any subroutine or function in your module. Here’s a good example illustrating how to declare a module level constant:
Const c As Double = 299792458 'm/s Speed of Light in a Vacuum Sub SpecialRelativity100() 'E=mc^2 Dim mass As Double Dim Energy As Double mass = 100 'kg Energy = mass * c ^ 2 Debug.Print Energy End Sub Sub SpecialRelativity50() 'E=mc^2 Dim mass As Double Dim Energy As Double mass = 50 'kg Energy = mass * c ^ 2 Debug.Print Energy End Sub
Const declaration was placed at the top of your module. It’s declared outside of any subroutine or function. This is the key for module level constants. Declarations must be made at the top of your module and must not be inside a procedure. Once you do that, you can access the constant from any procedure (subroutine or function) in that module.
In this example, we used Einstein’s famous E=mc2 special relativity formula to calculate the energy of an object with two different masses. We succesfully referenced the speed of light constant,
You can start to see the appeal of using constants with this example. You don’t want to accidentally change the value of
Project Level Constants with VBA Public Const
Project level constants can be accessed by any subroutine or function inside your workbook, even if they’re in different modules. This is going to be a fun scientific example so stick with me. Let’s say you have a module named
Public Const declaration. This module will allow you to calculate the energy of an object based on its mass.
Public Const c As Double = 299792458 'm/s Speed of Light in a Vacuum Sub SpecialRelativity25() 'E=mc^2 Dim mass As Double Dim Energy As Double mass = 25 'kg Energy = mass * c ^ 2 Debug.Print Energy & " Joules" End Sub
The Public keyword is the secret to making your constants usable across multiple modules.
Our second module,
Const h As Double = 6.62607015 * 10 ^ (-34) 'J/Hz - Planck Constant Sub PlanckEinstein500() 'v=c/lambda and E=hv 'E=h*c/lambda Dim lambda As Double 'nanometers, wavelength Dim Energy As Double lambda = 500 Energy = h * c / (lambda * 10 ^ -9) Debug.Print Energy & " Joules" End Sub
Even though we never defined the speed of light constant,
Public Const declaration in our
On a related note, the code above illustrates how you can use formulas to define constants, as long as your formulas don’t contain any variables.
Common Errors with Constants
This section covers a couple common issues people encounter when working with VBA constants. Read it carefully to avoid making the same errors on your project.
Unlike variables, you must set a constant equal to a value when declaring it. If you don’t set your constant to a value using the equal sign during your declaration, the declaration line will turn red and you’ll get a compile error reminding you the code was expecting an equal (=) sign:
Once a constant is declared in VBA, you cannot change its value. If you try to change the value stored in a constant, you’ll get a compile error stating “assignment to constant not permitted.”
Overwriting Constants (Precedence)
We just got done saying once a constant is declared in VBA, you can’t change its value. While that’s true, it’s worth noting that even module and project level constants can be overwritten by procedure level constants. For example:
Const MyColor As String = "Red" Sub Color_test1() Const MyColor As String = "Yellow" Debug.Print MyColor 'Prints Yellow End Sub Sub Color_test2() Debug.Print MyColor 'Prints Red End Sub
Even though we already have a module level constant,
If you were to run the
Similar behavior exists with project level constants assuming you had a
Public Const declaration in a separate module. Basically, procedure level constants take precedence over all other constants and module level constants take precedence over project level constants. Another way to think about this is the closer you are to your subroutine or function, the more power your constant declaration has (procedure > module > project).
Public Constants in Object Modules
Unfortunately, you’re not allowed to declare public constants inside object modules, like
Public Const inside any object module listed under the “Microsoft Excel Objects” group in your Project Explorer will create the following compile error:
We’ve used a couple scientific equations to illustrate when you might find it beneficial to declare constants in your VBA project. We showed you the difference between
Public Const and how placing your constant declarations in different spots can impact how you’re able to use your constants. We also explained a couple common errors people encounter when working with constants, including how once a constant is assigned a value, you cannot change it’s value later. That’s what variables are for!