Financial Analysis and Modeling Using Excel and VBA

Chapter 24. Debugging VBA Codes

Codes rarely run flawlessly the first few times : they may not run at all, they may stop in the middle, or they may run through but produce wrong or suspicious results. Errors in code are called bugs, and the process of finding and fixing them is called debugging. Like every modeler, you will have to spend time tracking down and fixing the bugs that invariably slip into almost all models. The good news is that most bugs are relatively easy to find and fix (unless you are working on a huge model with complex logic).

VBA provides several special tools to help with debugging, and learning at least some of them will help. But how fast you can debug a code will mostly depend on your experience, ingenuity, and understanding of the finance and mathematics of the problem. For beginners, debugging often becomes the most frustrating part of developing models. Rest assured that your skill at debugging will improve rapidly with experience.

It has been my experience that you can debug a code much faster by using a few tools systematically instead of trying to use all the different tools VBA offers in a haphazard manner. The few selected tools I discuss here are the ones you are likely to find most useful. I will not discuss VBA's Watch, or Locals Windows, which can be used for debugging, because I do not find them useful. I also do not discuss error handling, which involves including code in your models that notify the user when certain types of errors occur and ...