Top 10 Excel VBA Bug Reduction Tips - dummies

By John Walkenbach

There’s no way to completely eliminate bugs in your Excel VBA code, but you can certainly try. Using the following ten tips will help you keep those pesky bugs to a minimum.

Use an Option Explicit at the beginning of your module

When you follow this guideline, you have to define the data type for every variable that you use. It’s a bit more work, but you avoid the common error of misspelling a variable name. There’s a nice side benefit, too: Your routines will often run faster if you declare your variables.

Format your code with indentation

Using indentation to delineate code segments is quite helpful. If you have several nested For…Next loops, for example, consistent indentation makes it much easier for you to keep track of them all.

Be careful with On Error Resume Next

This statement causes Excel to ignore any errors and continue. In some cases, using this statement causes Excel to ignore errors that shouldn’t be ignored. Your code could have bugs, and you wouldn’t even realize it.

Use lots of comments

Nothing is more frustrating than revisiting code that you wrote six months ago — and not having a clue as to how it works. Adding a few comments to describe your logic can save you lots of time down the road.

Keep your subroutines and functions simple

When you write your code in smaller modules — each of which has a single, well-defined purpose — debugging them is much easier than when you write one long module and have to debug it.

Ask someone else to test your code

Often, you are so close to the code that you’ve written that you overlook obvious flaws. Ask someone else to try out your code and stress test it by trying to make it fail.

Use the Help system

If something doesn’t seem to be working as it should, check the VBA Help system. It’s possible that a property or method doesn’t work the way you think it does. To access the VBA Help system, highlight a keyword in your code and press F1.

Use the internet to find answers

There are several good forums on the web that are devoted to VBA programming. Those who frequent these forums take great pride in answering questions quickly and thoroughly. Don’t overlook this helpful (and free) resource.

Understand Excel’s debugging tools

Although it can be a bit daunting at first, you’ll find that Excel’s debugger is an excellent tool. Invest some time and get to know it. Spending about an hour learning the details may save you dozens of hours in wasted time.

Consider a different approach

If you’re having trouble getting a particular routine to work correctly, you may want to scrap the idea and try something completely different. In most cases, Excel offers several alternative methods of accomplishing the same thing.