Three Types of VBA Errors

By scott, on July 31st, 2007

Generally, there are three types of errors that you will encounter when you are writing code: syntax errors, logic errors, and run-time errors. Each type of error requires a different means of troubleshooting. Following is a brief description of each type of error.

Syntax Errors

Let’s start with the easiest type of program error to deal with, the syntax error. A syntax error occurs when your code is written improperly. An example would be a misspelled or missing variable or keyword:

Dim strName as

Obviously, an essential piece of code is missing in the statement: the data type. A correct statement would read:

Dim strName as String

The key to eliminating syntax errors is to use the Option Explicit statement in every module. Option Explicit will require that all variables are explicitly declared in your project. Explicitly declaring your variables is important for several reasons. Simple misspellings in a variable name can cause unwanted behavior and data loss. When working with databases, corrupt data can be entered without any error ever being generated.

Note:

VBA treats a misspelled variable as just another variable. For example,
lPayment? = 7,224.45

Me.txtBalance.Value = lPaymint

The variable lPayment on the second line is misspelled. VBA won’t recognize the misspelling if Option Explicit isn’t turned on, and it will assume that you want it to create a new variable (variant by default). As a result the text box will be updated with an Empty value instead of the desired value of $7,224.45.

Another important reason to use explicit variable declaration is to optimize your project. By default, undeclared variables use the variant data type. This data type may use significantly more memory than you intend for the variable’s purpose. For example, you may want to use a byte variable in your code. Bytes occupy 1 byte of memory space. If you didn’t explicitly declare the variable, it would be initialized as a variant even though it would only hold 1 byte of data. This means that you’d be using up 16 times the memory you actually need.

Remember, you can also use the VBE to force variable declaration. Simply select Tools | Options and go to the Editor tab. Once you’re there you can turn Require Variable Declaration on. This will insert Option Explicit at the top of each new module; however, it will not update your existing modules. Go back into your project and insert Option Explicit in any modules you have previously created.

Note:

VBA doesn’t allow the same degree of customization as VB as far as compiler settings are concerned. However, there are still some optimizations available. In the VBE, select Tools | Options and go to the General tab. Here you will find a Compile On Demand option. If this is checked, your project should run faster because modules will not be compiled until they are loaded for execution.

Also on the General tab is a Background Compile option. This option allows the computer to compile your project in the background while the computer is idle. Selecting the Background Compile option can slightly improve the run-time speed of your project (Compile On Demand must also be enabled to use this feature).

Caution:

As a final measure of caution, you should always compile your project before it is distributed. This will ensure that all syntax errors have been found. A frequent problem is that your code will refer to a control on a form that has been deleted. If you don’t compile the program ahead of time, you’ll be running to desktops and moving your program’s execution point to the next line of code, or worse, you’ll be the author of the dreaded “AllUser” e-mail informing people there is a problem and instructing them to stay out of the template, document, spreadsheet, or whatever. The process is very simple; just go to the Debug menu and choose Compile Project.

Logic Errors

Logic errors occur when your code doesn’t respond the way you intended. Perhaps your logic is unsound, or the program may flow in a different manner than you intended. The best way to check these errors is to manually step through your code using Watch windows. This can be especially tricky when using a series of UserForms—make sure you understand the call stack.

Tip:

If you are using a series of UserForms, do not place any code after you call the Show method of the next UserForm. Your program’s execution will always resolve back through the call stack and could produce unexpected results.

Run-Time Errors

You cannot avoid run-time errors. However, you can plan for them. Planning for run-time errors involves developing an error handler. The Developer’s Edition of Office 2000 includes a VBA Error Handler add-in that makes working with your error handler easier. Although, we’ll briefly discuss this add-in toward the end of the chapter, we’ll assume you are working in the normal Office environment and discuss how you can implement error handling without this add-in.

Share on Facebook