Writing Excel Macros with VBA, 2nd Edition

Chapter 4. The Visual Basic Editor, Part II

In this chapter, we conclude our discussion of the Visual Basic Editor. Again, let us remind the reader that he or she may want to read quickly through this chapter and refer to it later as needed.

Navigating the IDE

If you prefer the keyboard to the mouse (as I do), then you may want to use keyboard shortcuts. Here are some tips.

General Navigation

The following keyboard shortcuts are used for navigating the IDE:

F7

Go to the Code window.

F4

Go to the Properties window.

Ctrl-R

Go to the Project window.

Ctrl-G

Go to the Immediate window.

Alt-F11

Toggle between Excel and the VB IDE.

Navigating the code window at design time

Within the code window, the following keystrokes are very useful:

F1

Help on the item under the cursor.

Shift-F2

Go to the definition of the item under the cursor. (If the cursor is over a call to a function or subroutine, hitting Shift-F2 sends you to the definition of that procedure.)

Ctrl-Shift-F2

Return to the last position where editing took place.

Tracing code

The following keystrokes are useful when tracing through code (discussed in Section 4.6, later in this chapter):

F8

Step into

Shift-F8

Step over

Ctrl-Shift-F8

Step out

Ctrl-F8

Run to cursor

F5

Run

Ctrl-Break

Break

Shift-F9

Quick watch

F9

Toggle breakpoint

Ctrl-Shift-F9

Clear all breakpoints

Bookmarks

It is also possible to insert bookmarks within code. A bookmark marks a location to which we can return easily. To insert a bookmark, or to move to the next or previous bookmark, use the Bookmarks submenu of the Edit menu. The presence of a bookmark is indicated by a small blue square in the left margin of the code.

Getting Help

If you are like me, you will probably make extensive use of Microsoft’s Excel VBA help files while programming. The simplest way to get help on an item is to place the cursor on that item and hit the F1 key. This works not only for VBA language keywords but also for portions of the VBA IDE.

Note that Microsoft provides multiple help files for Excel, the VBA language, and the Excel object model. While this is quite reasonable, occasionally the help system gets a bit confused and refuses to display the correct help file when we strike the F1 key. (I have not found a simple resolution to this problem, other than shutting down Excel and the Visual Basic Editor along with it.)

Note also that a standard installation of Microsoft Office does not install the VBA help files for the various applications. Thus, you may need to run the Office setup program and install Excel VBA help by selecting that option in the appropriate setup dialog box. (Do not confuse Excel help with Excel VBA help.)

Creating a Procedure

There are two ways to create a new procedure (that is, a subroutine or a function) within a code module. First, after selecting the correct project in the Project Explorer, we can select the Procedure option from the Insert menu. This will produce the dialog box shown in Figure 4-1. Just type in the name of the procedure and select Sub or Function (the Property choice is used with custom objects in a class module). We will discuss the issue of public versus private procedures and static variables later in this chapter.

Figure 4-1. The Add Procedure dialog box

A simpler alternative is to simply begin typing:

Sub SubName

or:

Function FunctionName

in any code window (following the current End Sub or End Function statement, or in the general declarations section). As soon as the Enter key is struck, Excel will move the line of code to a new location and thereby create a new subroutine. (It will even add the appropriate ending—End Sub or End Function.)

Run Time, Design Time, and Break Mode

The VBA IDE can be in any one of three modes: run mode, break mode, or design mode. When the IDE is in design mode, we can write code or design a form.

Run mode occurs when a procedure is running. To run (or execute) a procedure, just place the cursor anywhere within the procedure code and hit the F5 key (or select Run from the Run menu). If a running procedure seems to be hanging, we can usually stop the procedure by hitting Ctrl-Break (hold down the Control key and hit the Break key).

Break mode is entered when a running procedure stops because of either an error in the code or a deliberate act on our part (described a bit later). In particular, if an error occurs, Excel will stop execution and display an error dialog box, an example of which is shown in Figure 4-2.

Figure 4-2. An error message

Error dialog boxes offer a few options: end the procedure, get help (such as it may be) with the problem, or enter break mode to debug the code. In the latter case, Excel will stop execution of the procedure at the offending code and highlight that code in yellow. We will discuss the process of debugging code a bit later.

