Writing Excel Macros with VBA

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Microsoft Excel is an application of enormous power and flexibility. But despite its powerful feature set, there is a great deal that Excel either does not allow you to do or does not allow you to do easily through its user interface. In these cases, we must turn to Excel programming.

Let me give you two examples that have come up in my consulting practice.

The Excel user interface does not have a built-in method for selecting worksheet cells based on various criteria. For instance, there is no way to select all cells whose value is between 0 and 100 or all cells that contain a date later than January 1, 1998. There is also no way to select only those cells in a given column that are different from their immediate predecessors. This can be very useful when you have a sorted column and want to extract a set of unique values, as shown in Figure 1-1.

Figure 1-1: Selecting unique values

I have been asked many times by clients if Excel provides a way to make such selections. After a few such questions, I decided to write an Excel utility for this purpose. The dialog for this utility is shown in Figure 1-2. With this utility, the user can select a match type (such as number, date, or text) and a match criterion. If required, the user supplies one or two values for the match. This has proven to be an extremely useful utility.

Figure 1-2: The Select Special utility

In this book, we will develop a simpler version of this utility, whose dialog is shown in Figure 1-3. This book will also supply you with the necessary knowledge to enhance this utility to something similar to the utility shown in Figure 1-2.

Figure 1-3: Select Special dialog

As you may know, data labels can be edited individually by clicking twice (pausing in between clicks) on a data label. This places the label in edit mode, as shown in Figure 1-4. Once in edit mode, we can change the text of a data label (which breaks any links) or set a new link to a worksheet cell. Accomplishing the same thing programmatically is also very easy. For instance, the code:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Excel user interface does not have a built-in method for selecting worksheet cells based on various criteria. For instance, there is no way to select all cells whose value is between 0 and 100 or all cells that contain a date later than January 1, 1998. There is also no way to select only those cells in a given column that are different from their immediate predecessors. This can be very useful when you have a sorted column and want to extract a set of unique values, as shown in Figure 1-1.

Figure 1-1: Selecting unique values

I have been asked many times by clients if Excel provides a way to make such selections. After a few such questions, I decided to write an Excel utility for this purpose. The dialog for this utility is shown in Figure 1-2. With this utility, the user can select a match type (such as number, date, or text) and a match criterion. If required, the user supplies one or two values for the match. This has proven to be an extremely useful utility.

Figure 1-2: The Select Special utility

In this book, we will develop a simpler version of this utility, whose dialog is shown in Figure 1-3. This book will also supply you with the necessary knowledge to enhance this utility to something similar to the utility shown in Figure 1-2.

Figure 1-3: Select Special dialog

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As you may know, data labels can be edited individually by clicking twice (pausing in between clicks) on a data label. This places the label in edit mode, as shown in Figure 1-4. Once in edit mode, we can change the text of a data label (which breaks any links) or set a new link to a worksheet cell. Accomplishing the same thing programmatically is also very easy. For instance, the code:

ActiveChart.SeriesCollection(1).DataLabels(2).Text = "=MyChartSheet!R12C2"

