Programming Excel with VBA and .NET

Write Bug-Free Code

I encourage a guided, trial-and-error approach to learning how to program. This is mainly because I don’t think anyone can remember all the facts and information you need to know without having some way to apply that information in a practical way. Also, I think most of us are impatient by nature and want to get started as soon as possible.

However, I don’t want you to confuse this approach with disorganization or sloppiness. Either of those bad habits will make your programming experience difficult and frustrating. The following list is a collection of good habits that will pay off as you learn and develop your career:

Figure 1-33. Your digital signature now appears in the macro security warning

Declare all your variables

Adding Option Explicit to the top of each class or module helps make sure you don’t accidentally misspell a variable name and cause a subtle error that can be hard to locate.

Type carefully

Many names in Excel, such as worksheet names or named ranges, can’t be checked through Option Explicit and misspelling one of those in code can lead to similarly hard-to-locate errors.

Use short, descriptive names

There are different conventions for naming variables and procedures but the crux of all of them is to be short and descriptive. Be careful not to be too descriptive though. I try to keep variable names down to a few characters and I tend to use whole words when naming procedures.

Avoid ActiveSheet and Selection

I know Excel records code this way, but it is much better to get a worksheet or range by name if possible. Relying on which worksheet or range is selected makes it harder to debug and reuse your code. The exception to this guideline is when you really want to act on the ActiveSheet or Selection, such as when you are creating general tools that work on any worksheet or range.

Try to think clearly

For complicated tasks, it can help to write out what you want to do on a pad of paper, then try to do those steps in Excel with macro recording on. Often it helps to break a task up into several different steps and make those steps procedures that you can call from one central procedure.

Rely on friends

There are a lot of programmers in the Excel community and they communicate through a number of very active newsgroups. Those are great places to look for answers and to find samples.

Copy others

I don’t mean you should plagiarize copyright-protected work, but it’s OK to copy most code snippets, and it’s good practice to follow the coding style of others if you find it elegant.

Share with others

This is the other side of relying on friends and copying others. Don’t be afraid of feedback, either.

Take a break

The best programmers I know lead balanced lives. You’ll be surprised how many problems seem to solve themselves once you relax.