VBA Programming Basics

The rest of this chapter gives you a very basic introduction to programming VBA code. What you learn won't make you an expert VBA programmer by any means, but you'll learn enough to write your own simple macros and understand the examples I take you through in Chapter 12.

Understanding Program Variables

Your VBA procedures often will need to store temporary values for use in later statements and calculations. For example, you might want to store values for total sales and total expenses to use later in a gross margin calculation. Although you probably could get away with using the underlying application to store these values (for example, in a cell in an Excel worksheet), this almost always isn't practical. Instead, VBA (like all programming languages) lets you store temporary values in variables.

Declaring Variables

Declaring a variable tells VBA the name of the variable you're going to use. (It also serves to specify the data type of the variable, which I'll explain later in this section; see "Variable Data Types.") You declare variables by including Dim statements (Dim is short for dimension) at the beginning of each Sub or Function procedure.

The Placement of Variable Declarations

Technically, you can put variable declarations anywhere you like within a procedure and VBA won't complain. The only real restriction is that the Dim statement must precede the first use of the variable in a procedure. However, it's not only traditional to list all your Dim statements together at the top of a procedure, it's also clearer.

In its simplest form, a Dim statement has the following syntax:

 Dim variableName 

Here, variableName is the name of the variable. The name must begin with a letter, it can't be longer than 255 characters, it can't be a VBA keyword, and it can't contain a space or any of the following characters:

 . ! # $ % & @ 

For example, the following statement declares a variable named totalSales:

 Dim totalSales 

Variable Case Considerations

To avoid confusing variables with the names of objects, properties, or methods, many programmers begin their variable names with a lowercase letter. This is the style I use in this book.

Also, note that VBA preserves the case of your variable names throughout a procedure. For example, if you declare a variable named totalSales and you later enter this variable name as, for instance, totalsales, VBA will convert the name to totalSales automatically as part of its syntax checking. This means two things:

• If you want to change the case used in a variable, change the first instance of the variable (usually the Dim statement).

• After you've declared a variable, you should enter all subsequent references to the variable entirely in lowercase. Not only is this easier to type, but you'll immediately know if you've misspelled the variable name if you see that VBA doesn't change the case of the variable name after you enter the line.

Most programmers set up a declaration section at the beginning of each procedure and use it to hold all their Dim statements. Then, after the variables have been declared, they can use them throughout the procedure. Listing 11.3 shows a function macro that declares two variablestotalSales and totalExpensesand then uses Excel's Sum function to store a range sum in each variable. Finally, the GrossMargin calculation uses each variable to return the function result.

Listing 11.3. A Function That Uses Variables to Store the Intermediate Values of a Calculation

 Function GrossMargin()     '     ' Declarations     '      Dim totalSales     Dim totalExpenses     '     ' Code     '      totalSales = Application.Sum(Range("Sales"))     totalExpenses = Application.Sum(Range("Expenses"))     GrossMargin = (totalSales - totalExpenses) / totalSales End Function 

In the GrossMargin function, notice that you store a value in a variable with a simple assignment statement of the following form:

 variableName = value 

Variable Data Types

The data type of a variable determines the kind of data the variable can hold. Table 11.1 lists all the VBA data types.

Table 11.1. The VBA Data Types

