Chapter 6

Working with Variables, Constants, and Enumerations

This chapter covers the basics of working with variables, constants, and enumerations. Variables are used very often; they 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 instead. However, there is another kind of constant that the programmer does not create. And this type of constant is used all the time. Many useful constants are built into VBA, to represent elements in Access, text color options in Excel, styles in Word, and so on.

For our purposes, the term enumeration means a numbered list—like a list of all the items you need to buy to paint a room. The list contains both the numbers and the names of the items. So you can refer to each item either by its number in the list or by its name. Essentially, an enumeration is a group of related, predefined constants. But constants are more commonly identified by their names rather than their numbers in the list. That's because the name AnimationFlylntoFromLeft is easier to use in your programming than its number, 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 related information at the same time. It's similar to an enumeration. Arrays are so important in computer programming that I'll devote an entire chapter to them: Chapter 7, “Using Array Variables.”

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

Working with Variables

Variables are used in nearly all computer programs, even short programs like macros. Think of a variable as a named area in the computer's 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. Or you can even change the contents. In other words, the value in a variable can vary while the program runs. A constant, by contrast, doesn't vary during program execution.

Choosing Names for Variables

VBA imposes several constraints on how you name your variables:

• 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 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 in its own language or the name of a built-in function, statement, or object member. 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. VBA will throw you an error message if you use @ or start your variable name with 6 or try any other illegal moves. VBA 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. I number the lines in the code in this book so I can reference them in the text.)

Declaring a Variable

Recall from Chapter 5 that VBA lets you declare variables either implicitly or explicitly. As you'll see shortly, each approach has its 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 illustrates how to make implicit declarations so you know that technique if that's your preference.

DECLARING A VARIABLE IMPLICITLY

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 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 this. 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 variables 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.

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

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 more difficult for you to wipe out the contents of an existing variable unintentionally when trying to create a new variable.
• VBA can 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.

Choosing the Scope and Lifetime of a Variable

The scope of a variable is the area in VBA where it can operate. Think of it as similar to your scope of activity at work: those areas in which you perform tasks and those areas in which 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 a 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. Lifetime, here, refers to how long during program execution the variable is in existence.

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

PROCEDURE SCOPE

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, and 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.

PRIVATE 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).

FIGURE 6.1 Private variable declarations appear in the declarations area.

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.

PUBLIC SCOPE

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.

http://en.wikipedia.org/wiki/Leszynski_naming_convention

Listing 6.1 shows 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 the user 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.

http://c2.com/cgi/wiki?GlobalVariablesAreBad

USING STATIC VARIABLES

Besides declaring variables with Dim, Private, and Public, you can also use 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, a static variable has the scope of a local variable but the lifetime of a public or private variable. There is one particular situation where static variables come in handy: toggling.

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 True and False. A Boolean variable has only those two possible values.

The following statement declares the static String variable strSearchTerm1:

Static strSearchTerm1 As String

Specifying the Data Type for a Variable

Table 6.2 explains the data types that VBA supports and the amount of memory each variable type requires.

The next few pages discuss these data types in detail.

DO YOU NEED TO SPECIFY THE DATA TYPE?

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.

BOOLEAN

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.

BYTE

A Byte variable takes up the least memory of any data type—just 1 byte—and can store a number from 0 to 255.

CURRENCY

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.

DATE

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.

DECIMAL

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.

DOUBLE

The Double data type is for floating-point numbers and can handle negative values from −1.79769313486231570E+308 to −4.94065645841246544E−324 and positive numbers from 4.94065645841246544E−324 to 1.79769313486231570E+308

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) 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.

INTEGER

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.

LONG

The Long data type is for the national debt. A Long can hold integer 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.

OBJECT

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.

SINGLE

The Single data type, like the Double data type, is for working with floating-point numbers. Single can handle negative values from −3.4028235E+38 through −1.401298E-45 and positive values from 1.401298E-45 through 3.4028235E+38

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.

STRING

The String data type is for handling text:

• Variable-length String variables can contain up to about 2 billion characters. They take up 10 bytes plus the storage required for the string.
• Fixed-length String variables 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 variable 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 variable 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 variable that's four characters long, VBA stores Outp. Fixed-length String variables 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 variable implicitly, but (as usual) you'll do best to declare your String variables explicitly, along with all your other variables.

VARIANT

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 of 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 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.

DECIDING AMONG TYPES FOR VARIABLES

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.

Working with Constants

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 of value.

Declaring Your Own Constants

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.

SYNTAX

The syntax for the Const statement is as follows:

[Public/Private] Const constant [As type] = 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"

EXAMPLE

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, 2013#
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, 2013. (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/2013#, 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.

Choosing the Scope and Lifetime for Your Constants

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"

Working with Enumerations

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 (numbers) that have individual names. It's a set of items, related in some way.

Here's an enumeration, a set of items that you need to paint a room. Note that another way to describe this is that it's a numbered list:

1. Brushes
2. Paint
3. Masking tape
4. Drop cloth
5. Sandpaper

You could now refer to any of these items by either their number in the enumeration or by their name.

An enumeration is typically used in your programming 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 pick one of 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 (integer 32) displays a question-mark icon, and the enumerated constant vbExclamation (48) displays an exclamation-point icon. The enumerated constant vbInformation (64) refers to an information icon. However, in practice, the integers are rarely used. 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 Options in the Visual Basic Editor, then select the Auto List Members check box.

You can also define your own enumerations in custom objects that you create.

The Bottom Line

Understand what variables are and what you use them for. 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 various different papers, just as the value contained in a programming variable can vary. In both cases, 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.

Master It Explicitly declare a variable named CustomersAge.

Create and use variables. 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.

Master It This variable name cannot be used, for two reasons. Fix it so it is a legitimate variable name:

Dim 1Turn! as Integer

Specify the scope and lifetime of a variable. Variables have a range of influence, depending on how you declare them.

Master It Create a variable named AnnualSales that will be available to any procedure within its own module but not to other modules.

Work with constants. Constants, like variables, are named locations in memory that contain a value. Unlike with variables, however, the value in a constant does not change during program execution.

Master It Define a string constant using the Dim command. Name your constant FirstPrez, and assign it the value George Washington.

Work with enumerations. Enumerations provide a handy name for each item in a list, often a list of properties.

Master It In the Project Explorer, click the ThisDocument object to select it. Then locate the JustificationMode property in the Properties window, and choose one of that property's enumerated constants by clicking the small down arrow that appears, then clicking one of the constants in the drop-down list.