We discuss each scope further in the following sections.
A note about the examples in this chapter
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 don't 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 one declared within a procedure. You can use local variables only in the procedure in which they're declared. When the procedure ends, the variable no longer exists, and Excel frees up the memory that the variable used. If you need the variable to retain its value when the procedure ends, declare it as a Static variable. (See the section Static variables later in this chapter.)
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
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. Here's an example:
Dim x As Integer, y As Integer, z As Integer Dim First As Long, Last As Double
CAUTION
Unlike some languages, VBA doesn't 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.
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 Long Sub Procedure1() ' - [Code goes here] - End SubSub Procedure2() ' - [Code goes here] - End Sub
The value of a module-wide variable retains its value 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 variables in all modules 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 in 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're 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. Note that an End statement is not the same as an End Sub statement.
You declare static variables by using the Static keyword.
Sub MySub() Static Counter as Long '- [Code goes here] - End Sub
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. Not only does this technique make your code more readable, it also makes it easier to change should the need ariseyou 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 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. Here's an example:
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 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. Here's an 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 message 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
It's often useful to record a macro just to discover the various constants that can be used. And, if you have the AutoList Members option turned on, you can often get some assistance while you enter your code (see Figure 3.2). In many cases, VBA lists all the constants that you can assign to a property.
FIGURE 3.2 VBA displays a list of constants that you can assign 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 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 not fixed.
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 that 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 yearsmore 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, and extends through December 31, 9999. Therefore, be careful that you don't attempt to use a date in a worksheet that is outside Excel's acceptable date range.
In Chapter 5, Creating Function Procedures, we describe some relatively simple VBA functions that enable you to create formulas that work with pre-1900 dates in a worksheet.
About Excel's date bug
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 doesn't 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 doesn't 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.
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/2019# Const Noon = #12:00:00#
CAUTION
Dates are always defined using month/day/year format, even if your system is set to display dates in a different format (for example, day/month/year).
If you use a message box to display a date, it's 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.
Assignment Statements
An assignment statement is a VBA instruction that evaluates an expression and assigns the result to a variable or an object. Excel's Help system defines expression as a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can perform a calculation, manipulate characters, or test data.