Macro Storage

The Store macro in option on the Macro dialog box instructs Excel where to store the macros you record. Excel provides three different storage locations for your macros: the current workbook, a new workbook, or the Personal Macro Workbook.

You can store a recorded macro to your current workbook, commonly referred to as the active workbook, by selecting the This Workbook option. Use this option if you plan to share the workbook with other users. Storing the macros in the active workbook makes macros available to any user who opens the workbook.

You can record a macro to a New Workbook. Excel creates the workbook automatically and adds the new macro to it. If you store a macro in another workbook, you need to open that workbook whenever you want to use that macro. You store macros in separate workbooks when you want to store specific types of macros in different workbooks. For example, you may that repeats a process until meeting a specific condition or displays a custom dialog box. More complex Excel macros require the use of VBA.

The Macro Recorder does work well in conjunction with the Visual Basic Editor. For example, if you want to create a macro that sums each column of data in your worksheet, you record the macro that sums a column. You then edit the macro in the Visual Basic Editor to run the macro until Excel process all columns. Combining the use of the Macro Recoder and the Visual Basic Editor simplifies the macro creation by allowing Excel to code part of it for you. See the section "Record a Macro" for more information on recording a macro in Excel.

want to place all macros that perform budget calculations in one workbook.

You can record a global macro by selecting Personal Macro Workbook, which serves as a common storage location for macros that you expect to use with other workbooks. Excel stores your Personal Macro Workbook as Personal.xls in the XlStart folder. This workbook does not exist until you store a macro in it. After you create the workbook, it loads whenever you run Excel as a hidden workbook. Excel hides the Personal Macro workbook so that you are unaware of it being open.

The storage location you select for a macro depends on where you want access to the macro. If you create the macro with the Macro Recorder, you can select the storage location of the macro when you create it on the Record Macro dialog box.

See the section "Record a Macro" for more information about storing macros.

0 0

Post a comment