Variables, Constants, and Data Types with Excel VBA

In VBA, variables are used to store temporary values, pass parameters, and perform calculations. Usually, before using a variable, it is declared; that is, you specify in advance the exact variable names you will use in your program, and at the same time, you declare the data type that this variable will hold. In VBA, the Dim statement is used to declare a variable:

Dim <VariableName> [As <DataType>]

For example:

Dim i As Integer, j As Integer

Dim x As Double

VBA enforces the following rules for naming variables. A name cannot exceed 255 characters and must begin with a letter, which can be followed by letters, numbers, or an underscore. Uppercase and lowercase letters are not distinguished. The name must not contain spaces, punctuation, or special characters, except for the last character. At the end of the variable name, one of six special characters — type-declaration characters — can optionally be added:

! # $ % & @

These characters are not considered part of the variable name: if both string1$ and string1 are used in a program, they refer to the same string variable.

Additionally, VBA keywords and the names of standard objects cannot be used as variable names. Therefore, it is recommended to start variable names with a lowercase letter rather than an uppercase one.

It is allowed to use letters from alphabets other than Latin, including Cyrillic, which can be convenient for Russian-speaking users.

It should also be noted that in VBA, declaring variables is not mandatory, and undeclared variables can be used. Memory for variables can be allocated dynamically, and the data type of a variable can be inferred from the last character of its name.

Ultimately, the programmer decides how variables will be used in their program. For this purpose, VBA provides the statement:

Option Explicit

If you begin your module with this statement (which must be placed at the very beginning of the module, before the first procedure), VBA will require all variables in this module to be declared and will generate an error message whenever it encounters an undeclared variable. Furthermore, if you enable the Require Variable Declaration option on the Editor tab of the Options dialog box in the VBA editor (accessed via Tools | Options in the Visual Basic integrated development environment), VBA will generate an error message every time it encounters an undeclared variable.

Enabling this option causes the Visual Basic editor to automatically add the Option Explicit statement at the beginning of each newly created module. However, this setting does not affect previously created modules — to add this statement to existing modules, you must do so manually.

A brief list of the VBA data types is given in Table 2.1.

Table 2.1. VBA Data Types

When declaring a variable, specifying its data type can be omitted. In this case, the variable type will be determined by the last character of the variable name: @, #, %, &, !, or $ (corresponding to Currency, Double, Integer, Long, Single, or String, respectively).

If the last character is not one of these and no explicit type is specified, the variable will be assigned the default data type Variant, which allows it to hold data of any type.

It should be remembered that in the same procedure, you cannot use variable names that differ only by the type-declaration character at the end of the variable name. For example, it is not allowed to use both var$ and var% in the same procedure. Similarly, it is not allowed to explicitly declare a variable that already has a type-declaration character at the end using the syntax:

Dim <VariableName> As <VariableType>

For example, you will get an error message if you attempt to enter any of the following declarations:

Dim var1% As String

Dim var2% As Integer

To define the data types of procedure or function arguments, the data type is specified directly in the procedure or function header. For example, the following procedure header declares its parameters as string variables:

Sub SpSt(str1 As String, str2 As String, str3 As String)

The data type of a function’s return value is specified at the end of the function header. For example, the following declaration specifies that the function returns a short integer:

Function FSpS(str1 As String) As Integer

To make a program run faster and use less memory, it is recommended to use specific variable types whenever possible rather than the universal Variant type. Processing Variant variables requires not only additional memory (see the sizes in Table 2.1) but also additional time: at runtime, VBA determines the exact data type of the variable and, if necessary, converts it to the required type.

However, there are situations where Variant variables are necessary—for example, when you do not know in advance what type of data will be assigned to the variable.

Next, consider the use of named constants. The Const statement, similar to Dim, is used to declare them. Its syntax is as follows:

Const <ConstantName> [As <DataType>] = <Expression>

where <Expression> is any value or formula that returns the value to be used as a constant. For example, the following statement declares an integer constant maxLen:

Const maxLen% = 30

Like variables, constants can hold values of different data types, but they do not change during program execution.

TIP
If you plan to use constants in your program, it is recommended to give them meaningful names and declare them at the very beginning of the module, then use only these named constants throughout the program. This makes the program not only easier to understand but also easier to maintain and debug.

In addition to user-defined constants, there are also predefined built-in constants that can be used in programs without prior declaration. Information about predefined built-in constants used for various Microsoft Office and Visual Basic objects can be found in the help documentation—usually in the sections describing object properties (less often in the method descriptions). When naming built-in constants, a standard convention is used to indicate the application to which the constant belongs. For example, built-in constants related to Excel objects start with the prefix xl, while those related to VBA objects start with vb.