8  Variables

Variables are used to store data in memory during program execution. They are used to hold values that can be manipulated and changed as needed.

In VBA, variables are declared using the Dim statement, which stands for “dimension.” The Dim statement is followed by the variable name and its data type.

For example, to declare an integer variable named counter, you would use the following syntax:

Dim counter As Integer

In this example, counter is the variable name, and Integer is the data type. The As keyword is used to specify the data type of the variable.

8.1 What Happens When You Declare a Variable?

When you declare a variable in VBA, the following steps occur:

  1. Memory Allocation: VBA reserves a block of memory to store the variable.
  2. Data Type: The data type of the variable determines the size of the memory block allocated.
  3. Initial Value: The variable is initialized with a default value based on its data type.
    • For numeric data types, the default value is 0.
    • For string data types, the default value is an empty string "".
    • For boolean data types, the default value is False.
    • For date data types, the default value is 0 (December 30, 1899).
    • For object data types, the default value is Nothing.
    • For variant data types, the default value is Empty.
    • For array data types, the default value is an empty array.
  4. Scope: The variable is available for use within the scope in which it was declared.
  5. Lifetime: The variable exists until the end of the procedure in which it was declared.
  6. Reusability: The variable can be reused to store different values during program execution.
  7. Data Type Checking: VBA performs data type checking to ensure that the variable is used correctly (if Option Explicit is enabled).

8.2 Naming Conventions

When naming variables, follow these conventions:

  • Use camelCase for variable names: Start with a lowercase letter and capitalize the first letter of each subsequent word. For example:
    • studentName
    • studentAge
    • totalScore
    • isPassed
    • maxValue
  • Use PascalCase for Function and Sub names: Start with an uppercase letter and capitalize the first letter of each subsequent word. For example
    • CalculateGPA
    • DisplayStudentInfo
    • GetStudentName
    • UpdateStudentRecord
    • CalculateTotalScore
  • Use UPPERCASE for constants: Use all uppercase letters with underscores to separate words. For example, MAX_VALUE, PI.
  • Use descriptive names: Choose names that describe the purpose of the variable. For example:
    • studentName instead of name
    • studentAge instead of age
    • CalculateTotalLateness instead of CalculateTL
    • MAX_SCORE instead of max
  • Avoid reserved words: Don’t use reserved words or keywords as variable names. For example:
    • Dim Dim As Integer is not allowed (Dim is a reserved keyword)
    • Dim Integer As Integer is not allowed (Integer is a reserved keyword)
    • Dim Sub As Integer is not allowed (Sub is a reserved keyword)
  • Avoid abbreviations: Use full words instead of abbreviations to make the code more readable. For example:
    • studentName instead of stdName
    • studentAge instead of sAge
VBE IntelliSense and Naming Conventions

If you are inconsistent when declaring your variables (or parameters) the Visual Basic Editor will automatically change the capitalization. For example, if you declare a variable as studentName and then later declare it as StudentName, the VBE will automatically change all instances of studentName to StudentName. This can be confusing and make your code harder to read. The best and permanent solution is to standardise the case across the whole project.

8.3 Data Types

VBA supports various data types that can be used to declare variables. Each data type has specific characteristics and uses. The choice of data type affects the memory used and the range of values that can be stored.

Table 8.1 shows the data types available in VBA and their corresponding ranges of values. Notice that numbers can be stored in different data types depending on the range and precision required.

Table 8.1: Data types in VBA and their corresponding ranges of values.
Data Type Bytes Used Range of Values
Byte 1 0 to 255
Boolean 2 True or False
Integer 2 -32,768 to 32,767
Long 4 -2,147,483,648 to 2,147,483,647
LongLong 8 -9,223,372,036,854,775,808 to -9,223,372,036,854,775,807
Single 4 -3.40E38 to -1.40E-45 for negative values; 1.40E-45 to 3.40E38 for positive values
Double 8 -1.79E308 to -4.94E-324 for negative values; 4.94E-324 to 1.79E308 for positive values
Currency 8 -922,337,203,685,477 to 922,337,203,685,477
Date 8 1/1/0100 to 1 2/31 /9999
Object 4 Any object reference
String 1 per character Varies
Variant Varies Varies

