Constants and Enumerations In Excel VBA

Constants and enumerations are used less frequently than variables but serve important purposes.

Constants are predefined values of various data types that cannot be changed during program execution. They are usually given meaningful names, making the code easier to understand and maintain than if literal values were used directly.

Compared to variables, programs can access constants faster. Therefore, you should use constants whenever a value is fixed and never changes during the program’s run.

There are two main types of constants:

• User-defined constants:
  Defined by the developer at a central location in the code and can be used throughout the program. This central definition means that if the constant’s value needs to be changed, it only has to be updated once during design time. The scope of constants is analogous to that of variables.
• Built-in constants:
  These are predefined by VBA and cannot be altered by the developer.
  For example, when inserting cells, you might have already used the built-in constants xlShiftDown and xlShiftToRight. They represent the numbers 4121 and 4161 respectively, which are less memorable than the constant names.
  Another commonly used built-in constant is vbCrLf, which represents a newline character in a message box (MsgBox).

Enumerations are collections of related integer constants with meaningful names. You can define your own enumerations or use predefined ones.

The following example first works with a constant and a variable:

Sub ConstantsExample()
    Const MaxValue As Integer = 55
    Dim MinValue As Integer
    MinValue = 15
    MsgBox MaxValue - MinValue
    MinValue = 35
    MsgBox MaxValue - MinValue
End Sub

Explanation:

• The constant MaxValue is declared as an Integer and cannot be changed.
• The variable MinValue can be modified within the procedure.

Color Constants

VBA has the following predefined color constants:

• vbBlack: Black
• vbRed: Red
• vbGreen: Green
• vbYellow: Yellow
• vbBlue: Blue
• vbMagenta: Magenta
• vbCyan: Cyan
• vbWhite: White

You can create any color using the RGB() function. This function takes three parameters representing the red, green, and blue components of a color, each ranging from 0 to 255.

Defining and Using an Enumeration

Below is an example where a custom enumeration is defined and then used within a procedure. The enumeration block is between Enum and End Enum:

Enum Color
    Red
    Yellow
    Blue
    Black = 5
    Orange
End Enum

Sub UseEnumeration()
    Dim F As Color
    F = Orange
    MsgBox F
End Sub

Explanation:

• Enumerations are declared outside of procedures, typically at the top of a module just below Option Explicit.
• The example Color enumeration has five elements.
• If no values are assigned, the first element defaults to 0, and subsequent elements increment by 1 (i.e., 0, 1, 2, 3, …).
• If a value is assigned (e.g., Black = 5), that element takes the specified value, and subsequent elements continue incrementing from there (here: Orange becomes 6).
• In the procedure, a variable of type Color is declared. When assigning a value, VBA shows a list of enumeration members. You can assign any integer, but assigning values outside the defined enumeration goes against the purpose of enumerations.
• The MsgBox displays the numeric value associated with the assigned enumeration element (in this case, 6 for Orange).

There are many predefined enumerations in VBA. For example « Aligning Cells, » the Weight property determines the thickness of a cell border. Valid constants from the enumeration xlBorderWeight include xlHairline (very thin line), xlThin (thin line), xlMedium (medium line), and xlThick (thick line).