Data Type	Description
Boolean	Takes one of two logical values: TRue or False.
Byte	Used for small, positive integer values (from 0 to 255).
Currency	Used for monetary or fixed-decimal calculations where accuracy is important. The value range is from 922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Date	Used for holding date data. The range is from January 1, 0100 to December 31, 9999. When setting a value to a Date variable, enclose the date in pound signs (for example, newDate = #8/23/2005#).
Double	Double-precision floating point. Negative numbers range from 1.79769313486232E308 to 4.94065645841247E324. Positive numbers range from 4.94065645841247E324 to 1.79769313486232E308.
Integer	Used for integer values in the range 32,768 to 32,767.
Long	Large integer values. The range is from 2,147,483,648 to 2,147,483,647.
Object	Used for objects (see "Working with Objects," later in this chapter).
Single	Single-precision floating point. Negative numbers range from 3.402823E38 to 1.401298E45. Positive numbers range from 1.401298E45 to 3.402823E38.
String	Holds text values.
Variant	Can take any kind of data.

You specify a data type by including the As keyword in a Dim statement. Here's the general syntax:

 Dim variableName As DataType 

variableName is the name of the variable and DataType is one of the data types from Table 11.1. For example, the following statement declares a variable named textString to be of type String:

 Dim textString As String 

Building VBA Expressions

VBA variables aren't much use unless you do something with them. In other words, a procedure is merely a lifeless collection of Dim statements until you define some kind of relationship among the variables and your program objects. To establish these relationships, you need to create expressions that perform calculations and produce results. This section introduces you to the basics of building VBA expressions.

Understanding Expression Structure

You can think of an expression as being like a compact version of a function macro. In other words, in the same way that a function takes one or more arguments, combines them in various ways, and returns a value, so too does an expression take one or more inputs (called operands), combine them with special symbols (called operators), and produce a result. The main difference, though, is that an expression must do everything in a single VBA statement.

For example, consider the following statement:

 might = "right" 

Here, the left side of the equation is a variable named might. The right side of the equation is the simplest of all expressions: a text string. In other words, a string value is being stored in a variable.

Here's a slightly more complex example:

 energy = mass * (speedOfLight ^ 2) 

Again, the left side of the equation is a variable (named energy) and the right side of the equation is an expression. For the latter, a variable named speedOfLight is squared and then this result is multiplied by another variable named mass. In this example, you see the two main components of any expression:

• Operands These are the "input values" used by the expression. They can be variables, object properties, function results, or literals. (A literal is a specific value, such as a number or text string. In the first expression example, "right" is a string literal.)

• Operators These are symbols that combine the operands to produce a result. In the example just shown, the * symbol represents multiplication and the ^ symbol represents exponentiation.

This combination of operands and operators produces a result that conforms to one of the variable data types outlined earlier: Date, String, Boolean, Object, Variant, or one of the numeric data typesByte, Integer, Long, Single, Double, or Currency. When building your expressions, the main point to keep in mind is that you must maintain data type consistency throughout the expression. This means you must watch for three things:

• The operands must use compatible data types. Although it's okay to combine, for example, an Integer operand with a Long operand (because they're both numeric data types), it wouldn't make sense to use a Double operand and a String operand, for instance.

• The operators you use must match the data types of the operands. For example, you wouldn't want to multiply two strings together.

• If you're storing the expression result in a variable, make sure the variable's data type is consistent with the type of result produced by the expression. For example, don't use a Currency variable to store the result of a string expression.

VBA Operators

You've already seen the first of VBA's operators: the assignment operator, which is just the humble equal sign (=). You use the assignment operator to assign the result of an expression to a variable or to an object property.

Note that the assignment operator is not the same as the equal sign from mathematics. To understand why, you need to bear in mind that VBA always derives the result of the right side of the equation (that is, the expression) before it modifies the value of the left side of the equation. This seems like obvious behavior, but it's the source of a handy trick that you'll use quite often. In other words, you can use the current value of whatever is on the left side of the equation as part of the expression on the right side. For example, consider the following code fragment:

 currYear = 2005 currYear = currYear + 1 

The first statement assigns the literal value 2005 to the currYear variable. The second statement also changes the value stored in the currYear, but it uses the expression currYear + 1 to do it. This looks weird until you remember that VBA always evaluates the expression first. In other words, it takes the current value of currYear, which is 2005, and adds 1 to it. The result is 2006 and that is what's stored in currYear when all is said and done.

VBA has a number of different operators that you can use to combine functions, variables, and values in a VBA expression. These operators work much like the operatorssuch as addition (+) and multiplication (*)that you use to build formulas in Excel worksheets and Word tables. VBA operators fall into four general categories: arithmetic, concatenation, comparison, and logical.

VBA's arithmetic operators are similar to those you use to build Excel formulas, Word fields, and Access expressions. Table 11.2 lists each of the arithmetic operators you can use in your VBA statements.

Table 11.2. The VBA Arithmetic Operators

Operator	Name	Example	Result
+	Addition	10+5	15
-	Subtraction	10-5	5
-	Negation	-10	-10
*	Multiplication	10*5	50
/	Division	10/5	2
\	Integer division	11\5	2
^	Exponentiation	10^5	100000
Mod	Modulus	10 Mod 5	0

You use the concatenation operator (&) to combine text strings within an expression. One way to use the concatenation operator is to combine string literals. For example, the expression "soft" & "ware" returns the string software. Note that the quotation marks and ampersand aren't shown in the result. You can also use & to combine any kind of operand, as long as the operands use the String data type.

