2. This Sounds Like BASIC, So Why Doesn’t It Look Familiar?

I Can’t Understand This Code

As mentioned previously, if you have taken a class in a procedural language such as BASIC or COBOL, you might be confused when you look at VBA code. Even though VBA stands for Visual Basic for Applications, it is an object-oriented version of BASIC. Here is a bit of VBA code:

This code likely makes no sense to anyone who knows only procedural languages. Unfortunately, your first introduction to programming in school (assuming you are over 30 years old) would have been a procedural language.

Here is a section of code written in the BASIC language:

If you run this code, you get a pyramid of asterisks on your screen:

If you have ever been in a procedural programming class, you can probably look at the code and figure out what is going on because procedural languages are more English-like than object-oriented languages. The statement Print "Hello World" follows the verb-object format, which is how you would generally talk. Let’s step away from programming for a second and think about a concrete example.

Understanding the Parts of VBA “Speech”

If you were going to write code for instructions to play soccer using BASIC, the instruction to kick a ball would look something like this:

“Kick the Ball”

Hey—this is how you talk! It makes sense. You have a verb (kick) and then a noun (ball). The BASIC code in the preceding section has a verb (print) and a noun (asterisk). Life is good.

Here is the problem. VBA doesn’t work like this. In fact, no object-oriented language works like this. In an object-oriented language, the objects (nouns) are most important, hence the name: object-oriented. If you were going to write code for instructions to play soccer with VBA, the basic structure would be as follows:

Ball.Kick

You have a noun (ball), which comes first. In VBA, this is an object. Then you have the verb (kick), which comes next. In VBA, this is a method.

The basic structure of VBA is a bunch of lines of code where you have

Object.Method

Needless to say, this is not English. If you took a romance language in high school, you will remember that those languages use a “noun adjective” construct. However, no one uses “noun verb” to tell someone to do something:

Water.Drink
Food.Eat
Girl.Kiss

That is why VBA is confusing to someone who previously took a procedural programming class.

Let’s carry the analogy a bit further. Imagine you walk onto a grassy field and there are five balls in front of you. There is a soccer ball, basketball, baseball, bowling ball, and tennis ball. You want to instruct the kid on your soccer team to “Kick the soccer ball.”

If you tell him to kick the ball (or ball.kick), you really aren’t sure which one of the five balls he will kick. Maybe he will kick the one closest to him, which could be a problem if he is standing in front of the bowling ball.

For almost any noun, or object in VBA, there is a collection of that object. Think about Excel. If you can have one row, you can have a bunch of rows. If you can have one cell, you can have a bunch of cells. If you can have one worksheet, you can have a bunch of worksheets. The only difference between an object and a collection is that you will add an s to the name of the object:

Row becomes Rows.

Cell becomes Cells.

Ball becomes Balls.

When you refer to something that is a collection, you have to tell the programming language to which item you are referring. There are a couple of ways to do this. You can refer to an item by using a number. For example, if the soccer ball is the second ball, you might say this:

Balls(2).Kick

This works fine, but it could be a dangerous way to program. For example, it might work on Tuesday. However, if you get to the field on Wednesday and someone has rearranged the balls, Balls(2).Kick might be a painful exercise.

A much safer way to go is to use a name for the object in a collection. You can say the following:

Balls("Soccer").Kick

With this method, you always know that it will be the soccer ball that is being kicked.

So far, so good. You know a ball will be kicked, and you know it will be the soccer ball. For most of the verbs, or methods in Excel VBA, there are parameters that tell how to do the action. These parameters act as adverbs. You might want the soccer ball to be kicked to the left and with a hard force. In this case, the method would have a number of parameters that tell how the program should perform the method:

Balls("Soccer").Kick Direction:=Left, Force:=Hard

When looking at VBA code, the colon-equals combination indicates that you are looking at parameters of how the verb should be performed.

Sometimes, a method will have a list of 10 parameters, some of which are optional. For example, if the Kick method has an Elevation parameter, you would have this line of code:

Balls("Soccer").Kick Direction:=Left, Force:=Hard, Elevation:=High

Here is the confusing part. Every method has a default order for its parameters. If you are not a conscientious programmer and you happen to know the order of the parameters, you can leave off the parameter names. The following code is equivalent to the previous line of code:

Balls("Soccer").Kick Left, Hard, High

This throws a monkey wrench into our understanding. Without the colon-equals, it is not obvious that you have parameters. Unless you know the parameter order, you might not understand what is being said. It is pretty easy with Left, Hard, and High, but when you have parameters like the following:

