VBA Constants

A constant is a named value that does not change. The value of a constant is determined at design-time and cannot be altered during the execution of a program. Constants should be used instead of hard-coding literal values and should be used instead of variables when the value should not change.

Creating Constants

To create a constant in VBA use the Const keyword, give the constant a name, give the constant an optional type, and assign the constant a value. Constants must be assigned a value on the same line they are declared. Constants cannot be used with objects, arrays, or user-defined types. Constants can be declared Public or Private outside of a procedure, giving them Public or Module scope respectively, but cannot be declared Public inside Class modules. Constants can be declared inside a procedure and have procedure scope.

Constant declaration syntax: [Public|Private] Const CONSTANT_NAME [As Type] = Value

Option Explicit

Public Const A  As Long = 1 'Public Scope
Private Const B As Long = 2 'Module Scope
Const C         As Long = 3 'Module Scope

Public Sub Example()
    
    Const PI As Double = 3.14159265359 'Procedure Scope

End Sub

Constants are normally defined using a literal value but they can also be defined in terms of other previously declared constants.

Const PI  As Double = 3.14159265359
Const PI2 As Double = PI * 2

Where to Use Constants

A constant should be used whenever a program needs to use a value that will not change. Literal values should be replaced with constants and constants should be used in place of variables when the value of the variable should not change.

In Place of Literals

Hard-coded literal values make code difficult to read, understand, and maintain. A hard-coded literal can lack context which opens up the possibility to lose track of what the literal value means. To an outsider reading the code, the origin and purpose of a literal value may be completely unknown, which can cause confusion. Another major problem with using hard-coded literal values is that they can repeat throughout the body of code and if the value ever needs to be updated it can be extremely difficult to track down and accurately update every place it was used. Literal values may also overlap, so a literal value that happens to be the same as another may be mistakenly changed even though they represent something different. Constants are more descriptive and flexible than literal values. The origin and purpose of a constant is more clearly defined. If the constant ever needs to be updated it only needs to be updated once where it is defined and there is no ambiguity with other values that happen to be the same literal value but serve a different purpose.

Note: The example below is trivial so it appears like overkill to use constants this way but as the complexity of an application grows it greatly simplifies code and makes the code more secure.

Option Explicit

Public Sub Example()

    Dim i As Long
    For i = 2 To 100
        If Cells(i, 1).Value = "Hello, World!" Then
            MsgBox "Hello, World!"
        End If
    Next i

End Sub

Option Explicit

Public Const HEADER_ROW As Long = 1

Public Enum SpreadsheetColumns
    MessageColumn = 1
    FlagColumn
End Enum

Public Sub Example()

    Const SEARCH_MESSAGE As String = "Hello, World!"
    Const OUTPUT_MESSAGE As String = "Hello, World!"

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, MessageColumn).End(xlUp).Row

    'Rewritten with constants instead of literals
    Dim i As Long
    For i = HEADER_ROW + 1 To LastRow
        If Cells(i, MessageColumn).Value = SEARCH_MESSAGE Then
            MsgBox OUTPUT_MESSAGE
        End If
    Next i

End Sub

When a Variable Should Not Change

Constants should be used in place of variables when the value should never change. Using variables to represent constant values opens up the possibility that the value will be updated accidentally or intentionally when it should never be changed.

Public Function Circumference(Radius As Double)

    'RETURNS WRONG ANSWER

    Dim PI As Double
    PI = 3.14159265359

    'Whoops. PI should never change so this should not be allowed. Function is wrong.
    PI = 1 

    Circumference = 2 * PI * Radius

End Sub

Public Function Circumference(Radius As Double)

    'CAUSES COMPILE ERROR

    Const PI = 3.14159265359

    'Whoops. PI is immutable and this will cause a compile error. Function will not run.
    PI = 1 

    Circumference = 2 * PI * Radius

End Sub

Enums

Enums can be used to store related integer constants together under a common type. Enums will be discussed in greater detail in the Enums section.

Option Explicit

Public Enum FavoriteColors
    White = &HFFFFFF
    Black = &H0&
    Red = &HFF&
    Orange = &HC0FF&
    Yellow = &HFFFF&
    Green = &H50B000
    Blue = &HFF0000
    Indigo = &H82004B
    Violet = &HFF008F
End Enum

Public Sub Example()

    Range("A1").Interior.Color = FavoriteColors.Green

End Sub

Built-In Constants

VBA has a number of built-in constants and enums which can be used with VBA functions. Most libraries used with VBA define their own constants and enums which are used with the classes and functions in those libraries.