Typically, numeric data types (in several programming languages) are referred to as:

  • Integer:
    • Whole numbers without any fractional or decimal part.
    • Can be both positive and negative numbers or zero.
    • Typically used for counting, indexing, and situations where exact whole numbers are required.
    • VBA integer types include Byte, Integer, Long, and LongLong.
  • Floating-Point:
    • Represents real numbers (integers and numbers with fractional parts).
    • Used when you need to represent a wider range of values with varying degrees of precision.
    • VBA floating-point types include Single and Double.
Why ‘Floating-Point’ Numbers?

The term “floating-point” refers to the fact that the decimal point can “float” to the left or right, allowing the representation of both very large and very small numbers.

8.4 Variant Data Type

The Variant data type is a special data type in VBA that can store any type of data. It is a versatile data type that can hold numeric, string, date, or boolean values. The Variant data type is the default data type if you do not specify a data type when declaring a variable.

When a variable is not initialized, it is vbEmpty, which is equivalent to Empty in VBA. This means that the variable does not contain any data. This is the default value for Excel cells that have not been assigned a value, for example.

What is the Data Type of a Variable?

To discover the data type of a variable, you can use the VarType function in VBA to return an integer representing the data type of a variable (see VBA VarType Function).

8.5 Type Coercion

When you declare an Integer variable and then attempt to assign it a Double value, VBA will perform an implicit type conversion, often referred to as “type coercion.”

VBA will convert the Double value to an Integer, but it will do so by truncating or rounding the decimal portion of the Double value, effectively discarding the decimal places.

Truncation is the process of removing the decimal portion of a number, effectively rounding down to the nearest whole number.

In the memory, the value is stored as an integer, and the decimal portion is lost.

For example:

Option Explicit

Sub DoubleAssignedToIntegerExample()

    Dim myInteger1 As Integer
    myInteger1 = 123.5
    Debug.Print "Integer Value: " & myInteger1 'Round up
    Debug.Assert myInteger1 = 124
    
    Dim myInteger2 As Integer
    myInteger2 = 123.51
    Debug.Print "Integer Value: " & myInteger2 'Round up
    
    Dim myInteger3 As Integer
    myInteger3 = 123.49999
    Debug.Print "Integer Value: " & myInteger3 'Truncation

End Sub

8.6 Constants

Constants are named elements that do not change in your program. They are used to define values that remain constant throughout the execution of the program.

In VBA, constants are declared using the Const statement, followed by the constant name, the data type, and the value. If you need to declare a constant that is accessible by all procedures across all modules, you can use the Public keyword. This is useful when you have split your code into multiple modules for better organization, but still need that some information is available globally.

In Listing 8.1, we show examples of how to declare constants in VBA.

Listing 8.1: Example of declaring constants in VBA. The Const statement is used to define constants with specific values that remain constant throughout the program. The Public keyword is used to make the constant accessible by all procedures across all modules. 
'Constants accessible by procedures within module
Const NUM_QUARTERS As Long = 4
Const RATE = 0.0725
Const MOD_NAME As String = "Budget Macros"

'Constant accessible by all procedures across all modules
Public Const APP_NAME As String = "Budget Application"

Using constants in your VBA code has several advantages:

  1. Improved Code Readability: Constants make your code more readable by providing meaningful names for values that are used multiple times.
  2. Higher Flexibility: If the value of a constant changes, you only need to modify it in one place, rather than searching for and updating multiple occurrences of the value throughout your code. This makes your code easier to maintain and less error-prone.

In Listing 8.2, we show how some constants can be used to convert measurements from metric to imperial units.

Listing 8.2: Example of using constants to convert measurements from metric to imperial units. The user is prompted to enter a height in centimeters, which is then converted to feet and inches using the constants FEET_TO_CENTIMETERS and FEET_TO_INCHES. The result is displayed in the format <feet>'<inches>". 
Const FEET_TO_CENTIMETERS As Double = 30.48
Const FEET_TO_INCHES As Double = 12

Sub ConvertToMetricWithConstants()

    Dim heightInCentimeters As Integer
    heightInCentimeters = InputBox("Enter height in centimeters:")

    Dim feet As Integer
    Dim inches As Integer
    
    feet = Int(heightInCentimeters / FEET_TO_CENTIMETERS)
    inches = ((heightInCentimeters / FEET_TO_CENTIMETERS) - feet) * FEET_TO_INCHES
    
    Dim heightInFeetAndInches As String
    heightInFeetAndInches = feet & "'" & inches & """"

    MsgBox "Height in feet and inches: " & heightInFeetAndInches
    
End Sub