Shapes.AddShape type:=1, Left:=10, Top:=20, Width:=100, Height:=200

it gets confusing to see

Shapes.AddShape 1, 10, 20, 100, 200

The preceding is valid code. However, unless you know that the default order of the parameters for this Add method is Type, Left, Top, Width, Height, this code will not make sense. The default order for any particular method is the order of the parameters as shown in the help topic for that method.

To make life more confusing, you are allowed to start specifying parameters in their default order without naming them, and then switch to naming parameters when you hit one that does not match the default order. If you want to kick the ball to the left and high, but do not care about the force (you are willing to accept the default force), the following two statements are equivalent:

Balls("Soccer").Kick Direction:=Left, Elevation:=High
Balls("Soccer").Kick Left, Elevation:=High

However, keep in mind that as soon as you start naming parameters, they have to be named for the remainder of that line of code.

Some methods simply act on their own. To simulate pressing the F9 key, you use this code:

Application.Calculate

Other methods perform an action and create something. For example, you can add a worksheet using the following:

Worksheets.Add Before:=Worksheets(1)

However, because Worksheets.Add creates a new object, you can assign the results of this method to a variable. In this case, you must surround the parameters with parentheses:

Set MyWorksheet = Worksheets.Add(Before:=Worksheets(1))

One final bit of grammar is necessary: adjectives. Just as adjectives describe a noun, properties describe an object. Because you are Excel fans, let’s switch from the soccer analogy to an Excel analogy midstream. There is an object to describe the active cell. Fortunately, it has a very intuitive name:

ActiveCell

Suppose you want to change the color of the active cell to yellow. There is a property called InteriorColor for a cell that uses a complex series of codes. However, you can turn a cell to yellow by using this code:

ActiveCell.Interior.ColorIndex = 6

You can see how this can be confusing. Again, there is the Noun-dot-Something construct, but this time it is Object.Property rather than Object.Method. Telling them apart is quite subtle—there is no colon before the equal sign. A property is almost always being set equal to something, or perhaps the value of a property is being assigned to something else.

To make this cell color the same as cell A1, you might say this:

ActiveCell.Interior.ColorIndex = Range("A1").Interior.ColorIndex

Interior.ColorIndex is a property. By changing the value of a property, you can make things look different. It is kind of bizarre—change an adjective, and you are actually doing something to the cell. Humans would say, “Color the cell yellow,” whereas VBA says this:

ActiveCell.Interior.ColorIndex = 6

Table 2.1 summarizes the VBA “parts of speech.”

Table 2.1. Parts of the VBA Programming Language

VBA Is Not Really Hard

Knowing whether you are dealing with properties or methods will help you set up the correct syntax for your code. Don’t worry if it all seems confusing right now. When you are writing VBA code from scratch, it is tough to know whether the process of changing a cell to yellow requires a verb or an adjective. Is it a method or a property?

This is where the beauty of the macro recorder comes in. When you don’t know how to code something, you record a short little macro, look at the recorded code, and figure out what is going on.

VBA Help Files: Using F1 to Find Anything

This is a radically cool feature, but you need to jump through a few 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. Follow these steps to see whether you have VBA help installed:

1. Open Excel and switch to the VB Editor by pressing Alt+F11. From the Insert menu, select Module (see Figure 2.1).
   

   Figure 2.1. Insert a new module in the blank workbook.

2. Type the three lines of code shown in Figure 2.2. Click inside the word MsgBox.
   

   Figure 2.2. Click inside the word MsgBox and press F1.

3. With the cursor in the word MsgBox, press F1. If the VBA help topics are installed, you will see the help topic shown in Figure 2.3.
   

   Figure 2.3. If the VBA help topics have been installed, you will get this screen.

However, if you get a message saying that help is not available on this topic, 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.

Using Help Topics

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

Figure 2.4. Most help topics include code samples.

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, if there are objects or methods about which you are unsure, you can get help by inserting the cursor in any keyword and pressing F1.

Examining Recorded Macro Code: Using the VB Editor and Help

Let’s take a look at the code that you recorded in Chapter 1, “Unleash the Power of Excel with VBA,” to see whether it makes more sense now in the context of objects, properties, and methods. You can also see whether it’s possible to correct the errors brought about by the macro recorder.

Figure 2.6 shows the first code that Excel recorded in the example from Chapter 1.

Figure 2.6. Recorded code from the example in Chapter 1.

Now that you understand the concept of Noun.Verb or Object.Method, consider the first line of code that says Workbooks.OpenText. In this case, Workbooks is an object, while OpenText is a method. Click your cursor inside the word OpenText and press F1 for an explanation of the OpenText method (see Figure 2.7).

