Excel creates non-existent worksheets in VBA Project Explorer

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.

Solution 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?