Variables, Data Types, and Constants
Variables , Data Types, and Constants
VBA's main purpose in life is to manipulate data. Some data resides in objects, such as worksheet ranges. Other data is stored in variables that you create.
A variable is simply a named storage location in your computer's memory. Variables can accommodate a wide variety of data types - from simple Boolean values ( True or False ) to large, double-precision values (see the following section). You assign a value to a variable by using the equal sign operator (more about this in the upcoming section, "Assignment Statements").
You make your life easier if you get into the habit of making your variable names as descriptive as possible. VBA does, however, have a few rules regarding variable names :
-
You can use alphabetic characters , numbers , and some punctuation characters, but the first character must be alphabetic.
-
VBA does not distinguish between case. To make variable names more readable, programmers often use mixed case (for example, InterestRate rather than interestrate ).
-
You cannot use spaces or periods. To make variable names more readable, programmers often use the underscore character ( Interest_Rate ).
-
Special type declaration characters ( # , $ , % , & , or ! ) cannot be embedded in a variable name .
-
Variable names can be as long as 254 characters - but using such long variable names is not recommended.
The following list contains some examples of assignment expressions that use various types of variables. The variable names are to the left of the equal sign. Each statement assigns the value to the right of the equal sign to the variable on the left.
x = 1 InterestRate = 0.075 LoanPayoffAmount = 243089.87 DataEntered = False x = x + 1 MyNum = YourNum * 1.25 UserName = "Bob Johnson" DateStarted = #12/14/2006#
VBA has many reserved words, which are words that you cannot use for variable or procedure names. If you attempt to use one of these words, you get an error message. For example, although the reserved word Next might make a very descriptive variable name, the following instruction generates a syntax error:
Next = 132
Unfortunately, syntax error messages aren't always very descriptive. The preceding instruction generates this error message: Compile error: Syntax error . It would be nice if the error message were something like Reserved word used as a variable . So if an instruction produces a strange error message, check the VBA Help system to ensure that your variable name doesn't have a special use in VBA.
Defining data types
VBA makes life easy for programmers because it can automatically handle all the details involved in dealing with data. Not all programming languages make it so easy. For example, some languages are strictly typed, which means that the programmer must explicitly define the data type for every variable used.
Data type refers to how data is stored in memory - as integers, real numbers, strings, and so on. Although VBA can take care of data typing automatically, it does so at a cost: slower execution and less efficient use of memory. As a result, letting VBA handle data typing may present problems when you're running large or complex applications. Another advantage of explicitly declaring your variables as a particular data type is that VBA can perform some additional error checking at the compile stage. These errors might otherwise be difficult to locate.
Table 8-1 lists VBA's assortment of built-in data types. (Note that you can also define custom data types, which I describe later in this chapter in " User -Defined Data Types.")
| Data Type | Bytes Used | Range of Values |
|---|---|---|
| Byte | 1 byte | to 255 |
| Boolean | 2 bytes | True or False |
| Integer | 2 bytes | “32,768 to 32,767 |
| Long | 4 bytes | “2,147,483,648 to 2,147,483,647 |
| Single | 4 bytes | -3.402823E38 to “1.401298E-45 (for negative values); 1.401298E - 45 to 3.402823E38 (for positive values) |
| Double | 8 bytes | -1.79769313486232E308 to -4.94065645841247E-324 (negative values); 4.94065645841247E-324 to 1.79769313486232E308 (for positive values) |
| Currency | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| Decimal | 12 bytes | +/ “79,228,162,514,264,337,593,543,950,335 with no decimal point; |
| Date | 8 bytes | January 1, 0100 to December 31, 9999 |
| Object | 4 bytes | Any object reference |
| String (variable length) | 10 bytes + string length | 0 to approximately 2 billion characters |
| String (fixed length) | Length of string | 1 to approximately 65,400 characters |
| Variant (with numbers) | 16 bytes | Any numeric value up to the range of a double data type. It can also hold special values such as Empty, Error, Nothing, and Null. |
| Variant (with characters) | 22 bytes + string length | 0 to approximately 2 billion |
| User-defined | Varies | Varies by element |
 |
To test whether data typing is important, I developed the following routine, which performs some meaningless calculations in a loop and then displays the procedure's total execution time:
Sub TimeTest() Dim As Long, y As Long Dim A As Double, B As Double, C As Double Dim i As Long, j As Long Dim StartTime As Date, EndTime As Date ' Store the starting time StartTime = Timer ' Perform some calculations x = 0 y = 0 For i = 1 To 5000 x = x + 1 y = x + 1 For j = 1 To 5000 A = x + y + i B = y - x - i C = x / y * i Next j Next i ' Get ending time EndTime = Timer ' Display total time in seconds MsgBox Format(EndTime - StartTime, "0.0") End Sub
On my system, this routine took 5.1 seconds to run (the time will vary, depending on your system's processor speed). I then commented out the Dim statements, which declare the data types. That is, I turned the Dim statements into comments by adding an apostrophe at the beginning of the lines. As a result, VBA used the default data type, Variant . I ran the procedure again. It took 14.7 seconds, almost three times as long as before.
The moral is simple: If you want your VBA applications to run as fast as possible, declare your variables!
A workbook that contains this code is available on the companion CD-ROM in a file named timing text.xlsm .
| |
| Note | The Decimal data type is rather unusual because you cannot actually declare it. In fact, it is a subtype of a variant. You need to use the VBA CDec function to convert a variant to the Decimal data type. |
Generally , it's best to use the data type that uses the smallest number of bytes yet still can handle all the data that will be assigned to it. When VBA works with data, execution speed is a function of the number of bytes that VBA has at its disposal. In other words, the fewer bytes used by data, the faster VBA can access and manipulate the data.
For worksheet calculation, Excel uses the Double data type, so that's a good choice for processing numbers in VBA when you don't want to lose any precision. For integer calculations, you can use the Integer type (which is limited to values less than or equal to 32,767). Otherwise, use the Long data type. In fact, using the Long data type even for values less than 32,767 is recommended, because this data type may be a bit faster than using the Integer type. When dealing with Excel worksheet row numbers, you want to use the Long data type because the number of rows in a worksheet exceeds the maximum value for the Integer data type.
Declaring variables
If you don't declare the data type for a variable that you use in a VBA routine, VBA uses the default data type, Variant . Data stored as a Variant acts like a chameleon: It changes type, depending on what you do with it.
The following procedure demonstrates how a variable can assume different data types:
Sub VariantDemo() MyVar = "123" MyVar = MyVar / 2 MyVar = "Answer: " & MyVar MsgBox MyVar End Sub
In the VariantDemo procedure, MyVar starts out as a three-character string. Then this string is divided by two and becomes a numeric data type. Next, MyVar is appended to a string, converting MyVar back to a string. The MsgBox statement displays the final string:
Answer: 61.5.
To further demonstrate the potential problems in dealing with Variant data types, try executing this procedure:
Sub VariantDemo2() MyVar = "123" MyVar = MyVar + MyVar MyVar = "Answer: " & MyVar MsgBox MyVar End Sub
The message box displays Answer: 123123 . This is probably not what you wanted. When dealing with variants that contain text strings, the + operator performs string concatenation.
DETERMINING A DATA TYPE
You can use the VBA TypeName function to determine the data type of a variable. Here's a modified version of the previous procedure. This version displays the data type of MyVar at each step. You see that it starts out as a string, is then converted to a double, and finally ends up as a string again.
Sub VariantDemo2() MyVar = "123" MsgBox TypeName(MyVar) MyVar = MyVar / 2 MsgBox TypeName(MyVar) MyVar = "Answer: " & MyVar MsgBox TypeName(MyVar) MsgBox MyVar End Sub
Thanks to VBA, the data type conversion of undeclared variables is automatic. This process might seem like an easy way out, but remember that you sacrifice speed and memory - and you run the risk of errors that you may not even know about.
Declaring each variable in a procedure before you use it is an excellent habit. Declaring a variable tells VBA its name and data type. Declaring variables provides two main benefits:
-
Your programs run faster and use memory more efficiently . The default data type, Variant , causes VBA to repeatedly perform time-consuming checks and reserve more memory than necessary. If VBA knows the data type, it doesn't have to investigate, and it can reserve just enough memory to store the data.
-
You avoid problems involving misspelled variable names. This assumes that you use Option Explicit to force yourself to declare all variables (see the next section). Say that you use an undeclared variable named CurrentRate . At some point in your routine, however, you insert the statement CurentRate = .075 . This misspelled variable name, which is very difficult to spot, will likely cause your routine to give incorrect results.
FORCING YOURSELF TO DECLARE ALL VARIABLES
To force yourself to declare all the variables that you use, include the following as the first instruction in your VBA module:
Option Explicit
When this statement is present, VBA will not even execute a procedure if it contains an undeclared variable name. VBA issues the error message shown in Figure 8-1, and you must declare the variable before you can proceed.
Figure 8-1: VBA's way of telling you that your procedure contains an undeclared variable.
| Tip | To ensure that the Option Explicit statement is inserted automatically whenever you insert a new VBA module, enable the Require Variable Declaration option in the Editor tab of the VBE Options dialog box (choose Tools |
Scoping variables
A variable's scope determines in which modules and procedures the variable can be used. Table 8-2 lists the three ways in which a variable can be scoped.
| Scope | How a Variable with This Scope Is Declared |
|---|---|
| Single procedure | Include a Dim or Static statement within the procedure. |
| Single module | Include a Dim or Private statement before the first procedure in a module. |
| All modules | Include a Public statement before the first procedure in a module. |
I discuss each scope further in the following sections.
| |
This chapter contains many examples of VBA code, usually presented in the form of simple procedures. These examples demonstrate various concepts as simply as possible. Most of these examples do not perform any particularly useful task; in fact, the task can often be performed in a different (perhaps more efficient) way. In other words, don't use these examples in your own work. Subsequent chapters provide many more code examples that are useful.
| |
LOCAL VARIABLES
A local variable is a variable declared within a procedure. Local variables can be used only in the procedure in which they are declared. When the procedure ends, the variable no longer exists, and Excel frees up its memory. If you need the variable to retain its value when the procedure ends, declare it as a Static variable. (See "Static variables," later in this section.)
The most common way to declare a local variable is to place a Dim statement between a Sub statement and an End Sub statement. Dim statements usually are placed right after the Sub statement, before the procedure's code.
| Note | If you're curious about this word, Dim is a shortened form of Dimension. In old versions of BASIC, this statement was used exclusively to declare the dimensions for an array. In VBA, the Dim keyword is used to declare any variable, not just arrays. |
The following procedure uses six local variables declared by using Dim statements:
Sub MySub() Dim x As Integer Dim First As Long Dim InterestRate As Single Dim TodaysDate As Date Dim UserName As String Dim MyValue ' - [The procedure's code goes here] - End Sub
Notice that the last Dim statement in the preceding example doesn't declare a data type; it simply names the variable. As a result, that variable becomes a variant.
You also can declare several variables with a single Dim statement. For example:
Dim x As Integer, y As Integer, z As Integer Dim First As Long, Last As Double
| Caution | Unlike some languages, VBA does not let you declare a group of variables to be a particular data type by separating the variables with commas. For example, the following statement, although valid, does not declare all the variables as integers: |
Dim i, j, k As Integer
In VBA, only k is declared to be an integer; the other variables are declared variants. To declare i , j , and k as integers, use this statement:
Dim i As Integer, j As Integer, k As Integer
If a variable is declared with a local scope, other procedures in the same module can use the same variable name, but each instance of the variable is unique to its own procedure.
In general, local variables are the most efficient because VBA frees up the memory that they use when the procedure ends.
| |
Like most other dialects of BASIC, VBA lets you append a character to a variable's name to indicate the data type. For example, you can declare the MyVar variable as an integer by tacking % onto the name:
Dim MyVar%
Type-declaration characters exist for most VBA data types. Data types not listed in the following table don't have type-declaration characters.
Open table as spreadsheet | Data Type | Type-Declaration Character |
|---|---|
| Integer | % |
| Long | & |
| Single | ! |
| Double | # |
| Currency | @ |
| String | $ |
This method of data typing is essentially a holdover from BASIC; it's better to declare your variables by using the other techniques described in this chapter. I list these type declaration characters here just in case you encounter them in an older program.
| |
MODULE-WIDE VARIABLES
Sometimes, you want a variable to be available to all procedures in a module. If so, just declare the variable before the module's first procedure (outside of any procedures or functions).
In the following example, the Dim statement is the first instruction in the module. Both Procedure1 and Procedure2 have access to the CurrentValue variable.
Dim CurrentValue as Integer Sub Procedure1() ' - [Code goes here] - End Sub Sub Procedure2() ' - [Code goes here] - End Sub
Normally, the value of a module-wide variable does not change when a procedure ends normally (that is, when it reaches the End Sub or End Function statement). An exception is if the procedure is halted with an End statement. When VBA encounters an End statement, all module-wide variables lose their values.
PUBLIC VARIABLES
To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module level (before the first procedure declaration) by using the Public keyword rather than Dim . Here's an example:
Public CurrentRate as Long
The Public keyword makes the CurrentRate variable available to any procedure in the VBA project, even those in other modules within the project. You must insert this statement before the first procedure in a module (any module). This type of declaration must appear in a standard VBA module, not in a code module for a sheet or a UserForm.
STATIC VARIABLES
Static variables are a special case. They are declared at the procedure level, and they retain their value when the procedure ends normally. However, if the procedure is halted by an End statement, static variables do lose their values.
You declare static variables by using the Static keyword:
Sub MySub() Static Counter as Integer - [Code goes here] - End Sub
| |
Some programmers name variables so that users can identify their data types by just looking at their names. Personally, I don't use this technique very often because I think it makes the code more difficult to read, but you might find it helpful.
The naming convention involves using a standard lowercase prefix for the variable's name. For example, if you have a Boolean variable that tracks whether a workbook has been saved, you might name the variable bWasSaved . That way, it is clear that the variable is a Boolean variable. The following table lists some standard prefixes for data types:
Open table as spreadsheet | Data Type | Prefix |
|---|---|
| Boolean | b |
| Integer | i |
| Long | l |
| Single | s |
| Double | d |
| Currency | c |
| Date/Time | dt |
| String | str |
| Object | obj |
| Variant | v |
| User-defined | u |
| |
Working with constants
A variable's value may change while a procedure is executing (that's why it's called a variable ) . Sometimes, you need to refer to a named value or string that never changes: a constant.
Using constants throughout your code in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it's better to declare the value as a constant and use the constant's name rather than its value in your expressions. This technique not only makes your code more readable, it also makes it easier to change should the need arise - you have to change only one instruction rather than several.
DECLARING CONSTANTS
You declare constants with the Const statement. Here are some examples:
Const NumQuarters as Integer = 4 Const Rate = .0725, Period = 12 Const ModName as String = "Budget Macros" Public Const AppName as String = "Budget Application"
The second example doesn't declare a data type. Consequently, VBA determines the data type from the value. The Rate variable is a Double , and the Period variable is an Integer . Because a constant never changes its value, you normally want to declare your constants as a specific data type.
Like variables, constants also have a scope. If you want a constant to be available within a single procedure only, declare it after the Sub or Function statement to make it a local constant. To make a constant available to all procedures in a module, declare it before the first procedure in the module. To make a constant available to all modules in the workbook, use the Public keyword and declare the constant before the first procedure in a module. For example:
Public Const InterestRate As Double = 0.0725
| Note | If your VBA code attempts to change the value of a constant, you get an error ( Assignment to constant not permitted ). This is what you would expect. A constant is a constant, not a variable. |
USING PREDEFINED CONSTANTS
Excel and VBA make available many predefined constants, which you can use without declaring. In fact, you don't even need to know the value of these constants to use them. The macro recorder generally uses constants rather than actual values. The following procedure uses a built-in constant ( xlLandscape ) to set the page orientation to landscape for the active sheet:
Sub SetToLandscape() ActiveSheet.PageSetup.Orientation = xlLandscape End Sub
I discovered the xlLandscape constant by recording a macro. I also could have found this information in the Help system. And, if you have the AutoList Members option turned on, you can often get some assistance while you enter your code (see Figure 8-2). In many cases, VBA lists all the constants that can be assigned to a property.
Figure 8-2: VBA displays a list of constants that can be assigned to a property.
The actual value for xlLandscape is 2 (which you can discover by using the Immediate window). The other built-in constant for changing paper orientation is xlPortrait , which has a value of 1 . Obviously, if you use the built-in constants, you don't really need to know their values.
| Note | The Object Browser, which I discuss briefly in Chapter 7, can display a list of all Excel and VBA constants. In the VBE, press F2 to bring up the Object Browser. |
Working with strings
Like Excel, VBA can manipulate both numbers and text (strings). There are two types of strings in VBA:
-
Fixed-length strings are declared with a specified number of characters. The maximum length is 65,535 characters.
-
Variable-length strings theoretically can hold up to 2 billion characters.
Each character in a string requires 1 byte of storage, plus a small amount of storage for the header of each string. When you declare a variable with a Dim statement as data type String , you can specify the length if you know it (that is, a fixed-length string), or you can let VBA handle it dynamically (a variable-length string).
In the following example, the MyString variable is declared to be a string with a maximum length of 50 characters. YourString is also declared as a string; but it's a variable-length string, so its length is unfixed.
Dim MyString As String * 50 Dim YourString As String
Working with dates
You can use a string variable to store a date, but if you do, it's not a real date (meaning you can't perform date calculations with it). Using the Date data type is a better way to work with dates.
A variable defined as a date uses 8 bytes of storage and can hold dates ranging from January 1, 0100 to December 31, 9999. That's a span of nearly 10,000 years - more than enough for even the most aggressive financial forecast! The Date data type is also useful for storing time- related data. In VBA, you specify dates and times by enclosing them between two hash marks ( # ).
| Note | The range of dates that VBA can handle is much larger than Excel's own date range, which begins with January 1, 1900. Therefore, be careful that you don't attempt to use a date in a worksheet that is outside of Excel's acceptable date range. |
Here are some examples of declaring variables and constants as Date data types:
Dim Today As Date Dim StartTime As Date Const FirstDay As Date = #1/1/2007# Const Noon = #12:00:00#
| Caution | Dates are always defined using month/day/year format, even if your system is set up to display dates in a different format (for example, day/month/year). |
| |
It is commonly known that Excel has a date bug: It incorrectly assumes that the year 1900 is a leap year. Even though there was no February 29, 1900, Excel accepts the following formula and displays the result as the 29th day of February, 1900:
=Date(1900,2,29)
VBA does not have this date bug. The VBA equivalent of Excel's DATE function is DateSerial . The following expression (correctly) returns March 1, 1900:
DateSerial(1900,2,29)
Therefore, Excel's date serial number system does not correspond exactly to the VBA date serial number system. These two systems return different values for dates between January 1, 1900 and February 28, 1900.
| |
If you use a message box to display a date, it is displayed according to your system's short date format. Similarly, a time is displayed according to your system's time format (either 12- or 24- hour ). You can modify these system settings by using the Regional Settings option in the Windows Control Panel.
| CD-ROM | The companion CD-ROM includes a workbook that contains several VBA worksheet functions that enable you to work with dates prior to January 1, 1900. The file is named |
extended date functions.xlsm 
EAN: 2147483647
Pages: 319