The Visual Basic Editor

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

No. Recording is useful for very simple macros only. Macros that use variables , looping, or any other type of program flow changes cannot be recorded. You can, however, often take advantage of the macro recorder to write some parts of your code or to discover the relevant properties or methods .

I turned on the macro recorder when I edited a chart, but many of the commands weren't recorded.

Yes, that's a problem with the initial release of Excel 2007. Perhaps it will be fixed in a later update.

I have some macros that are general 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.xlsb , is stored in your \XLStart directory.

I can't find my Personal Macro Workbook. Where is it?

The Personal.xlsb file doesn't exist until you record a macro to it.

I locked my VBA project with a password, and I forget what it was. Is there any way to unlock it?

Several third-party password-cracking products exist. Use a Web search engine to search for Excel password. The existence of these products should tell you that Excel passwords are not very secure.

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 Option Explicit is included at the top of a module, it means that you must declare every variable before you use it in a procedure (which is a good idea). If you don't want this line to appear in new modules, activate the VBE, choose Tools Options, click the Editor tab, and clear 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 and the font used by choosing the Tools Options command (Editor Format tab) in the VBE.

Can I delete a VBA module by using VBA code?

Yes. The following code deletes Module1 from the active workbook:

 With ActiveWorkbook.VBProject     .VBComponents.Remove .VBComponents("Module1") End With 

This might not work with Excel 2002 and later. See the next question.

I wrote a macro in Excel 2000 that adds VBA code to the VB project. When I run it in Excel 2007, I get an error message. What's wrong?

Excel 2002 introduced a new setting: Trust Access to Visual Basic Project. By default, this setting is turned off. To change it in Excel 2007, choose Office Excel Options Trust Center. Click the Trust Center Settings button to display the Trust Center dialog box. Click the Macro Settings tab and place a check mark next to Trust Access to the VBA Project Object Model.

How can I write a macro to change the user 's macro security setting? I want to avoid the "this workbook contains macros" message when my application is opened.

The ability to change the security level using VBA would pretty much render the entire macro security system worthless. Think about it.

How does the UserInterfaceOnly option work when protecting a worksheet?

When protecting a worksheet using VBA code, you can use a statement such as

 ActiveSheet.Protect UserInterfaceOnly:=True 

This causes the sheet to be protected, but your macros can still make changes to the sheet. It's important to understand that this setting is not saved with the workbook. When the workbook is re-opened, you'll need to re-execute the statement in order to reapply the UserInterfaceOnly protection.

How can I tell whether a workbook has a macro virus?

In the VBE, activate the project that corresponds to the workbook. Examine all the code modules (including the ThisWorkbook code module) and look for VBA code that is not familiar to you. Usually, virus code will not be formatted well and will contain many unusual variable names . Another option is to use a commercial virus-scanning program.

I'm having trouble with the concatenation operator (&) in VBA. When I try to concatenate two strings, I get an error message.

VBA is probably 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.

I distributed an Excel 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, you can't change this setting with VBA.