8

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?

Those blue icons on the left do not really exist enter image description here

enter image description here

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.

Sheet3-nonexistent Sheet31-exists

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.

Improve this question
14
  • You can try copying sheets to another workbook, as stated here
    – ZygD
    Nov 30, 2015 at 0:51
  • So if you target them by their index numbers, are they correct?
    – Raystafarian
    Nov 30, 2015 at 15:12
  • @ZygD - I have copied the sheets. It did this again. I can't copy the sheets to a new workbook every time it screws up.
    – mechengr02
    Dec 2, 2015 at 18:21
  • @Raystafarian - what do you mean "target them by their index numbers"? If you mean reference the sheet numbers, referencing a sheet # goes to the sheet with that number. If it references a sheet that does not exist, it fails.
    – mechengr02
    Dec 2, 2015 at 18:22
  • 2
    Is there any way you can provide a link to the file so we can take a look at the project explorer objects?
    – Raystafarian
    Dec 6, 2015 at 10:26

5 Answers 5

Reset to default
2

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?
Improve this answer
2
  • CASE2: "List_HolAbbr" are always values (dates). "rg" is valid. It is selected by the user as a parameter in the UDF. If the range doesn't contain anything to SUMIF on, it does nothing. Defined as double - I don't recall why they are defined as double.
    – mechengr02
    Dec 7, 2015 at 17:14
  • Case 1: I open the file on a cloud computer at work. Can't tell you if anything changed. IT is always changing things and screwing things up.
    – mechengr02
    Dec 7, 2015 at 17:18
1

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!

Improve this answer
1

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.

Improve this answer
0

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 :)

Improve this answer
0

I had the same problem and could solve it. Use the problems inspector in Information section. This will help you to remove invisible objects.

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .