Storing Macros in the Personal Macro Workbook

When you record a macro, you can specify storing the macro code in the Personal Macro Workbook. When you store a macro in the Personal Macro Workbook, Excel creates a file named Personal.xls and places it in the XLStart folder, which is a subdirectory of Program Files\Microsoft Office\Office. Files that were saved to the XLStart folder are loaded automatically each time you start Excel. The Personal Macro Workbook is a convenient place to store general-purpose macros like the following one.

You will now record a general-purpose macro called FormulasOnOff. The purpose of this macro is to toggle the display of worksheet formulas on and off.

1. Choose Tools | Macro | Record New Macro.

2. In the Record Macro dialog box, enter FormulasOnOff in the Macro name box.

3. Choose Personal Macro Workbook from the Store macro in dropdown list.

4. Click in the Shortcut key text box, and press Shift+F.

5. Choose OK to exit the Record Macro dialog box.

6. Press Ctrl+~ (tilde character) to turn on the display of formulas, or choose Tools | Options and click the Formulas check box on the View tab in the Windows options area. When you turn on the display of formulas, the worksheet cells show the formulas instead of the values that the formulas produce. If you are recording this macro on a blank sheet, the only thing you'll notice is a change in the width of the worksheet columns.

7. Click the Stop Recording button on the Stop Recording toolbar, or choose Tools | Macro | Stop Recording.

8. To see the macro code, press Alt+F11 or choose Tools | Macro | Visual Basic Editor.

In the Visual Basic Editor screen, the Project Explorer window now shows an additional VBA project (Personal.xls). To open the project, click the plus sign (+) to the left of the project name. The VBA project contains two folders: Microsoft Excel Objects and Modules. Click the plus sign next to the Modules folder to open it and then double-click Modulel. The Code window shows the contents of the FormulasOnOff macro (Figure 1-15). Each Excel workbook contains a single project. The first time you record a macro, Excel creates a module folder and places your macro code in Modulel. If you record another macro in the same workbook, Excel places it below the previously recorded macro in the same Modulel sheet. All macros recorded in the same work session are stored in the same module. If you close Excel, reopen the same workbook and record a new macro, Excel will store it in a new module.

Micfosoll Visual Basic - PERSONAL.XLS -

Modulel (CodeJJ

BE 131

«^f file Edit View jriseit Foimal


Run Joois Add-lns Wridow Help

. a x

H • H ii «

M . M :■ M i1 W i* (3



frl ii it iwiw ffS.i

» % ]

Protect - VBAPioiecl

I j(G0n*r4ll _vj jFornHilasOnOff


□ m jo]

1 ForrnulasOnOff Macro 1 Macro recorded 5ß1/2CC2 by Julitta Koro!

d Si VBBProject (ChapOl.Kls)

B ¿5 Microsoft Excel Objects ES] shkceti (Sheeci) iffl] Sheet2 (Stiftst2)

SQ St»at3 (Sieeö) i 0 ThisWorliorf;

'Keyboard Shortcut: Ctrl+Shift+F

□ ^ Modules : «J Modulel B 1« VBAProiect (PERSONAL.XLS)

-: —* Microsoft Exoel Objects Sheet! (Sheet 1) Q TheWorkbock

Active Window. DisplayFormules = True End Sub


EI -Ö Modules i (itModiisl:


¡Module 1 Module


Alphabetic | Categorised ]

ffffffBI Moduls 1



Figure 1-15: In the Project Explorer window you can select the project you want to work with.

When you recorded your macro, you turned on the display of formulas. The macro name suggests that the macro can toggle the formulas on and off. To make this macro behave exactly this way, you must edit it.

The recorded macro line sets the display of formulas in the active window to True:

ActiveWindow.DisplayFormulas = True

The False setting will turn off the display of formulas:

ActiveWindow.DisplayFormulas = False

To make a toggle in VBA, you need to connect both statements in the following way:

ActiveWindow.DisplayFormulas = Not ActiveWindow.DisplayFormulas

Replace the recorded macro line with the statement above and run the macro. No matter how many times you play this macro, it always knows what to do. You can use the same idea to create macros that toggle the display of gridlines or other Microsoft Excel features on and off.

When you close Microsoft Excel, you will be prompted to save the changes you made to the Personal Macro Workbook. Click OK to save the changes. When you restart Excel, the Personal Macro Workbook will automatically load in the background.

If you want to store other macros in the Personal Macro Workbook, you can take one of the following routes:

■ Record a new macro and choose the Personal Macro Workbook for its storage location.

■ Switch to the Visual Basic Editor and open the project that contains the macro you want to move to the Personal Macro Workbook. Cut the macro code from the current location and open the Personal Macro Workbook project. Paste the macro code into the existing module, or create a new module prior to pasting.

■ Choose File | Import File... to bring the macro code from a text file or another Visual Basic project file (*.frm, *.bas, *.cls).

0 0

Post a comment