DECLARING A VARIABLE IMPLICITLY

Declaring a variable implicitly means that you just use it in your code without first declaring it explicitly (for example: Dim x As Integer).

When you declare a variable implicitly, the first time you mention it in your code is when you assign a value to it (x = 12). This implicit value assignment causes VBA to check to make sure that there isn't already an existing variable with that same 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, which we'll get to shortly).

VBA assigns an implicitly declared variable to the Variant data type, which has a dozen or so subtypes. In the previous example, the variable's subtype is string because you assigned text to it. VBA usually assigns the variable the value Empty (a special value used to indicate Variant variables that have not yet been used during execution) when it creates it, but in our example 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 rather serious disadvantages:

• Typos It's easier to make a mistake when you're 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 many variables, it can be difficult and time-consuming to catch little typo mistakes like this. This kind of mistake (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 Visual Basic Editor displays an error message and halts execution.)

• Memory issue 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 (thought pretty rare these days), 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. Memory and speed are far less significant issues than they used to be, but some special-purpose computers (built into a forklift for instance) can even today be small and slow.

  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. However, this technique is rarely employed any more.

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.

You could implicitly declare the String variable UserName with the following statement, which assigns the value Jane Magnolia to the variable:

UserName$ = “Jane Magnolia"

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 is the best practice. It means telling VBA that the variable exists before you use it. VBA allocates memory space to that variable and registers it. You can also declare the variable type at the same time, which is 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 (available to all your macros in the module), declare it in the General Declarations area up at the top of the Code window. You'll learn more about scope later.)

Locating all your declarations at the start of a macro's code 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 and other programmers. When you write complex code, this is an important consideration.
• Making the Editor enforce 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-type errors at design time or compile time that, with implicit declaration, 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 minor 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, or data type for the variable in the declaration. In the previous example, the MyValue variable will have 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 a little 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 will be a String variable, but strManager will be a Variant because the As String part of the code applies only to strReportingEmployee. And the default, if the type isn't specified, is Variant.

PROCEDURE SCOPE

Recall that 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 time that its procedure is actively running. As soon as that procedure stops running (the End Sub line of code executes). VBA evaporates all local variables and reclaims the memory that held them for future use. This is true even if later on that same procedure is executed again. Local variables don't persist once execution moves outside their procedure. But again, macros are usually written within only a single procedure, so local variables are usually all you need.

For example, say you declare a Variant variable named Supervisor, like this:

Dim Supervisor = “Paul Smith"

You can then use this Supervisor variable in the rest of that procedure—for example, retrieving the text stored in it or changing that text. But when the procedure stops running, VBA removes the variable. VBA forgets its name and its value.

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 (local) scope isn't sufficient—you need to use either private scope or public scope.

PRIVATE SCOPE

A variable declared 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, without needing to create a function. 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, but use them up 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()
…
End Sub

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

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 here you place the Dim statement in the declarations area of a module (up top) rather than within a particular 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. (The term private is a bit confusing because they're not private to a procedure, only private to their module.)

PUBLIC SCOPE

A variable declared with public scope is available anywhere in an entire 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 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 retrieve the value for use any time 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. And Private variables are limited to a single module.

Listing 6.1 shows an AutoExec procedure.

This code consists of three different parts: the General Declarations area, followed by two macros:

• 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. 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, a synonym for remain alive.

USING STATIC VARIABLES

Besides declaring variables with Dim, Private, and Public, you can also use the Static keyword, which is special. You use it to extend the lifetime of a local variable. 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. To be practical, there is one particular situation where static variables come in handy: toggling. Think of a light switch. It has two possible states: on or off. But because it's a physical switch, it is always in one of those positions. In other words, its “state” persists. And when you flip it, reversing its state, you can be said to toggle it.

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, but when next executed turns italics off. Then back on, then off, and so on. In code, such a toggle behavior 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 as usual 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. Just like a light switch. The Not command in effect flips the switch.

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're permitted to 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.

But remember that even though something is possible, it might not be wise. You can drive with your legs crossed, but it's a bad idea.

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.
• The Variant data type takes up more memory than any of the other data types except long strings.
• Your code is harder for humans to read and to debug. This is more of a concern than speed or memory issues.

To sum up, most programming experts frown on the Variant and suggest that it's better to always specify the data types when you declare your variables. So let's now take a closer look at the data types in VBA.

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

Variables can be converted from one data type to a different one. 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

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

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

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.

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

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 (integer) data type is for calculating the California state 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 problem.)

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.

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

STRING

The String data type is for handling text, and it comes in two flavors—variable-length and fixed-length:

• 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 text assigned to a fixed-length String variable is longer than the fixed length, VBA truncates the text, lopping off the necessary characters from the right end of the string.

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 types of database fields, where the number of characters is predictable and invariable. For example, state abbreviations could be stored in a fixed length of two. This approach can be useful because no delimiter is necessary. It's speedy, too.

• You declare a two-character fixed-length string like this:

  Dim strStateAbbreviation As String * 2

• Strings can contain letters, numbers (digit characters, rather than true numbers), 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. You'll mostly use strings and integers. Here are some straightforward rules to direct your choices:

• If the variable will contain only the values True and False, you can 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, which is like an integer only bigger.)
• 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.

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