Forum Discussion
Remove "Ghost" Excel sheet object from VBA
After trying many methods that did not work from CoPilot and other AI engines, the easiest method was to export all the modules, userforms, and sheets to remove these "Ghost objects". There appears to be a more time consuming and complicated method to manually edit the binary code directly in the VBA project structure.
It appears that it is not "corrupt" per se but some how the codename (CAD2) is somewhere in the project structure.
I was successful exporting all the modules, userforms and sheets and then transferred all to a new Excel file to save. The ghost files are gone. Not everything transfers. I had some "buttons" that referenced macros that looked for the previous version name, so you have to be careful to name the project the same name or edit those buttons references macros.
The following procedure is more detailed than HansVogelaar's. It will remove ghost document modules (like Sheet11 and ThisWorkbook1) from a macro enabled workbook (.xlsm or .xlsb):
● In the VB Editor, open Tools > References and take a screenshot.
● Export and remove all VBA form, class, and standard modules.
● Save the workbook as .xlsx (not macro enabled), then close Excel.
● Reopen the .xlsx file, which should have no remaining ghost modules.
● Import VBA form, class, and standard modules previously exported.
● Use Tools > References to restore necessary references.
● Save as macro enabled (.xlsm).
● For each remaining ghost-like Sheet module (like Sheet111…):
– Activate the old sheet, press Ctrl+A, and press Ctrl+C to copy all.
– Insert a new sheet and press Ctrl+V to paste all from the old sheet.
– Record the old sheet’s name, then delete the old sheet.
– Rename the new sheet with the old sheet’s name.
● Resave the .xlsm file, which should have no remaining ghost-like Sheet modules.
● Carefully test the result.