Figure 2.7. Help topic for the OpenText method.

The help file confirms that OpenText is a method or an action word. The default order for all the arguments that can be used with OpenText appears in the gray box. Notice that only one argument is required: FileName. All the other arguments are listed as optional.

Optional Parameters

The help file can tell you if you happen to skip an optional parameter. 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.

Now 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 a recipe for disaster. For example, your code may work 98 percent of the time. However, 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.

Defined Constants

Look at the help file entry for DataType in Figure 2.7, which says it can be one of these constants: xlDelimited or xlFixedWidth. The help file says these are the valid xlTextParsingType constants that 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, which 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 (and 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 10 possible xlColumnDataType constants are revealed in a new help topic.

To see a demo of defined constants, search for Excel VBA 2 at YouTube.

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 will not run because it does not understand the TrailingMinusNumbers parameter. Sadly, the only way to learn to handle this frustrating problem 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 be delimited by a comma. Because we do not want to treat two commas as a single comma, the Treat Consecutive Delimiters as One check box is not selected. Sometimes, a field may contain a comma, such as “XYZ, Inc.” In this case, the field should have quotes around the value, as specified in the Text Qualifier box (see Figure 2.11). This second step of the wizard is handled by the following parameters of the OpenText method:

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

Figure 2.11. The second step 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 this case, you 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 happen to click the Advanced button on the third step of the wizard, you have an opportunity to specify something other than the default Decimal and Thousands separator, as well as the setting for Trailing Minus for negative numbers (see Figure 2.13).

Figure 2.13. The TrailingMinusNumbers parameter comes from the Advanced Text Import Settings. If you change either of the separator fields, new parameters are recorded by the macro recorder.

Every action that you perform in Excel while recording a macro gets translated to VBA code. In the case of many dialog boxes, the settings that you do not change will often get recorded along with the items that you do change. When you click OK to close the dialog, the macro recorder often records all the current settings from the dialog in the macro.

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

Selection.End(xlDown).Select

You can click to 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 appears, saying that there are two possible help topics for End—one in the Excel library and one in the VBA library (see Figure 2.14).

Figure 2.14. Sometimes you must choose which help library to use.

If you are new to VBA, you might not know which help library to select. Select one and then 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 you need.

Figure 2.15. If the help topic is not the topic you need, it is easy enough to try again.

Close Help, press F1 again, and select 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.

Properties Can Return Objects

Recall that the discussion at the start of this chapter said the basic syntax of VBA is Object.Method. Consider the line of code currently under examination:

Selection.End(xlDown).Select

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.

Based on this information, you might 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.

Using Debugging Tools to Figure Out Recorded Code

This section introduces some awesome debugging tools that are featured in VB Editor. These tools are excellent for helping you see what a recorded macro code is doing.

Stepping Through Code

Generally, a macro runs quickly—you start it, and less than a second later, it is done. If something goes wrong, you do not have an opportunity to figure out what it is doing. However, using Excel’s Step Into feature makes it possible to run one line of code at a time.

To use this feature, make sure your cursor is in the ImportInvoice procedure, and then from the menu select Debug, Step Into, as shown in Figure 2.17. Alternatively, you can press F8.

Figure 2.17. Using the Step Into feature allows you to run a single line of code at a time.

The VB Editor is now in Break 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, “You 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. At this point, you can press Alt+Tab to switch to Excel and see that the Invoice.txt file has been parsed into Excel. Note that A1 is selected (see Figure 2.19).

Figure 2.19. The Excel window behind the VBA Editor shows that the Invoice.txt file has 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("A11").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 11 for the Total row.

Now that you have identified the problem area, you can stop the code execution by using the Reset command. You can start the Reset command by either 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, you need to close the Invoice.txt file without saving.

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

More Debugging Options: Breakpoints

If you have hundreds of lines of code, you might not want to step through each line one at a time. If you have a general idea that the problem is happening in one particular section of the program, 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). (If you don’t see the margin area, go to Tools, Options, Editor Format and choose Margin Indicator Bar).

Figure 2.23. The large brown dot signifies a breakpoint.

Next, from the Start menu select Run, Run Sub or press F5. The program 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.

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

Backing Up or Moving Forward in Code

When you are stepping through code, you might want to jump over some lines of code, or you might have corrected some lines of code that you want to run again. This is easy to do when you are working in Break mode. One favorite method is to use the mouse to grab the yellow arrow. The cursor changes to an icon, which means 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 right-click the line to which you want to jump, and then select 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.

