JohnTravolski
New Member
- Joined
- Nov 25, 2015
- Messages
- 46
- Office Version
- 2019
- Platform
- Windows
Recently I have run into a situation twice in the past week where an Excel .xlsm workbook I open and save on a regular basis started to complain "Can't find project or library" every time I open it.
This is because the workbook has a custom function I defined in the VBA, which apparently became corrupt somehow. If I open the VBA editor with Alt + F11, and I go to the modules in the corrupt workbook, it brings up a window, but rather than showing me the code, it is just a blank window that appears to have frozen pixels underneath it (if I move the window, the pixels don't change, and if there were other windows opened up underneath it, you can still see those windows even after moving it). So I can't even see the project code.
From some cursory research, apparently this is a compiled VBA corruption issue. A suggested solution was to add the registry 32-bit dword "ForceVBALoadFromSource" with a value of 1 to the key "Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options". Sure enough, as soon as I did this, it fixed it and the workbook and it opens normally now. If I resave this workbook as a copy, delete the registry dword I added, and then reopen the newly-saved version, the issue goes away.
Apparently the compiled VBA was getting corrupted, and it was suggested it may be related to OneDrive and some syncing issue somehow. However, OneDrive isn't even installed on my computer, and I don't do any type of cloud backup. So I guess something is going wrong during the saving process causing the VBA to be corrupted.
My goal is to understand why this has suddenly happened twice in the past week given it has never happened for years before of regularly updating this workbook on this exact same Excel version. I'm concerned it's a sign of a bigger problem on my system. Given OneDrive isn't installed, do you have any thoughts on why this is happening?
This is Excel 2019 (Version 1807 build 10325). The workbook size is 18 MB. There are only a handful of macros defined in it.
This is because the workbook has a custom function I defined in the VBA, which apparently became corrupt somehow. If I open the VBA editor with Alt + F11, and I go to the modules in the corrupt workbook, it brings up a window, but rather than showing me the code, it is just a blank window that appears to have frozen pixels underneath it (if I move the window, the pixels don't change, and if there were other windows opened up underneath it, you can still see those windows even after moving it). So I can't even see the project code.
From some cursory research, apparently this is a compiled VBA corruption issue. A suggested solution was to add the registry 32-bit dword "ForceVBALoadFromSource" with a value of 1 to the key "Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options". Sure enough, as soon as I did this, it fixed it and the workbook and it opens normally now. If I resave this workbook as a copy, delete the registry dword I added, and then reopen the newly-saved version, the issue goes away.
Apparently the compiled VBA was getting corrupted, and it was suggested it may be related to OneDrive and some syncing issue somehow. However, OneDrive isn't even installed on my computer, and I don't do any type of cloud backup. So I guess something is going wrong during the saving process causing the VBA to be corrupted.
My goal is to understand why this has suddenly happened twice in the past week given it has never happened for years before of regularly updating this workbook on this exact same Excel version. I'm concerned it's a sign of a bigger problem on my system. Given OneDrive isn't installed, do you have any thoughts on why this is happening?
This is Excel 2019 (Version 1807 build 10325). The workbook size is 18 MB. There are only a handful of macros defined in it.
