How to Declare VBA Constants in Excel

The Pi's value is 3.14, the gravitation pull of the Earth is 9.8 m/s², you have an interest in VBA, etc. These all are constants and they don't change.

In VBA you can define variables whose value can't be changed in the program. The VBA programmer defines these constant values by themselves in the program to use them again and again.

How to Define a Constant Value in VBA?

We use the keyword Const to declare a constant in VBA.

The syntax of declaring a constant variable in VBA is:

[<Scope Specifier>] Const <VarName> as <variable_type> = <value>

[<Scope Specifier>]: The scope specifier is optional. You specify the scope of the constant (public or private) if you want, otherwise don't declare it. By default, the scope of a constant is private. You can read about Scope Specifiers here.

Note: We never use the Dim keyword to declare a constant in VBA.

<VarName>: It is the name of the constant variable.

<variable_type>: The type of the constant. For example, Integer, String, Date, etc.

<value>: The value of the constant variable.

A simple example of the Constant Variable is:

Const pi as double = 3.14

Here we declared the constant value of pi in a variable named pi. Now we can use this pi in our program. The value will always be 3.14. If you try to change the value of a constant variable, the Excel VBA will pop up an error message.

VBA Constant Variable Examples

Study the below code:

Const pi As Double = 3.14
Const rad As Double = 6371

Sub Earth()
 sArea = 4 * pi * Sqr(rad)
 Debug.Print sArea
End Sub

Sub Mars()
 rad = 3389.5
 sArea = 4 * pi * Sqr(rad)
 Debug.Print sArea
End Sub

Here, we have defined two constants, pi, and the rad. The pi's value is 3.14 and rad is 6371 which is the earth's radius.

Now, when we run the first sub the Earth, it works perfectly fine and prints the surface area of the Earth.

In the next sub-Mars, we redefined the constant rad as the radius of mars is different. When we run this program, it throws an error saying, "Assignment to constant is not permitted".

How to reinitialize a constant in VBA

As you have seen in the above example that we can't assign new values to a constant. Yes, you can't.

But if you still need to use the same name as a different fixed value, just use the Const keyword before the assignment.

The below code will work perfectly.

Const pi As Double = 3.14
Const rad As Double = 6371

Sub Earth()
 sArea = 4 * pi * Sqr(rad)
 Debug.Print sArea
End Sub

Sub Mars()
 Const rad = 3389.5
 sArea = 4 * pi * Sqr(rad)
 Debug.Print sArea
End Sub

The above subroutine will work perfectly without any errors. But I don't recommend this approach. The best way is to identify the public and private constants and define them separately. And this brings us to our next segment.

Public and Private Constants in VBA

As we learned in the above examples, some constants may be universal and some may differ for different objects. Like, the pi value is constant for the entire universe, but the number of planets very solar system to solar system and radius of planets varies planet to planet.

As the Earth's radius is constant for it, not for the universe. Similarly, in programs, there will be some constants will be private to sub's and module's and some will be public constant for the entire VBA project. Your job is to identify them and declare them differently.

Let's take another example:

Public Const pi As Double = 3.14     ' This can be accessed from any module in the project
Private Const planets As Integer = 8 ' this is private to this module

Sub Earth()
 Const rad As Double = 6371    'Private to this subroutine. Can't be accessed outside
 sArea = 4 * pi * Sqr(rad)
 Debug.Print sArea
End Sub

Sub Mars()
 Const rad As Double = 3389.5 'Private to this subroutine. Can't be accessed outside
 sArea = 4 * pi * Sqr(rad)
 Debug.Print sArea
End Sub

This is a simple variable scope specifying. You can read about the variable scope setting here in detail.

So yeah guys, this is how you declare and use constants in Excel VBA. I tried to explain in a creative way. I hope I was explanatory enough.  If you have any doubt, ask in the comments section below. I will be happy to hear and reply to you.

Related Articles:

Excel VBA Variable Scope | we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers. These scope specifiers can be used to set the visibility/scope of a variable in Excel VBA.

ByRef and ByVal Arguments |When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in original argument.

Delete sheets without confirmation prompts using VBA in Microsoft Excel|Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.

Add And Save New Workbook Using VBA In Microsoft Excel 2016|In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc

Display A Message On The Excel VBA Status Bar The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering

Turn Off Warning Messages Using VBA In Microsoft Excel 2016|This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.