Corrupt VBA Project Module Not Found Error

I managed to find a workaround to this problem, so sharing the solution in the event that someone else comes across a similar problem.

It seems that the VBA Project got corrupt somehow. Below, find some suggestions and workarounds in trying to solve something similar in the future.

1. This is what worked for me. Convert the .xlsm file to a .xls file. You can do this by changing the file extension when renaming the file.
2. You can also try to convert to .zip file type, and then convert back to .xlsm. Note: convert COPIES of your original, just in case.

Other suggestions (as Chris Nelisen suggested) are:

• Export your VBA modules regularly
• Save different versions as you are building

This is a well-described issue, and it exactly matches what I have just experienced (even including the fact that I haven't been versioning recently).

My file has an xlsb suffix. Resaving with a different suffix did not work for me on the same PC, but I emailed the file to another PC, opened it, saved as xlsm, sent it back to the original machine and it now works fine again. I can even re-save with my preferred xlsb suffix and it still works.

I've also run this script to make a backup of my modules:

Sub ExportVbaModules()
     
'Acknowledgements to Andy Pope [ozgrid thread 60787]
     
    Dim objMyProj As VBProject 'if error, go to VBA editor - tools - References - Microsoft Visual Basic-Extensibility5.3
    Dim objVBComp As VBComponent
     
    Set objMyProj = Application.ActiveWorkbook.VBProject
     
    For Each objVBComp In objMyProj.VBComponents
        If objVBComp.Type = vbext_ct_StdModule And objVBComp.Name <> "" Then
            objVBComp.Export "C:\Users\MyName\VbaBackups\" & objVBComp.Name & ".txt"
        End If
    Next

End Sub

The And objVBComp.Name <> "" stops it from erroring when it encounters a corrupted module but turned out not to be necessary as the 'fixed' file contained no corrupted modules.

Since that export routine is so fast (40 modules / 100kb saved in <1s) I will be assigning it to a button on the ribbon with a better naming convention for the files it creates.

I copied my .xslm file from my PC to my OneDrive account.

I open the file on my iPad OneDrive account and then export to Excel for iOS. The file opens and says links and macros are disabled. I then save a copy of the file back to the OneDrive account. I go back to my PC and open the file from OneDrive. I re-establish the links.

The macros are from a backup. This is an issue if you aren't backing up your macros.

My workaround, works perfectly:

1. Open Excel in safe mode (pressing and holding Ctrl while you start the program, or by using the /safe switch (excel.exe /safe) when you start the program from the command line)
2. Open corrupted workbook (from safe mode, File->Open-> navigate)
3. Do not enable macro if asked
4. Make sure macro is present (Alt+F11) - not necessary
5. Save as new workbook
6. Close safe mode excel
7. Open saved workbook as usual

Do you have this file on OneDrive?

If yes, I was facing that issue, and resolved restoring the last save. If you open OneDrive site (onedrive.live.com), find the file, and select Version History. Download the penultimate.

When this happens on 64 bit Excel, I simply open the exact same file in 32 bit Excel and the macros re-appear.

When this happens on 32 bit Excel, I simply open the exact file in 64 bit Excel and the macros re-appear.