This is a radically cool feature, but I might make you jump through hoops first. If you are going to write VBA macros, you absolutely must have the VBA help topics installed. The problem: The VBA help topics are not installed in the default Office install. Here is how to see whether you have it.

If instead you get a message saying that help is not available on this topic, you have to find the original CDs (or get your network administrator to grant rights to the installation folder) so that you can install the VBA help topics. Go through the process of doing a reinstall. During reinstall, select the custom install and be sure to select the VBA help files.

If you request help on a function or method, the help walks you through the various arguments available to you. If you browse to the bottom of most help topics, the help provides code samples under the Example heading. This section of each help topic is a great resource (see Figure 2.4).

Figure 2.4. Most help topics have an example.

It is possible to select the code, copy it to the clipboard by pressing Ctrl+C (see Figure 2.5), and then paste it into your module by pressing Ctrl+V.

Figure 2.5. Highlight code in the help file and copy with Ctrl+C.

After you record a macro, undoubtedly there will be objects or methods that you are not sure of. Insert the cursor in any keyword and press F1 to get help on that topic.

What happens if you skip an optional parameter? The help file can tell you. For StartRow, the help file indicates that the default value is 1. If you leave out the StartRow parameter, Excel starts importing at row 1. This is fairly safe. Look at the help file note about Origin. If this argument is omitted, you inherit whatever value was used for Origin the last time someone used this feature in Excel on this computer. That is certainly a recipe for disaster—your code may work 98 percent of the time, but immediately after someone imports an Arabic file, Excel will remember the setting for Arabic and assume this is what your macro wants if you don’t explicitly code this parameter.

Look at the help file entry for DataType in Figure 2.7. The help file says that it can be one of these constants: xlDelimited or xlFixedWidth. The help file says that these are the valid xlTextParsingType constants. These constants are predefined in Excel VBA. In the VB Editor, press Ctrl+G to bring up the Immediate window. In the Immediate window, type this line and press Enter:

Print xlFixedWidth

The answer appears in the Immediate window. xlFixedWidth is the equivalent of saying “2” (see Figure 2.8). Ask the Immediate window to Print xlDelimited. This is really the same as typing 1. Microsoft correctly assumes that it is easier for someone to read code that uses the somewhat English-like term xlDelimited rather than 1.

Figure 2.8. In the Immediate window of the VB Editor, query to see the true value of constants such as xlFixedWidth.

If you were an evil programmer, you could certainly memorize all these constants and write code using the numeric equivalents of the constants. However, the programming gods (as well as the next person who has to look at your code) will curse you for this.

In most cases, the help file either specifically calls out the valid values of the constants, or offers a blue hyperlink that causes the help file to expand and show you the valid values for the constants (see Figure 2.9).

Figure 2.9. Click the blue hyperlink to see all the possible constant values. Here, the ten possible xlColumnDataType constants are revealed in a new help topic.

My one complaint with this excellent help system is that it does not identify which parameters may be new to a given version. In this particular case, TrailingMinusNumbers was introduced in Excel 2002. If you attempt to give this program to someone who is still using Excel 2000, the code doesn’t run because it does not understand the TrailingMinusNumbers parameter. This is a frustrating problem, and the only way to learn to handle it is through trial and error.

If you read the help topic on OpenText, you can surmise that it is basically the equivalent of opening a file using the Text Import Wizard. In the first step of the wizard, you normally choose either Delimited or Fixed Width. You also specify the File Origin and at which row to start (see Figure 2.10). This first step of the wizard is handled by these parameters of the OpenText method:

Origin:=437
StartRow:=1
DataType:=xlDelimited

Figure 2.10. The first step of the Text Import Wizard in Excel is covered by three parameters of the OpenText method.

Step 2 of the Text to Columns Wizard enables you to specify that your fields are delimited by a comma. We don’t want to treat two commas as a single comma, so Treat Consecutive Delimiters as One is unchecked. Sometimes, a field may contain a comma—for example “XYZ, Inc.” In this case, the field should have quotes around the value. This is specified in the Text Qualifier box (see Figure 2.11). This second step of the wizard is handled by these parameters of the OpenText method:

TextQualifier:=xlDoubleQuote
ConsecutiveDelimiter:=False
Tab:=False
Semicolon:=False
Comma:=True
Space:=False
Other:=False

Figure 2.11. Step 2 of the Text Import Wizard is handled by the seven parameters of the OpenText method.