Not Stepping Through Each Line of Code

When you are stepping through code, you might want to run a section of code without stepping through each line, such as when you get to a loop. You might want VBA to run through the loop 100 times, so 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. Instead, click the cursor on the line you want to step to and press Ctrl+F8 or select Debug, Run to Cursor.

Querying Anything While Stepping Through Code

Even though variables have not yet been discussed, you can query the value of anything while in Break mode. However, keep in mind that the macro recorder never records a variable.

Using the Immediate Window

Press Ctrl+G to display the Immediate window in the VB Editor. While the macro is in Break mode, 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 that can be typed into the Immediate window while a macro is in Break mode.

Instead of typing Print, you can type a question mark: ?Selection.Address. Read the question mark as “What is.”

When invoked with Ctrl+G, the Immediate window usually appears at the bottom of the Code window. You can use the resize handle, which is located 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 that can be used 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. For example, if you have just run one line of code, in the Immediate window you can 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, click in the Immediate window at the end of the line containing the last query (see Figure 2.29).

Figure 2.29. Place the cursor at the end of the previous command and press Enter to avoid typing the same commands over in the Immediate window.

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 $11:$11. The previous answer, $E$11:$G$11, is pushed down the window (see Figure 2.30).

Figure 2.30. The prior answer ($E$11:$G$11) is shifted down, and the current answer ($11:$11) 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 by clicking 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 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 is an excellent technique to use when you are trying to figure out a sticky bit of code. For example, you can query 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.

Querying by Hovering

In many instances, you can hover the cursor over an expression in the code, and then wait a second for a ToolTip to be displayed that shows the current value of the expression. This is an invaluable tool when you get to looping in Chapter 5, “Looping and Flow Control.” It will also 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 you hover the cursor over ActiveCell.Formula, you will get a ToolTip showing 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? This chapter started by complaining that this didn’t seem much like BASIC. However, by now you have to admit that the Visual Basic environment is great to work in and that the debugging tools are excellent.

Querying by Using a Watch Window

In Visual Basic, a watch is not something you wear on your wrist; instead, it allows you to watch the value of any expression while you step through code. Let’s say that in the current example, you want to watch to see what is selected as the code runs. You can do this by setting 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, usually at the bottom of the code window. When you start running the macro, import the file and press End+Down to move to the last row with data. Right after the End(xlDown) code is executed, the Watches window shows 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("A11").Select. The Watches window is updated to show the current address of the Selection is now $A$11 (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.

Using a Watch to Set a Breakpoint

Right-click any line in the Watches window and select 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.

Using a Watch on an Object

In the preceding example, you watched a specific property: Selection.Address. It is also possible to watch an object such as Selection. In Figure 2.38, when a watch has been set up 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. When you look at Figure 2.39, you can see more than you ever wanted to know about Selection! There are properties that you probably never realized were available. You can also 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. For example, the Borders collection has a plus next to it, which means that you can click any + icon to see more details.

Object Browser: The Ultimate Reference

In the VB Editor, press F2 to open the Object Browser, which lets you browse and search the entire Excel object library (see Figure 2.40). A 409-page book is available that is a reprint of this entire object model from the Object Browser. However, you do not need this book because the built-in Object Browser is much more powerful and always available at the touch of F2. The next few pages will teach you how to use the Object Browser.

Figure 2.40. Press F2 to display the Object Browser.

Press F2 and the Object Browser appears where the code window normally appears. The topmost drop-down currently shows <All Libraries>. There is an entry in this drop-down for Excel, Office, VBA, each workbook that you have open, plus additional entries for anything that you check in Tools, References. For now, go to the drop-down and select only Excel.

In the left window of the Object Browser is a list of all classes available for Excel. Click the Application class in the left window. The right window adjusts to show all properties and methods that apply to the Application object (see Figure 2.41). Click something in the right window, such as ActiveCell. The bottom window of the Object Browser tells you that ActiveCell is a property that returns a range. It also tells you that ActiveCell is read-only (an alert that you cannot assign an address to ActiveCell to move the cell pointer).

Figure 2.41. Select a class, and then a member. The bottom window tells you the basics about the particular member.

You have learned from the Object Browser that ActiveCell returns a range. When you click the green hyperlink for Range in the bottom window, you will see all the properties and methods that apply to Range objects and, hence, to the ActiveCell property. Click any property or method, and then click the yellow question mark near the top of the Object Browser to go to the help topic for that property or method.

Type any term in the text box next to the binoculars, and click the binoculars to find all matching members of the Excel library.

Methods appear as green books with speed lines. Properties appear as index cards with a hand pointing to them.

The search capabilities and hyperlinks available in the Object Browser make it much more valuable than an alphabetic printed listing of all of the information. Learn to make use of the Object Browser in the VBA window by pressing F2. To close the Object Browser and return to your code window, click the lower X in the upper-right corner (see Figure 2.41).

Seven Tips for Cleaning Up Recorded Code

At this point, you have two tips for recording code from Chapter 1. So far, this chapter has covered how to understand the recorded code, how to access VBA help for any word, and how to use the excellent VBA debugging tools to step through your code. The remainder of this chapter presents seven tips to use when cleaning up recorded code.

Tip 1: Don’t Select Anything

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, this is done rarely in VBA. There are a couple exceptions to this rule. For example, you need to select a cell when setting up a formula for conditional formatting. 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:

Cells.Select
Selection.Columns.AutoFit

After streamlining the recorded code

Cells.Columns.AutoFit

There are a couple of 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, highlight literally 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.42 and 2.43).

