VBA Const

What is VBA Const (Constants)?

Variables are the heart and soul of any programming language. I have never seen a coder or developer who doesn’t rely on variables in their project or program. As a coder even I am not different from others, I too use variables 99% of the time. We all using the “Dim” statement we declare VBA variables. All these while in our articles we have shown you about declaring variables through the “Dim” statement. But we declare variables using another way as well. In this article, we will show you the alternative way of declaring variables i.e. “VBA Constant’ method.

“Const” stands for “Constants” in VBA. Using the VBA “Const” word we can declare variables just like how we declare variables using the “Dim” keyword. We can declare this variable at the top of the module, in between the module, in any sub procedure and function procedure and also in the class module.

To declare the variable we need to use the word “Const” to declare the constant value. Once the variable is declared and assigned a value we cannot change the value throughout the script.

Syntax of Const Statement in VBA

Const statement is slightly different than the “Dim” statement. To understand it better let look at the well-written syntax of the VBA Const statement.

• Const: With this word, we initialize the process of declaring the constants.
• Variable Name: This is as usual as naming the variable. We rather call it as Const Name instead of Variable Name.
• Data Type: What kind of value our declared variable is going to hold.
• Variable Name: Next and final part is what is the value we are going to assign to the variable we have declared. The assigned value should be as per the data type.

Condition of Constants in VBA

• The name of the constant we are declaring can contain a maximum of 256 characters of length.
• The name of the constant cannot start with a number, rather it should start with the alphabet.
• We cannot VBA reserved keywords to declare the constants.
• The constant name should not contain any space or special characters except underscore character.
• Multiple constants can be declared with a single statement

Examples of Const Statement in VBA

Let declare your first variable through the VBA Const statement. We can declare constants at the subprocedure level, module level, and at the project level as well.

Now, look at how to declare at the Sub Procedure level.

In the above example, constant “k” is declared inside the subprocedure named as Const_Example1(). And we have assigned the value as 75.

Now, look at the module level Constant declaration.

At the top of the module, I have declared 3 constants in the module “Module 1”.

These VBA constants can be accessed in the “Module 1” at any number of Sub Procedures within this module i.e. “Module 1”.

Make Constants Available Across Modules

Once the constants are declared at the top of the VBA module we can access those constants within the module with all the subprocedures.

But how can we make them available with all the modules in the workbook.’

To make them available across modules we need to declare them with the word “Public”.

Now the above variable not only available with Module 1 rather we can use them with Module 2 as well.

Difference Between VBA Dim Statement & Const Statement

You must have a doubt what is the difference between traditional “Dim” statement and new “Const” statement in VBA.

We have one difference with these i.e. look at the below image.

In the first image as soon we declare a variable we have assigned some values to them.

But in the second image using the “Dim” statement first we have declared variables.

After declaring a variable we have assigned values separately in the different lines.

This is how we can use the VBA “Const” statement to declare constants which are a similar way of declaring variables with the “Dim” statement.

Recommended Articles

This has been a guide to VBA Const (Constants). Here we discuss how to use Constant statement in VBA along with examples and also, its key differences with Dim statement. Below are some useful excel articles related to VBA –

• VBA Exit Sub
• VBA Declare Array
• VBA DoEvents
• VBA COUNTA
• VBA Global Variables
• VBA Option Explicit
• VBA Text
• VBA UsedRange