You use the comparison operators in an expression that compares two or more numbers, text strings, variables, or function results. If the statement is true, the result of the formula is given the logical value TRue (which is equivalent to any nonzero value). If the statement is false, the formula returns the logical value False (which is equivalent to 0). Table 11.3 summarizes VBA's comparison operators.

Table 11.3. The VBA Comparison Operators

Operator	Name	Example	Result
=	Equal to	10=5	False
>	Greater than	10>5	TRue
<	Less than	10<5	False
>=	Greater than or equal to	"a">="b"	False
<=	Less than or equal to	"a"<="b"	true
<>	Not equal to	"a"<>"b"	true
Like	Like	"Smith" Like "Sm?th"	true

You use the logical operators to combine or modify TRue/False expressions. Table 11.4 summarizes VBA's logical operators.

Table 11.4. The VBA Logical Operators

Operator	General Form	What It Returns
And	Expr1 And Expr2	TRue if both Expr1 and Expr2 are true; False otherwise.
Eqv	Expr1 Eqv Expr2	true if both Expr1 and Expr2 are TRue or if both Expr1 and Expr2 are False; False otherwise.
Imp	Expr1 Imp Expr2	False if Expr1 is true and Expr2 is False; true otherwise.
Or	Expr1 Or Expr2	true if at least one of Expr1 and Expr2 are true; False otherwise.
Xor	Expr1 Xor Expr2	False if both Expr1 and Expr2 are true or if both Expr1 and Expr2 are False; true otherwise.
Not	Not Expr	true if Expr is False; False if Expr is true.

Working with Objects

Many of your VBA procedures will perform calculations using simple combinations of numbers, operators, and the host application's built-in functions. You'll probably find, however, that most of your code manipulates the application environment in some way, whether it's formatting document text, entering data in a worksheet range, or setting application options. Each of these itemsthe document, the range, the applicationis called an object in VBA.

You can manipulate objects in VBA in various ways, but we'll concentrate on the following two:

• You can make changes to the object's properties.

• You can make the object perform a task by activating a method associated with the object.

Each Office application's objects are arranged in a hierarchy. The most general objectthe Application objectrefers to the program itself. In Word, for example, the Application object contains more than 30 objects, including the Documents object (the collection of all open documents, each one a Document object), the Options object (the settings available in the Options dialog box), and the RecentFiles object (the names of the files that have been used most recently).

Many of these objects have objects beneath them in the hierarchy. A Document object, for example, contains objects that represent the document's characters, words, sentences, paragraphs, bookmarks, and much more. Similarly, a Paragraph object contains objects for the paragraph format and the tab stops.

To specify an object in the hierarchy, you usually start with the uppermost object and add the lower objects, separated by periods. For example, following is one way you could specify the first word in the second paragraph in a document named Memo.doc:

 Application.Documents("Memo.doc").Paragraphs(2).Range.Words(1) 

As you'll see, there are ways to shorten such long-winded "hierarchical paths."

Working with Object Properties

Every object has a defining set of characteristics. These characteristics are called the object's properties, and they control the appearance and position of the object. For example, each Window object has a WindowState property you can use to display a window as maximized, minimized, or normal. Similarly, a Word Document object has a Name property to hold the filename, a Saved property that tells you whether the document has changed since the last save, a Type property to hold the document type (regular or template), and many more.

When you refer to a property, you use the following syntax:

 Object.Property 

For example, the following expression refers to the ActiveWindow property of the Application object:

 Application.ActiveWindow 

Setting the Value of a Property

To set a property to a certain value, you use the following syntax:

 Object.Property = value 

Here, value is an expression that returns the value to which you want to set the property. As such, it can be any of VBA's recognized data types, including the following:

• A numeric valueFor example, the following statement sets the size of the font in the active cell to 14:

   ActiveCell.Font.Size = 14 

  
  

• A string valueThe following example sets the font name in the active cell to Times New Roman:

   ActiveCell.Font.Name = "Times New Roman" 

  
  

• A logical value (in other words, True or False)The following statement turns on the Italic property in the active cell:

   ActiveCell.Font.Italic = True 

  
  

Returning the Value of a Property

Sometimes you need to know the current setting of a property before changing the property or performing some other action. You can store the current value of a property in a variable by using the following syntax:

 variable = Object.Property 

Here, variable is a variable or another property. For example, the following statement stores the contents of the active cell in a variable named cellContents:

 cellContents = ActiveCell.Value 

