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?

 

[E1375]

Hello, today less than 2 hours ago, I opened a Workbook that has code in the Open Event, has code in WorkSheet1, has code in Modules and has code in Forms. After opening, executing and quitting the Workbook, I go to a point where ALL my VBA code completely vanished. I have no idea what could have caused this problem. I read a "post" searching Google with "VBA Excel Command Buttons Not Working" and found a problem caused by MS. After certain patch is applied to Windows 10 Pro, on those computers that have 2 or more monitors (which is My case, I have 2), ActiveX Command Buttons stop working. As fas as I have searched, MS has not fixed this "old" and "known" problem. I experienced loosing all my code while working ONLY on monitor 1 and not using monitor 2 at all. I don't know if the monitor issue is related to loosing all code. I already disconnected monitor 2 from the PC. I am going to try to replicate the problem. Fortunately my PC is connected to a Synology NAS and all the files I have on the directory where I have the workbook in question, are backed up automatically and the NAS is configured to keep the last 8 versions of each file. I already increased it to 25 and was able to recover an earlier version. Will come back if able to replicate the incident.

 

[wackywoo105]

Have you had any help with this? I am having the same issues.

No. No help yet. It's certainly a recent issue, although it did occur once a few months back. Now it is happening regularly with certain excel files that haven't changed in years.

Today I have a new issue. I noticed it with Access the other day (so commented out the offending code) and now it has affected Excel. The following code is causing Excel to crash.

Sheets("Sheet1").Activate

I have a theory, but haven't had time to investigate. I notice one of the yellow box pop ups at the top about activating code is different to previous, and was probably changed in a recent update. I'm wondering if this is ignored maybe it clears the code and when you save the file it's all gone.

 

[wackywoo105]

I had a play around with my theory and cannot replicate the code vanishing. I open and save and close the workbook, completely ignoring all pop up about macros. When I open it again the code is all there.

When it happens it very insidious. I don't realise until I notice calculations and colour coding are no longer working and when I check the code has vanished.

Currently I keep a backup of the file with VBA in tact. I copy that file elsewhere, open it and paste the code back. It is getting very tiring to do this.

Where can issues like this be reported to Microsoft?

 

[MARK858]

Where can issues like this be reported to Microsoft?

As you are on 365 then maybe follow the steps in the article in the link below (please note that "I Don't Like Something" is now "Report a Problem" on my version [the article is from 2019]).
There is also a Contact Support option in the Help menu

Sending Effective Bug Reports to the Excel Team

I recently had a conversation with several members of the Excel team about how they process submissions that are sent in through the Feedback icon that appeared on the Help tab in Excel. There is a specific set of steps you can use that will make...

www.mrexcel.com

 

[wackywoo105]

Thanks. At least I know it's not just me. I will try and study what is happening and pass the details to MS. I was hoping it was just a dodgy update and would fix itself. If it happened more often I could probably get a better handle on why on may be happening.

 

[wackywoo105]

After watching this issue I think it is linked to the excel documents being accessed by Access? I'm also having an issue with a spreadsheet becoming corrupt after access has opened it. It will work fine several times and then on the odd occasion will error and the become corrupt and refuse to save.

Are there any known issues with excel documents acting up after being opened by Access vba and if so any fixes?

 

[wackywoo105]

This happened again today with a rarely used spreadsheet that access hasn't been anywhere near. Command buttons not working. This workbook is acting odd. In the in the VBA editor I cannot select any of the sheets or modules.

 

[rlv01]

Have you considered a fresh re-install of Excel?

 

[wackywoo105]

Have you considered a fresh re-install of Excel?

My problem is I'm experiencing this on 2 different computers and with different documents, some of which are accessed separately on each PC, and some that are shared in google drive.

For now I have copied all the VBA for various workbooks into word documents. My concern is trying to replace it from a backup excel file, I may find the backup has also lost the VBA. I recently wrote some new code for a new spreadsheet. Of course the code disappeared before I had a chance to back it up. That was fun.

 

[rlv01]

My problem is I'm experiencing this on 2 different computers and with different documents, some of which are accessed separately on each PC, and some that are shared in google drive.

For now I have copied all the VBA for various workbooks into word documents. My concern is trying to replace it from a backup excel file, I may find the backup has also lost the VBA. I recently wrote some new code for a new spreadsheet. Of course the code disappeared before I had a chance to back it up. That was fun.

And are the two different PC's managed by the same corporate IT department and have the same Anti-virus package on them? Code does not just 'vanish' on it's own and file corruption is rarely so targeted as to kill the VBA w/o doing other damage. There has to be a mechanism. That mechanism may be an add-in, or restrictions on cloud-based storage (see link), or an AV package, or malware - but it exists and should be findable by methodical troubleshooting. Example.

If you pull the file off the cloud to your local drive before opening, do the macros still vanish?
If you make the file read-only, do the macros vanish? Do you get any weird 'fail to delete' messages? Say from an AV package that's been set to see macro enabled files in cloud storage as a 'problem'?
If you hold down the shift key to disable macros on opening, do the macros vanish?
If you open the file with macros disabled and do a 'Save-As' to make an .xlsb file, when you open the .xlsb file (macros enabled) , do the macros vanish?
If you open the file with macros disabled and do a 'Save-As' to make an XL 2003 .xls file, when you open the .xls file (macros enabled) , do the macros vanish?
If you open Excel in "safe mode" before opening the workbook, do the macros vanish?
If you disable your Excel add-ins one at at time, does there come a point where the macros do not vanish?
If you temporarily disable your AV package, do the macros still vanish?
If you rollback Office to an earlier version/build, do the macros still vanish?

Redirecting