So, you’ve taken the plunge and started to learn all you can about macros. You’ve read some books, gone over the instructions for recording a macro and maybe even gone ahead and made some changes to make it run like it should (as you probably know by now, recorded macros don’t always work the first time around – some tweaking may be required). You’ve used some macros created and shared by the online Excel community. Now you want to know how best to organize your macros.
Here are some guidelines to help you build a macro library you can be proud of and your coworkers can benefit from.
- Name – You may be tempted to name that macro that formats all your pivot tables based on the formatting in the first pivot table, One Pivot Table to Rule Them All (or maybe that’s just me) but make sure that your macro name actually helps someone understand what your macro is for. You also want to keep this short and sweet so it’s easy to read in the macro box. Also note that no spaces are allowed in macro names.
- Description – Since you’re keeping your macro name short and sweet take the time to tell us what your macro does in this section. You want anyone who reads this to understand the purpose the macro.
- Shortcut Key – You may assign a shortcut key that you can use to run the macro without having to go to the developers tab, click on macros, select your macro and hit run. Try and pick something that makes sense but don’t worry too much about it. A lot of shortcut keys are already taken so you may just need to assign a random letter. I try to use one of the letters from the macro name and if it is already a shortcut Excel will change it, such as Ctrl+P is already a shortcut for Print so Excel will change it to Ctrl+Alt+P.
- Macro Workbook – Save a macro workbook that includes your macro, a spreadsheet that describes what the macro does and how to use it and an example that can be used by anyone to play around with the macro and learn how to use it. You want your coworkers to benefit from the work you’ve done so that they don’t have to spend their time trying to automate a task you’ve already automated.
- Macro Library – Build your macro library on your server. Don’t keep your macros saved in workbooks on your desktop or in your personal folder. Instead, ensure that they are saved in a public folder and easily accessible by everyone in your company. Just as you gave your macros short and sweet but descriptive names, do the same for the workbooks that house them.
- Share – Let your coworkers know where your macros library can be found. Encourage them to create their own and add them to the library. And of course, share with them the guidelines I’ve shared with you today so they too can help build an organized macro library.
