References in VBA editor not opening

Question

I am getting an error saying "Can't find project or library" error message when I try to run a VBA project. I am trying to go into the References to see if there is something there that is causing an issue, but when I click on it, it does nothing.

Any suggestions?

Answer 1

This can happen for any number of reasons, as VBA and excel are very sensitive. What i would suggest is unprotecting your workbook in any way that it is protected for now. Then try accessing your file via "safe mode" by holding ctrl when opening excel. Take the time to back up any important VBA codes. If you can't fix the errors in safe mode you may need to recreate the workbook with the saved VBA codes.

Also if you have a personal XLSB workbook it may have been corrupted. Go to your excel's XLSB file location and check by opening it and seeing if there are any issues.

I recently had this issue because i protected a sheet that VBA was trying to access when running a code so make sure everything is in order of when it was last working.

Answer 2

I also managet to fix it by disabling all macros, saving the file, and then enabling them from the "Macro Security" in the Developer tab.

Answer 3

I just fixed this issue by getting somebody else on the network in my office to go into the file (which worked for them), then saving it as a trusted file. I suppose this would work by emailing a copy of the file to a different device and going into it on there. While on the different device you can also go into VBA Editor > Tools > References then untick all that aren't necessary. This may or may not be necessary. More technical solutions may be available but this seems to be a simple one that worked for me.

Answer 4

This has happened to me twice in the last week. For me not only did nothing open under "tools / references" but although the VBA modules were visible they were completely empty. I don't know the cause, but I speculate that it is some combination of:

• The Power Pivot addin (I'm using Office 365 FWIW)
• Dax Studio (this was present second time but not first)
• The MS scripting runtime reference/library

The first time I managed to retrieve the situation, through a complex procedure; I'm not sure what part of the process worked the magic and for me it's not replicable. The second time (this morning) I adopted a simpler procedure.

1. I did the Office repair. That didn't work for me either.
2. I opened an empty .xlsm and went into the VBE.
3. I added a reference to the scripting runtime (Tools/References behaved as expected)
4. Leaving this empty .xlsm open I opened the s/sheet that was giving me the problem.
5. Voila: I got all my VBA back, Tools/References is OK.

Happy bunny - for now.

Answer 5

This has happened to me before. As usual, I'd suggest restarting Office. If that does not work, then go to Control Panel> Uninstalled Software> Microsoft Office. click on "Modify" and then once the wizard comes up, select "Repair".