Run macros in Personal Macro Workbook on Mac

Question

Unable to see and/or run macros in Personal Macro Workbook on Apple Mac unless that workbook is open

We have put some macros (copied from another computer) in Personal Macro Workbook and can run them from other workbooks if Personal Macro Workbook is open.

However, when Personal Macro Workbook is not open the Macros window in other workbooks (reached by clicking Macros on the Developer tab of the ribbon) is empty. If, with the Personal Macro Workbook still unopen, we try to record a macro in Personal Macro Workbook we get the error message "Personal macro workbook in the startup folder must stay open for recording".

I have seen a suggestion that in these circumstances we should just use the Unhide option in the Window menu, but that option is grayed out.

Is it possible that we might have created a different workbook with the same name (called "Personal Macro Workbook") which is not the Personal Macro Workbook that should be accessible from any workbook?

Is it perhaps that the Personal Macro Workbook is stored in the wrong place? Is there an XLSTART folder on Mac?

The Personal Macro Workbook.xlsb file that we have stored our macros in is shown in Finder in [User]>Library>Application Support>Microsoft>Office>Excel . Is that the correct location?

(It's Excel Mac 2011 running on OSX El Capitan.)

Answer 1

The answer to our problem was that, because we had closed all workbooks including the Personal Macro Workbook (PMW), the macros in the PMW were no longer available when we opened an existing workbook or created a new one. What we did to retrieve the PMW was to quit Excel altogether. When we then reopened Excel the PMW was automatically opened and the macros were therefore again available. (Alternatively we could have opened the PMW from Excel's File > Open Recent menu.)

I think what had happened was that, when importing macros by copying them in from elsewhere, we followed online advice to "unhide" the PMW. After we subsequently closed the spreadsheet we were working on the PMW remained visible so we closed it, without realising the consequences. What we should have done was simply hide it.

Excel's normal behaviour is for the PMW (if it exists) to open automatically whenever Excel is started and to remain hidden but open and available.

On a Mac, after all open workbooks have been closed, the Excel app continues to run. This means that if the PMW has been closed and a workbook is then opened from the Finder or by starting a new workbook from the Dock the macros are inaccessible. Therefore, in order to ensure that macros will be accessible it is necessary to ensure that the PMW is open. If the PMW has been closed it can be reopened by quitting Excel altogether (by using Cmd-Tab to switch to Excel then Cmd-Q to quit). When Excel subsequently restarts the PMW should reopen automatically. [Alternatively, instead of quitting and restarting Excel one could reopen the PMW from the File>Open Recent menu, and then use the Window > Hide menu to hide it.]

On Windows the behaviour is different. When all workbooks are closed, Excel also quits. It is not necessary to do anything else to ensure that the PMW is opened when Excel is next used and that all its macros are therefore accessible.