Step 3 of the wizard is where you actually identify the field types. In our case, we left all fields as General except for the first field, which was marked as a date in MDY (Month, Day, Year) format (see Figure 2.12). This is represented in code by the FieldInfo parameter.

Figure 2.12. The third step of the Text Import Wizard is fairly complex. The entire FieldInfo parameter of the OpenText method duplicates the choices made on this step of the wizard.

If you would happen to click the Advanced button on step 3 of the wizard, you have an opportunity to specify something other than the default Decimal and Thousands separator, plus the setting for Trailing Minus for negative numbers (see Figure 2.13). Note that the macro recorder does not write code for DecimalSeparator or ThousandsSeparator unless you change these from the defaults. The macro recorder does always record the TrailingMinusNumbers parameter.

Figure 2.13. The TrailingMinusNumbers parameter comes from this Advanced Text Import Settings. If you had changed either of the separator fields, new parameters would have been recorded by the macro recorder.

At this point, you can more or less see how just about every option that you do in Excel while the macro recorder is running corresponds to a bit of code in the recorded macro.

Here is another example. The next line of code in the macro is

Selection.End(xlDown).Select

You can click and get help for three topics in this line of code: Selection, End, and Select. Assuming that Selection and Select are somewhat self-explanatory, click in the word End and press F1 for help. A Context Help dialog box pops up, saying that there are two possible help topics for End. There is one in the Excel library and one in the VBA library (see Figure 2.14).

Figure 2.14. Sometimes when you click in a VBA keyword and press F1, you must guess which help library to use.

If you are new to VBA, it can be frustrating to know which one to select. Take your best guess and click Help. In this case, the End help topic in the VBA library is talking about the End statement (see Figure 2.15), which is not what we have here.

Figure 2.15. If you guess wrong, you are taken to a help topic that is completely off-topic. It is easy enough to try again.

Close Help, press F1 again, and choose the End object in the Excel library. This help topic says that End is a property. It returns a Range object that is equivalent to pressing End+Up or End+Down in the Excel interface (see Figure 2.16). If you click the blue hyperlink for xlDirection, you will see the valid parameters that can be passed to the End function.

Figure 2.16. The correct help topic for the End property.

In the discussion at the start of this chapter, I said that the basic syntax of VBA is Object.Method. In this particular line of code, the method is Select. The End keyword is a property, but from the help file, you see that it returns a Range object. Because the Select method can apply to a Range object, the method is actually appended to a property.

You might then assume that Selection is the object in this line of code. If you click the mouse in the word Selection and press F1, you will see that according to the help topic, Selection is actually a property and not an object. In reality, the proper code would be to say Application.Selection; however, when you are running within Excel, VBA assumes you are referring to the Excel object model, so you can leave off the Application object. If you were to write a program in Word VBA to automate Excel, you would be required to include an object variable before the Selection property to qualify to which application you are referring.

In this case, the Application.Selection can return several different types of objects. If a cell is selected, it returns the Range object.

Generally, a macro runs really fast. You start it, and less than a second later, it is done. If something goes wrong, you have no opportunity to figure out what it is doing. Using Excel’s Step Into feature, it is possible to run one line of code at a time.

Make sure the cursor is in the ImportInvoice procedure and from the menu select Debug, Step Into, as shown in Figure 2.17 (or press F8).

Figure 2.17. Stepping into code allows you to run a single line at a time.

The VB Editor is now in Step mode. The line about to be executed is highlighted in yellow with a yellow arrow in the margin before the code (see Figure 2.18).

Figure 2.18. The first line of the macro is about to run.

In this case, the next line to be executed is the Sub ImportInvoice() line. This basically says, “We are about to start running this procedure.” Press the F8 key to execute the line in yellow and move to the next line of code. The long code for OpenText is then highlighted. Press F8 to run this line of code. When you see that Selection.End(xlDown).Select is highlighted, you know that Visual Basic has finished running the OpenText command. Indeed, you can press Alt+Tab to switch to Excel and see the Invoice.txt file has been parsed into Excel. Note that A1 is selected (see Figure 2.19).

Figure 2.19. Switching to Excel shows that the Invoice.txt file has indeed been imported.

Switch back to the VB Editor by pressing Alt+Tab. The next line about to be executed is Selection.End(xlDown).Select. Press F8 to run this code. Switch to Excel to see the results. Now A10 is selected (see Figure 2.20).

