Étiquette : macro_variables_data_types

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:

Data Type	Memory Size	Value Range / Meaning
Boolean	2 bytes	True or False (logical values)
Byte	1 byte	Whole number from 0 to 255
Integer	2 bytes	Whole number from –32,768 to +32,767
Long	4 bytes	Long whole number from approximately –2.1 × 10⁹ to +2.1 × 10⁹
Single	4 bytes	Single-precision floating-point number: approx. –3.4×10³⁸ to –1.4×10⁻⁴⁵ (negative) and +1.4×10⁻⁴⁵ to +3.4×10³⁸ (positive)
Double	8 bytes	Double-precision floating-point number: approx. –1.8×10³⁰⁸ to –4.9×10⁻³²⁴ (negative) and +4.9×10⁻³²⁴ to +1.9×10³⁰⁸ (positive)
Date	8 bytes	Dates from January 1, 100 to December 31, 9999
Object	4 bytes	Reference to an object (see section 6.4, « Working with Object Variables »)
String	10 bytes + length of string	Variable-length text string
Variant	≥16 bytes	Data type not explicitly defined (not recommended)

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.

In addition to having a name, every variable has a data type that defines the kind of information it can store. The developer selects the data type based on whether the variable should hold text, whole numbers, decimal numbers, dates, or other types of data.

Moreover, the developer must consider the size of the numeric range that a variable might need to hold. It is best practice to choose the data type with the smallest memory requirement that can accommodate the expected range of values. Using a smaller data type not only saves memory but also typically allows for faster processing.

In Visual Basic, variables should always be declared explicitly. This practice helps prevent errors and avoids unnecessary memory usage.

You have already configured an important setting for VBA programming: In the menu Tools > Options, on the Editor tab, you checked the box for Require Variable Declaration.

With this option enabled, every time you open Excel, the line

Option Explicit

is automatically inserted at the top of each VBA module. This forces all variables to be declared before use, improving code reliability and maintainability.

A variable has a unique name by which it can be referenced in the code. In VBA, the following rules apply to variable names:

• They must begin with a letter.
• They can only contain letters, numbers, and a few special characters (for example, the underscore _).
• They should not include German umlauts (ä, ö, ü) or the sharp s (ß).
• Within a given scope, no two variables may share the same name (see section 5.1, « Scopes »).

Valid examples of variable names are: Temperature, Summe_Werte, or X12.

Invalid examples include: 5Tage (because it starts with a digit) or Tag#12 (because it contains an invalid character #).

Variables receive their values through assignment using the equal sign =. It is advisable to assign a value to a variable before its first use, for example:

Temperature = 25

Assigning values early makes programs clearer, easier to read, and less prone to errors.