Debugging Basics

No matter how adept you are at writing VBA code, sooner or later you encounter an error when running your macro. Not all errors are the result of bad code; you may, for example, encounter errors because the procedure may anticipate a different data type. Whatever the cause of the error, you need to determine the source of the error and how to resolve the issue. The process of finding errors is referred to as debugging.

As you work with the Visual Basic Editor, you can encounter design, compile, runtime, and logical errors. Although you normally fix design-time errors immediately upon the creation of the procedure, compile and runtime errors do not show up until the procedure executes, and may require some debugging to locate. The most difficult errors for you to locate typically consist of logical error, because you only know that you did not receive the anticipated results. Because logical errors do not produce an error message, you have to rely on debugging options to trace through your code and determine the error location.


Predominantly created when you write your code, most design errors consist of syntax errors that occur when you mistype a statement. You create design errors when you omit an argument for a function, or forget to use parentheses. The code MsgBox ( "Sample Text", for example, produces a syntax error due to the omission of the closing parenthesis.

As long as you use the Code Settings of the Visual Basic Editor, you immediately know when a syntax error occurs in your code. The Options dialog box includes an Auto Syntax Check option that instructs the Visual Basic Editor to check the syntax of each line of code that you type. If the Visual Basic Editor encounters an error, a Syntax Error message box displays immediately, highlighting the error and indicating what you need to correct it. You should always have this option selected. For more concerning the Visual Basic Editor, see Chapter 2.


If you have done any other programming, you know that compiling is the process of converting or translating your VBA code into a format that your computer can understand. With other programming languages, you compile a program before you run it. With VBA and Excel, the compile occurs automatically each time you run a procedure. This is true whether you run the procedure within the Visual Basic Editor or you select the corresponding macro in Excel. Because this process occurs so quickly, you are typically unaware that a compile even occurs.

If any errors occur during the compile process, an error message box pops up, and the Visual Basic Editor highlights the location of the error.

0 0

Post a comment