Corrupt VBA Project Module Not Found Error

Question

I have a workbook that was saved yesterday afternoon, and was working perfectly. I have opened it this morning, and none of the modules in the VBE are 'found'. Visually I can see them all sitting there.

When trying to open any of these modules to edit the code, the windows are greyed out, as below.

When I try exporting the code modules, I get the 'Module Not Found' errror.

Does anyone know a) why this has happened, and b) how can I fix this?

I thought initially it was the instance of my Excel, so have restarted the PC.

Any help is appreciated!

Answer 1

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

Answer 2

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.