Aside from encountering an error, there are several ways we can deliberately enter break mode for debugging purposes:

• Hit the Ctrl-Break key and choose Debug from the resulting dialog box.

• Include a Stop statement in the code, which causes Excel to enter break mode.

• Insert a breakpoint on an existing line of executable code. This is done by placing the cursor on that line and hitting the F9 function key (or using the Toggle Breakpoint option on the Debug menu). Excel will place a red dot in the left margin in front of that line and will stop execution when it reaches the line. You may enter more than one breakpoint in a procedure. This is generally preferred over using the Stop statement, because breakpoints are automatically removed when we close down the Visual Basic Editor, so we don’t need to remember to remove them, as we do with Stop statements.

• Set a watch statement that causes Excel to enter break mode if a certain condition becomes true. We will discuss watch expressions a bit later.

To exit from Break mode, choose Reset from the Run menu.

Note that the caption in the title bar of the VBA IDE indicates which mode is currently active. The caption contains the word “[running]” when in run mode and “[break]” when in break mode.

Errors

In computer jargon, an error is referred to as a bug . In case you are interested in the origin of this word, the story goes that when operating the first large-scale digital computer, called the Mark I, an error was traced to a moth that had found its way into the hardware. Incidentally, the Mark I (circa 1944) had 750,000 parts, was 51 feet long, and weighed over five tons. How about putting that on your desktop? It also executed about one instruction every six seconds, as compared to over 200 million instructions per second for a Pentium!

Errors can be grouped into three types based on when they occur—design time, compile time, or run time.

Design-Time Errors

As the name implies, a design-time error occurs during the writing of code. Perhaps the nicest feature of the Visual Basic Editor is that it can be instructed to watch as we type code and stop us when we make a syntax error. This automatic syntax checking can be enabled or disabled in the Options dialog box shown in Figure 4-3, but I strongly suggest that you keep it enabled.

Figure 4-3. The Options dialog box

Notice also that there are other settings related to the design-time environment, such has how far to indent code in response to the Tab key. We will discuss some of these other settings a bit later.

To illustrate automatic syntax checking, Figure 4-4 shows what happens when we deliberately enter the syntactically incorrect statement x == 5 and then attempt to move to another line. Note that Microsoft refers to this type of error as a compile error in the dialog box and perhaps we should as well. However, it seems more descriptive to call it a design-time error or just a syntax error.

Figure 4-4. A syntax error message

Compile-Time Errors

Before a program can be executed, it must be compiled, or translated into a language that the computer can understand. The compilation process occurs automatically when we request that a program be executed. We can also specifically request compilation by choosing the Compile Project item under the Debug menu.

If Excel encounters an error while compiling code, it displays a compile error message. For example, the code in Figure 4-5 contains a compile-time error. In particular, the first line:

Dim wb as Workbook

defines a variable of type Workbook to represent an Excel workbook. (We will go into all of this in Chapter 17, so don’t worry about the details now.) However, the second line:

Set wb = ActiveWorkbook.Name

attempts to assign the variable wb not to the active workbook, which would be legal, but to the name of the active workbook. This error is not caught during design time because it is not a syntax error. It is only at compile time, when Excel considers the statement in the context of the first statement, that the error becomes evident.

Figure 4-5. A compilation error message

Run-Time Errors

An error that occurs while a program is running is called a run-time error . Figure 4-6 illustrates a run-time error and its corresponding error message. In this example, the code:

Workbooks.Open "d:\temp\ExistNot.xls"

attempts to open an Excel workbook that does not exist. Notice that this error message is actually quite friendly—not only does it describe the error in clear terms (the file could not be found), but it also offers some suggestions for eliminating the problem.

Figure 4-6. A run-time error message

Logical Errors

There is one more type of error that we should discuss, since it is the most insidious type of all. A logical error can be defined as the production of an unexpected and incorrect result. As far as Excel is concerned, there is no error, because Excel has no way of knowing what we intend. (Thus, a logical error is not a run-time error, in the traditional sense, even though it does occur at run time.)

