Excel VBA Programming For Dummies, 3rd Edition

Chapter 22

Ten VBA Questions (and Answers)

In This Chapter

Storing worksheet function procedures

Limiting the macro recorder

Speeding up your VBA code

Declaring variables explicitly

Using the VBA line continuation character

The following ten questions (and answers) address some of the most common issues asked by VBA newcomers.

I created a custom VBA function. When I try to use it in a formula, the formula displays #NAME?. What’s wrong?

It’s possible that you spelled the function incorrectly. But it’s more likely that you put your function code in the wrong location. VBA code for worksheet functions must be in a standard VBA module, not in a module for a sheet or in ThisWorkbook. In the VBE, use Insert⇒Module to insert a standard module. Then cut and paste your code to the new VBA module.

This is a very common mistake, because a Sheet module looks exactly like a standard VBA module. Resist the temptation to put your code there. Spend four seconds and choose Insert⇒Module.