Constants in VBA – Declaration, Types. Scope

In the previous article, we learned about the variables in the VBA Programming language. Variables contain values that can be changed during the program execution. The next important concept is the use of constants in VBA.

So let us start learning.

What are the Constants in VBA?

Constants contain values in the program that do not change their value during the execution. The value of a constant can be changed once assigned at the time of its declaration.

Constants can be Local, Module level as well as Public. It has a data type just like any variable in VBA. The only difference between a constant and a variable is that the value of a constant cannot be changed once assigned at the time of declaration.

You can download this practice workbook to follow along with the article.

Types of Constants in VBA

There are two types of constants in VBA.

• User-Defined Constants – These are declared using the Const keyword and As keyword. The user assigns the value at the time of declaration.
• Intrinsic Constants – These are the system constants built into the VBA language. The prefix Vb is used to identify them. For Example, VbOkOnly in Msgbox function.

Syntax and Declaration

Const <const_name> As <const_DataType> = <const_value>

The following points explain the declaration of a constant in VBA.

• Const – We use the Const keyword instead of Dim to declare a constant
• <const_name> – This is the constant’s name. It follows the same naming conventions as a variable in VBA.
• As – This is also a keyword used in the constant declaration statement to tell the data type of the continuous.
• <const_DataType> – This is the data type of the constant. It can be Byte, Integer, Long, Single, Double, String, etc. Datta type of Constant depends on the amount of memory space and the kind of data we want to store in the constant.
• = – This is the assignment operator (=)
• <const_value> – This is the value of the constant that we are assigning at the time of declaration. It cannot be changed once assigned.

Examples of Constants in VBA

In this section of the blog, we would implement some examples to learn the usage of constants in the VBA Programming language.

Example 1 – Local Constant in Sub Procedure

When we declare a constant in a Sub Procedure, it becomes local to it and we cannot access a local constant from anywhere outside that sub procedure.

Follow these steps to enter the code in the VBA editor.

• Press Alt and F11 keys to open the VBA editor. Select the VBA project for the current workbook.
• Go to the Insert tab on the ribbon and click on Module to insert a new module.
• Copy and Paste the following procedure into the New Module.

Sub Example()

'declaring the constant string and assigning value

Const Website_Name As String = "ExcelUnlocked"

'printing the value of constant string using message box

Call MsgBox(Website_Name)

End Sub

When you run the above sub procedure, you get the following message box on the screen.

Now, if you try to reassign the value of Website_Name, an error message shows.

When we try to access the value of this local constant declared in one Sub procedure from another Sub procedure, then it is not accessible.

Now when we run Example_2(), we see that the message box did not access the value of Website_Name.

Example 2 – Module Level Constant

A Module level constant can be accessed from all the procedures of that module. To declare a Module level constant, we need to code the constant declaration statement at the top of the module and not inside of any Sub procedure.

'declaring at the top
Const Website_Name As String = "ExcelUnlocked"

Sub Example()

'printing the value of constant in one procedure

Call MsgBox(Website_Name)

End Sub

Sub Example_2()

'accessing the value of constant in another sub procedure

Call MsgBox(Website_Name)

End Sub

When you run any of the two Sub procedures, you see that both the Sub procedures can print the value of Website_Name because it is accessible from both.

Example 3 – Application level Constant

An Application Level constant can be accessed from all the Sub Procedures of all the Modules of the VBA Project. We need to use the Public keyword to make it accessible from all the modules of the VBA project.

Public Const <const_name> As <const_DataType> = <const_value>

This brings us to an end.

Thank you for reading.