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

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 Name is the name of your constant, Type is the data type you want to declare and Value is the value you want to assign to the constant. Let’s walk through an example.

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, Gravity, representing the constant acceleration of gravity as 9.80665 m/s2. Notice the placement of the Const declaration. It’s inside the procedure. By declaring the constant inside our Force_Gravity routine, we’re making it so the value stored in Gravity cannot be accessed in any other routine.

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

If you try to reference the Gravity constant from another subroutine or function, even one located inside the same module, it won’t have a value. That means you’re allowed to define multiple procedure level constants with the same name and different values as long as they’re in different procedures. Take this code, for example:

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 color, you’re able to store different strings in them because they’re both procedure level constants. They only retain their value inside the procedure they were created in.

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

Notice the 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, c, in both subroutines.

You can start to see the appeal of using constants with this example. You don’t want to accidentally change the value of c when accessing it in different procedures! That’s the risk you’d run into if you had declared it as a variable.

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 mMassEnergy and a module named mWavelengthEnergy. You can see both of our modules in this screenshot from our Project Explorer Pane.

VBA public const across modules

In the mMassEnergy module, you’ll declare a project level constant using the 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, mWavelengthEnergy, will use the speed of light public constant, c, but it will calculate the energy of light given its wavelength using the Planck-Einstein equation, E=hv. Unlike the module level constant, c, the Planck constant, h, is declared as a procedure level constant, so it can only be used within the mWavelengthEnergy 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, c, in this module, our subroutine successfully calculates the energy as 3.78370639456939E-19 Joules. It’s able to do this with the help of our Public Const declaration in our mMassEnergy module.

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.

Initializing Constants

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:

VBA Const not initialized

Reassigning Constants

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.”

VBA 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, MyColor, defined with a constant value of “Red”, the Color_test1 macro declares a new constant with the same name. It doesn’t treat this as a duplicate declaration. Instead of generating an “ambiguous name detected” error, when you run this macro, “Yellow” is printed to the VBA Immediate Window.

If you were to run the Color_test2 macro, though, it would print the value stored in the module level MyColor constant (“Red”).

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 ThisWorkbook or Sheet1. For example, let’s say you’re working on a VBA project in Excel. Trying to declare a Public Const inside any object module listed under the “Microsoft Excel Objects” group in your Project Explorer will create the following compile error:

VBA public constants not allowed in object modules

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 Const and 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!

I hope you found this tutorial helpful. If you haven’t already done so, please subscribe to our free wellsrPRO VBA Training using the form below and share this article on Twitter and Facebook.