VBA Modules
Can I use the VBA macro recorder to record all of my macros?
No. Recording is useful only for very simple macros. Macro that use looping or any other type of program flow changes cannot be recorded. However, you can often take advantage of the macro recorder to write some parts of your code. This is usually much easier than entering the code manually.
Can you record everything that you do in Excel to a VBA macro?
No. Although most of your actions will generate some VBA code, a few commands are not recorded. For example, actions performed in the Module General or Module Format tabs of the Options dialog box are not recorded.
Why is there no toolbar button that will start macro recording at the current cursor position in a VBA module?
Good question. It sure would be handy. When Excel doesn't provide the tool you need, it's often possible to create your own. Here's a simple VBA subroutine that will start the macro recorder and put the code at the current position:
Sub MarkAndRecord() If TypeName(ActiveSheet) <> "Module" Then Exit Sub SendKeys "%TRM" SendKeys "%TRE" End Sub
I have some macros that are general purpose in nature. I would like to have these available all the time. What's the best way to do this?
Consider storing those general purpose macros in your Personal Macro Workbook. This is a (normally) hidden workbook that is loaded automatically by Excel. When you record a macro, you have the option of recording it to your Personal Macro Workbook. The file, PERSONAL.XLS, is stored in your EXCEL/XLSTART directory. Another option is to convert this file to an add-in, and then specify that the add-in is loaded whenever Excel starts (use the Tools Add-Ins command for this).
Can I copy a VBA macro from one workbook to another?
Certainly. Select the text and use the normal Edit menu commands to copy and paste using the Windows Clipboard. To copy a complete module to a different workbook, just press Ctrl and drag the module's tab to the other workbook.
I copied some VBA code that I recorded. When I pasted it to a different module, it contained an error. What gives?
I know of at least one instance where copying code will produce an error. If you record a macro while entering a forced line break in a cell (using Alt+Enter), the generated code contains what appears to be a small square (it's actually ASCII character 10). If you copy and paste this line of code, the carriage return is interpreted literally and the single line appears on two lines. To correct it, replace the small square with Chr(10).
Is there a way to view a VBA module and a worksheet at the same time? These are both in the same workbook.
Yes. Use the Window New Window command to create a new window into the current workbook. You can activate sheets in each window independently of the other. You can have as many windows for a single workbook as you like.
When I insert a new module, it always starts with an "Option Explicit" line. What does this mean?
If this line is included at the top of a module, it means that you must declare every variable before you use it (which is a good idea). If you don't want this line to appear in new modules, select the Tools Options command, click the Module General tab, and uncheck the Require Variable Declaration check box. Then you can either declare variables or let VBA handle the data typing automatically.
Why does my VBA code appear in different colors? Can I change these colors?
VBA uses color to differentiate various types of text - comments, keywords, identifiers, statements with a syntax error, and so on. You can adjust these colors using the Tools Options command (Module Format tab).
I want to delete a VBA module using VBA code. Can I do this?
Yes, you can use the Delete method for the Modules collection - as long as the code that deletes the module isn't contained in the module that you want to delete.
I can't seem to get the VBA line continuation character (underscore) to work.
The line continuation sequence is actually two characters: a space followed by an underscore.
I assigned a key combination to a VBA macro and then I changed the name of the macro. Now the key combination doesn't work.
When you change the name of a VBA subroutine that's executed by a key combination, you get an error message the first time you attempt to invoke the macro. After the first time, it works correctly. This was a known problem in Excel 5 and continues to be a problem in Excel 95.
When I make a change - even a minor change - to my VBA code, there is a delay before it runs the first time. Why is this?
Excel detects when you make a change to your VBA code, and recompiles it before you can execute it again. The time delay should only be apparent the first time you execute the new code.
Are there any VBA code-entry tricks I should be aware of?
One trick that I find particularly useful is the undocumented multilevel undo (or Alt+Backspace) and redo (or F4). Unlike the undo in a worksheet, you can use these commands repeatedly. For example, if you delete 10 lines using 10 separate commands, you can get each line back in turn by press Alt+Backspace 10 times.
Any other undocumented VBA module tricks?
One more. If your module contains many different procedures, you can use Ctrl+Up and Ctrl+Down to quickly jump to the next or previous procedure in the module.
Is it possible to hide a VBA sheet? When a VBA module is active, there is no Format menu.
Yes. In a VBA module the command to hide the current sheet is Edit Sheet Hide.
Is it possible to hide a VBA module so the end user cannot unhide it?
You can hide the module by setting its Visible property to xlVeryHidden. After doing so, it will not appear in the list of hidden sheets. The only way to unhide the module is via VBA: set the Visible property to True.
Why isn't the File Print Preview command available in a VBA module?
The Excel designers apparently thought this command was not necessary because you can't really do any formatting on a VBA module sheet. If you really must preview a VBA module, copy the code to a worksheet and use the worksheet's print preview feature.
I'm having trouble with the concatenation operator (&) in VBA. When I try to concatenate two strings, I get an error message.
This is probably because VBA is interpreting the ampersand as a type declaration character. Make sure you insert a space before and after the concatenation operator.
What's a type definition character?
VBA lets you append a character to a variable's name to indicate the data type. For example, you can declare the MyVar variable as an integer by tacking % onto the name:
Dim MyVar%
Here's a list of the type-declaration characters supported by VBA:
- Integer %
- Long &
- Single !
- Double #
- Currency @
- String $
I've noticed after deleting a major amount of VBA code that the XLS file size is not reduced accordingly. Why is this?
This is because Excel does not do a good job cleaning up its symbol table. This sometimes causes some subtle problems with variables that you no longer use. One way to fix it is to insert a new module and use the Clipboard to copy the VBA code to the blank module. Then, delete the original module. Save the workbook and you'll usually find that it's much smaller than it was.
I distributed an XLS application to many users. On some machines, my VBA error-handling procedures don't work. Why not?
The error-handling procedures won't work if the user has the Break on All Errors option set. This option is available in the Options dialog box under the Module General tab. Unfortunately, there is no easy way to change this setting using VBA. To avoid this problem, you can distribute your application as an XLA add-in.