Figure 2.20. Verify that the End(xlDown).Select command worked as expected. This is equivalent to pressing the End key and then the down arrow.

Press F8 again to run the Range("A14").Select line. If you switch to Excel by pressing Alt+Tab, you will see that this is where the macro starts to have problems. Instead of moving to the first blank row, the program moved to the wrong row (see Figure 2.21).

Figure 2.21. The recorded macro code blindly moves to Row 14 for the Total row.

Now that you have identified the problem area, you can either stop the code execution by using the Reset command (either by selecting Run, Reset or by clicking the Reset button on the toolbar; see Figure 2.22). After clicking Reset, you should return to Excel and undo anything done by the partially completed macro. In this case, close the Invoice.txt file without saving.

Figure 2.22. The Reset button in the toolbar stops a macro that is in Break mode.

If you have hundreds of lines of code, you might not want to step through each line one by one. You might have a general knowledge that the problem is happening in one particular section of the program. In this case, you can set a breakpoint. You can then have the code start to run, but the macro breaks just before it executes the breakpoint line of code.

To set a breakpoint, click in the gray margin area to the left of the line of code on which you want to break. A large brown dot appears next to this code, and the line of code is highlighted in brown (see Figure 2.23).

Figure 2.23. The large brown dot signifies a breakpoint.

Now, from the menu select Run, Run Sub or press F5. The program quickly executes but stops just before the breakpoint. The VB Editor shows the breakpoint line highlighted in yellow. You can now press F8 to begin stepping through the code (see Figure 2.24).

Figure 2.24. The yellow line signifies that the breakpoint line is about to be run next.

After you have finished debugging your code, remove the breakpoints. You can do this by clicking the dark brown dot in the margin to toggle off the breakpoint. You can also select Debug, Clear All Breakpoints or press Ctrl+Shift+F9 to clear all breakpoints that you’ve set in the project.

When you are stepping through code, you might want to jump over some lines of code. Or, perhaps you’ve corrected some lines of code and you want to run them again. In Step mode, it is easy to do this. My favorite method is to use the mouse to grab the yellow arrow. The cursor changes to an icon, meaning that you can move the next line up or down. Drag the yellow line to whichever line you want to execute next (see Figure 2.25). The other option is to click the cursor in the line to which you want to jump and then choose Debug, Set Next Statement.

Figure 2.25. The cursor as it appears when dragging the yellow line to a different line of code to be executed next.

When you are stepping through code, you might want to run a section of code without stepping through each line. This is common when you get to a loop. You might want VBA to run through the loop 100 times so that you can step through the lines after the loop. It is particularly monotonous to press the F8 key hundreds of times to step through a loop. Click the cursor on the line you want to step to and press Ctrl+F8 or select Debug, Run to Cursor.

We haven’t talked about variables yet (the macro recorder never records a variable), but you can query the value of anything while in Step mode.

Using the Immediate Window

Press Ctrl+G to display the Immediate window in the VB Editor. While the macro is in Break mode, you can ask the VB Editor to tell you the currently selected cell, the name of the active sheet, or the value of any variable. Figure 2.26 shows several examples of queries typed into the Immediate window.

Figure 2.26. Queries (and their answers), which can be typed into the Immediate window while a macro is in Break mode.

When invoked with Ctrl+G, the Immediate Window usually appears at the bottom of the Code window. You can use the resize handle (above the blue Immediate title bar) to make the Immediate window larger or smaller (see Figure 2.27).

Figure 2.27. Resizing the Immediate window.

There is a scrollbar on the side of the Immediate window. You can use this to scroll backward or forward through past entries in the Immediate window.

It is not necessary to run queries only at the bottom of the Immediate window. Here is an example. In this case, I’ve just run one line of code. In the Immediate window, I ask for the Selection.Address to ensure that this line of code worked (see Figure 2.28).

Figure 2.28. The Immediate window shows the results before the current line is executed.

Press the F8 key to run the next line of code. Instead of retyping the same query, I can click in the Immediate window at the end of the line containing the last query (see Figure 2.29).

Figure 2.29. There is no need to type the same commands over in the Immediate window. Place the cursor at the end of the previous command and press Enter.

Press Enter, and the Immediate window runs this query again, displaying the results on the next line and pushing the old results farther down the window. In this case, the selected address is $1:$1. The previous answer, $E$14:$G$14, is pushed down the window (see Figure 2.30).

