- Sharing macros helps others because they can now take advantage of both the time a macro saves and the consistency it provides.
- Sharing macros helps you because others can test the code and find any bugs you may have missed. It also helps you because as others use your macros, they will often have ideas and suggestions for how you can make the macro even better.
- Sharing macros helps your organization because the whole team becomes more productive.
Sharing the macro you created is the fifth and final step in properly creating a macro. In the March 2nd blog post we shared that the 5 steps to properly create a macro are:
- Know what you want.
- Know how to do it without a macro.
- Record a macro.
- Test the recorded macro and edit as needed.
- Share the macro with others.
The first two steps were discussed in the Plan your macro or else… blog post. You can read about step 3 in the How to record a macro blog post. Step 4 was covered in the Testing Macros blog post. Today’s blog post covers step 5, how to share a macro with others.
How to share a macro with others
First – Save the macro file
To share the macro with others, you will need to give them a copy of the Macro-Enabled workbook or Macro-Enabled document that has your macro. Simply choose File, Save As for the appropriate file, and be sure to change the file type to either Excel Macro-Enabled workbook or Word Macro-Enabled document.
Note: These instructions will not work if, when you recorded the macro, you chose to save the macro in Word’s “All Documents (Normal.dotm)” or Excel’s “Personal Macro Workbook”. In that case you can follow these instructions if you first copy the macro code into a module of a new document or workbook.
Second – Share the file
To share the file, either email it to your coworker, or save it somewhere on your fileserver or other location where they can get it.
Third – Show your friend how to set their Macro Security settings
Macros are extremely powerful, and if created by the wrong person, they can cause problems. For example, people with malicious intent can create macro viruses that can delete files and programs from your computer. Not to worry though, Microsoft has added Macro Security with four different settings, to help protect the computer. The four settings are…
- Disable all macros without notification
This disables all macros. With this setting in place your friend will not be able to run your macro.
- Disable all macros with notification
This disables all macros but gives the user the option to enable the macros. Once the file is opened, the user has the option to click the enable button. If they enable them, then the macros can run without any additional security issues. If they do not click enable, then the macros will not run.
THIS IS THE RECOMMENDED OPTION.
- Disable all macros except digitally signed macros
This disables all macros, except those that have been digitally signed by the author. If the macro has been digitally signed by a publisher, and if the user has signified that they trust the publisher, then the user will be able to run the macros without needing to enable it. If the macros have not been digitally signed, the user will have the option to enable them.
- Enable all macros (not recommended; potentially dangerous code can run)
As stated in its name, this setting is dangerous. It allows all macros to run, without warning. Thus, if the file contains a macro virus, serious damage could take place on your computer.
DO NOT CHOOSE THIS OPTION.To set macro security in do the following:
First, from the ribbon, select File, Options.
Second, from the menu on the left, choose Trust Center.
Third, click the Trust Center Settings…. button.
Fourth, from the menu on the left, choose Macro Settings.
Fifth, choose Disable all macros with notification.
Sixth, click OK.
Seventh, click OK.
Fourth – Show your friend how to enable the macros
Each time your friend opens the macro-enabled file, a Security Warning message will be displayed below the ribbon. Explain to your friend that in order to run the macros, they will need to first click the Enable content button.
If the macro-enable file is on a network location, they may see the Do you want to make this file a Trusted Document? dialog box. If they click No, the macros can still be run. If they click Yes, the macros can also still be run, but the next time the file is opened, the security warning will be bypassed because the user said the file is now a trusted document.
Fifth, show your friend how to run the macro
Have your friend do the following to run the macro:
First, from the ribbon select View, Macros, View Macros, or by pressing Alt F8 on the keyboard.
Second, from the Macros in drop down choose the workbook or document that contains the macros.
Third, select the desired macro.
Fourth, click Run.
This blog post has shown how to share a macro with others. If you would like some help creating, editing, and/or improving your macros, please contact TechMentors.