Debug errors in Office Visual Basic for Applications code

Power User Corner

By Frank C. Rice

If the thought of having to fix Microsoft Visual Basic® for Applications (VBA) code in your favorite Office program sometimes keeps you awake at night, the tools and techniques described in this column can help you on the path to becoming a true VBA guru.

Applies to
Microsoft Office XP

See all Power User columns
See all columns

Let's face it. If you work with Microsoft Office programs long enough, sooner or later, this could happen to you. You have a reputation in your office as "the" person who has Microsoft Office (insert program name here) down pat. And sure enough, one day Mr. Zork, your supervisor, comes into your cubicle and says he's getting some strange error message and that he has a report that's overdue and you have to fix it. Now!

He then grabs you by your favorite pocket protector and pulls you into his office. You look at the cryptic error message and, to your horror, realize that some line in some VBA procedure may be the culprit. (VBA is the programming language for all of the Office programs.)

What do you do? Well, instead of bolting out the door in a frantic search for the IT guru, you use the tools and strategies discussed in this column to fix the problem, retain your reputation, and stash away some much-needed brownie points for another day.

Debugging strategies

Although many books have been written about fixing bugs (errors) in code and, more important, about writing code that makes analysis and debugging easy, you can fix many of the more common problems that might arise if you are equipped with just a few tools and techniques. Additionally, once you gain some experience in using these tools and start to understand how code works, you will feel more confident about your abilities and know that this same experience can be applied to more complicated problems.

When debugging code, your task is to determine where something went wrong. And although there are no magic tricks to debugging, the Visual Basic Editor provides several debugging tools to help you analyze errors. The Visual Basic Editor is the window where you write, test, and debug VBA code in Office programs. We will look at using those tools to do the following:

  • Set breakpoints to suspend execution of your code when you suspect a problem.
  • Step through code line by line.
  • Test VBA statements by executing them from the Immediate window.
  • Monitor the value of variables and other objects as the code is running.

If some of these terms are foreign to you, don't worry because I will explain them all before we are done.

If you are going through the steps in this column for practice, you should use a sample file and not one that contains critical data or code. For example, if you are practicing in Microsoft Access, you could use a copy of the Northwind sample database. Using a file that contains important information to practice these techniques could result in a loss of data or cause the file to stop working.

Setting a breakpoint

To use the other debugging tools we'll discuss, first you need to suspend execution of the code. To do that, you set a breakpoint. A breakpoint is a location in the code at which execution of the code is halted so that you can examine it before continuing. When suspended, the code is still running but paused between statements. Variables—temporary places to store information in a computer's memory—retain their values, and the Visual Basic Editor window displays the code that is currently running.

