Chapter 22
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 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:
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.
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:
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.