Welcome to Entomology 101. The term program bug, as you probably know, refers to a problem with software. In other words, if software doesn't perform as expected, it has a bug. Fact is, all major software has bugs — lots of bugs. It has been said that software that doesn't contain bugs is probably so trivial that it's not worth using. Excel itself has hundreds (if not thousands) of bugs. Fortunately, the vast majority of these bugs are relatively obscure and appear in only very unusual circumstances.
When you write VBA programs, your code probably will have bugs. This is a fact of life and not necessarily a reflection of your programming ability. The bugs may fall into any of the following categories:
^ Logic flaws in your code. You can often avoid these bugs by carefully thinking through the problem your program addresses.
^ Incorrect context bugs. This type of bug surfaces when you attempt to do something at the wrong time. For example, you may try to write data to cells in the active sheet when the active sheet is not a worksheet.
1 Extreme-case bugs. These bugs rear their ugly heads when you encounter data you didn't anticipate, such as very large or very small numbers.
1 Wrong data type bugs. This type of bug occurs when you try to process data of the wrong type, such as attempting to take the square root of a text string.
1 Wrong version bugs. This type of bug involves incompatibilities between different Excel versions. For example, you may develop a workbook using Excel 2003 and then find out that the workbook doesn't work with Excel 95 or 97. You can usually avoid such problems by avoiding version-specific features. Often, the easiest approach is to develop your application using the lowest version number of Excel that users might have.
1 Beyond-your-control bugs. These are the most frustrating. An example occurs when Microsoft upgrades Excel and makes a minor, undocumented change that causes your macro to bomb.
Debugging is the process of identifying and correcting bugs in your program. Developing debugging skills takes time, so don't be discouraged if this process is difficult at first.
It's important to understand the distinction between bugs and syntax errors. A syntax error is a language error. For example, you might misspell a keyword, omit the Next statement in a For-Next loop, or have a mismatched parenthesis. Before you can even execute the procedure, you must correct these syntax errors. A program bug is much subtler. You can execute the routine, but it doesn't perform as expected.
Was this article helpful?