O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

2.11. Debugging VBA Code

Problem

You want to know how to debug your code in VBA.

Solution

Use VBA's built-in debugger by accessing the Debug menu in the VBA IDE or working with the Debug toolbar (select View → Toolbars → Debug to show the Debug toolbar).

Discussion

When you are writing code, VBA will automatically check for syntax errors as soon as you press Enter following each code line. If VBA finds no syntax errors, your code will be accepted and it will be highlighted to denote comments and keywords. If VBA finds an error, it will immediately display an error message box with information on the error. It will also highlight your code statement and indicate which part is causing the error. You can then edit the statement in the code window until you get it right.

Alas, VBA cannot automatically detect logical errors in your code. That technology is a long way off for any computing language. In the meantime, we do have integrated debuggers to help us find and correct logical errors. Logical errors are the sort of error where your code, though syntactically correct, does not do what you intend. For example, you might have written an integration routine based on the Improved Euler method and it does not appear to work correctly; e.g., it's giving you garbage results. Such poor results could be due to a time step that's too small, an incorrect formula in your code, or a particularly nasty function that you're trying to integrate.

For tracking down and fixing logical errors, the debugger ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required