When we then reopened Excel the PMW was automatically opened and the macros were therefore again available. What we did to retrieve the PMW was to quit Excel altogether. 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.
(It's Excel Mac 2011 running on OSX El Capitan.) The Personal Macro Workbook.xlsb file that we have stored our macros in is shown in Finder in >Library>Application Support>Microsoft>Office>Excel. Is it perhaps that the Personal Macro Workbook is stored in the wrong place? Is there an XLSTART folder on Mac?
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? 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. 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". 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.
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. Unable to see and/or run macros in Personal Macro Workbook on Apple Mac unless that workbook is open