Étiquette : 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).

The list the most important data types supported by VBA, along with their memory requirements and value ranges:

Example: Declaring and Using Variables

The following VBA procedure declares variables of several of the above types, assigns values to them, and displays those values in worksheet cells:

Sub Variables()
    Dim By As Byte
    Dim Bo As Boolean
    Dim It As Integer, Lg As Long
    Dim Sg As Single, Db As Double
    Dim Dt As Date
    Dim St As String
    By = 200
    Bo = True
    It = 20000
    Lg = 200000
    Sg = 0.1 / 7
    Db = 0.1 / 7
    Dt = "15/03/2020"
    St = "String value"
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A1").Value = By
    Range("A2").Value = Bo
    Range("A3").Value = It
    Range("A4").Value = Lg
    Range("A5").Value = Sg
    Range("A6").Value = Db
    Range("A5:A6").NumberFormatLocal = "0,00000000000000000000"
    Range("A7").Value = Dt
    Range("A8").Value = St
    Range("A:A").Columns.AutoFit
End Sub

Explanation:

• Variables are declared using the Dim keyword, followed by As to specify the data type.
• Multiple variables can be declared in one line, but a common mistake is:

• Dim a, b As Integer

This declares b as an Integer but a as a Variant! The correct declaration is:

Dim a As Integer, b As Integer

• Boolean variables can only hold the values True or False.
• For numeric data types, exceeding the valid value range causes a runtime error.
• Whole numbers stored in Byte, Integer, or Long variables are stored exactly.
• Numbers with decimal places stored in Single or Double variables have limited precision. For example, the value 18.55 may be stored as approximately 18.549999. This slight inaccuracy is usually negligible in most calculations but should be kept in mind.
• Decimal values in VBA code must use a decimal point (.), regardless of locale.
• The difference between Single and Double lies in their precision: Double provides higher precision.
• The example sets the number of decimal places displayed in cells A5 and A6 to 20 using the NumberFormatLocal property to illustrate this difference.
• String and date values must be enclosed in double quotation marks. For dates, the format DD/MM/YYYY is recommended.
• Rows and columns can be optimally resized using the AutoFit() method.

Note:

If a variable is declared without an explicit data type using As, it defaults to the Variant type. This is discouraged because Variant variables consume more memory, execute more slowly, and are harder to debug and maintain.

Navigation Tips:

• To jump to a variable’s declaration, select the variable name and press Shift + F2.
• To return to the usage point, press Ctrl + Shift + F2.

The TypeName() function is very useful when you do not know the data type of a variable, cell content, or object. It returns the data type as a string.

Example:

Sub DetectType()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("B2").Value = TypeName(Range("A2").Value)
    Range("B3").Value = TypeName(Range("A3").Value)
    Range("B4").Value = TypeName(Range("A4").Value)
    Range("B5").Value = TypeName(Range("A5").Value)
    Range("B6").Value = TypeName(Range("A6").Value)
    Range("B7").Value = TypeName(Range("A7").Value)
    Range("B8").Value = TypeName(Range("A8").Value)
End Sub

Explanation:

• This example writes the data type of the adjacent cell in column A into column B.
• Numbers, whether integer or decimal, are recognized as Double by TypeName().
• For example, the number in cell A5 no longer « remembers » if it originally came from a Single variable in VBA or was manually typed by the user. The function simply identifies it as a Double, the highest precision numeric type.