Excel macros range in complexity from recorded aids that highlight all of the values in a single column to extensive forecasting and problem-solving tools written in Visual Basic for Applications. If the macro is useful to a single person in a company, it is likely to be exponentially more valuable when shared among a workgroup performing similar tasks. Excel does not store its macros separate from the workbooks in which they work; they are embedded within the spreadsheet. Sharing macros means sharing workbooks, perhaps in the form of a master workbook on which all others can be based.
Step 1
Write the macro to be shared in an empty spreadsheet. Click the "View" tab on the ribbon menu. If you are going to record the macro, click the small arrow at the bottom of the "Macros" button and then select "Record Macro…" from the drop-down menu. If you elect to write the macro using Visual Basic for Applications, select "View Macros…" from the drop-down menu. Provide a macro name and click "Create" on the "Macro" dialog.
Video of the Day
Step 2
Save the spreadsheet as an "Excel Macro-Enabled Workbook" file. If the macros are saved in other types of Excel files, they may not be visible due to internal security settings.
Step 3
Distribute the Excel workbook master file to those co-workers with whom you want to share the macro. When a user bases a workbook on this master file, the macro remain embedded.
Video of the Day