This chapter covers the basics of working with variables, constants, and enumerations. Variables provide a way of storing and manipulating information. Variables come in several types, such as String variables for storing text, various numeric data types for storing numbers (for example, Integer variables for storing integer values), Date variables for storing dates and time, Boolean variables for storing True/False
values, and even Object variables for storing objects.
A constant is a named item that stores a value that doesn't change. Constants, like variables, exist only while a program is executing. Most programmers rarely create their own constants; they just use variables. However, many useful constants are built into VBA, to represent elements in Access, text color options in Excel, styles in Word, and so on. An enumeration is a predefined list of unique integers that have individual names and meanings in a particular context—essentially, an enumeration is a group of related, predefined constants. But constants are more commonly identified by words rather than the integers. That's because the constant word AnimationFlyIntoFromLeft
is easier to use in code than a number like 1312.
The one type of variable that this chapter doesn't discuss is the Array variable, which is used to store a set of multiple pieces of information at the same time (keeping them separate). Chapter 7, "Using Array Variables," shows you how to work with arrays.
In this chapter you will learn to do the following:
Understand what variables are and what you use them for
Create and use variables
Specify the scope and lifetime of a variable
Work with constants
Work with enumerations
A variable is a named area in memory that you use for storing data while a procedure is running. For example, in Chapter 5, "Understanding the Essentials of VBA Syntax," you created a variable that stored a simple string of text that you then displayed in a message box:
myVariable = "Sample variable text" MsgBox myVariable
The first statement sets aside an area in memory, names it myVariable
, and assigns the string Sample variable text
to it. The second statement retrieves the contents (called the value) of myVariable
from memory and uses the MsgBox
function to display it in a message box. The contents of myVariable
remain in memory, so you can use the value again if necessary while the macro is running.
VBA imposes several constraints on variable names:
Variable names must start with a letter and can be up to 255 characters in length. Usually, you'll want to keep them much shorter than this so that you can easily type them into your code and so that your lines of code don't rapidly reach awkward lengths.
The Visual Basic Editor's AutoComplete feature helps make long variable names a little more manageable: Type enough of the variable's name to distinguish it from any keywords and other variable names, and press Ctrl+spacebar. If you've typed enough letters to uniquely identify the variable, the Visual Basic Editor inserts its name; if not, the Visual Basic Editor displays the drop-down list of keywords and names starting with those letters.
Variable names can't contain characters such as periods, exclamation points, mathematical operators (+, −, /, *
), or comparison operators (=, <>, >, >=, <, <=
), nor can they internally contain type-declaration characters (@, &, $, #
). (You'll learn about the type-declaration characters later in this chapter.)
Variable names can't contain spaces but can contain underscores, which you can use to make the variable names more descriptive by combining words. User_Response
is one example. However, it's more common to just omit the underscore and let capitalization segregate the words, as in UserResponse
.
As a general rule, you're pretty safe if you stick with straightforward alphanumerics enlivened with the occasional underscore if you like underscores.
For example, all of the following variable names are fine, although the last one is awkwardly long:
i
John
MyVariable
MissionParameters
The_String_the_User_Entered_in_the_Input_Box
On the other hand, these variable names are not usable:
My Variable
—Contains a space
My!Variable
—Contains an exclamation point
Time@Tide
—Contains a type-declaration character (@)
1_String
—Does not start with a letter
Each variable name must be unique within the scope in which it's operating (to prevent VBA from confusing it with any other variable). Typically, the scope within which a variable operates is a procedure, but if you declare the variable as public or module-level private (discussed later in the chapter), its scope is wider.
The other constraint on variable names is that you should avoid assigning to a variable a name that VBA already uses as the name of a function, a statement, or a method. Doing so is called shadowing a VBA keyword. It doesn't necessarily cause problems, but it may prevent you from using that function, statement, or method without specifically identifying it to VBA by prefacing its name with VBA
. For example, instead of Date
, you'd have to use VBA.Date
—no big deal, but worth avoiding in the first place. After all, why add this complexity when it's simpler to just make up your own, unique variable names? Why do things that provide you with no real benefit and have drawbacks like making your code harder to read?
There's no reason to shadow a VBA keyword, but VBA has so many keywords that it's surprisingly easy to do so.
Don't worry about accidentally creating a variable name that violates one of the rules listed in this section. VB will throw you an error message if you use @ or start your variable name with 6 or try any other illegal moves. VB will either report "Invalid Character" or separate your variable name into multiple words, such as changing 56nin
into 56 nin
, thinking you are trying to use line numbers in your code. (You can, if you wish, number your lines, and VBA will execute the code by just ignoring the line numbers.)
Recall from Chapter 5 that VBA lets you declare variables either implicitly or explicitly. As you'll see shortly, each method has pros and cons. However, as you'll also see, explicit declarations are almost always a good idea, and when you've been working with VBA for even a little while, you'll probably use them all the time. For this reason, it's best to declare your variables explicitly right from the beginning. But this chapter also teaches you how to use implicit declarations so that you know how to do so.
Declaring a variable implicitly means that you just use it in your code without first declaring it explicitly. When you declare a variable implicitly, VBA checks to make sure that there isn't already an existing variable with that name. It then automatically creates a variable with that name for you and assigns it the Variant data type, which can contain any type of data except a fixed-length string.
For example, in the previous chapter, you declared the variable myVariable
by using the following implicit declaration:
myVariable = "Sample variable text"
Here, myVariable
is implicitly declared as a variable—because it is used in a statement rather than first being declared explicitly (usually with the Dim
command).
VBA assigns an implicitly declared variable to the Variant data type, which has a dozen or so subtypes. In this case, the variable's subtype is a string because it contains text. VBA usually assigns the variable the value Empty
(a special value used to indicate Variant variables that have never been used) when it creates it, but in this case the variable receives a value immediately (because the string of text is assigned to it). VBA then assigns the string type because it can see you're storing a string in the variable.
The advantage of declaring a variable implicitly is that you don't have to code it ahead of time. You write less code. When you want a variable, you simply declare it on the spot by using it in a statement. But declaring a variable implicitly also has a couple of disadvantages:
It's easier to make a mistake typing the variable's name elsewhere in your code. For example, suppose you implicitly declare the variable FilesToCreate
and then later type FllesToCreate
instead. VBA doesn't query the latter spelling (with its double ll
typo). No error messages are displayed. VBA merely creates another, new, different variable with the ll
name.
When you're working with a number of variables, it can be difficult and time consuming to catch little typo mistakes like these. And a mistake like this (having two variables when you think you have only one) causes errors. The problem in this example is that you think you're referring to the FilesToCreate
variable, but you're not. VBA can detect this kind of error, but only if explicit declaration is enforced. (Enforced here means that if you try to get away with using an undeclared variable—the one with the typo was never formally declared—the Visual Basic Editor displays an error message and halts execution.)
The Variant variable type takes up more memory than other types of variable because it has to be able to store various types of data. This difference is negligible under most normal circumstances, particularly if you're using only a few variables or writing only short procedures. However, if you're using many variables in a huge program running on a computer with limited memory, the extra memory used by Variant variables might slow down a procedure or even run the computer out of memory. What's more important on an underpowered computer is that manipulating Variants takes longer than manipulating the other data types. This is because VBA has to keep checking to see what sort of data is in the variable.
You can get around this second disadvantage in a couple of ways: first, by using a type-declaration character to specify the data type when you declare a variable implicitly or, second (as you will see in the next section), by simply telling VBA to force you to declare variables explicitly—and to display an error message if you don't.
A type-declaration character is a character that you add to the end of a variable's name in an implicit declaration to tell VBA which data type to use for the variable. Table 6.1 lists the type-declaration characters.
Table 6.1. Type-declaration characters
Character | Data Type of Variable | Example |
---|---|---|
| Integer |
|
| Long |
|
| Currency |
|
| Single |
|
| Double |
|
| String (variable length) |
|
So you could implicitly declare the String variable UserName
with the following statement, which assigns the value Jane Magnolia
to the variable:
UserName$ = "Jane Magnolia"
And you could implicitly declare the currency variable Price
by using this statement:
Price@ = Cost * Margin
You use the type-declaration character only when declaring the variable. Thereafter, you can refer to the variable by its name—UserName
and Price
in the previous examples.
Declaring a variable explicitly means telling VBA that the variable exists before you use it. VBA allocates memory space to that variable and registers it as a known quantity. You can also declare the variable type at the same time—a good idea but not obligatory.
You can declare a variable explicitly at any point in code before you use it, but custom and good sense recommend declaring all your variables at the beginning of the procedure that uses them. (Or, to give a variable greater scope, declare it in the General Declarations area up at the top of the Code window. More on scope later.)
Locating all your declarations at the top of a procedure makes them easy to find, which helps anyone reading the code.
Declaring variables explicitly offers the following advantages:
Your code is easier to read and to debug, both for you yourself and for other programmers. When you write complex code, this is an important consideration.
Forcing explicit variable declarations is accomplished by adding an Option Explicit
statement at the top of a module—in the General Declarations section of the Code window. This enforcement makes it more difficult for you to create new variables unintentionally by mistyping the names of existing variables.
It is also more difficult for you to wipe out the contents of an existing variable unintentionally when trying to create a new variable.
VBA can also catch some data-typing errors at design time or compile time that with implicit declarations wouldn't surface until runtime.
Your code runs a fraction faster because VBA won't need to determine each variable's type while the code is running.
The disadvantage of declaring variables explicitly is that doing so takes a little more time, effort, and thought. For most code, however, this disadvantage is far outweighed by the advantages.
To declare a variable explicitly, you use one of the following keywords: Dim, Private, Public
, or Static
.
For example, the following statement declares the variable MyValue
:
Dim MyValue
Dim
is the most common keyword to use for declaring a variable, and you'll probably want to use it for most of your variable declarations. You use the other keywords to specify a different scope, lifetime, and data type for the variable in the declaration. In the previous example, the MyValue
variable receives the default scope and lifetime and the Variant data type, which makes it suitable for general-purpose use.
You can also declare multiple variables on the same line by separating the variable statements with commas:
Dim Supervisor As String, ControllerCode As Long
This can help you keep down the number of declaration lines in your code, but it makes the declarations harder to read, so it's not usually a good idea.
Be warned that when you declare multiple variables on the same line, you must specify the data type for each, as in the previous example. You might be tempted to try a little abbreviation, like this, hoping for a couple of String variables:
Dim strManager, strReportingEmployee As String
This statement doesn't create two String variables: strReportingEmployee
is a String variable, but strManager
is a Variant because the As String
part of the code applies only to strReportingEmployee
.
The scope of a variable is the area in VBA within which it can operate. Think of it as similar to your scope of activity at work: what areas you perform tasks in, and what areas you don't. Your scope might be the office cubicles area of the building, but if you were found slinking around inside the walk-in safe, there would be trouble. Entering the safe is not part of your job description.
The default scope of a variable is the procedure that declares the variable (either implicitly or explicitly). In other words: the scope is between the Sub
and End Sub
(or Function
and End Function
) that define the start and end of a procedure. Macros are most often fairly short, and thus their code is most often contained within a single procedure. For these typical macros, there's no reason for a variable to have scope any larger than its own procedure.
Here's an example of procedure-level scope. Suppose you have a module named Financial_Procedures
that contains the procedures Breakeven_Table
and Profit_Analysis_Table
, each of which uses a variable named Gross_Revenue
and another named Expenses
. The variables in each procedure are distinct from the variables in the other procedure, so there is no danger of VBA confusing the two. (For the human reader, though, using the same variable names in different procedures rapidly becomes confusing when debugging. In general, it's a good idea to use unique variable names, even at the default procedure level.)
The lifetime of a variable is the period during which VBA remembers the value of the variable. You need different lifetimes for your variables for different purposes. A variable's lifetime is tied to its scope.
Sometimes you need to access a variable from outside the procedure in which it's declared. In these cases, you need to declare a different, wider scope for the variable.
A variable can have three types of scope:
Procedure
Private
Public
A variable with procedure scope (also known as procedure-level scope or local scope) is available only to the procedure that contains it. As a result, the lifetime of a local variable is limited to the duration of the procedure that declares it: As soon as that procedure stops running, VBA removes all local variables from memory and reclaims the memory that held them. This is true even if later on that same procedure is executed again. Local variables don't persist if execution moves outside their procedure.
Procedure scope is all you'll need for variables that operate only in the procedure in which they're declared. For example, say you implicitly declare a Variant variable named Supervisor
like this:
Supervisor = "Paul Smith"
You can then use the Supervisor
variable in the rest of that procedure—for example, retrieving the text stored in it or changing that text. When the procedure stops running, VBA removes the variable and reclaims the memory it occupied.
To explicitly declare a local variable, use the Dim
keyword and place the declaration inside the procedure like this:
Sub Create_Weekly_Report() Dim strSupervisor As String Dim lngController As Long ... End Sub
Here, the second line declares the variable strSupervisor
as the String data type, the third line declares the variable lngController
as the Long data type. (The section "Specifying the Data Type for a Variable," a bit later in this chapter, goes through the variable types.)
On the other hand, if you need to pass any of these variables to another procedure that you call from the current procedure, procedure scope isn't sufficient—you need to use either private scope or public scope.
A variable with private scope is available to all the other procedures in the module that contains it but not to procedures in other modules. Using private variables enables you to pass the value of a variable from one procedure to another. Unlike local variables, which retain their value only as long as the procedure that contains them is running, private variables retain their value as long as any procedure in the project that contains them is executing.
To declare a variable with private scope, you can use either the Dim
keyword or the Private
keyword at the beginning of a module, placing it up top before the Sub
statement for the first procedure in the module, like this:
Dim strSupervisor As String
Private
blnConsultantAssigned As Boolean
Sub Assign_Personnel()
The Visual Basic Editor displays the private declarations above the dividing line that appears between the General Declarations area and the code below it (see Figure 6.1).
You'll notice that the Dim
statement here uses exactly the same syntax as the earlier declaration for the local variable. The only difference is that to declare a private variable, you place the Dim
statement in the declarations area rather than within a procedure. Because the Private
statement has the same effect as the Dim
statement for declaring private variables and can't be used within a procedure, it's clearer to use the Private
statement rather than the Dim
statement for declaring private variables.
A variable declared with public scope is available anywhere in a project. It's accessible by all procedures in all modules in the project that contains it.
To declare a public variable, you use the Public
keyword in the General Declarations area at the beginning of a module (up above the Sub
statement for the first procedure in the module). Here's an example:
Option Explicit
Public
intMyVar As Integer
The second statement declares the public variable intMyVar
as the Integer type.
Like private variables, public variables retain their value as long as the project that contains them is open (still running). For example, if you want to track the user's name through a series of operations in Word, you can create an AutoExec
procedure that prompts users to enter their name when they start Word. (AutoExec
is the built-in name for a procedure that runs automatically when Word starts. Word, when you start it, searches to see if there is a Sub
named AutoExec
and, if so, executes that procedure.)
By storing the result of the user's input in a public variable, you can then retrieve the value for use anytime later in the same Word session. You can see how this would be handy if several macros needed the information contained in a variable. Remember that local variables (those declared inside a procedure) are destroyed as soon as that procedure reaches its End Sub
statement and shuts down.
Listing 6.1 shows an AutoExec
procedure:
Example 6.1. An AutoExec
procedure
1. Public strCurrentUser As String 2. 3. Sub AutoExec() 4. strCurrentUser = InputBox("Please enter your name.", _ "Current User Identity")
5. End Sub 6. 7. Sub Identify_Current_User() 8. MsgBox "The current user is " & strCurrentUser, _ vbOKOnly + vbInformation, "Current User" 9. End Sub
This code consists of three different parts:
Line 1 declares the public String variable strCurrentUser
.
Lines 3 through 5 contain the AutoExec
procedure. This procedure runs each time the user starts Word. Line 4 displays an input box that prompts users to enter their name and stores their response in the public variable strCurrentUser
.
Lines 7 through 9 contain the Identify_Current_User
procedure, which simply displays a message box that gives the name of the user, along with lead-in text and an information icon and title bar for completeness.
You can test these procedures by stepping through (by pressing the F8 key) first the AutoExec
procedure and then the Identify_Current_User
procedure in the Visual Basic Editor. But to see their effect, you'll have to create the procedures and then exit Word. When you restart Word, the AutoExec
procedure displays the input box for you to enter your name. At any point thereafter (until you exit Word), you can access the value in the strCurrentUser
variable. For example, you could run the Identify_Current_User
procedure at any time (until you close Word itself) and VBA displays a message box with the name you entered. A public variable is said to persist.
Besides declaring variables with Dim, Private
, and Public
, there's also the Static
keyword, which is special. You can use it to cause even a local variable to persist. Use Static
instead of Dim
when you want to declare a static variable—a variable whose values you want to preserve between calls to the procedure in which they are declared.
Static variables are similar to public variables in that their lifetime is not limited to the duration of the procedure that declares them. The difference is that static variables, once declared, are available only to the procedure that declared them, whereas public variables are available to all procedures once they've been declared. So, static variables have the scope of a local variable but the lifetime of a public or private variable.
Static variables are useful for maintaining information on a process that you need to run a number of times during a session of the application, either to maintain a running total (for example, a count of the times you performed a procedure) or to keep at hand a piece of information that may prove useful when you run a procedure a second or subsequent time. Typically you employ a static variable in a procedure that toggles something between two states. For example, you could create a procedure that when first executed turns on italics, then when next executed turns italics off, then back on, and so on. Such a toggle would look something like this:
Sub ToggleItal()
Static
switch As Boolean
switch = Not switch
If switch Then
MsgBox "on"
Else
MsgBox "Off"
End If
End Sub
You can test this by stepping through it (pressing F8 after clicking the first line of the procedure). Each time you execute the procedure, you get a different message. The Not
command switches a Boolean variable type back and forth between 1 (True) and 0 (False). A Boolean variable has only those two possible values.
The following statement declares the static String variable strSearchTerm1
:
Static strSearchTerm1 As String
Table 6.2 explains the data types that VBA supports and the amount of memory each variable type requires.
Table 6.2. VBA variable data types
Variable | Short Description | Memory Required |
---|---|---|
Boolean |
| 2 bytes |
Byte | An integer from 0 to 255 | 1 byte |
Currency | A positive or negative number with up to 15 digits to the left of the decimal point and 4 digits to the right of it | 8 bytes |
Date | A floating-point number with the date to the left of the decimal point and the time to the right of it | 8 bytes |
Decimal | An unsigned integer scaled by a power of 10 | 12 bytes |
Double | A floating-point number with a negative value from −1.79769313486232308 to −4.94065645841247−324 or a positive value from 4.94065645841247−324 to 1.79769313486232308 | 8 bytes |
Integer | An integer from −32,768 to 32,767 | 2 bytes |
Long | An integer from −2,147,483,648 to 2,147,483,647 | 4 bytes |
Object | A reference to an object | 4 bytes |
Single | A floating-point number with a negative value from −3.40282338 to −1.401298−45 or a positive value from 1.401298−45 to 3.40282338 | 4 bytes |
String | A string of text, either variable length or fixed length | Variable-length string: 10 bytes plus the storage for the string |
Fixed-length String: the storage for the string | A string whose length doesn't change | Whatever size is specified for the length |
Variant | Any type of data except a fixed-length string in a subtype of the Variant | Variants containing numbers: 16 bytes; Variants containing characters: 22 bytes plus the storage for the characters |
The next few pages discuss these data types in detail.
Specifying the data type for each variable you create is a good idea, but it's not compulsory. You can almost always use the default Variant data type (as you've done a couple of times so far in this book's examples) and let VBA figure out which subtype to assign to the Variant.
There are four disadvantages to using the Variant data type like this:
Sometimes VBA makes a mistake when trying to interpret which kind of subtype you intended. This can cause rather obscure bugs.
Using the Variant data type causes your code to run more slowly. However, with short procedures (or long procedures involving relatively few variables), memory and speed are rarely an issue.
Your code is harder for humans to read and to debug. This can be more of a concern than memory or speed issues.
The Variant data type takes up more memory than any of the other data types except long strings.
Chapter 18, "Building Well-Behaved Code," which discusses how to optimize your code, explains the pros and cons of specifying data types for your variables.
A Boolean variable can be set only to True
or False
. You can use the keywords True
and False
to set the value of a Boolean variable, as in the second line in the following code (the first declares the Boolean variable blnProduct_Available
):
Dim blnProduct_Available As Boolean
blnProduct_Available = True
You can then retrieve the result of the Boolean variable and take action accordingly:
If blnProduct_Available = True Then MsgBox "The product is available." Else 'blnProduct_Available = False MsgBox "The product is not available." End If
When you convert a Boolean variable to another data type (such as a numeric value), True
returns −1
and False
returns 0
. When you convert a numeric value to a Boolean value, 0
returns False
and all other numbers (whether positive or negative) return True
.
Boolean variables take up 2 bytes each in memory.
A Byte variable takes up the least memory of any data type—just 1 byte—and can store a number from 0 to 255.
The Currency data type is designed for use with money. It allows for positive and negative numbers with up to 15 digits to the left of the decimal point and 4 digits to the right of it. Unlike the Single and Double data types, the Currency data type is exact, not rounded.
To implicitly declare a Currency variable, use the type-declaration character @
. For example, you could work out your weekly salary with a little simple math:
Sub Calculate_Weekly_Salary() Salary@ = InputBox("Enter your salary.", _ "Calculate Weekly Salary") WeeklySalary@ = Salary / 52 MsgBox WeeklySalary End Sub
Currency variables take up 8 bytes each.
The Date data type is relatively complex. VBA works with dates and times as floating-point numbers, with the date displayed to the left of the decimal point and the time to the right. VBA can handle dates from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59.
You can enter date variables as literal date values—such as 6/30/36 or June 30, 1936—by placing a # sign before and after the literal date value:
#June 30, 1936#
When you move the insertion point from the line in the Code window in which you've entered a literal date value between # signs, VBA converts the data to a number and changes the display to the date format set in your computer. For example, if you enter June 30, 1936, VBA will probably display it as 6/30/36
. Likewise, you can enter a literal time value (for example, #10:15PM#) and VBA converts it to a number and displays it according to the current time format (for example, 10:15:00 PM
).
Date variables take up 8 bytes each.
The Decimal data type stores unsigned integers, scaled by powers of 10. Unsigned means that the integers carry no plus or minus designation. Note that you can't declare a Decimal variable directly: You can use the Decimal data type only within a Variant data type (discussed later in this section).
Decimal variables take up 12 bytes each.
The Double data type is for floating-point numbers and can handle negative values from −1.79769313486232308 to −4.94065645841247−324 and positive numbers from 4.94065645841247–324 to 1.79769313486232308. Some numbers in this range cannot be represented exactly in binary, so VBA rounds them.
Double here stands for double-precision floating point—the way in which the number is handled by the computer. Single (discussed later in this list) stands for single-precision floating point.
You can use the #
type-declaration character to declare a Double variable implicitly. Double variables take up 8 bytes each.
The Integer data type is the most efficient way of handling numbers within its range (from 32,768 to 32,767), a range that makes it useful for many procedures. For example, if you wanted to repeat an action 300 times, you could use an Integer variable for the counter, as in the following lines:
Dim intMyVar As Integer For intMyVar = 1 to 300 'repeat actions Next intMyVar
Integer variables take up 2 bytes each. The Integer is the most commonly used numeric data type for many programming tasks. This is because unless you're working with something like moon rockets or the national debt, most math will fall within the Integer type's range.
The Long data type is for the national debt. A Long can hold integer numeric values larger or smaller than those the Integer data type can handle: Long variables can handle numbers from −2,147,483,648 to 2,147,483,647. (For numbers even larger or smaller than these, use the Double data type, but beware of its rounding.)
Long variables use the type-declaration character &
for implicit declarations and take up 4 bytes each.
The Object data type is for storing addresses that reference objects (for example, objects in an application's object model), providing an easy way to refer to an object.
Object variables take up 4 bytes each.
The Single data type, like the Double data type, is for working with floating-point numbers. Single can handle negative values from −3.40282338 to −1.401298–45 and positive values from 1.401298–45 to 3.40282338. Some numbers in this range cannot be represented exactly in binary, so VBA rounds them.
Use the exclamation point type-declaration character to declare a Single variable implicitly (if you must use implicit declarations). Single variables take up 4 bytes each.
The String data type is for handling text:
Variable-length strings can contain up to about 2 billion characters. They take up 10 bytes plus the storage required for the string.
Fixed-length strings can contain from 1 to about 64,000 characters. They take up only the storage required for the string. If the data assigned to the string is shorter than the fixed length, VBA pads the data with trailing spaces to make up the full complement of characters. If the data assigned to the string is longer than the fixed length, VBA truncates the data after the relevant character. VBA counts the characters from the left end of the string—for example, if you assign the string Output
to a fixed-length string that's four characters long, VBA stores Outp
. Fixed-length strings are rarely used in most programming, with the exception of managing certain databases where there's a rule that a string cannot be longer than a specified length.
Strings can contain letters, numbers (digits), spaces, and punctuation, not to mention special characters like @
and *
.
You can use the $
type-declaration character to declare a string implicitly, but (as usual) you'll do best to declare your strings explicitly, along with all your other variables.
The Variant data type, as mentioned earlier in this chapter, is the default type. It's assigned by VBA to any variable whose data type isn't specified by you—so a declaration such as Dim myUntypedVariable
creates a Variant. However, Dim intVariable As Integer
creates a variable of the Integer data type. (You can also declare a Variant variable explicitly: Dim myVariant As Variant
, for example.)
Variants can handle most of the different types of data, but there are a couple characteristics of Variants to keep in mind:
Variants can't contain fixed-length string data. If you need to use a fixed-length string, you must specify a fixed-length string data type.
Variant variables can also contain four special values: Empty
(which means the variable hasn't yet been initialized), Error
(a special value used for tracking errors in a procedure), Nothing
(a special value used for disassociating a variable from the object it was associated with), and Null
(which you use to indicate that the variable deliberately contains no data).
Variant variables take up more memory than other types. Variant variables that contain numbers take up 16 bytes, and Variant variables that contain characters take up 22 bytes plus the storage required for the characters.
If you found the details of the different types of variables confusing, relax. First, you can usually avoid the whole issue of choosing a variable type by declaring the variable either implicitly or explicitly and letting VBA assign the Variant data type with the appropriate subtype. Second, if you do choose to specify data types for some or all of your variables, you can apply a few straightforward rules to direct your choices:
If the variable will contain only the values True
and False
, declare it as the Boolean data type.
If the variable will always contain an integer (if it will never contain a fraction), declare it as the Integer data type. (If the number may be too big for the Integer data type, declare it as the Long data type instead.)
If the variable will be used for calculating money, or if you require no-rounding fractions, use the Currency data type.
If the variable may sometimes contain a fraction, declare it as the Single or Double data type.
If the variable will always contain a string, declare it as the String data type.
A constant is a named item that keeps a constant value during execution of a program. VBA provides many built-in constants, but you can also declare your own constants to help you work with information that stays constant through a procedure. But recall that many programmers simply use variables rather than constants, even for values that won't change (such as the number of eggs in a dozen). However, constants are available if you or your superiors find them useful.
To declare your own constants, use the Const
statement. By declaring a constant, you can simplify your code when you need to reuse a set value a number of times in your procedures.
The syntax for the Const
statement is as follows:
[Public/Private] Constconstant
[Astype
] =expression
Here, Public
and Private
are optional keywords used for declaring public or private scope for a constant. You'll learn how they work in a moment. constant is the name of the constant, which follows the normal rules for naming variables. type is an optional argument that specifies the data type of the constant. expression is a literal (a value written into your code), another constant, or a combination of the two.
As with variables, you can declare multiple constants in the same line by separating the statements with a comma:
Const conPerformer As String = "Carmen Singer", _ conTicketPrice As String = "$34.99"
Declaring a constant in VBA works in a similar way to declaring a variable explicitly, but you declare the value of the constant when you declare the constant (rather than at a later point of your choosing). You can't change its value afterward.
As an example, take a look at the following statements:
Const
conVenue As String = "Davies Hall"Const
conDate As Date = #December 31, 2010# MsgBox "The concert is at " & conVenue & " on " _ & conDate & "."
The first line declares the constant conVenue
as a String data type and assigns it the data Davies Hall
. The second line declares the constant conDate
as a Date string type and assigns it the date December 31, 2010
. (When you finish creating this line of code and move the insertion point to another line, VBA changes the date to the date format set in your computer's clock—#12/31/2010#
, for example.) The third line displays a message box containing a string concatenated from the three text items in double quotation marks, the conVenue
string constant, and the conDate
date constant.
Scope works the same way for constants as it does for variables. The default scope for a constant declared in a procedure is local—that is, its scope is the procedure that declares it. Consequently, its lifetime is the time for which the procedure runs. But you can set a different scope and lifetime for your constants by using the Public
or Private
keyword.
To declare a private constant, place the declaration at the beginning of the module in which you want the constant to be available. A private constant's lifetime isn't limited, but it's available only to procedures in the module in which it's declared:
Private Const conPerformer As String = "Carmen Singer"
To declare a public constant, place the declaration at the beginning of a module. A public constant's lifetime isn't limited, and it's available to all procedures in all modules in the project in which it's declared:
Public Const conTicketPrice As String = "$34.99"
In addition to constants you can create in your code, VBA includes sets of predefined constants. An enumeration is a predefined list of unique integers that have individual names and meanings in a particular context. An enumeration is typically used to specify a property of an object. Each integer in the enumeration has a meaning to VBA and a name that allows you to refer to it easily. The names that correspond to the integers in the enumeration are called enumerated constants.
For example, when you use the MsgBox
function to display a message box using VBA, you can use the enumerated constants in the VbMsgBoxStyle
enumeration to specify the type of message box you want to show. If you require an icon in the message box, you can specify which icon from the list of available built-in icons. For example, one of the icons—a stop sign—is the enumerated constant vbCritical
or the integer 16
. The enumerated constant vbQuestion
or the integer 32
displays a question-mark icon, and the enumerated constant vbExclamation
or the integer 48
displays an exclamation-point icon. The enumerated constant vbInformation
or the integer 64
refer to an information icon. The enumerated constants (names like vbQuestion
) are far easier for humans to grasp, read, and remember than the values (the various integers like 16, 32, 64
, and so on) to which they are mapped. So, although you could use the integers in your code, it's better to stick with the enumerated constants like vbQuestion
.
VBA includes many built-in enumerations, and the Visual Basic Editor displays the list of available enumerated constants to help you select the appropriate integer value when you're creating code. To see such a list, type this into a procedure:
msgbox("inga",
As soon as you type the comma, up pops the list of enumerated constants, all the available button styles for a message box, including vbQuestion, vbYesNo, vbOKOnly
, and so on. As you might guess, the vbOKOnly
style displays only a single button, captioned OK. The vbYesNo
style displays two buttons, one captioned Yes, the other No.
You just click one of these button styles in the list of enumerated constants to enter it into your code. If you don't see the list, choose Tools
You can also define your own enumerations in custom objects that you create.
Variables are a cornerstone of computer programming; they are extremely useful for the same reason that files are useful in the real world. You give a name to a variable for the same reason that you write a name to identify a file folder. And a file can, over time, contain different papers, just as the value contained in a variable can vary. The contents vary; the name remains the same. It's good practice to always specifically name a variable before using it in your code. This is called explicit declaration.
Explicitly declare a variable named CustomersAge
.
When creating (declaring) a new variable, you should avoid using words or commands that are already in use by VBA, such as Stop
or End
. There are also restrictions such as not using special characters.
This variable name cannot be used for two reasons. Fix it so it is a legitimate variable name:
Dim 1Turn! as Integer
Variables have a range of influence, depending on how you declare them.
Create a variable named AnnualSales
that will be available to any procedure within its own module but not to other modules.
Constants, like variables, are named locations in memory that contain a value. Unlike variables, however, the value in a constant does not change during program execution.
Define a string constant using the Dim
command. Name your constant FirstPrez
, and assign it the value George Washington
.
Enumerations provide a handy name for each item in a list, often a list of properties.
In the Project Explorer, click the ThisDocument
object in the Normal project to select it. Then locate the JustificationMode
property in the Properties window, and open its list of enumerated values.
18.118.162.201