Figure 2.42. Select from here to here...

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

Tip 2: Cells(2,5) Is More Convenient Than Range(“E2”)

The macro recorder uses the Range() property frequently. If you follow the macro recorder’s example, you will find yourself building a lot of complicated code. For example, if you have the row number for the total row stored in a variable, you might try to build this code:

Range("E" & TotalRow).Formula = "=SUM(E2:E" & TotalRow-1 & ")"

In this code, you are using concatenation to join the letter E with the current value of the TotalRow variable. This works, but eventually you will have to refer to a range where the column is stored in a variable. Say that FinalCol is 10, which indicates Column J. To refer to this column in a Range command, you need to do something like this:

FinalColLetter = MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",FinalCol,1)
Range(FinalColLetter & "2").Select

Alternatively, perhaps you could do something like this:

FinalColLetter = CHR(64 + FinalCol)
Range(FinalColLetter & "2").Select

These approaches work for the first 26 columns but fail for the remaining 99.85 percent of the columns.

You could start to write 10-line functions to calculate that the column letter for column 15896 is WMJ, but it is not necessary. Instead of using Range("WMJ17"), you can use the Cells(Row,Column) syntax.

Chapter 3, “Referring to Ranges,” covers this in complete detail. However, for now you need to understand that Range("E10") and Cells(10, 5) both point to the cell at the intersection of the fifth column and tenth row. Chapter 3 also shows you how to use .Resize to point to a rectangular range. Cells(11, 5).Resize(1, 3) is E11:G11.

Tip 3: Ride the Range from the Bottom to Find Last Row

It is difficult to trust data from just anywhere. If you are analyzing data in Excel, remember that the data can come from “who knows what” system written “who knows how long ago.” The universal truth is that eventually some clerk will find a way to break the source system and enter a record without an invoice number. Maybe it will take a power failure to do it, but invariably, you 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.44, pressing End+Down would move the cursor to cell A5 rather than cell A10.

Figure 2.44. 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. This may seem silly in the Excel interface because it is easy to see whether you are at the end of the data. However, 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)

Tip 4: Use Variables to Avoid Hard-Coding Rows and Formulas

The macro recorder never records a variable. Variables are easy to use, but just as in BASIC, a variable can remember a value. Variables are discussed in more detail in Chapter 5.

It is recommended that you set the last row with data to a variable. Be sure to use meaningful variable names such as FinalRow:

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

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

Cells(FinalRow + 1, 1).Value = "Total"

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

Cells(FinalRow + 1, 5).Formula = "=SUM(E2:E" & FinalRow & ")"

Tip 5: R1C1 Formulas That Make Your Life Easier

The macro recorder often writes formulas in an arcane R1C1 style. However, most people change the code back to use a regular A1-style formula. After reading Chapter 6, “R1C1-Style Formulas,” you will understand there are times when you can build an R1C1 formula that is much simpler than the corresponding A1-style formula. By using an R1C1 formula, you can add totals to all three cells in the Total row with the following:

Cells(FinalRow+1, 5).Resize(1, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

Tip 6: Learn to Copy and Paste in a Single Statement

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

Tip 7: Use With...End With to Perform Multiple Actions

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

Next Steps

By now, you should know how to record a macro. You should also be able to use help and debugging to figure out how the code works. This chapter provided seven tools for making the recorded code look like professional code.

The next chapters go into more detail about referring to ranges, looping, and the crazy, but useful R1C1 style of formulas that the macro recorder loves to use. In addition, you are introduced to 30 useful code samples.