VBA code vanishing

[wackywoo105]

A couple of month ago I noticed all the VBA code had disappeared from a spreadsheet. I realised this as a button stopped working and when I went in to look at the code to see why, there was noting there. I thought I must have done something, so put the code back in and forgot about it.

The other day it happened again to different file on a work computer. All VBA code gone and it was password protected. VBA window for sheets and thisworkbook is just empty. File is still xlsm and hasn't been saved in a different format. I searched online and can find many people experiencing the same, but no reason or solution. I tried the repair on open option, which I read to do on here, and it did nothing. Again I put the code back from a backup.

Today the code from this file has again vanished. This file is in a Google Drive. It is also edited with VBA from an access database. It has been working fine like this for years. I know office has recently updated to version 2311, and I have a separate issue this update had introduced into access.

Can anyone help with why the VBA code keeps disappearing from this file? Is it possibly a bug introduced with a recent office update?

 

[Peter_45]

Hi.
Not sure if you are still checking for replies but I've had a similar issue and more by luck than judgement found a solution which may work for you.
In the file that wasn't working, the vba code had disappeared and trying to run anything in it just gave an error. This is what worked for me:
Open the faulty file. Do not enable macros.
Open another file that has vba and is working as it should. Run any vba in that file.
Look in the vba window of the faulty file. You should now be able see the code, Save the faulty file.
Re-open the faulty file and enable macros. Cross your fingers
It worked for me anyway

 

[wackywoo105]

Hi.
Not sure if you are still checking for replies but I've had a similar issue and more by luck than judgement found a solution which may work for you.
In the file that wasn't working, the vba code had disappeared and trying to run anything in it just gave an error. This is what worked for me:
Open the faulty file. Do not enable macros.
Open another file that has vba and is working as it should. Run any vba in that file.
Look in the vba window of the faulty file. You should now be able see the code, Save the faulty file.
Re-open the faulty file and enable macros. Cross your fingers
It worked for me anyway

Thanks. I'm still struggling with this. I'm fairly sure it has nothing to do with Access now. I read the below elsewhere and am trying it. I only just implemented on one PC, so haven't had time to see if it works. The problem is I don't always run the VBA code each time a file is used, so I don't notice the code has vanished until the file has already been saved without it. I now have the VBA code saved in word documents, as I even had backup excel files lose the code.

Excel is recompiling and the excel code that does this is broken from microsoft. An ongoing issue. You might try using this registry fix to force complete recompilation. It resolved ALL of my corrupt workbook issues for months since deployed.

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options]
"ForceVBALoadFromSource"=dword:00000001