Constants in VBA

A constant is a named value that remains fixed during the execution of a VBA program.

For example:

Const PI As Double = 3.14159

Here, PI represents a fixed value.

Constants are useful when the same fixed value is used multiple times in a program.

They can help to:

• Make code easier to understand.
• Avoid repeating fixed values.
• Make programs easier to maintain.
• Reduce accidental changes to fixed values.
• Give meaningful names to important values.

The Const keyword is used to declare a constant in VBA.

Const PASS_MARKS As Integer = 40

Here:

• Const declares the constant.
• PASS_MARKS is the constant name.
• Integer is the data type.
• 40 is the fixed value.

The basic syntax is:

Const constantName As DataType = value

Example:

Const MAX_MARKS As Integer = 500

The constant MAX_MARKS represents 500.

A constant can store a text value.

Const COURSE_NAME As String = "ADCA"

MsgBox COURSE_NAME

The constant COURSE_NAME stores the text ADCA.

An Integer constant can store a fixed whole-number value.

Const PASS_MARKS As Integer = 40

MsgBox PASS_MARKS

The value of PASS_MARKS remains fixed.

A constant can also store a decimal value using the Double data type.

Const PI_VALUE As Double = 3.14159

MsgBox PI_VALUE

Double constants are useful for fixed decimal values.

The Currency data type can be used for fixed financial values.

Const REGISTRATION_FEE As Currency = 500

MsgBox REGISTRATION_FEE

This can be useful when a fixed fee is used throughout a program.

A constant can also represent a fixed Boolean value.

Const SYSTEM_ACTIVE As Boolean = True

If SYSTEM_ACTIVE Then

    MsgBox "System is active"

End If

The constant represents a fixed logical value.

VBA constants can also represent fixed date values.

Const START_DATE As Date = #1/1/2026#

MsgBox START_DATE

The constant represents the specified date.

The main difference between a constant and a variable is whether the value can be changed after declaration.

A variable can be assigned a new value.

Dim marks As Integer

marks = 50

marks = 80

MsgBox marks

The value of marks changes from 50 to 80.

A constant is assigned its value when it is declared.

Const PASS_MARKS As Integer = 40

MsgBox PASS_MARKS

You cannot later assign another value to PASS_MARKS.

A constant cannot be assigned a new value after it has been declared.

Const MAX_MARKS As Integer = 500

MAX_MARKS = 600

The second assignment is invalid because MAX_MARKS is a constant.

A constant can be declared inside a procedure.

Sub CheckResult()

    Const PASS_MARKS As Integer = 40

    If 75 >= PASS_MARKS Then

        MsgBox "Pass"

    End If

End Sub

This constant is declared inside the procedure.

A constant can also be declared at the module level so that procedures in the module can use it.

Option Explicit

Const PASS_MARKS As Integer = 40

Sub CheckResult()

    If 75 >= PASS_MARKS Then

        MsgBox "Pass"

    End If

End Sub

The constant is declared outside the procedure.

A constant can be declared as Public in an appropriate module when it needs to be available throughout the VBA project.

Public Const COMPANY_NAME As String = "Soopro Pathshala"

This can be useful when several procedures need to use the same fixed value.

A module-level constant can also be declared as Private.

Private Const DEFAULT_FEE As Currency = 1000

A Private constant is intended to be used within its containing module.

Constants can be used in mathematical calculations.

Sub CalculatePercentage()

    Const TOTAL_MARKS As Double = 500

    Dim marks As Double
    Dim percentage As Double

    marks = 425

    percentage = marks / TOTAL_MARKS * 100

    MsgBox percentage

End Sub

Using a named constant makes the formula easier to understand.

Constants can be used in If conditions.

Sub CheckMarks()

    Const PASS_MARKS As Integer = 40

    Dim marks As Integer

    marks = 65

    If marks >= PASS_MARKS Then

        MsgBox "Pass"

    Else

        MsgBox "Fail"

    End If

End Sub

The condition compares the student's marks with the fixed passing mark.

Constants can be useful in applications where a fixed fee is used.

Const ADMISSION_FEE As Currency = 500

Sub ShowFee()

    MsgBox "Admission Fee: " & ADMISSION_FEE

End Sub

If the fee is changed in the future, the declaration can be updated in one place.

Constants can represent fixed limits used in a program.

Const MAX_STUDENTS As Integer = 100

Sub CheckStudents()

    Dim totalStudents As Integer

    totalStudents = 75

    If totalStudents <= MAX_STUDENTS Then

        MsgBox "Limit not exceeded"

    End If

End Sub

Constants are also useful for fixed text values.

Const COURSE_NAME As String = "Excel VBA"

Sub ShowCourse()

    MsgBox "Course: " & COURSE_NAME

End Sub

This avoids repeatedly typing the same text throughout the program.

Using a meaningful constant name can make code easier to understand.

Less descriptive:

If marks >= 40 Then

Using a named constant:

Const PASS_MARKS As Integer = 40

If marks >= PASS_MARKS Then

The second example clearly explains what the value 40 represents.

Use meaningful names for constants.

Examples:

PASS_MARKS
MAX_MARKS
MAX_STUDENTS
ADMISSION_FEE
COURSE_NAME
COMPANY_NAME

Using uppercase letters is a common convention for making constants easy to recognize, although VBA does not require constant names to be uppercase.

Beginners can make several mistakes when working with constants.

• Trying to change the value of a constant.
• Using an unclear constant name.
• Forgetting the initial value.
• Using a constant when the value actually needs to change.
• Declaring the constant in a scope where it cannot be accessed as intended.

Let's use a constant to define the passing marks for a student result system.

Sub StudentResult()

    Const PASS_MARKS As Integer = 40

    Dim marks As Integer

    marks = Range("B2").Value

    If marks >= PASS_MARKS Then

        Range("C2").Value = "Pass"

    Else

        Range("C2").Value = "Fail"

    End If

End Sub

The passing mark is defined once and then used in the condition.

• Use constants for values that should remain fixed.
• Give constants meaningful names.
• Use appropriate data types.
• Keep commonly used project constants organized.
• Use constants instead of repeatedly writing the same fixed value.
• Do not use a constant when the value needs to change.

A simple workflow for creating a constant is:

1. Identify a value that should remain fixed.
2. Choose a meaningful constant name.
3. Choose an appropriate data type.
4. Declare it using Const.
5. Assign its fixed value.
6. Use the constant wherever required.

Const MAX_MARKS As Integer = 500

Dim marks As Integer

marks = 425

MsgBox marks & " / " & MAX_MARKS

A constant is a named value that remains fixed during program execution. In VBA, constants are declared using the Const keyword.

Constants can represent numbers, text, dates, Boolean values, and other appropriate fixed values. They are especially useful for values such as passing marks, maximum limits, fixed fees, company names, and other settings that should not change during execution.

Using meaningful constants makes VBA code more readable and easier to maintain.