sets the data label for the second data point to the value of cell B12. Note that the formula must be in R1C1 notation. (We will explain the code in Chapter 21, so don't worry about the details now.)

Figure 1-4: A data label in edit mode

Unfortunately, however, Excel does not provide a simple way to link all of the data labels for a data series with a worksheet range, beyond doing this one data label at a time. In Chapter 21, we will create such a utility, the dialog for which is shown in Figure 1-5. This dialog provides a list of all the data series for the selected chart. The user can select a data series and then define a range to which the data labels will be linked or from which the values will be copied. If the cell values are copied, no link is established, and so changes made to the range are not reflected in the chart. There is also an option to control whether formatting is linked or copied.

Figure 1-5: Set Data Labels dialog

I hope that these illustrations have convinced you that Excel programming can at times be very useful. Of course, you can do much more mundane things with Excel programs, such as automating the printing of charts, sorting worksheets alphabetically, and so on.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In general, the education of an Excel programmer breaks down into a few main categories, as follows.

The Visual Basic Editor: First, you need to learn a bit about the environment in which Excel programming is done. This is the so-called Visual Basic Editor or Excel VBA Integrated Development Environment (IDE for short). We take care of this in Chapter 3 and Chapter 4.
The Basics of Programming in VBA: Next, you need to learn a bit about the basics of the programming language that Excel uses. This language is called Visual Basic for Applications (VBA). Actually, VBA is used not only by Microsoft Excel, but also by the other major components in the Microsoft Office application suite: Access, Word, and PowerPoint. Any application that uses VBA in this way is called a host application for VBA. (There are also a number of non-Microsoft products that use VBA as their underlying programming language. Among the most notable is Visio, a vector-based drawing program.) It is also used by the standalone programming environment called Visual Basic (VB).
We will discuss the basics of the VBA programming language in Chapter 5 through Chapter 8.
Object Models and the Excel Object Model: Each VBA host application (Word, Access, Excel, PowerPoint, Visual Basic) supplements the basic VBA language by providing an object model to deal with the objects that are particular to that application.
For instance, Excel VBA includes the Excel object model , which deals with such objects as workbooks, worksheets, cells, rows, columns, ranges, charts, pivot tables, and so on. On the other hand, the Word object model deals with such objects as documents, templates, paragraphs, fonts, headers, tables, and so on. Access VBA includes two object models, the Access object model and the DAO object model, that allow the programmer to deal with such objects as database tables, queries, forms, and reports. (To learn more about the Word, Access, and DAO object models, see my books

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We begin with some general facts related to programming and programming languages that will help to give the main subject matter of this book some perspective. After all, VBA is just one of many programming languages, and anyone who wants to be a VBA programmer should have some perspective on where VBA fits into the greater scheme of things. Rest assured, however, that we will not dwell on side issues. The purpose of this chapter is to give a very brief overview of programming and programming languages that will be of interest to readers who have not had any programming experience, as well as to those who have.

Simply put, a programming language is a very special and very restricted language that is understood by the computer at some level. We can roughly divide programming languages into three groups, based on the purpose of the language:

Languages designed to manipulate the computer at a low level, that is, to manipulate the operating system (Windows or DOS) or even the hardware itself, are called low-level languages. An example is assembly language.
Languages designed to create standalone applications, such as Microsoft Excel, are high-level languages . Examples are BASIC, COBOL, FORTRAN, Pascal, C, C++, and Visual Basic.
Languages that are designed to manipulate an application program, such as Microsoft Excel, are application-level languages. Examples are Excel VBA, Word VBA, and PowerPoint VBA.

Those terms are not set in concrete and may be used differently by others. However, no one would disagree that some languages are intended to be used at a lower level than others.

The computer world is full of programming languages—hundreds of them. In some cases, languages are developed for specific computers. In other cases, languages are developed for specific types of applications. Table 2-1 gives some examples of programming languages and their general purposes.

Table 2-1: Some Programming Languages

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Simply put, a programming language is a very special and very restricted language that is understood by the computer at some level. We can roughly divide programming languages into three groups, based on the purpose of the language:

Languages designed to manipulate the computer at a low level, that is, to manipulate the operating system (Windows or DOS) or even the hardware itself, are called low-level languages. An example is assembly language.
Languages designed to create standalone applications, such as Microsoft Excel, are high-level languages . Examples are BASIC, COBOL, FORTRAN, Pascal, C, C++, and Visual Basic.
Languages that are designed to manipulate an application program, such as Microsoft Excel, are application-level languages. Examples are Excel VBA, Word VBA, and PowerPoint VBA.

Those terms are not set in concrete and may be used differently by others. However, no one would disagree that some languages are intended to be used at a lower level than others.

The computer world is full of programming languages—hundreds of them. In some cases, languages are developed for specific computers. In other cases, languages are developed for specific types of applications. Table 2-1 gives some examples of programming languages and their general purposes.

Table 2-1: Some Programming Languages
Language	General Purpose
ALGOL	An attempt to design a universal language
BASIC	A simple, easy-to-learn language designed for beginners
C, C++

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The issue of what constitutes good programming style is, of course, subjective, just as is the issue of what constitutes good writing style. Probably the best way to learn good programming style is to learn by example and to always keep the issue somewhere in the front of your mind while programming.

This is not the place to enter into a detailed discussion of programming style. However, in my opinion, the two most important maxims for good programming are:

When in doubt, favor readability over cleverness or elegance.
Fill your programs with lots of meaningful comments.

Let us take the second point first. It is not possible to overestimate the importance of adding meaningful comments to your programs—at least any program with more than a few lines.

The problem is this: good programs are generally used many times during a reasonably long lifetime, which may be measured in months or even years. Inevitably, a programmer will want to return to his or her code to make changes (such as adding additional features) or to fix bugs. However, despite all efforts, programming languages are not as easy to read as spoken languages. It is just inevitable that a programmer will not understand (or perhaps not even recognize!) code that was written several months or years earlier, and must rely on carefully written comments to help reacquaint himself with the code. (This has happened to me more times that I would care to recall.)

Let me emphasize that commenting code is almost as much of an art as writing the code itself. I have often seen comments similar to the following:

' Set x equal to 5
x = 5

This comment is pretty useless, since the actual code is self-explanatory. It simply wastes time and space. (In a teaching tool, such as this book, you may find some comments that would otherwise be left out of a professionally written program.)

A good test of the quality of your comments is to read just the comments (not the code) to see if you get a good sense not only of what the program is designed to do, but also of the steps that are used to accomplish the program's goal. For example, here are the comments from a short BASIC program that appears in Appendix F:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The first step in becoming an Excel VBA programmer is to become familiar with the environment in which Excel VBA programming is done. Each of the main Office applications has a programming environment referred to as its Integrated Development Environment (IDE). Microsoft also refers to this programming environment as the Visual Basic Editor.

Our plan in this chapter and Chapter 4 is to describe the major components of the Excel IDE. We realize that you are probably anxious to get to some actual programming, but it is necessary to gain some familiarity with the IDE before you can use it. Nevertheless, you may want to read quickly through this chapter and the next and then refer back to them as needed.

In Office 97, the Word, Excel, and PowerPoint IDEs have the same appearance, shown in Figure 3-1. (Beginning with Office 2000, Microsoft Access also uses this IDE.) To start the Excel IDE, simply choose Visual Basic Editor from the Macros submenu of the Tools menu, or hit Alt-F11.

Figure 3-1: The Excel VBA IDE

Let us take a look at some of the components of this IDE.

The window in the upper-left corner of the client area (below the toolbar) is called the Project Explorer. Figure 3-2 shows a close-up of this window.

Figure 3-2: The Project Explorer

Note that the Project Explorer has a treelike structure, similar to the Windows Explorer's folders pane (the left-hand pane). Each entry in the Project Explorer is called a node . The top nodes, of which there are two in Figure 3-2, represent the currently open Excel VBA projects (hence the name Project Explorer). The view of each project can be expanded or contracted by clicking on the small boxes (just as with Windows Explorer). Note that there is one project for each currently open Excel workbook.

Each project has a name, which the programmer can choose. The default name for a project is VBAProject. The top node for each project is labeled:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The window in the upper-left corner of the client area (below the toolbar) is called the Project Explorer. Figure 3-2 shows a close-up of this window.

Figure 3-2: The Project Explorer

Note that the Project Explorer has a treelike structure, similar to the Windows Explorer's folders pane (the left-hand pane). Each entry in the Project Explorer is called a node . The top nodes, of which there are two in Figure 3-2, represent the currently open Excel VBA projects (hence the name Project Explorer). The view of each project can be expanded or contracted by clicking on the small boxes (just as with Windows Explorer). Note that there is one project for each currently open Excel workbook.

Each project has a name, which the programmer can choose. The default name for a project is VBAProject. The top node for each project is labeled:

                  ProjectName (WorkbookName)

where ProjectName is the name of the project and WorkbookName is the name of the Excel workbook.

At the level immediately below the top (project) level, as Figure 3-2 shows, there are nodes named:

Microsoft Excel Objects
Forms
Modules
Classes

Under the Microsoft Excel Objects node, there is a node for each worksheet and chartsheet in the workbook, as well as a special node called ThisWorkbook, which represents the workbook itself. These nodes provide access to the code windows for each of these objects, where we can write our code.

Under the Forms node, there is a node for each form in the project. Forms are also called UserForms or custom dialog boxes. We will discuss UserForms later in this chapter.

Under the Modules node, there is a node for each code module in the project. Code modules are also called standard modules. We will discuss modules later in this chapter.

Under the Classes node, there is a node for each class module in the project. We will discuss classes later in this chapter.

The main purpose of the Project Explorer is to allow us to navigate around the project. Worksheets and UserForms have two components—a visible component (a worksheet or dialog) and a code component. By right-clicking on a worksheet or UserForm node, we can choose to view the object itself or the code component for that object. Standard modules and class modules have only a code component, which we can view by double-clicking on the corresponding node.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Properties window (see Figure 3-1) displays the properties of an object and allows us to change them.

When a standard module is selected in the Project window, the only property that appears in the Properties window is the module's name. However, when a workbook, sheet, or UserForm is selected in the Projects window, many of the object's properties appear in the Properties window, as shown in Figure 3-4.

The Properties window can be used to change some of the properties of the object while no code is running—that is, at design time . Note, however, that some properties are read-only and cannot be changed. While most properties can be changed either at design time or run time , some properties can only be changed at design time and some can only be changed at run time. Run-time properties generally do not appear in the Properties window.

Figure 3-4: The Properties window

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Code window displays the code that is associated with the selected item in the Project window. To view this code, select the object in the Projects window and either choose Code from the View menu or hit the F7 key. For objects with only a code component (no visual component), you can just double-click on the item in the Projects window.

Generally, a code module (standard, class, or UserForm) contains more than one procedure. The IDE offers the choice between viewing one procedure at a time (called procedure view) or all procedures at one time (called full-module view ), with a horizontal line separating the procedures. Each view has its advantages and disadvantages, and you will probably want to use both views at different times. Unfortunately, Microsoft has not supplied a menu choice for selecting the view. To change views, we need to click on the small buttons in the lower-left corner of the Code window. (The default view can be set using the Editor tab of the Options dialog box.)

Incidentally, the default font for the module window is Courier, which has a rather thin looking appearance and may be somewhat difficult to read. You may want to change the font to FixedSys (on the Editor Format tab of the Options dialog, under the Tools menu), which is very readable.

At the top of the Code window, there are two drop-down list boxes (see Figure 3-1). The Object box contains a list of the objects (such as forms and controls) that are associated with the current project, and the Procedure box contains a list of all of the procedures associated with the object selected in the Object box. The precise contents of these boxes varies depending on the type of object selected in the Project Explorer.

Section 3.3.2.1: A workbook or sheet object

When a workbook or sheet object is selected in the Project window, the Object box contains only two entries: general, for general procedures, and the object in question, either Workbook, Worksheet, or Chart. When the object entry is selected, the Procedure box contains empty code shells for the events that are relevant to that object. Figure 3-5 shows an example.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Immediate window (see Figure 3-1) has two main functions. First, we can send output to this window using the command Debug.Print. For instance, the following code will print whatever text is currently in cell A1 of the active worksheet to the Immediate window:

Debug.Print ActiveSheet.Range("A1").Text

This provides a nice way to experiment with different code snippets.

The other main function of the Immediate window is to execute commands. For instance, by selecting some text in the active document, switching to the Immediate window, and entering the line shown in Figure 3-9, the selected text will be boldfaced (after hitting the Enter key to execute the code).

Figure 3-9: The Immediate Window

The Immediate window is an extremely valuable tool for debugging a program, and you will probably use it often (as I do).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you need more space for writing code, you can close the Properties window, the Project window, and the Immediate window. On the other hand, if you are fortunate enough to have a large monitor, you can split your screen as shown in Figure 3-10 to see the Excel VBA IDE and an Excel workbook at the same time. Then you can trace through each line of your code and watch the results in the workbook! (You can toggle between Excel and the IDE using the Alt-F11 key combination.)

Figure 3-10: A split screen approach

Many of the windows in the IDE (including the Project, Properties, and Immediate windows) can be in one of two states: docked or floating. The state can be set using the Docking tab on the Options dialog box, which is shown in Figure 3-11.

Figure 3-11: The Docking options

A docked window is one that is attached, or anchored, to an edge of another window or to one edge of the client area of the main VBA window. When a dockable window is moved, it snaps to an anchored position. On the other hand, a floating window can be placed anywhere on the screen.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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

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.

Section 4.1.1.1: 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.

Section 4.1.1.2: 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

Section 4.1.1.3: 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.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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

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.

Section 4.1.1.1: 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.

Section 4.1.1.2: 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

Section 4.1.1.3: 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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!

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In the next few chapters, we will discuss the basics of the VBA programming language, which underlies all of the Microsoft Office programming environments. During our discussion, we will consider many short coding examples. I hope that you will take the time to key in some of these examples and experiment with them.

We have already discussed the fact that comments are important. Any text that follows an apostrophe is considered a comment and is ignored by Excel. For example, the first line in the following code is a comment, as is everything following the apostrophe on the third line:

' Declare a string variable
Dim WksName as String
WksName = Activesheet.Name   ' Get name of active sheet

When debugging code, it is often useful to temporarily comment out lines of code so they will not execute. The lines can subsequently be uncommented to restore them to active duty. The CommentBlock and UncommentBlock buttons, which can be found on the Edit toolbar, will place or remove comment marks from each currently selected line of code and are very useful for commenting out several lines of code in one step. (Unfortunately, there are no keyboard shortcuts for these commands, but they can be added to a menu and given menu accelerator keys.)

The very nature of Excel VBA syntax often leads to long lines of code, which can be difficult to read, especially if we need to scroll horizontally to see the entire line. For this reason, Microsoft recently introduced a line-continuation character into VBA. This character is the underscore, which must be preceded by a space and cannot be followed by any other characters (including comments). For example, the following code:

ActiveSheet.Range("A1").Font.Bold = _
True

is treated as one line by Excel. It is important to note that a line continuation character cannot be inserted in the middle of a literal string constant, which is enclosed in quotation marks.

The VBA language has two types of constants. A literal constant (also called a constant or literal

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We have already discussed the fact that comments are important. Any text that follows an apostrophe is considered a comment and is ignored by Excel. For example, the first line in the following code is a comment, as is everything following the apostrophe on the third line:

' Declare a string variable
Dim WksName as String
WksName = Activesheet.Name   ' Get name of active sheet

When debugging code, it is often useful to temporarily comment out lines of code so they will not execute. The lines can subsequently be uncommented to restore them to active duty. The CommentBlock and UncommentBlock buttons, which can be found on the Edit toolbar, will place or remove comment marks from each currently selected line of code and are very useful for commenting out several lines of code in one step. (Unfortunately, there are no keyboard shortcuts for these commands, but they can be added to a menu and given menu accelerator keys.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The very nature of Excel VBA syntax often leads to long lines of code, which can be difficult to read, especially if we need to scroll horizontally to see the entire line. For this reason, Microsoft recently introduced a line-continuation character into VBA. This character is the underscore, which must be preceded by a space and cannot be followed by any other characters (including comments). For example, the following code:

ActiveSheet.Range("A1").Font.Bold = _
True

is treated as one line by Excel. It is important to note that a line continuation character cannot be inserted in the middle of a literal string constant, which is enclosed in quotation marks.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The VBA language has two types of constants. A literal constant (also called a constant or literal ) is a specific value, such as a number, date, or text string, that does not change, and that is used exactly as written. Note that string constants are enclosed in double quotation marks, as in "Donna Smith" and date constants are enclosed between number signs, as in #1/1/96#.

For instance, the following code stores a date in the variable called dt:

Dim dt As Date
dt = #1/2/97#

A symbolic constant (also sometimes referred to simply as a constant) is a name for a literal constant.

To define or declare a symbolic constant in a program, we use the Const keyword, as in:

Const InvoicePath = "d:\Invoices\"

In this case, Excel will replace every instance of InvoicePath in our code with the string "d:\Invoices\". Thus, InvoicePath is a constant, since it never changes value, but it is not a literal constant, since it is not used as written.

The virtue of using symbolic constants is that, if we decide later to change "d:\Invoices\" to "d:\OldInvoices\", we only need to change the definition of InvoicePath to:

Const InvoicePath = "d:\OldInvoices\"

rather than searching through the entire program for every occurrence of the phrase "d:\Invoices\".

It is generally good programming practice to declare any symbolic constants at the beginning of the procedure in which they are used (or in the Declarations section of a code module). This improves readability and makes housekeeping simpler.

In addition to the symbolic constants that you can define using the Const statement, VBA has a large number of built-in symbolic constants (about 700), whose names begin with the lowercase letters vb. Excel VBA adds additional symbolic constants (1266 of them) that begin with the letters xl. We will encounter many of these constants throughout the book.

Among the most commonly used VBA constants are vbCrLf, which is equivalent to a carriage return followed by a line feed, and vbTab, which is equivalent to the tab character.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·

Table of Contents

Section 3.3.2.1: A workbook or sheet object

Section 4.1.1.1: Navigating the code window at design time

Section 4.1.1.2: Tracing code

Section 4.1.1.3: Bookmarks

Section 4.1.1.1: Navigating the code window at design time

Section 4.1.1.2: Tracing code

Section 4.1.1.3: Bookmarks