Excel creates non-existent worksheets in VBA Project Explorer

microsoft excelmicrosoft-excel-2010vba

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.

Best Answer

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?

Related Solutions

Mac – How to easily toggle display of page breaks for all worksheets in an Excel 2010 workbook

Here's what I was able to come up with. I tested it successfully in Excel 2010. I'm very new to VBA so I struggled with this for a while. ws.Activate was the key, since DisplayPageBreaks only applies to the active sheet. Credit to RocketDonkey's post for making me realize that. Credit also to Rick Rothstein for the beautifully simple toggle code concept which I applied in my answer.

Sub ToggleWkBkPageBreaks()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

        ws.Activate

        ActiveSheet.DisplayPageBreaks = True + False - ActiveSheet.DisplayPageBreaks

    Next ws

End Sub

Macro for cleaning all sheets in a workbook (VBA – Excel 2010)

Cleraing all sheets in a workbook:

Sub ClearAll()

    Set wbook = ActiveWorkbook

    For Each sht In wbook.Worksheets
       sht.Activate
       sht.Cells.Select
       Selection.ClearContents
       Selection.ClearFormats ' edit: clear formats too
       sht.Cells(1, 1).Select ' edit: select the first cell to cancel selection of the whole sheet
    Next sht


End Sub

Edit1: see source

Deleting them instead of cleaning:

Sub DeleteAll()

    bAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False

    Set wbook = ActiveWorkbook

    wbook.Sheets(1).Activate

    For Each sht In wbook.Worksheets
        If sht.Name = wbook.ActiveSheet.Name Then ' we don't delete the active sheet but just its conntet
            sht.Cells.Select
            Selection.ClearContents
            Selection.ClearFormats
            sht.Cells(1, 1).Select
        Else
            sht.Activate
            ActiveWindow.SelectedSheets.delete
            wbook.Sheets(1).Activate
        End If
    Next sht

    wbook.ActiveSheet.Name = "Sheet1" ' we rename the last remaining sheet to the default name

    Application.DisplayAlerts = bAlerts

End Sub

Best Answer

Related Solutions

Mac – How to easily toggle display of page breaks for all worksheets in an Excel 2010 workbook

Macro for cleaning all sheets in a workbook (VBA – Excel 2010)

Related Question