I realized I asked about excel 2010, but that only covers Windows OS. How can I use the Personal Macro Workbook in Excel 2011 on OSX?
The first Microsoft KB that shows up for the Personal Macro Workbook in OSX erroneously lists the location of the workbook as:
Hard disk drive:Applications:Microsoft Office version:Office:Startup:Excel:Personal Macro Workbook
In OSX Yosemite (10.10), the location of the Personal Macro Workbook is different. Starting at
`~/Library/Application Support/Microsoft/Office/Excel/Personal Macro Workbook.xlsb`
The easiest way to create this is to record a dummy macro. In excel, you first need to ensure the Developer tab is enabled by going to Excel → Preferences → Sharing and Privacy → Ribbon → Customize and in the Show or Hide Tabs area, make sure the Developer checkbox is checked:
Once this is enabled, you'll see the Developer Tab on the ribbon:
Now, hit the record button and choose to store the macro in Personal Macro Workbook:
Once you click OK, click the record button again to stop recording:
Now, you'll see the Personal Macro Workbook Module in the Visual Basic Editor's Project Pane:
To save, exit excel and choose to save changes:
Now, you'll have the Personal Macro Workbook available to you anytime you use excel. You can create and save frequently used macros so they are easily available, or you could even rename a workbook you already have that has a bunch of macros and replace the one you just created.