6

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.

enter image description here

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

enter image description here

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

enter image description here

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!

Dean
  • 2,326
  • 3
  • 13
  • 32
  • I think the file is corrupt? Possible to see the file? – Siddharth Rout Aug 26 '20 at 09:07
  • 1
    It's a sad fact of Office development that files do get corrupted from time to time. So much so that when I'm working on a significant project I regularly export all the vba to text files, and save copies of the documents. You _might_ be able to recover some of you code using online services (and at some expence). If not, well, lesson learned I guess... – chris neilsen Aug 26 '20 at 09:16
  • 1
    Yeah, it's a sad reality. The silly thing is I haven't been versioning the past couple days, so I would otherwise lose all that work! I have managed to find a solution though. I first tried to convert to `.zip` and then convert back to `.xlsm` but that didn't work. For some reason convert the file to `.xls` and it opened perfectly fine with all code intact. Needless to say, have now backed up and exported all! Weird!! Thanks both. – Dean Aug 26 '20 at 09:21
  • 1
    It might be worth adding that as your answer. Although not ideal but it seems a valid workaround – Zac Aug 26 '20 at 10:14

8 Answers8

4

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
Dean
  • 2,326
  • 3
  • 13
  • 32
  • I wonder if there is a way to extract modules from a excel file, they are truly just zipped archives and it sounds like your code was still there somewhere! – FreeSoftwareServers Dec 11 '20 at 15:44
3

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.

DuDa
  • 3,718
  • 4
  • 16
  • 36
paul45
  • 31
  • 1
  • Wow after pulling my hair out, this worked for me. I uploaded the bad workbook to OneDrive from PC then synced the file back to my local folder and opened it on PC via desktop app. My macros were disabled but I could see the code on VBA editor. Reenable macros and save the file again, and it will work correctly. – kindofhungry May 14 '21 at 17:00
  • uploading corrupt file to onedrive and downloaing a copy of it again back to local harddrive worked! even when excel online mentioned that disabled features in the online view won't be present in the downloaded file - but since I only needed the code it was OK. – smartini Jun 24 '21 at 05:40
  • This trick worked for me as well. Excel on windows was saying catastrophic failure, so I opened the same file on Mac and saved it. Opened it on Windows again and it worked fine. How ironic!! – Rishi Mehta Mar 10 '23 at 16:41
3

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
Dmitry Vintsuk
  • 31
  • 2
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.

LondonJustin
  • 73
  • 1
  • 1
  • 7
0

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.

luiz moraes
  • 1
  • 1
  • Actually this is a perfectly good answer. This answer reminded me to check OneDrive for a copy of my corrupted file. – scott_f Mar 29 '21 at 01:42
0

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.

Tom Kingston
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 07 '22 at 13:00
0

Try to open the excel file in repair mode and save as the file one more time.

Open and Repair

Jagadesh
  • 1
  • 1
0

I tried everything suggested and nothing worked. I could only see the module when I opened my VB editor. It was not available through the view macros ribbon shortcut. I was unable to export the module or copy it to a new workbook.

What finally worked for me was emailing it to myself, downloading it through my 365 outlook account via a web browser, and then the code was there.

Garret
  • 1
  • 1