Developer FAQ - Visual Basic Editor
Note: This document was written for Excel 97 - 2000.
In Excel 95, my VBA modules were located in my workbook. I can't see them when I open the file using Excel 97 or Excel 2000.
The modules are still there, but you view and edit them in the Visual Basic Editor. Press Alt+F11 to toggle between the VBE and Excel.
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 variable, 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 or to discover the relevant properties or methods.
Excel 95 had a "record at mark" feature that let you record a macro beginning at a particular location within an existing macro. Is that feature still available?
No, it was removed beginning with Excel 97. To add new recorded code to an existing macro, you need to record it and then cut and paste the code to your existing macro.
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 \XLStart directory.
I can't find my Personal Macro Workbook. Where is it?
The Personal.xls file doesn't exist until you record a macro to it.
Every time my macro copies a worksheet, the new sheet name appears in the Project window of VBEas something like Sheet11111111111(Sheet 1(9)). What's the deal with this?
These strange names are the "code names" for Sheet objects, and they can get very unweildy if you do a lot of sheet copying. You can change the code name using the Properties window in the VBE.
I locked my project with a password, and forget what it was. Is there any way to unlock it?
Although Excel 97 and Excel 2000 workbooks are more secure than previous versions, several third-party password cracking products exist. Use a Web search engine and search for Excel password.
How can I write a macro to change the password of my project?
You can't. The protection elements of a VBA project are not exposed in the object model. Most likely, this was done to make it more difficult for password cracking software.
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, activate the VBE and select the Tools - Options command, click the Editor tab, and uncheck the Require Variable Declaration check box. Then you can either declare your 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 (as well as the font used) by using the Tools - Options command (Editor Format tab) in the VBE.
I want to delete a VBA module by using VBA code. Can I do this?
Yes. The code below deletes Module1 from the active workbook.
With ActiveWorkbook.VBProject .VBComponents.Remove .VBComponents("Module1") End With
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 that you insert a space before and after the concatenation operator.
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.
In Excel 95, I set my VBA module to be "very hidden" to prevent users from seeing it. When the workbook is opened in Excel 97, the module can be viewed in the VBE. Is this right?
I don't know if it's right, but that's the way it is. Excel 97 and later does not support the xlVeryHidden property for modules.
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 doesn't always do a good job of cleaning up after itself. This sometimes causes some subtle problems with variables that you no longer use. One way to fix it is to export your module to a file, delete the module, and then import it again.
My workbook contains a VBA subroutine named Test. I tried to use Excel's Name box to create a range named Test, and Excel dumped me to the VB Editor, with the cursor on my Test subroutine. What's this all about?
I can only guess that it's a bug of some sort. Apparently, Excel thinks the name is already defined, possibly as an XLM macro. If you use the Insert - Name - Define command to define your range name, the problem won't occur.
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 (General tab) in the VBE. Unfortunately, there is no way to change this setting using VBA. To avoid this problem, you can distribute your application as an XLA add-in.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks