Monday, January 11, 2010

How do I organize my Excel spreadsheet macros?

If you have an Excel workbook that is going to have multiple macros, sometimes it is a good idea to create another spreadsheet to organize your macros, especially if many people are going to be using them. In the first column I list the name of the macro. The next column is a brief description. For this particular example, there are several different spreadsheets being used so it is important to know which workbooks need to be open while the macro function is performed, which is listed in the next column. After that I listed the file to actually run the macro from and the name of the output file. The final column is what worksheet is automatically saved by the macro.

As you can see, some of the macros perform the same function but the code is and works differently.

Finally, at the bottom of the sheet I have listed a few notes to help clarify a few possibly confusing issues. The main purpose of this spreadsheet is for clarity and making sure all of the users are on the same page.

Press Alt+F11 to bring up the macro editor. You can import or export macro modules as needed. This is what the Microsoft Visual Basic editor looks like.

Here is my list of macros.

Pictured is one of the actual macros. This one protects the specific cells in the spreadsheet that I don’t want users to ever change. Later on I will get go in further detail on just exactly how these macros work.