0

I have an excel with a macro in it. When I try to open it it gives me "Compile error: Can't find project or library" and brings me to a VBA window at seemingly random place in the source code of the macro.

I know what the offending library is and I know that I need to Tools|References and uncheck the one labeled "MISSING", but I cannot. In the debug mode the "References" item of the Tools menu is greyed out and I cannot launch it. And if I try to hit the "stop" button to get out of debug mode, it tries to compile the macro again, and gives me "Can't find project or library" error again.

In fact, I cannot even close the excel normaly, since to do that I need to leave debug mode and when I do that it tries to compile macro, gets same error and immediately brings me to debug mode again.

So, is there a way out of this predicament?

Community
  • 1
  • 1
nsg
  • 539
  • 1
  • 6
  • 18
  • Im just quesing, but check if you are not in debug mode, when you trying to access references. That can be reason why they are greyed out. Its that blue square in VBA editor – Luboš Suk May 31 '16 at 14:35
  • 1
    When in debug, you could toogle all code as comment (see [how-to-comment-and-uncomment-blocks-of-code-in-the-office-vba-editor](http://stackoverflow.com/questions/12933279/how-to-comment-and-uncomment-blocks-of-code-in-the-office-vba-editor)) – Andreas N. May 31 '16 at 14:37
  • @AndreasN. but his error isnt from code, but from invalid reference. And its not in code, so cant be commented. – Luboš Suk May 31 '16 at 14:39
  • then I can only speculate, but could a solution be to open without firing the open event? [http://www.jkp-ads.com/Articles/preventopenevent.asp](http://www.jkp-ads.com/Articles/preventopenevent.asp) – Andreas N. May 31 '16 at 15:02
  • @AndreasN. suggestion to toggle code as comment kind of worked. Instead of commenting code I cut it (Ctrl-A Ctrl-X). It suggested to reset the project, which I accepted and the compile error did not come back. So I was able to rush to the references and uncheck the offending library. – nsg May 31 '16 at 15:10
  • Can you confirm that the code that is causing the error is part of an event or called by an event? If so, you could try opening the Immediate window and typing `Application.EnableEvents = False`. This should prevent the event's code from running again and you getting the error message again after you exit debug mode. – jones-chris May 31 '16 at 15:12
  • @Luboš, yes this is exactly why they are greyed out -- I am in debug mode. And when I try to get out of debug mode by pressing "stop" button (or "that blue square" as you called it) It tries to compile again instantly and I am back to debug mode. – nsg May 31 '16 at 15:13
  • @christopheralan88, no, it is not part of event (there are no event handlers) – nsg May 31 '16 at 15:16

2 Answers2

0

This might work..or might not

Open Brand new workbook

add macro, and enter code simple such as

sub test
    range("a1").select
end sub

Then on range("a1").select line, press F9.

This will highlight the line red.

Press F5 to run macro.

The macro will remain in break mode.

Open the workbook you had problem with.

Once you have your problem workbook opened, press square button and

you should be able to edit stuffs.

MutjayLee
  • 578
  • 3
  • 6
  • I can open my problem workbook, but "References" item is greyed out (because I am in break mode) and when I hit stop button to exit debug mode, it gives me the error right away and immediately puts me back into debug mode. – nsg Jun 01 '16 at 20:14
0

Here is a support article

https://support.microsoft.com/en-gb/kb/826922

One can launch Excel in automation mode by using the /automation switch from the command line. "The /automation switch disables all automatically opened files and auto-run macros."

S Meaden
  • 8,050
  • 3
  • 34
  • 65
  • Does not help. Even when I start with /automation switch, it still tries to compile macros and get stuck in the same pattern. – nsg Jun 01 '16 at 20:10
  • Ok, next thing, is once you have your automation Excel session then in the debug window type `Application.EnableEvents = False` which will prevent ThisWorkbook_Open events running. Finally, to stop workbook running any `Sub auto_open` macro open the workbook with the shift key held down. If nothing attempts to run then nothing attempts to compile. Also, try moving the workbook to an untrusted location so that you get a dialog offering to Disable Macros to which you say yes. – S Meaden Jun 01 '16 at 22:09