Running Macro Causes Reference File to Save somewhere else

[heylookitsme]

I can't seem to replicate this error but it happens at least once a week. I open a file where I created some script. Does not matter if it is an older file or a newer one and I get the dreaded error "Can't find Project or Library". This means that the Reference Library is missing a Module/Tool that it can no longer reference. I scratch my head and wonder for a moment, "I did not make any changes to my tool references so how can this be. To further agitate me When I open VBA Editor and select Tool Reference the sheet does nothing. Won't show me tools and now the code has disappeared. I can see the modules in the project window but the sheet is blank.

So first I have to fix it. Usually, I can fix it by first closing out of all Excel Apps. Then, while holding down Ctrl and clicking on a new Excel app to open, I open Excel in Safe mode. I open the file. Then I go into VBA Editor and select Tools Reference and I can see that one of my files instead of being stored in with the system files it is normally saved to, it has been saved to my C Drive Documents. When I go look there the file is not there. However, if I search for the file in my system files (C:Program Files\Common Files\Microsoft Shared\VBA) I find it and re-sync it.

But what is crazy to me is how did that file location change in the first place? Has anyone else experienced this issue more than a few times?

I am 100% certain it is nothing in my code causing this to occur.

I think the problem relates to when Excel has a large file or several files open causing it to run at a high PC Utilization rate, Excel begins to act very crazy. When this occurs and I start shutting things down, the next time I re-open the file is when I will notice the file reference error.

[#2]

I am sure someone has an answer to this issue. It just happened again to me on three separate occasions. Files I have used for a few weeks or months work and save just fine. Then one day I go to save and get the dreaded Excel has found an error and is unable to save this file. We can repair blah, blah blah, then go to repair and save in different location and same error. Looping like an endless EDM concert. When I go into my VBA the modules are solid white, the project modules are there and will let you select them but no code in the module. When I try and select the reference library, nothing will happen, can't access it now. However, when I open the file in Safe Mode I am able to access the modules and Reference Library where I see Visual Basic For Applications reference file location is showing C:\Users\myname\Documents\ where I have the workbook itself saved. The library reference is normally located in the Microsoft shared\VBA\VBA7.1\VBE7,DLL which is where the file is currently it is not in my documents folder. Somehow, Microsoft has changed that reference library file but not actually placed the file there. This is crazy... If anyone runs into this issue HERE IS THE TEMPORARY FIX... Close the file and loose your changes you made. Close anything in Excel you may have opened. Then while holding the cntrl button select the Excel Application and a box will pop up asking if you want to open in Safe Mode. Say Yes. This will disable macros but still allow you to have access to it. You won't need to access this most times this occurs and you can simply save the file. Close it and re-open it (not in safe mode) and the file works again. Maybe for a few days or months but it will probably happen again at some point without any reason as to why.

[#3]

I was truly hoping to get some support in this community but I guess I will take my questions to MrExcel community instead.

[#4]

Hi @heylookitsme 

So you're running your macro and before your macro ends its procedure, someone opens your reference file as read only, what would happen to the rest of the procedure?

[#5]

Can't say I follow what your asking. If I have a file open with Write access and I am running a procedure against it and someone opens the same Excel file as read-only, both would be two separate actions because the file is not a shared file. The reference file for each person resides on their C Drive in the Microsoft Library folders. One factor I have found that could be contributing to this error is Microsoft becomes extremely unstable when you have some large files open. Microsoft needs a built in procedure that calculates the processor and ram usage to determine if opening a file will cause the program to not run correctly and prevent the user from opening a new file until one is shut down. I would much rather be told I can't open another file then to open one and have strange behaviors occur that are not explainable.