To illustrate, the following code purports to compute the average of some numbers:

Dim x(3) As Integer
Dim Ave As Single
x(0) = 1
x(1) = 3
x(2) = 8
x(3) = 5
Ave = (x(0) + x(1) + x(2) + x(3)) / 3
MsgBox "Average is: " & Ave

The result is the message box shown in Figure 4-7. Unfortunately, it is incorrect. The penultimate line in the preceding program should be:

Ave = (x(0) + x(1) + x(2) + x(3)) / 4

Note the 4 in the denominator, since there are 4 numbers to average. The correct average is 4.25. Of course, Excel will not complain because it has no way of knowing whether we really want to divide by 3.

Figure 4-7. The result of a logical error

Precisely because Excel cannot warn us about logical errors, they are the most dangerous, because we think that everything is correct.

Debugging

Invariably, you will encounter errors in your code. Design-time and compile-time errors are relatively easy to deal with because Excel helps us out with error messages and by indicating the offending code. Logical errors are much more difficult to detect and to fix. This is where debugging plays a major role. The Excel IDE provides some very powerful ways to find bugs.

Debugging can be quite involved, and we could include a whole chapter on the subject. There are even special software applications designed to assist in complex debugging tasks. However, for most purposes, a few simple techniques are sufficient. In particular, Excel makes it easy to trace through our programs, executing one line at a time, watching the effect of each line as it is executed.

Let us try a very simple example, which you should follow along on your PC. If possible, you should arrange your screen as in Figure 4-8. This will make it easier to follow the effects of the code, since you won’t need to switch back and forth between the Excel window and the Excel VBA window. The code that we will trace is shown in Example 4-1. Note that lines beginning with an apostrophe are comments that are ignored by Excel.

Figure 4-8. Top-and-bottom windows for easy debugging

Example 4-1. A Simple Program to Trace

Sub Test()

Dim ws As Worksheet

Set ws = ActiveSheet

' Insert a value into cell A1
ws.Cells(1, 1).Value = "sample"
' Make it bold
ws.Cells(1, 1).Font.Bold = True
' Copy cell
ws.Cells(1, 1).Copy
' Paste value only
ws.Cells(2, 1).PasteSpecial Paste:=xlValues

End Sub

Make sure that an empty worksheet is active in Excel. Switch to the VBA IDE and place the insertion point somewhere in the code. Then hit the F8 key once, which starts the tracing process. (You can also choose Step Into from the Debug menu.)

Continue striking the F8 key, pausing between keystrokes to view the effect of each instruction in the Excel window. (You can toggle between Excel and the IDE using Alt-F11.) As you trace through this code, you will see the word “sample” entered into cell A1 of the active worksheet, changed to appear in boldface, copied to the Clipboard, and pasted as normal text into the cell A2. Then you can begin to see what Excel VBA programming is all about!

Let us discuss some of the tools that Excel provides for debugging code.

Tracing

The process of executing code one line at a time, as we did in the previous example, is referred to as tracing or code stepping . Excel provides three options related to tracing: stepping into, stepping over, and stepping out of. The difference between these methods refers to handling calls to other procedures.

To illustrate the difference, consider the code shown in Example 4-2. In ProcedureA, the first line of code sets the value of cell A1 of the active worksheet. The second line calls ProcedureB and the third line boldfaces the contents of the cell. ProcedureB simply changes the size and name of the font used in cell A1. Don’t worry about the exact syntax of this code. The important thing to notice is that the second line of ProcedureA calls ProcedureB.

Example 4-2. Sample Code for Tracing Methods

Sub ProcedureA()
   ActiveSheet.Cells(1, 1).Value = "sample"
   Call ProcedureB
   ActiveSheet.Cells(1, 1).Font.Bold = True
End Sub

Sub ProcedureB()
   ActiveSheet.Cells(1, 1).Font.Size = 24
   ActiveSheet.Cells(1, 1).Font.Name = "Arial"
End Sub

Stepping into

Step Into executes code one statement (or instruction) at a time. If the statement being executed calls another procedure, stepping into that statement simply transfers control to the first line in the called procedure. For instance, with reference to the previous code, stepping into the line:

