Tricks of the Microsoft Office Gurus
| 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.
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
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.
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:
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:
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.
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.
You use the logical operators to combine or modify TRue/False expressions. Table 11.4 summarizes VBA's logical operators.
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:
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:
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, ...)
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])
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
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")
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
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
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
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
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.)
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
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")
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.
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]
The basic idea is simple. When VBA encounters the For...Next statement, it follows this five-step process:
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
Following are some notes on For...Next loops:
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]
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:
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
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||