Figure 2.30. The prior answer ($E$14:$G$14) is shifted down, and the current answer ($1:$1) appears below the query.

Press F8 four more times to run through the line of code with Cells.Select. Again, position the cursor in the Immediate window just after Print Selection.Address and press Enter. The query is run again, and the most recent address is shown with the prior answers moved down in the Immediate window (see Figure 2.31).

Figure 2.31. After selecting all cells with Cells.Select, place the cursor after the query in the Immediate window and press Enter. The new answer is that the selected range is all rows from 1 to 1,084,576.

You can also use this method to change the query. Click to the right of the word Address in the Immediate window. Press the Backspace key to erase the word Address and instead type Rows.Count. Press Enter, and the Immediate window shows you the number of rows in the selection (see Figure 2.32).

Figure 2.32. Delete part of a query, type something new, and press Enter. The previous answers are pushed down, and the current answer is displayed.

This technique is excellent when you are trying to figure out a sticky bit of code. I might find myself querying the name of the active sheet (Print Activesheet.Name), the selection (Print Selection.Address), the active cell (Print ActiveCell.Address), the formula (Print ActiveCell.Formula) in the active cell, the value of the active cell (Print ActiveCell.Value, or Print ActiveCell because Value is the default property of a cell), and so on.

To dismiss the Immediate window, click the X in the upper-right corner of the Immediate window. (Ctrl+G does not toggle the window on and off.)

Querying by Hovering

In many instances, you can hover your cursor over an expression in the code. Wait a second, and a ToolTip pops up showing you the current value of the expression. This is invaluable for you to understand when you get to looping in Chapter 5, “Looping and Flow Control.” And, it still will come in handy with recorded code. Note that the expression that you hover over does not have to be in the line of code just executed. In Figure 2.33, Visual Basic just selected Row 1 (making A1 the ActiveCell). If I hover the cursor over ActiveCell.Formula, I get a ToolTip showing me that the formula in the ActiveCell is the word InvoiceDate.

Figure 2.33. Hover the mouse cursor over any expression for a few seconds, and a ToolTip shows the current value of the expression.

Sometimes the VBA window seems to not respond to hovering. Because some expressions are not supposed to show a value, it is difficult to tell whether VBA is not displaying the value on purpose or whether you are in the buggy “not responding” mode. Try hovering over something that you know should respond (such as a variable). If you get no response, hover, click into the variable, and continue to hover. This tends to wake Excel up from the stupor, and hovering will work again.

Are you impressed yet? I started the chapter complaining that this didn’t seem much like BASIC, but you have to admit that the Visual Basic environment is great to work in. These debugging tools are excellent.

Querying by Using a Watch Window

In Visual Basic, a watch is not something you wear on your wrist. It allows you to watch the value of any expression while you step through code. Let’s say that in the current example, I want to watch to see what is selected as the code runs. I would set up a watch for Selection.Address.

From the VB Editor Debug menu, select Add Watch.

In the Add Watch dialog, enter Selection.Address in the Expression text box and click OK (see Figure 2.34).

Figure 2.34. Setting up a watch to see the address of the current selection.

A Watches window is added to the busy Visual Basic window. It is usually added at the bottom of the code window. I started running the macro, importing the file and pressing End+Down to move to the last row with data. Right after the Cells.Select code is executed, the Watches window shows me that Selection.Address is $A$10 (see Figure 2.35).

Figure 2.35. Without having to hover or type in the Immediate window, you can always see the value of watched expressions.

Press the F8 key to run the code Range("A14").Select. The Watches window is updated to show the current address of the Selection is now $A$14 (see Figure 2.36).

Figure 2.36. After running another line of code, the value in the Watches window updates to indicate the address of the new selection.

Right-click the glasses icon in the Watches window and choose Edit Watch. In the Watch Type section of the Edit Watch dialog, select Break When Value Changes (see Figure 2.37). Click OK.

Figure 2.37. Select Break When Value Changes in the bottom of the Edit Watch dialog.

The glasses icon has changed to a hand with triangle icon. You can now press F5 to run the code. The macro starts running lines of code until something new is selected. This is very powerful. Instead of having to step through each line of code, you can now conveniently have the macro stop only when something important has happened. A watch can also be set up to stop when the value of a particular variable changes.

