Bug Reduction Tips for Excel 2016 VBA - dummies

By John Walkenbach

There’s no way to tell you how to completely eliminate bugs in your Excel 2016 VBA programs. Finding bugs in software can be a profession by itself, but you can heed a few tips to help you keep those bugs to a minimum:

  • Use an Option Explicit statement at the beginning of your modules. This statement requires you to define the data type for every variable you use. This creates a bit more work for you, but you avoid the common error of misspelling a variable name. And it has a nice side benefit: Your routines run a bit faster.

  • Format your code with indentation. Using indentations helps delineate different code segments. If your program has several nested For-Next loops, for example, consistent indentation helps you keep track of them all.

  • Be careful with the On Error Resume Next statement. This statement causes Excel to ignore any errors and continue executing the routine. In some cases, using this statement causes Excel to ignore errors that it shouldn’t ignore. Your code may have bugs, and you may not even realize it.

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

  • Keep your Sub and Function procedures simple. By writing your code in small modules, each of which has a single, well-defined purpose, you simplify the debugging process.

  • Use the macro recorder to help identify properties and methods. When you can’t remember the name or the syntax of a property or method, it’s a good idea to record a macro and look at the recorded code.

  • Understand Excel’s debugger. Although it can be a bit daunting at first, the Excel debugger is a useful tool. Invest some time and get to know it.

Debugging code is not a fun activity for most (it ranks right up there with getting audited by the IRS), but it’s a necessary evil that goes along with programming. As you gain more experience with VBA, you spend less time debugging and, when you have to debug, you are more efficient at doing so.