VBA Const Statement

Learn the importance of VBA’s Const statement in optimizing your code. Here is the detailed post to learn declaring the Constants using VBA.

The VBA Const Statement

Purpose

The Const statement in VBA is used to declare a constant or fixed value that cannot be changed during the execution of the code. This means that the value assigned to a constant will remain the same throughout the code and cannot be altered by any means. The purpose of using the Const statement is to assign a meaningful name to a constant value, making the code more readable, maintainable, and less prone to errors.

Syntax

The syntax of the Const statement is as follows:

Const ConstantName As DataType = ConstantValue

Where:
ConstantName: the name of the constant variable that must adhere to VBA naming conventions.
DataType: the data type of the constant value (e.g., String, Integer, Boolean).
ConstantValue: the value assigned to the constant variable.

Examples of the VBA Const Statement

Example 1: Declaring a Constant String

Suppose we want to assign the value “John” to a variable called “EmployeeName,” but we do not want this value to be changed throughout the code. We can achieve this using the Const statement as follows:

Const EmployeeName As String = "John"

In this example, “EmployeeName” is the constant name, “String” is the data type, and “John” is the constant value assigned to the variable. Now, if we try to change the value of “EmployeeName” in the code, it will result in an error.

Example 2: Declaring a Constant Integer

In this example, we will declare a constant integer and use it to calculate the area of a rectangle.

Const Length As Integer = 10
Const Width As Integer = 5
Dim Area As Integer
Area = Length * Width
MsgBox "The area of the rectangle is " & Area & " square units."

The result displayed in the message box will be “The area of the rectangle is 50 square units.” Again, any attempt to change the value of “Length” or “Width” will result in an error.

Example 3: Using Constants in Formulas

We can also use constants in formulas to make our code more readable. Let’s say we want to calculate the perimeter of a square, and we know that all sides are equal. Instead of hard-coding the value of the side, we can declare a constant variable and use it in the formula as follows:

Const Side As Integer = 4
Dim Perimeter As Integer
Perimeter = Side * 4
MsgBox "The perimeter of the square is " & Perimeter & " units."

Now, if we need to change the value of the side, we can simply change it in one place (in the constant declaration) instead of multiple places in the code.

Example 4: Using Constants in Loops

We can also use constants in loops to save time and make our code more efficient. In the example below, we have a list of employees and want to display their names using a loop. Instead of typing out the employee names, we can declare a constant array and use it in the loop as follows:

Const EmployeeNames As Variant = Array("John", "Jane", "Mark")
For i = 0 To UBound(EmployeeNames)
    Debug.Print EmployeeNames(i)
Next i

The result of this loop will print out the names “John,” “Jane,” and “Mark” without having to repeat the names in the code.

Example 5: Using Conditional Statements with Constants

We can also use constants in conditional statements to make the code more readable. In the example below, we have a program that checks if a person is eligible for a discount based on their age. We can assign the minimum age requirement for the discount as a constant and use it in the conditional statement as follows:

Const MinAge As Integer = 18
Dim Age As Integer
Age = InputBox("Enter your age:")
If Age >= MinAge Then
    MsgBox "Congratulations! You are eligible for the discount."
Else
    MsgBox "Sorry, you do not meet the age requirement for the discount."
End If

Now, if we need to change the minimum age requirement, we can do so in one place (in the constant declaration) without having to modify the conditional statement.

Important Notes & Remarks

• We cannot assign a value to a constant variable more than once in a program. Once a value is assigned, it cannot be changed.
• The value assigned to a constant variable must be known at compile time.
• We cannot use the ‘Dim’ or ‘Public’ keyword with a constant variable.
• Constants are stored in the read-only memory (ROM) and are not allocated memory at run-time.

The Const statement is an essential tool for declaring constant values in VBA. It helps make the code more readable, maintainable, and efficient. We can use constants in various ways, including calculations, loops, conditional statements, and more. However, it is essential to note that constants, once declared, cannot be modified, which can also be a disadvantage in some cases. Therefore, it is crucial to use constants wisely and in scenarios where the value assigned does not need to be changed.

Did you find this post on the VBA Const statement informative and helpful? Have you used constants in your VBA code before? Please share your feedback and views in the comments section below. Thank you!