Sharing Macros with Others

Frank has several workbooks that use macros. The macros are saved to his Personal workbook. Frank would like to send a few of the macros to other people to use. Whenever he sends a copy of the workbooks, the macros don't go with the workbook. Frank wonders about the best way to share the macros with others.

There are actually several ways that you can share macros with others. First, though, I should explain why macros stored in your Personal workbook aren't shared with others when you share workbooks. The answer is simple—macros are stored in workbooks. They can either be stored in your Personal workbook (as Frank mentioned), or they can be stored in a macro-enabled workbook. When you share a workbook with someone else, if that workbook is macro-enabled and if it contains macros, then those macros travel with the workbook and are shared with the other person.

If, however, your macros are in your Personal workbook, then they remain with that workbook—the Personal workbook. They are not stored in any other workbook, even though you can use the macros on any workbook on your system. (This is the benefit of storing macros in the Personal workbook—they are available whenever you are using Excel.) If you don't send your Personal workbook to the other person, they won't have access to those macros because you didn't send the workbook where those macros are stored. (As a sidenote, it is not generally a good idea to send your Personal workbook to another person, as that might overwrite the Personal workbook on their system.)

In order to share macros with others, you always need to work in the Visual Basic Editor. This is where you can access the actual macro commands, which are written in a language called Visual Basic for Applications, or VBA.

To get to the Visual Basic Editor, simply press Alt+F11. You'll then see the VB Editor displayed on the screen. (See Figure 1.)

Figure 1. The Visual Basic Editor.

It would be overkill to go into a full discussion of how to use the VB Editor at this point, but if you are curious, you'll want to get a good Visual Basic tutorial book or take a good online course. (I offer both; let me know if you need info.)

For the purposes of this tip, you need to understand that at the left side of the VB Editor is a navigation pane called the Project Explorer. This pane is hierarchical in nature, with the top level in the pane being individual projects (workbooks) that are currently open on your system. Notice in the figure above that at the left you can see a project called PERSONAL.XLSB. This is the Personal workbook on my system. Fred will have such a project on his system, as well, since he indicated that he has his macros stored in his Personal workbook.

If you double-click on PERSONAL.XLSB, it expands within the Project Explorer to show the objects it contains. One of those is a folder called Modules. Click the plus sign to the left of this folder, and you can see the contents contained within the folder. (See Figure 2.)

Figure 2. Contents of the Modules folder.

As you might expect, the Modules folder contains modules. (Makes sense, right?) A module is nothing but a container for macros. It is a way to organize your macros so they can be more easily managed. It is possible for there to be multiple modules within the Modules folder. If you double-click on a module, it is opened up in the VB Editor so that you can, if desired, change the contents. (See Figure 3.)

Figure 3. The contents of an individual module.

The contents, as shown in this example, are quite simple. However, an individual module can contain dozens or even hundreds of macros. It is at this point—when you get down to the actual macro level within a module—that you can start to share your macros with others.

Perhaps the simplest way to share macros with others is by using editing techniques you are already familiar with. You can select text in the VB Editor (the text that makes up your macro) and copy that text. To select the text that makes up the macro, select everything from the beginning of the procedure (the line that begins with the Sub keyword or the Function keyword) through the end of the procedure (the line that begins with End Sub or End Function). Once selected, you can then press Ctrl+C to copy the selected lines to the Clipboard.

At this point, you could paste the macro somewhere else—such as a text file, an e-mail message, or a document—and then send it to somebody. That person would then need to copy the lines and paste them into a module using the VB Editor. They could then use the macro on their system.

If you want to share all of the macros contained in a module with someone else, the easiest way to do it is to export the module. Assuming you can see the module in the navigation pane at the left of the VB Editor, follow these steps:

1. Right-click on the module you want to export. The VB Editor displays a Context menu.
2. Choose Export File from the Context menu. The Export File dialog box is displayed.
3. Use the controls in the dialog box to specify a name for your file and the location where it should be stored. (I always find the Desktop to be a good location to start with.)
4. Click the Save button.
5. Close the Visual Basic Editor and get out of Excel.

Now, within Windows, navigate to the location you specified in step 3. You should see the file you just exported, and it will have a BAS filename extension. You can now share this file with another person just as you would share any other file. For instance, you could e-mail it, stick it on a USB drive, or store it on a network drive. The other person, then, would then load this exported module into their system by basically following the same steps listed above, but choosing Import File from the Context menu displayed in step 1.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13915) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.