Working with Object Methods

An object's properties describe what the object is, whereas its methods describe what you can do with the object. For example, in Word you can spellcheck a Document object using the CheckSpelling method. Similarly, you can sort a Table object by using the Sort method.

How you refer to a method depends on whether the method uses any arguments. If it doesn't, the syntax is similar to that of properties:

 Object.Method 

For example, the following statement saves the active document:

 ActiveDocument.Save 

If the method requires arguments, you use the following syntax:

 Object.Method (argument1, argument2, ...) 

When to Use Method Parentheses

Technically, the parentheses around the argument list are necessary only if you'll be storing the result of the method in a variable or object property.

For example, Word's Document object has a Close method that you can use to close a document programmatically. Here's the syntax:

 Object.Close(SaveChanges[, OriginalFormat, RouteDocument]) 

Object	The Document object.
SaveChanges	A constant that specifies whether the file is saved before closing.
OriginalFormat	A constant that specifies whether the file is saved in its original format.
RouteDocument	A true or False value that specifies whether the document is routed to the next recipient.

Formatting Required Arguments

For many VBA methods, not all the arguments are required. For the Close method, for example, only the SaveChanges argument is required. In this chapter and in Chapter 12, I differentiate between required and optional arguments by displaying the optional arguments in square brackets ([]).

For example, the following statement prompts the user to save changes, saves the changes (if applicable) in the original file format, and routes the document to the next recipient:

 ActiveDocument.Close wdPromptToSaveChanges, wdOriginalFormat, True 

To make your methods clearer to read, you can use VBA's predefined named arguments. For example, the syntax of the Close method has three named arguments: SaveChanges, OriginalFormat, and RouteDocument. Here's how you would use them in the preceding example:

 ActiveDocument.Close _     SaveChanges:=wdPromptToSaveChanges, _     OrignalFormat:=wdOriginalFormat, _     RouteDocument:=True 

Notice how the named arguments are assigned values by using the := operator.

Working with Object Collections

A collection is a set of similar objects. For example, Word's Documents collection is the set of all the open Document objects. Similarly, the Paragraphs collection is the set of all Paragraph objects in a document. Collections are objects, too, so they have their own properties and methods, and you can use the properties and methods to manipulate one or more objects in the collection.

The members of a collection are called the elements of the collection. You can refer to individual elements using either the object's name or by using an index. For example, the following statement closes a document named Budget.doc:

 Documents("Budget.doc").Close 

On the other hand, the following statement uses an index to select the first Bookmark object in the active document:

 ActiveDocument.Bookmarks(1).Select 

If you don't specify an element, VBA assumes you want to work with the entire collection.

Assigning an Object to a Variable

As you learned earlier in this chapter (see "Understanding Program Variables") you can declare a variable as an Object data type by using the following form of the Dim statement:

 Dim variableName As Object 

After you've set up your object variable, you can assign an object to it by using the Set statement. Set has the following syntax:

 Set variableName = ObjectName 

variableName	The name of the variable.
ObjectName	The object you want to assign to the variable.

For example, the following statements declare a variable named budgetSheet to be an Object and then assign it to the 2005 Budget worksheet in the Budget.xls workbook:

 Dim budgetSheet As Object Set budgetSheet = Workbooks("Budget.xls").Worksheets("2005 Budget") 

Declare Specific Object Types

For faster performance, use specific object types instead of the generic Object type in your Dim statements. For example, the following statement declares the budgetSheet variable to be of type Worksheet:

 Dim budgetSheet As Worksheet 

Working with Multiple Properties or Methods

Because most objects have many properties and methods, you'll often need to perform multiple actions on a single object. This is accomplished easily with multiple statements that set the appropriate properties or run the necessary methods. However, this can be a pain if you have a long object name.

For example, take a look at the FormatParagraph procedure shown in Listing 11.4. This procedure formats a paragraph with six statements. Note that the Paragraph object nameThisDocument.Paragraphs(1)is quite long and is repeated in all six statements.

Listing 11.4. A Procedure That Formats a Range

 Sub FormatParagraph()     ThisDocument.Paragraphs(1).Style = "Heading 1"     ThisDocument.Paragraphs(1).Alignment = wdAlignParagraphCenter     ThisDocument.Paragraphs(1).Range.Font.Size = 16     ThisDocument.Paragraphs(1).Range.Font.Bold = True     ThisDocument.Paragraphs(1).Range.Font.Color = RGB(255, 0, 0) ' Red     ThisDocument.Paragraphs(1).Range.Font.Name = "Times New Roman" End Sub 

