Excel creates non-existent worksheets in VBA Project Explorer

Question

Excel 2010 Professional Plus (32bit) seems to have created a new set of worksheets and renamed all the original worksheets in the VBA screen, e.g. Sheet10 becomes Sheet101, Sheet13 becomes Sheet131. This causes the UDF to stop functioning. The "new" "sheet10" and "sheet13" do not seem to exist anywhere but in the VBA project window. The "new" sheets have a blue icon next to them.

The UDF does work. Until these new sheets are created and the old sheets are renamed. There are only 12 worksheets & one workbook in the Excel file. The VBAproject shows 2 workbooks & 20 worksheets.

1) What causes this?
2) How can I fix it?
3) How can it be prevented?

Added images showing the properties of non-existent of "sheet3" and properties of real sheet "sheet31". All of the non-existent sheets & workbook have a long list of properties.

XLS file here You'll have to download it since it opens in google's viewer.

UPDATE: 1/6/2016 So as of today, the formulas in this XLS are all errors (#VALUE) when I opened it. Excel has not created the non-existent sheets as seen in my last update. Last week the XLS & formulas were working and I made no changes. This is the new workbook I copied all the sheets to as suggested in the posts below. The original workbook (the one shown in the pix w/ the non-existent worksheets) does not have the #VALUE errors. Both workbooks are on the same computer and have been updated together over the last month+ for comparison purposes.

UPDATE3, 1/6/2016 I just accidentally moved a text cell, then clicked undo, and all the #VALUE errors went away and I now have all the right calculations. WTF.

Answer 1

The key to your problem is the message box "Automation Error. Catastrophic Failure".

There is not a single diagnostic for it, however it could be....

• Case 1

If a workbook is trying to run a macro that contains objects they are not included in the references section of the VBA editor, this kind of error could happen.

For example, if it requires a specific DLL to be correctly registered on the PC. If these DLLs are on your pc, then the workbook is fine, but on another PC without the DLLs, it will happen.

Any project will include the following as default:

Visual Basic For Applications
Microsoft Excel x.0 Object Library
OLE Automation
Microsoft Office x.0 Object Library
and possibly if a form has ever been added -
Microsoft Forms 2.0 Object Library

Where x is the Excel version.

Have you opened and modified the macro in another PC before running it on this?

It that's so then the references has been updated to the version of the other system and they're not found in your system.

• Case 2

One of your UDFs is causing the problem. Recreate the problem and check the function highlighted in yellow.

Conforming to the images you posted, then the problem is in Sumbytext(rg as Range,ltr as String)as Double. You have to:

• Trace the UDF logic step by step to find where it could be failing.
• In the named range "List_HolAbbr" are always values? Which?
• The UDF receives a range on variable rg. Is always a valid range?
• Since they use integer values, why Sumbytest and MidResult are defined as Double?

Answer 2

Reposting my solution from this other thread.

Here is my solution, it works consistently and you don't need to manually copy the sheets and code across to a blank workbook. I've tested this method on several corrupted workbooks that were giving me the "Automation error - Catastrophic failure" error upon launch.

NOTE: Original corrupted file was saved as .xlsm

1. Open a blank Excel workbook
2. Developer tab > Macro security > Disable all macros without notification
3. Close Excel
4. Double-click corrupted file, for example, MyFile.xlsm
5. File > Save as... > MyFile.xlsb (not .xlsm), choosing the .xlsb format is what does the trick
6. Developer tab > Macro security > Enable all macros (or whatever level of security you prefer)
7. Close Excel
8. Double-click MyFile.xlsb

The file is now fixed! You can re-save the MyFile.xlsb file as .xlsm if required. In my experience .xlsm files become corrupted quite easily, so I'm going to get into the habit of always using the .xlsb format.

Hope somebody finds this helpful :)

Answer 3

My solution is similar to the .xlsb solution posted by @Scoox (thanks!) but had to modify it because I am unable to change the macro-settings on my work PC:

1. Make copy of all macros in text files
2. Save corrupted file as .xlsx and close
3. Re-open file, save as .xlsb and close
4. Paste all macros in .xlsb file

Not ideal but worked for me!

Answer 4

SuMau's solution worked for me, though I had to add an additional step: If your VBAProject uses any non-default references, you'll need to restore those as well.

To make them easier to find, keep the original workbook open along with the one you've converted to an xlsx file. That way, when you choose "Tools -> References" from the VBA menu, all the references you want to add will be listed right below the checked ones at the top of the list.

When you have both files open, you can also drag any code modules from the old file to the one you're patching. This allows you to easily port over all your VBA code that's not on worksheet or workbook objects.