I had a strange one last week while assisting a user reported problem.
I’ve got a workbook and a VBA referenced XLA.
The workbook loaded fine, but produced errors when you tried to execute macros.
The debug point was on the “Left” function, which is a classic symptom of a broken reference.
Checking the VBA Project, I noticed that the referenced XLA was not loaded at all, even though it was referenced.
The XLA was in the right spot. Security and permissions were OK.
No matter how many times I closed and re-opened Excel, it still would not load that referenced XLA.
I even tried rebooting the computer.
It turns out that the user had suffered a “serious crash” just hours earlier. The next time he started Excel is came up with all sorts of recovery questions, which went something like ‘this workbook suffered a serious crash, are you sure you want to open it?’.
Excel does this for all the suspect workbooks – even referenced XLAs – except that with referenced XLAs, that question is not presented to the user, it just assumes ‘no, you dont want to open this corrupt workbook’ (though it wasn’t corrupt at all).
I opened the XLA file by itself, answered the recovery question and all was good next time Excel ran.
That’s not the only way to do it though.
It turns out that Excel maintains a list of Disabled Items that you can enable by going into Excel’s menu:
Help > About Excel > Disabled Items…
Then highlight the items and click Enable.
A fairly strange place to manage disabled items. I guess since it’s an Office-wide feature they might have struggled for UI consistency – who knows?
I spent some time trying to simulate a crash, but couldn’t. It must have been pretty serious!