To set a breakpoint

  1. Start your Office program, such as Access, Microsoft Excel, Microsoft Word, Microsoft Outlook®, Microsoft PowerPoint®, or Microsoft FrontPage®.
  2. Start the Visual Basic Editor by either pointing to Macro on the Tools menu and then clicking Visual Basic Editor or pressing ALT+F11.

 Note   Looking back at the scenario in the opening paragraphs of this column, if an Office program stops running because of an error in the code, a dialog box is displayed that asks whether you want to debug the code. If you click the Debug button, the Visual Basic Editor opens automatically and the offending line of code is highlighted in yellow.

  1. On the Insert menu, click Module. A module is a container for VBA procedures. Procedures containing code can also exist in other areas in the program. For example, code can exist in a document in Word or in a workbook in Excel. Assuming that you have created a blank file in your program to practice with, the module you just inserted should be named Module1.
  2. The large window you see is called the Code window. (If you don't see this, on the View menu, click Code.) Scroll to the bottom of the Code window and insert the following procedure. This procedure displays a message and then enters a loop that displays the first four letters of the alphabet in two columns as upper-case and lower-case, and then displays another message:

    Sub DebugDemo()
    Dim strBegin As String
    Dim strEnd As String
    Dim strLetter As String
    Dim intNum As Long

    strBegin = "Before the loop."
    Debug.Print strBegin

    For intNum = 1 To 4
    strLetter = Chr(64 + intNum) & " " & Chr(96 + intNum)
    Debug.Print strLetter
    Next intNum

    strEnd = "After the loop."
    Debug.Print strEnd

    End Sub
  3. Click the gray margin beside the Debug.Print strEnd statement near the bottom. Notice that a red dot appears in the margin. This breakpoint indicates that execution of the code will pause when it reaches this statement. You can remove the breakpoint by clicking the red dot, but for now leave the breakpoint.

Using the Immediate window

Now that you have set a breakpoint, you need to run the procedure containing the code. There are a couple of ways to do this, but the most useful way is to open the Immediate window and run the code from there.

The Immediate window can be used as a kind of scratch pad for running code as well as for displaying the results of your procedures. In addition, you can use the Immediate window for testing the syntax of VBA statements before you use them in your subroutines or functions. For now, we'll use it to run the procedure we just added. Functions are procedures that can be called by another procedure to perform some task and can return a value to the procedure that called it. Conversely, subroutines also perform some task but can't return a value.

To run a procedure by using the Immediate window

  1. While still in the Visual Basic Editor, on the View menu, click Immediate Window. The Immediate window is displayed below the Code window.
  2. To run the procedure, type Module1.DebugDemo in the Immediate window, and then press ENTER.

 Note    If the procedure had been a function instead of a subroutine, you would need to type a ? and Module1.<function name>, and then press ENTER.

  1. After running the procedure, notice that execution of the code is suspended, and the statement where you set the breakpoint is highlighted in yellow. Also notice that the results from the first two Debug.Print statements in the procedure are displayed in the Immediate window. The third Debug.Print statement isn't displayed, because you paused execution before it could be run.
  2. Now, remove the text from the Immediate window by selecting everything in the Immediate window except the name of the subroutine (DebugDemo) and pressing DELETE.
  3. Remove the breakpoint by clicking the red dot, and then press ENTER to rerun the procedure from the Immediate window. The results from all three of the Debug.Print statements are displayed.

Now that you are familiar with setting breakpoints and using the Immediate window, let's explore a couple of other debugging techniques.

Stepping through code line by line

A good way to see what your code is doing is to step through it line by line. This technique also uses other tools in the Visual Basic Editor to debug your code, such as the Debug.Print statements you saw earlier. To step through code in this way, you can use the Step Into command. Stepping through statements line by line is sometimes referred to as "tracing."

To step through code line by line

  1. In the Visual Basic Editor, set a breakpoint on the End Sub statement of the DebugDemo subroutine.
  2. Clear the contents of the Immediate window.
  3. Click the mouse anywhere in the DebugDemo subroutine.
  4. On the Debug menu, click Step Into or press F8. Notice that the first line of the procedure is highlighted in yellow.
  5. Press F8 several times and watch the Immediate window as the insertion point moves though the code one line at a time. The results of the Debug.Print statements are displayed.

Using the Immediate window to test VBA statements

In addition to being useful for running procedures, the Immediate window can also be useful for testing statements that you suspect may be buggy, as well as for testing statements before you add them to your code.

To test VBA statements in the Immediate window

  1. In the Immediate window, type the following statement: Msgbox "These tools make debugging easy!"
  2. Press ENTER. A message box is displayed.
  3. Click OK to close the message box and return to the Visual Basic Editor.
  4. In the Immediate window, type Debug.Print "The Immediate window is really useful."
  5. Press ENTER. This time, the results of the statement are displayed (printed) in the Immediate window.

Using the Locals window

The Locals window automatically displays the values of variables and other objects in the current procedure, providing the easiest way to view variable value changes as each statement in the procedure is run.

To use the Locals window

  1. Set a breakpoint on the End Sub statement in the DebugDemo subroutine.
  2. Close the Immediate window.
  3. On the View menu, click Locals Window. The Locals window is displayed below the Code window.
  4. Click in the procedure, and then press F8 a few times as you watch the Locals window. The Locals window provides a great view of the variables in the procedure as they change with the running code. You can use the Locals window to monitor the variables and see whether they change as you expect them to as your code runs.

In this column, we have just touched the surface of the tools and techniques that are available to help you in writing and using VBA code.

More information

About the author

Frank C. Rice works for the Office Developer Center team. In addition to contributing to the Office Power User Corner column, Frank writes developer articles for the Microsoft Developer Network (MSDN).

If you like this column and want to hear about more fun and useful Office offerings, sign up for our newsletter.

See all Power User columns
See all columns