To shorten this procedure, VBA provides the With statement. Here's the syntax:

 With object     [statements] End With 

object	The name of the object.
statements	The statements you want to execute on object.

The idea is that you strip out the common object and place it on the With line. Then all the statements between With and End With need only reference a specific method or property of that object. In the FormatParagraph procedure, the common object in all six statements is ThisDocument.Paragraphs(1). Listing 11.5 shows the FormatParagraph2 procedure, which uses the With statement to strip out this common object and make the previous macro more efficient.

Listing 11.5. A More Efficient Version of FormatParagraph()

 Sub FormatParagraph2()     With ThisDocument.Paragraphs(1)         .Style = "Heading 1"         .Alignment = wdAlignParagraphCenter         .Range.Font.Size = 16         .Range.Font.Bold = True         .Range.Font.Color = RGB(255, 0, 0) ' Red         .Range.Font.Name = "Times New Roman"     End With End Sub 

Code That Makes Decisions

A smart macro performs tests on its environment and then decides what to do next based on the results of each test. For example, suppose you've written a function macro that uses one of its arguments as a divisor in a formula. You should test the argument before using it in the formula to make sure that it isn't 0 (to avoid producing a Division by zero error). If it is, you could then display a message that alerts the user of the illegal argument.

Using If...Then to Make True/False Decisions

The most basic form of decision is the simple true/false decision (which could also be seen as a yes/no or an on/off decision). In this case, your program looks at a certain condition, determines whether it is currently true or false, and acts accordingly. In VBA, simple true/false decisions are handled by the If...Then statement. You can use either the single-line syntax:

 If condition Then statement 

or the block syntax:

 If condition Then     [statements] End If 

condition	You can use either a logical expression that returns true or False, or you can use any expression that returns a numeric value. In the latter case, a return value of zero is functionally equivalent to False, and any nonzero value is equivalent to TRue.
statements	The VBA statement or statements to run if condition returns TRue. If condition returns False, VBA skips over the statements.

Whether you use the single-line or block syntax depends on the statements you want to run if the condition returns a TRue result. If you have only one statement, you can use either syntax. If you have multiple statements, you must use the block syntax.

Listing 11.6 shows a revised version of the GrossMargin procedure from Listing 11.3, earlier in this chapter. This versioncalled GrossMargin2uses If...Then to check the totalSales variable. The procedure calculates the gross margin only if totalSales isn't zero.

Listing 11.6. An If...Then Example

 Function GrossMargin2()     Dim totalSales     Dim totalExpenses     totalSales = Application.Sum(Range("Sales"))     totalExpenses = Application.Sum(Range("Expenses"))     If totalSales <> 0 Then         GrossMargin2 = (totalSales - totalExpenses) / totalSales     End If End Function 

You Don't Need to Test for Zero

You can make the If...Then statement in the GrossMargin2 procedure slightly more efficient by taking advantage of the fact that in the condition, zero is equivalent to False and any other number is equivalent to true. This means you don't have to explicitly test the totalSales variable to see whether it's zero. Instead, you can use the following statements:

 If totalSales Then      GrossMargin2 = (totalSales - totalExpenses) / totalSales End If 

Using If...Then...Else to Handle a False Result

Using the If...Then statement to make decisions suffers from an important drawback: A False result only bypasses one or more statements; it doesn't execute any of its own. This is fine in many cases, but there will be times when you need to run one group of statements if the condition returns true and a different group if the result is False. To handle this, you need to use an If...Then...Else statement:

 If condition Then    [TrueStatements] Else    [FalseStatements] End If 

condition	The logical expression that returns TRue or False.
trueStatements	The statements to run if condition returns true.
FalseStatements	The statements to run if condition returns False.

If the condition returns true, VBA runs the group of statements between If...Then and Else. If it returns False, VBA runs the group of statements between Else and End If.

Let's look at an example. Suppose you want to calculate the future value of a series of regular deposits, but you want to differentiate between monthly deposits and quarterly deposits. Listing 11.7 shows a function macro called FutureValue that does the job.

Listing 11.7. A Procedure That Uses If...Then...Else

 Function FutureValue(Rate, Nper, Pmt, Frequency)     If Frequency = "Monthly" Then         FutureValue = FV(Rate / 12, Nper * 12, Pmt / 12)     Else         FutureValue = FV(Rate / 4, Nper * 4, Pmt / 4)     End If End Function 