Call ProcedureB

in ProcedureA transfers control to the first line of ProcedureB :

ActiveSheet.Cells(1, 1).Font.Size = 24

Further tracing proceeds in ProcedureB. Once all of the lines of ProcedureB have been traced, control returns to ProcedureA at the line immediately following the call to ProcedureB—that is, at the line:

ActiveSheet.Cells(1, 1).Font.Bold = True

Step Into has another important use. If we choose Step Into while still in design mode, that is, before any code is running, execution begins but break mode is entered before the first line of code is actually executed. This is the proper way to begin tracing a program.

Step Over (Shift-F8 or choose Step Over from the Debug menu)

Step Over is similar to Step Into, except that if the current statement is a call to another procedure, the entire called procedure is executed without stopping (rather than tracing through the called procedure). Thus, for instance, stepping over the line:

Call ProcedureB

in the previous procedure executes ProcedureB and stops at the next line:

ActiveSheet.Cells(1, 1).Font.Bold = True

in ProcedureA. This is useful if we are certain that ProcedureB is not the cause of our problem and we don’t want to trace through that procedure line by line.

Step Out (Ctrl-Shift-F8 or choose Step Out from the Debug menu)

Step Out is intended to be used within a called procedure (such as ProcedureB). Step Out executes the remaining lines of the called procedure and returns to the calling procedure (such as ProcedureA). This is useful if we are in the middle of a called procedure and decide that we don’t need to trace any more of that procedure, but want to return to the calling procedure. (If you trace into a called procedure by mistake, just do a Step Out to return to the calling procedure.)

Run To Cursor (Ctrl-F8 or choose Run To Cursor from the Debug menu)

If the Visual Basic Editor is in break mode, we may want to execute several lines of code at one time. This can be done using the Run To Cursor feature. Simply place the cursor on the statement immediately following the last line you want to execute and then execute Run To Cursor.

Set Next Statement (Ctrl-F9 or choose Set Next Statement from the Debug menu)

We can also change the flow of execution while in break mode by placing the cursor on the statement that we want to execute next and selecting Set Next Statement. This will set the selected statement as the next statement to execute, but will not execute it until we continue tracing.

Breaking out of Debug mode

When we no longer need to trace our code, we have two choices. To return to design mode, we can choose Reset from the Run menu (there is no hotkey for this). To have Excel finish executing the current program, we can hit F5 or choose Run from the Run menu.

Watching Expressions

It is often useful to watch the values of certain expressions or variables as we trace through a program. Excel provides several ways to do this.

Quick Watch (Shift-F9)

This feature is used to quickly check the value of a variable or expression while in break mode. We just place the insertion point over the variable name and hit Shift-F9 (or choose Quick Watch from the Debug menu). For instance, Figure 4-9 shows the Quick Watch dialog box when the expression x + 2 is selected in the code in Figure 4-10. According to Figure 4-9, at the time that Quick Watch was invoked, the expression x + 2 had the value 8. Note that if we had just placed the insertion point in front of the letter x, then Quick Watch would have reported the value of this variable alone.

Figure 4-9. The Quick Watch window

Another way to quickly get values for expressions or variables is to enable Auto Data Tips on the Editor tab of Excel VBA’s Options dialog box. With this feature enabled, when we place the mouse pointer over a variable or select an expression and place the mouse pointer over it, after a slight delay, a small yellow window will appear containing the value of the variable or expression. This is very useful!

The Locals and Watches windows

There are two special windows that aid in watching expressions: the Watches window and the Locals window. These are shown in Figure 4-10.

Figure 4-10. The Locals and Watches windows

The Locals window shows the values of all local variables. A local variable is a variable defined within the current procedure, and is therefore not valid in any other procedure. (We will discuss local variables in the next chapter.)

The Watches window shows all of the watches that we have set. A watch is a variable or expression that we place in the Watch window. Excel automatically updates the expressions in the Watch window after each line of code is executed and acts according to the type of watch defined, as described in the following list.

To add a watch, choose Add Watch from the Debug menu. This will produce the dialog box shown in Figure 4-11. We can then enter a variable or expression, such as x > 6, in the Expression text box. Note that there are three types of watches:

