Ten VBA Questions (and Answers) - The Part of Tens - Excel VBA Programming For Dummies, 4th Edition (2015)

Excel VBA Programming For Dummies, 4th Edition (2015)

Part VI. The Part of Tens

image

webextra Check out an additional Part of Tens chapter online at www.dummies.com/extras/vbaprogramming.

In this part …

image Discover the answers to common VBA questions.

image Expand your knowledge with Excel resources.

image Find out what you should and shouldn’t do in Excel VBA.

Chapter 22. Ten VBA Questions (and Answers)

In This Chapter

arrow Storing worksheet function procedures

arrow Limiting the macro recorder

arrow Speeding up your VBA code

arrow Declaring variables explicitly

arrow Using the VBA line continuation character

The following ten questions are commonly 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, choose 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 to 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?

Follow these steps:

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

The xxxxx corresponds to the name of your VBA project. If you didn’t give it a new name, your VBA project is named VBAProject.

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.

This procedure 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 may not be open (so it's not in the Workbooks collection). Or maybe you misspelled the workbook or worksheet 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 of Ctrl+Shift+↓:

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

For the other directions, use the constants xlToLeft, xlToRight, and 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 by 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. Don’t forget to return to Automatic calculation when the macro finishes.

· If your macro writes lots of 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:

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 to press Enter after the underscore.