I can't tell you how to completely eliminate bugs in your programs, but I can provide a few tips to help you keep those bugs to a minimum:
1 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.
i 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.
i Be careful with the On Error Resume Next statement. As I discuss in Chapter 12, 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.
i 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.
1 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.
1 Use the macro recorder to help identify properties and methods. When I can't remember the name or the syntax of a property or method, I often simply record a macro and look at the recorded code.
1 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 one of my favorite activities (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 become more efficient at doing so.
Was this article helpful?