In the preceding example, we watched a specific property: Selection.Address. It is also possible to watch an object, such as Selection. In Figure 2.38, you have set up a watch on Selection. You get the glasses icon and a + icon.

Figure 2.38. Setting a watch on an object gives you a + icon next to the glasses.

By clicking the + icon, you can see all the properties associated with Selection. Look at Figure 2.39! You can now see more than you ever wanted to know about Selection. There are properties that you probably never realized were available. You can see that the AddIndent property is set to False, and the AllowEdit property is set to True. There are useful properties in the list—you can see the Formula of the selection.

Figure 2.39. Clicking the + icon shows a plethora of properties and their current values.

In this Watches window, some entries can be expanded. The Borders collection has a plus next to it. Click any + icon to see even more details.

Nothing screams “recorded code” more than having code that selects things before acting upon them. This makes sense—in the Excel interface, you have to select Row 1 before you can make it bold.

However, in VBA we rarely have to do this. (There are a couple buggy features in charts where you do have to select the chart object to make the method work, but that’s an exception.) It is possible to directly turn on bold font to Row 1 without selecting it. The following two lines of code turn into one line.

Macro recorder code before being streamlined:

Rows("1:1").Select
Selection.Font.Bold = True

After streamlining the recorded code:

Rows("1:1").Font.Bold = True

There are several advantages to this method. First, there will be half as many lines of code in your program. Second, the program will run faster.

After recording code, I literally highlight from before the word Select at the end of one line all the way to the dot after the word Selection on the next line and press Delete (see Figures 2.43 and 2.44).

Figure 2.43. Select from here to here...

Figure 2.44. ...and press the Delete key. This is basic “101” of cleaning up recorded macros.

It is difficult to trust data from just anywhere. If you are analyzing data in Excel, remember that the data is often coming from who-knows-what system written who-knows-how-long ago. The universal truth is that sooner or later, some clerk somewhere will find a way to break the source system and will manage to enter a record without an invoice number. Maybe it takes a power failure to do it, but invariably, we cannot count on having every cell filled in.

This is a problem when using the End+Down shortcut. This key combination does not take you to the last row with data in the worksheet. It takes you to the last row with data in the current range. In Figure 2.45, pressing End+Down would move the cursor to cell A6 rather than cell A10.

Figure 2.45. End+Down fails in the user interface if a record is missing a value. Similarly, End(xlDown) fails in Excel VBA.

The better solution is to start at the bottom of the Excel worksheet and press End+Up. Now, this seems silly in the Excel interface because you can easily see whether you are really at the end of the data. But, because it is easy in Excel VBA to start at the last row, get in the habit of using this code to find the true last row:

Cells(Rows.Count, 1).End(xlUp)

The macro recorder never records a variable. They are very easy to use. They are discussed in more detail later; but just as in BASIC, a variable can remember a value.

My recommendation is to set the last row with data to a variable. I like to use meaningful variable names, so my favorite for this is FinalRow:

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

Now that you know the row number of the last record, it is easy to put the word Total in Column A of the next row:

Range("A" & FinalRow + 1).Value = "Total"

You can even use the variable when building the formula. This formula totals everything from E2 to the FinalRow of E:

Range("E" & FinalRow + 1).Formula = "=SUM(E2:E" & FinalRow & ")"

Recorded code is notorious for copying a range, selecting another range, and then doing an ActiveSheet.Paste. The Copy method as it applies to a range is actually much more powerful. You can specify what to copy and specify the destination in one statement.

Recorded code:

Range("E14").Select
Selection.Copy
Range("F14:G14").Select
ActiveSheet.Paste

Better code:

Range("E14").Copy Destination:=Range("F14:G14")

If you were going to make the total row bold, double underline, with a larger font and a special color, you might get recorded code like this:

Range("A14:G14").Select
Selection.Font.Bold = True
Selection.Font.Size = 12
Selection.Font.ColorIndex = 5
Selection.Font.Underline = xlUnderlineStyleDoubleAccounting

For four of those lines of code, VBA must resolve the expression Selection.Font. Because you have four lines that all refer to the same object, you can name the object once at the top of a With block. Inside the With...End With block, everything that starts with a period is assumed to refer to the With object:

With Range("A14:G14").Font
    .Bold = True
    .Size = 12
    .ColorIndex = 5
    .Underline = xlUnderlineStyleDoubleAccounting
End With