Chapter 28: Debugging Macros and Custom Functions

Overview

If you made it through the previous two chapters, you now have at least a smattering of Microsoft Visual Basic for Applications (VBA) at your command-as well as, we hope, an appetite for learning more. The best ways to acquire more expertise in this versatile programming language are to read a book on the subject, such as Microsoft Visual Basic 2005 Step by Step by Michael Halvorson (Microsoft Press, 2005), and to experiment. As you do your everyday work in Microsoft Office Excel 2007, look for chores that are ripe for automating. When you come across something macro-worthy, record your actions. Then inspect the code generated by the macro recorder. Make sure you understand what the recorder has given you (read the Help text for any statements you don't understand), and see whether you can find ways to make the code more efficient. Eliminate statements that appear unnecessary, and then see whether the code still does what you expect it to do. Look for statements that select ranges or other objects, and see whether you can make your code perform the essential tasks without first selecting those objects.

As you experiment and create larger, more complex macros and functions, you will undoubtedly produce some code that either doesn't run at all or doesn't give you the result you want. Missteps of this kind are an inevitable aspect of programming. Fortunately, the VBA language and the Visual Basic Editor provide tools to help you trap errors and root out bugs. Those tools are the subject of this chapter.

In this chapter, you'll look at two kinds of error-catching tools: those that help you at design time, when you're creating or editing code, and those that work at run time, while the code is running.