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.

Can I use the VBA macro recorder to record all my macros?

Only if your macros are very simple. Normally, you use it only to record simple macros or as a starting point for a more complex macro. The macro recorder can’t 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.

Many people eventually realize that the macro recorder is most useful as a way to identify properties and methods that are relevant to their task.

How can I prevent others from viewing my VBA code?

1. Activate your project in the VBE and choose Tools⇒xxxxx Properties.

The xxxxx corresponds to the name of your VBA project.

2. In the dialog box that appears, click the Protection tab and select Lock Project for Viewing.

3. Enter a password (twice) and click OK.

4. Save your workbook.

Doing so prevents casual users from viewing your code, but password protection is certainly not 100 percent secure. Password-cracking utilities exist.

What’s the VBA code for increasing or decreasing 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. However, if you open a workbook that has been created by using an earlier Excel version (before Excel 2007), the text Compatibility Mode appears in the title bar. This notice indicates that this workbook is limited to the old 256-x-65536 cell grid. You can get out of this mode (and thus get the new, bigger cell grid) by saving the file as a normal (XLSX or XLSM) workbook and then closing and reopening this new file.

You can, however, hide unwanted rows and columns. For example, you can hide all but the first 10 rows and 10 columns, giving you a 100-cell worksheet.

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?

Like many VBA error messages, this one isn’t very informative. 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 Fido:

Set X = ActiveWorkbook.Sheets("Fido")

In your case, the workbook that you think is open is probably not open (so it’s not in the Workbooks collection). Or, maybe you misspelled the workbook name.

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+ or Ctrl+Shift+→?)

Here’s the VBA equivalent for Ctrl+Shift+:

Range(ActiveCell, ActiveCell.End(xlDown)).Select

For the other directions, use the constants xlToLeft, xlToRight, or xlUp rather than xlDown.

How can I make my VBA code run as fast as possible?

Here are a few tips:

Be 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 (but make sure you do a calculation when you need to use the results!).

If your macro writes information to a worksheet, turn off screen updating by using Application.ScreenUpdating = False.

Don’t forget to reinstate these last two settings to their starting value when your macro is finished.

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:

Msg = "You selected the following:" & vbNewLine

Msg = Msg & UserAns

MsgBox Msg

I wrote some code that deletes worksheets. How can I avoid showing Excel’s warning prompt?

Insert this statement before the code that deletes the worksheets:

Application.DisplayAlerts = False

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. Be sure to use both characters and press Enter after the underscore.