In This Chapter
Storing worksheet function procedures
Limitation of the macro recorder
Speeding up your VBA code
Declaring variables explicitly
Using the VBA line continuation character
In this chapter, I answer the questions most frequently asked about VBA.
The Top Ten Questions about VBA
I created a custom VBA function. When I try to use it in a formula, the formula displays #NAME?. What’s wrong?
You probably have 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 This Workbook.
Can I use the VBA macro recorder to record all of my macros?
No. Normally you use it only to record simple macros or as a starting point for a more complex macro. It cannot record macros that use variables, looping, or any other type of program flow constructs. In addition, you cannot record a Function procedure in the VBA macro recorder.
How can I prevent others from viewing my VBA code?
1. In the VBE, choose Tools VBA Project Properties.
2. In the dialog box, click the Protection tab and select Lock Project for Viewing.
3. Enter a password (twice) and click OK.
Doing so prevents casual users from viewing your code, but it is certainly not 100 percent secure. Password-cracking utilities exist.
What’s the VBA code for increasing the number of rows and columns in a worksheet?
No such code exists. The number of rows and columns is fixed and cannot be changed. No way.
When I refer to a worksheet in my VBA code, I get a “subscript out of range” error. I’m not using any subscripts. What gives?
This error occurs if you attempt to access an element in a collection that doesn’t exist. For example, this statement generates the error if the active workbook doesn’t contain a sheet named MySheet:
Is there a VBA command that selects a range from the active cell to the last entry in a column or a row? (In other words, how can a macro accomplish the same thing as Ctrl+Shift+i or Ctrl+Shift+ —?)
Here’s the VBA equivalent for Ctrl+Shift+i:
For the other directions, use the constants xlToLeft, xlToRight, or xlUp instead of xlDown.
How can I make my VBA code run as fast as possible?
Here are a few tips:
Make sure to declare all your variables as a specific data type. (Use Option Explicit in each module’s Declarations section to force yourself to declare all variables.)
If you reference an object (such as a range) more than once, create an object variable using the Set keyword.
Use the With-End With construct whenever possible.
If your macro writes data to a worksheet and you have lots of complex formulas, set the calculation mode to Manual while the macro runs.
If your macro writes information to a worksheet, turn off screen updating by using Application.ScreenUpdating = False.
How can I display multiline messages in a message box?
The easiest way is to build your message in a string variable, using the vbNewLine constant to indicate where you want your line breaks to occur. The following is a quick example:
I’ve deleted all my macros, but Excel still asks me to enable macros when I open the workbook.
Empty modules in your workbook will cause this message. If you have empty module, remove them by right clicking and choosing Remove Module. Also, check the This Workbook and Sheet modules to make sure these modules don’t contain any macros.
Why can’t I get the VBA line-continuation character (underscore) to work?
The line continuation sequence is actually two characters: a space followed by an underscore. Make sure to use both characters and press Enter after the underscore.