The first three argumentsRate, Nper, and Pmtare, respectively, the annual interest rate, the number of years in the term of the investment, and the total deposit available annually. The fourth argumentFrequencyis either Monthly or Quarterly. The idea is to adjust the first three arguments based on the Frequency. To do that, the If...Then...Else statement runs a test on the Frequency argument:

 If Frequency = "Monthly" Then 

If the logical expression Frequency = "Monthly" returns true, the procedure divides the interest rate by 12, multiplies the term by 12, and divides the annual deposit by 12. Otherwise, a quarterly calculation is assumed, and the procedure divides the interest rate by 4, multiplies the term by 4, and divides the annual deposit by 4. In both cases, VBA's FV (future value) function is used to return the future value. (In Chapter11.xls on my website, the Tests worksheet shows an example of this function at work.)

Indent Statements for Easier Readability

If...Then...Else statements are much easier to read when you indent the expressions between If...Then, Else, and End If, as I've done in Listing 11.7. This lets you easily identify which group of statements will be run if there is a true result and which group will be run if the result is False. Pressing the Tab key once at the beginning of the first line in the block does the job.

Using the Select Case Statement

If you need to perform multiple tests on some data, VBA's Select Case statement is a good choice. The idea is that you provide a logical expression at the beginning and then list a series of possible results. For each possible resultcalled a caseyou provide one or more VBA statements to execute should the case prove to be true. Here's the syntax:

 Select Case TestExpression    Case FirstCaseList         [FirstStatements]    Case SecondCaseList         [SecondStatements]    <etc.>    Case Else        [ElseStatements] End Select 

TestExpression	This expression is evaluated at the beginning of the structure. It must return a value (logical, numeric, string, and so on).
CaseList	A list of one or more possible results for TestExpression. These results are values or expressions separated by commas. VBA examines each element in the list to see whether one matches the TestExpression. The expressions can take any one of the following forms: Expression Expression To Expression Is LogicalOperator Expression The To keyword defines a range of values (for example, 1 To 10). The Is keyword defines an open-ended range of values (for example, Is >= 100).
Statements	These are the statements VBA runs if any part of the associated CaseList matches the TestExpression. VBA runs the optional ElseStatements if no CaseList contains a match for the TestExpression.

Handling Multiple Matches

If more than one CaseList contains an element that matches the TestExpression, VBA runs only the statements associated with the CaseList that appears first in the Select Case structure.

Listing 11.8 shows how you would use Select Case to handle the Frequency argument problem.