• Watch Expression simply adds the expression to the Watches window, so we can watch its value as code is executed. In this example, the value of the expression will be either True or False, depending upon whether x is greater than 6.

• Break When Value Is True asks Excel to stop execution and enter break mode whenever the expression is true. In this example, VBA will break execution when x > 6 is true, that is, when x becomes greater than 6.

• Break When Value Changes asks Excel to enter break mode when the value of the expression changes in any way. (In this case, from True to False or vice-versa.)

Figure 4-11. The Add Watch dialog box

Altogether, the various tracing modes and watch types provide a very powerful set of tools for debugging code. I use them often!

Macros

In earlier days, a macro consisted of a series of keystrokes that was recorded and assigned to a hot key. When a user invoked the hot key, the recording would play and the recorded keystrokes would be executed.

These days, macros (at least for Microsoft Office) are much more sophisticated. In fact, an Excel macro is just a special type of subroutine—one that does not have any parameters. (We will discuss subroutines and parameters in Chapter 6.)

Recording Macros

Excel has the capability of recording very simple macros. When we ask Excel to record a macro by selecting Macro → Record New Macro from Excel’s (not Excel VBA’s) Tools menu, it takes note of our keystrokes and converts them into a VBA subroutine (with no parameters).

For example, suppose we record a macro that does a find and replace, replacing the word “macro” by the word “subroutine.” When we look in the Projects window under the project in which the macro was recorded, we will find a new subroutine in a standard code module:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/13/98 by sr
'

'
    Cells.Replace What:="macro", Replacement:="subroutine", _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

This is the same code that we might have written in order to perform this find and replace operation.

In certain situations, the macro recorder can serve as a very useful learning tool. If we can’t figure out how to code a certain action, we can record it in a macro and cut and paste the resulting code into our own program. (In fact, you might want to try recording the creation of a pivot table.)

However, before you get too excited about this cut-and-paste approach to programming, we should point out that it is not anywhere near the panacea one might hope. One problem is that the macro recorder has a tendency to use ad hoc code rather than code that will work in a variety of situations. For instance, recorded macro code will often refer to the current selection, which may work at the time the macro was recorded but is not of much use in a general setting, because the programmer cannot be sure what the current selection will be when the user invokes the code.

Another problem is that the macro recorder is only capable of recording very simple procedures. Most useful Excel programs are far too complicated to be recorded automatically by the macro recorder.

Finally, since the macro recorder does such a thorough job of translating our actions into code, it tends to produce very bloated code, which often runs very slowly.

Running Macros

As you may know, to run a macro from the user interface, we just choose Macros from the Macro submenu of the Tools menu (or hit Alt-F8). This displays the Macro dialog box shown in Figure 4-12. This dialog box lists all macros in the current workbook or in all workbooks. From here, we can do several things, including running, editing, creating, or deleting macros. (Choosing Edit or Create places us in the VB Editor.)

Figure 4-12. Excel’s Macro dialog box

We should also comment on what appears and does not appear in the Macro list box. All macros that we write will appear in the Macros dialog box (as will all recorded macros). However, there are a few variations. If we give the macro a unique name (within the context given in the “Macros in” list box), then only the name of the macro will appear in the list box. If the name is not unique, then it must be qualified by the name of the module in which the macro appears, as in:

Sheet5.ScrollChartTypes

in Figure 4-12. Unfortunately, the first version of a macro with a nonunique name is not qualified. (Note the presence of another ScrollChartTypes macro in Figure 4-12.)

Note that we can prevent a macro procedure from appearing in the Macros list box by making the procedure private, using the Private keyword, as in:

Private Sub HideThisMacro()

We will discuss Private and Public procedures in Chapter 6.

Finally, if you are like me, you will collect a great many macros over the years. As time goes by, you may forget the names of some of these macros and thus have trouble finding a macro when you need it. I would advise you to give some careful thought to creating a consistent naming convention for macros. I begin the names of all macros with a word that categorizes the macro. For instance, all of my macros that deal with worksheets begin with the letters Wks, as in:

Wks_Sort
Wks_Compare
Wks_Print