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?

 

[rlv01]

1. What version of Excel/Office do you have? What OS?
2. Is Office the 32bit or 64bit version.?
3. VBA code lives in a code module. There are several different types of code modules (standard, worksheet, form, etc).

When you say the code vanishes, what does that mean? Is it deleting the code text from the modules, and leaving the modules with fewer lines of code? Or is it deleting the modules? If the latter, does it delete all the diffent modules or just certain ones?

4. Have you ever 'cleaned' your code? Over time, Excel can build up a lot of "excess baggage". If you are still using a 32-bit* version of Excel, try using Rob Bovey's excellent and free code cleaner utility. This is has been one of my go-to utilities for years.

Excel VBA Code Cleaner Add-in

*It does not matter if Windows is 64bit, your office install could still be 32bit. Sadly, Code Cleaner is not compatible with the 64bit version of Office

 

[wackywoo105]

Many thanks. OS Windows 10 pro and 64bit office 365. No modules, just Sheet1(Sheet1) and ThisWorkbook contain code. It's fairly simple code, about 1 page long. After this has happened they are just blank VBA sheets with nothing entered. I paste the code back in from a back up and some time after it is erased again. Currently this is only affecting one document, but if it extends to others I will be in trouble.

I will try the cleaner add in ASAP and report back. EDIT No I won't if it's only for 32bit.

Microsoft® Excel® for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20028) 64-bit

 

[rlv01]

It's possible to use VBA to delete code in a code module. Here's an example from Chip Pearson's site

'This code will delete the procedure DeleteThisProc from the Module1. 
'You must specify the procedure type in order to differentiate between Property Get, Property Let, and Property Set procedure, all of

    Sub DeleteProcedureFromModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim StartLine As Long
        Dim NumLines As Long
        Dim ProcName As String
       
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
   
        ProcName = "DeleteThisProc"
        With CodeMod
            StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
            NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
            .DeleteLines StartLine:=StartLine, Count:=NumLines
        End With
    End Sub

Do you have any code that might resemble the above? Assuming your answer is "no", then my concern would shift to a possibly corrupted workbook. It might be time to think about salvage operations such has exporting your code and moving your worksheets to a new workbook.

 

[wackywoo105]

Nothing like that as far as I know. I will create new document and copy everything across and see how it goes.

I do love all these random issues you get with MS office.

 

[Dan_W]

To be honest, if this is what's happening on your work computer, then it sounds like the corporate virus scanner is probably gutting the file of the code. This is what happens at my company when XLSM files are emailed to me - any files containing VBA code are stripped of the code.

 

[wackywoo105]

To be honest, if this is what's happening on your work computer, then it sounds like the corporate virus scanner is probably gutting the file of the code. This is what happens at my company when XLSM files are emailed to me - any files containing VBA code are stripped of the code.

Thanks. It's my own business, so the PC isn't much different to my home PC, and it previously happened to a file on my home PC that hadn't been anywhere near the work PC. The current file is in Google Drive and is used on both, so maybe it may be my home PC is responsible, but I don't access the file very often from home, and many other excel files, which contain VBA, are so far unaffected.

 

[rlv01]

Here's another thing you can try that falls under the "sometimes it works, sometimes it doesn't" category.

1. Make a backup
2. Do a file save-as to an .xls file (Excel 97-2003 workbook).
3. Convert it back.

 

[wackywoo105]

I now have a problem with a different document. The document gave an error on opening and then wouldn't save. I linked it to a command button that is visible or not depending on the user which appears to have been deleted. As the code references this it caused it to crash. I put the button back and guess what, it gets deleted again. For now I have commented out this section of code.

This particular document has been working fine for several years.

 

[KristinaT]

I now have a problem with a different document. The document gave an error on opening and then wouldn't save. I linked it to a command button that is visible or not depending on the user which appears to have been deleted. As the code references this it caused it to crash. I put the button back and guess what, it gets deleted again. For now I have commented out this section of code.

This particular document has been working fine for several years.

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