Listing 11.8. A Procedure That Uses Select Case to Test Multiple Values

 Function FutureValue4(Rate, Nper, Pmt, Frequency)     Select Case Frequency         Case "Monthly"             FutureValue4 = FV(Rate / 12, Nper * 12, Pmt / 12)         Case "Quarterly"             FutureValue4 = FV(Rate / 4, Nper * 4, Pmt / 4)         Case Else             MsgBox "The Frequency argument must be either " & _                    """Monthly"" or ""Quarterly""!"     End Select End Function 

Code That Loops

It makes sense to divide up your VBA chores and place them in separate command or function macros. That way, you need to write the code only once and then call it any time you need it. This is known in the trade as modular programming; it saves time and effort by helping you avoid reinventing too many wheels.

There are also wheels to avoid reinventing within your procedures and functions. For example, consider the following code fragment:

 MsgBox "The time is now " & Time Application.Wait Now + TimeValue("00:00:05") MsgBox "The time is now " & Time Application.Wait Now + TimeValue("00:00:05") MsgBox "The time is now " & Time Application.Wait Now + TimeValue("00:00:05") 

The Wait Method

This code fragment uses the Excel Application object's Wait method to produce a delay. The argument Now + TimeValue("00:00:05") pauses the procedure for about five seconds before continuing.

This code does nothing more than display the time, delay for five seconds, and repeat this two more times. Besides being decidedly useless, this code just reeks of inefficiency. It's clear that a far better approach would be to take the first two statements and somehow get VBA to repeat them as many times as necessary.

The good news is that not only is it possible to do this, but VBA also gives you a number of methods to perform this so-called looping. I spend the rest of this chapter investigating each of these methods.

Using Do...Loop Structures

What do you do when you need to loop but you don't know in advance how many times to repeat the loop? This could happen if, for example, you want to loop only until a certain condition is met, such as encountering a blank cell in an Excel worksheet. The solution is to use a Do...Loop.

The Do...Loop has four syntaxes:

Do While condition [statements] Loop Do [statements] Loop While condition Do Until condition [statements] Loop Do [statements] Loop Until condition

Checks condition before entering the loop. Executes the statements only while condition is true. Checks condition after running through the loop once. Executes the statements only while condition is true. Use this form when you want the loop to be processed at least once. Checks condition before entering the loop. Executes the statements only while condition is False. Checks condition after running through the loop once. Executes the statements only while condition is False. Again, use this form when you want the loop to be processed at least once.

Listing 11.9 shows a procedure called BigNumbers that runs down a worksheet column and changes the font color to magenta whenever a cell contains a number greater than or equal to 1,000.

Listing 11.9. A Procedure That Uses a Do...Loop to Process Cells Until It Encounters a Blank Cell

 Sub BigNumbers()     Dim rowNum As Integer, colNum As Integer, currCell As Range     '     ' Initialize the row and column numbers     '      rowNum = ActiveCell.Row     colNum = ActiveCell.Column     '     ' Get the first cell     '     Set currCell = ActiveSheet.Cells(rowNum, colNum)     '     ' Loop while the current cell isn't empty     '     Do While currCell.Value <> ""         '         ' Is it a number?         '           If IsNumeric(currCell.Value) Then             '             ' Is it a big number?             '             If currCell.Value >= 1000 Then                 '                 ' If so, color it magenta                 '                 currCell.Font.Color = VBAColor("magenta")             End If         End If         '         ' Increment the row number and get the next cell         '          rowNum = rowNum + 1         Set currCell = ActiveSheet.Cells(rowNum, colNum)     Loop End Sub 

The idea is to loop until the procedure encounters a blank cell. This is controlled by the following Do While statement:

 Do While currCell.Value <> "" 

currCell is an object variable that is Set using the Cells method. Next, the first If...Then uses the IsNumeric function to check if the cell contains a number, and the second If...Then checks if the number is greater than or equal to 1,000. If both conditions are TRue, the font color is set to magenta using the VBAColor function described earlier in this chapter.

Using For...Next Loops

The most common type of loop is the For...Next loop. Use this loop when you know exactly how many times you want to repeat a group of statements. The structure of a For...Next loop looks like this:

 For counter = start To end [Step increment]     [statements] Next [counter] 

counter	A numeric variable used as a loop counter. The loop counter is a number that counts how many times the procedure has gone through the loop.
start	The initial value of counter. This is usually 1, but you can enter any value.
end	The final value of counter.
increment	This optional value defines an increment for the loop counter. If you leave this out, the default value is 1. Use a negative value to decrement counter.
statements	The statements to execute each time through the loop.

The basic idea is simple. When VBA encounters the For...Next statement, it follows this five-step process:

1.	Set counter equal to start.
2.	Test counter. If it's greater than end, exit the loop (that is, process the first statement after the Next statement). Otherwise, continue. If increment is negative, VBA checks to see whether counter is less than end.
3.	Execute each statement between the For and Next statements.
4.	Add increment to counter. Add 1 to counter if increment isn't specified.
5.	Repeat steps 2 through 4 until done.

Listing 11.10 shows a simple SubprocedureLoopTestthat uses a For...Next statement. Each time through the loop, the procedure uses the Application object's StatusBar property to display the value of counter (the loop counter) in the status bar. When you run this procedure, counter gets incremented by 1 each time through the loop, and the new value gets displayed in the status bar.

Listing 11.10. A Simple For...Next Loop

 Sub LoopTest()     Dim counter     For counter = 1 To 10         '         'Display the message         '         Application.StatusBar = "Counter value: " & counter         '         ' Wait for 1 second         '         Application.Wait Now + TimeValue("00:00:01")     Next counter     Application.StatusBar = False End Sub 

Mimicking the Wait Method

The LoopTest procedure works fine in Excel, but it will fail in the other Office applications because they don't implement the Wait method. If you need to get your code to delay for a short while, here's a simple procedure that does the trick:

 Sub VBAWait(delay As Integer)     Dim startTime As Long     startTime = Timer     Do While Timer - startTime < delay         DoEvents     Loop End Sub 

Note the use of the DoEvents function inside the Do While...Loop structure. This function yields execution to the operating system so that events such as keystrokes and application messages are processed while the procedure delays.

Following are some notes on For...Next loops:

• If you use a positive number for increment (or if you omit increment), end must be greater than or equal to start. If you use a negative number for increment, end must be less than or equal to start.

• If start equals end, the loop will execute once.

• As with If...Then...Else structures, indent the statements inside a For...Next loop for increased readability.

• To keep the number of variables defined in a procedure to a minimum, always try to use the same name for all your For...Next loop counters. The letters i through n traditionally are used for counters in programming. For greater clarity, you might want to use names such as counter.

• For the fastest loops, don't use the counter name after the Next statement. If you'd like to keep the counter name for clarity (which I recommend), precede the name with an apostrophe (') to comment out the name, like this:

   For counter = 1 To 10     [statements] Next 'counter 

  
  

• If you need to break out of a For...Next loop before the defined number of repetitions is completed, use the Exit For statement, described in the later section "Using Exit For or Exit Do to Exit a Loop."

Using For Each...Next Loops

A useful variation of the For...Next loop is the For Each...Next loop, which operates on a collection of objects. You don't need a loop counter because VBA just loops through the individual elements in the collection and performs on each element whatever operations are inside the loop. Here's the structure of the basic For Each...Next loop:

 For Each element In group     [statements] Next [element] 

element	A variable used to hold the name of each element in the collection.
group	The name of the collection.
statements	The statements to be executed for each element in the collection.

As an example, let's create a command procedure that converts a range of text into proper case (that is, the first letter of each word is capitalized). This function can come in handy if you import mainframe text into your worksheets, because mainframe reports usually appear entirely in uppercase. This process involves three steps:

1.

Loop through the selected range with For Each...Next.

2.

Convert each cell's text to proper case. Use Excel's PROPER() function to handle this:

 PROPER(text) 

text

The text to convert to proper case.

3.

Enter the converted text into the selected cell. This is the job of the Range object's Formula method:

 object.Formula = Expression 

object	The Range object in which you want to enter Expression.
Expression	The data you want to enter into object.

Listing 11.11 shows the resulting procedure, ConvertToProper. Note that this procedure uses the Selection object to represent the currently selected range.

Listing 11.11. A Subprocedure That Uses For Each...Next to Loop Through a Selection and Convert Each Cell to Proper Text

 Sub ConvertToProper()     Dim cellObject As Object     For Each cellObject In Selection         cellObject.Formula = Application.Proper(cellObject)     Next End Sub 

Using Exit For or Exit Do to Exit a Loop

Most loops run their natural course and then the procedure moves on. There might be times, however, when you want to exit a loop prematurely. For example, you might come across a certain type of cell, or an error might occur, or the user might enter an unexpected value. To exit a For...Next loop or a For Each...Next loop, use the Exit For statement. To exit a Do...Loop, use the Exit Do statement.

Listing 11.12 shows a revised version of the BigNumbers procedure, which exits the Do...Loop if it comes across a cell that isn't a number.

Listing 11.12. In This Version of the BigNumbers Procedure, the Do...Loop Is Terminated with the Exit Do Statement If the Current Cell Isn't a Number

 Sub BigNumbers2()     Dim rowNum As Integer, colNum As Integer, currCell As Range     '     ' Initialize the row and column numbers     '     rowNum = ActiveCell.Row     colNum = ActiveCell.Column     '     ' Get the first cell     '     Set currCell = ActiveSheet.Cells(rowNum, colNum)     '     ' Loop while the current cell isn't empty     '     Do While currCell.Value <> ""         '         ' Is it a number?         '         If IsNumeric(currCell.Value) Then            '            ' Is it a big number?            '            If currCell.Value >= 1000 Then                '                ' If so, color it magenta                '                currCell.Font.Color = VBAColor("magenta")            End If         '                ' Otherwise, exit the loop                '               Else                   Exit Do               End If               '               ' Increment the row number and get the next cell               '               rowNum = rowNum + 1               Set currCell = ActiveSheet.Cells(rowNum, colNum)         Loop End Sub 

From Here

• For lots of examples of command and function macros, see Chapter 12, "Putting VBA to Good Use: Practical Macros Everyone Can Use."

• You can combine repetitive or complex Access tasks into another kind of macro. To learn how, see Chapter 13, "Taking Advantage of Access Macros."

• For the details on using macros securely, in Chapter 14, see the section titled "Controlling VBA Security."