Exporting Excel Macros

October 17, 2005 3:26 PM

How do I export an Excel Macro?

Is it possible to take a macro that I created in Excel and export it to a file so that I can email it to a friend for his use?

The more I dig, the more it looks like this might be a VB question...

posted by devilshgrin to computers & internet (3 comments total)

Macros can be saved either to the worksheet they modify or to your personal worksheet.

If you save it to the end worksheet, you'd obviously just need to email that worksheet over. But the macro would only work for that worksheet.

If the macro is on your personal worksheet, things get a little more involved. Unfortunately, I don't have Excel on my machine here, so I can't be super specific. Basically what you want to do is start a new worksheet, unhide your personal worksheet, open the macro editor, copy the module that contains your macro to the new worksheet's module space, then save the new worksheet. (then send the worksheet to your friend and reverse the process)

I think if you do a search in Excel help for something like export macro, you should get similar instructions.

This is what MS says to do, though I haven't tried it. Might work better.

"Sharing macros with others

When you distribute databases, worksheets, presentations, and documents containing macros to others, they can start running the attached macros immediately. You can also distribute individual modules containing macros, separate from Office documents and projects, to other Office users.
To distribute individual modules separate from Office documents and projects

1. Right-click the module containing your macro in the Project window.
2. Click Export File and follow the directions to save the exported code to a stand-alone file.

Note that the code in this file will not run on its own; it must be imported into an existing Office document or project, depending on the Office program.
To import a code file

1. On the File menu in the Visual Basic Editor, click Import File.
2. Follow the directions to bring the file's code into your document or project."

Good luck!
posted by selfnoise at 3:37 PM on October 17, 2005

File > Save as > Microsoft Excel Add-in (*.xla).

Upon reopening, the workbook will be hidden but you can still see the modules in the Project Explorer.

When programming VBA in an add-in, there are some issues to consider (using ThisWorkbook as opposed to ActiveWorkbook) and so on, but the on-line help should be sufficient to help you out with this.

The XLA file can be distributed to another person and used either by opening it in the normal way or using the Tools > Add ins dialog.
posted by Ritchie at 3:40 PM on October 17, 2005

The excel formatting macro we use at work is a .xls workbook that we save in the our Excel startup folder. It does use "ActiveWorkbook" rather than "ThisWorkbook" in the VBA code. It works for any workbook that is open in Excel while the macro file is open.
posted by mullacc at 4:07 PM on October 17, 2005