How Do I Import Macros for Excel?

By Ron Price

Excel provides several ways to share macros among workbooks: copy and paste, as a .BAS file, or in a Personal.xlsb file.

Excel macros are associated only with the workbook in which they are created. However, it isn't necessary to recreate a good macro in another workbook to apply the same actions. Macros can be copied, moved or made available to other workbooks.

Tip

When working with macros in Excel 2013, the Developer tab should be active on the Excel Ribbon.

Copy a Macro from One Workbook to Another

Perhaps the simplest way to "import" a macro from one workbook into another is using Copy and Paste.

Step 1

Select the Developer tab and click on the Macros button to display the Macro dialog box.

Step 2

Use the Edit button to display the VB editor and the macro selected.

On the Macro dialog box, select the macro you wish to display in the Visual Basic (VB) editor and click on the Edit button to display the macro's VBA code.

Tip

You can also press the "ALT" key and the "F11" key together (ALT+F11) to open the VB editor from the anywhere in an Excel workbook.

Step 3

Highlight and copy only the statements, omitting the name of the function and the end line.

In the VB editor, select only the programming code statements of the macro; don't include the macro's title or the last line. Click on Edit and select Copy from the drop-down menu to copy the highlighted code.

Step 4

Click on the Create button to display the VB editor.

Open the workbook to which the macro is to be added. On the Developer tab, click on the Macros button. Assign a name to the macro and then click on the Create button to display the VB editor. When the VB editor opens, place the cursor on the blank line between the two statements. Click on Edit and choose Paste from the drop-down menu to insert the copied code. Click on File and Save to save the workbook and macro.

Export and Import a Macro

This method creates a VB code file (BAS file) that can be exported so it can be imported into other Excel workbooks.

Step 1

Choose Export File to save the macro as a .BAS file.

Open the VB editor and display the macro you wish to export. Click on File and choose Export File.

Step 2

Save the BAS file

On the File Save dialog box, navigate to the folder in which the file is to be saved, name the file, and click Save. The file is now available to be imported into other workbooks.

Step 3

An imported .BAS file is added as a module to a VB project.

To import a BAS file and add it to a workbook project, select the Developer tab and click on the Visual Basic button to open the VB editor. On the VB editor, click File and then click Import File to display the Import File dialog box. Navigate to the appropriate folder and select the file to be imported. Click Open to import the file.

Creating the Personal Macro Workbook

When Excel starts up, it opens a Personal.xlsb file, if present, as a hidden workbook. This file contains macros available to any Excel workbook running on one computer. Creating a Personal.xlsb file provides a macro library of the macros available on that computer.

Step 1

On the Record Macro dialog box, a workbook can be stored as a Personal Macro Workbook

To create the Personal.xlsb file, open a New workbook in Excel. On the Developer tab, click on the Record Macro button to display the Record Macro dialog box.

Step 2

The Stop Button is displayed while a Record Macro function is active.

Assign a name to the macro, then select Personal Macro Workbook on the "Store macro as" option. Click OK to start the recording. Perform the actions to be included in the macro. When the actions are completed, click on Stop Recording on the Developer tab or click the Stop Recording button on the Status Bar in the lower left-hand corner of the workbook.

Step 3

Save the Personal.xlsb file

After completing the macro, click on the Visual Basic button on the Developer tab to open the VB editor. Select VBAPROJECT (PERSONAL.XLSB) in the Project pane. Click on File and then the Save PERSONAL.XLSB option.