Hello,

I have worked on a file for long time using Excel 2003. I have few macros and custom functions written. I decided to use another computer to do some additional work on this file - mainly new formulas and outline, but no code writing. The file was opened as 2003 and was saved in the same fashion, was not converted to 2007 at any point.

I saved it and closed it. I then opened it on my original computer and after opening it gave me "Automation Error - Catastrophic Failure" message without an error number. Right after that it sent me to VBA screen and instead of having my file name under the Project Tree, it has "VBAproject". I click on it and can see my code and my forms, but the worksheets are not label properly plus they have this little blue icon next to them.

I tried to open the file again by clicking Disable macros, and no error was given but obviously can't do much with the file.

One thing I read around is about sharing and references. This file sharing options were turned off so that's not it. I then realized under VBA-Tools-References that the office 2007 is referencing different files for some of the selections, like Visual Basic For Applications, Microsoft Excel 12.0 Object Library, etc. I copied those files to the proper folders on the 2003 computer but that didn't do the trick. When I click disable macros and go to check the references in the 2003 version everything looks normal.

Any ideas? I did quite of work on this file so redoing this is the last resort but I hope I don't have to go that route.

Thank you in advance!

Hi,

Automation errors usually occur when a workbook is trying to run some macro that contains objects they are not included in the references section of the VBA editor. One example that would produce this is having some controls on a form which require a specific DLL to be correctly registered on a client PC. If these DLLs are on your pc, the workbook is fine. Put this same workbook on another PC without the DLLs, and an automation error will occur.


Hope this helps.

Thank you Jennifer,

So tell me what is the best way to check what dll is crashing? Should I simply check which files are referenced in the VBA-Tools-References and see of those files are on the other computer?

Whilst it might be a reference issue what you describe seems much more serious than the usual problems associated with a missing or incompatible reference.

It's not clear from your original description which Excel version you are currently using, though I assume 2007 as you mentioned "12.0" in the references.  FWIW those references are normal, indeed all 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 refers to the Excel version.

Can you open the file in Safe mode, from the Run command
"excel.exe /safe" without quotes
(you might then need to change security settings via the UI to open a file with VBA)

Can you open the file in Excel 2003

Is there anything you can think of in the file that's not typical about the xls/a(?) originally saved in 2003.

Regards,
Peter Thornton

Hello Peter and thank you for the response.

I am opening the file with Excel 2003 (on Windows XP if it matters). I have worked on this file for long time and just recently did some cosmetic work on Excel 2007 on another computer. I did some VBA coding at that point but it was minor and primarily for formatting help.

The issue is that now I open the file in 2003 and gives me the error. What is even more absurd, I re-worked an old file (which was never opened on 2007) and that file still gives the same error on few of my company's computers that are all running on 2003.

The current references look like this:

Visual Basic For Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Microsoft Forms 2.0 Object Library

I was able to uncheck the 3rd and 4th of these but still the file gives an error. When I open the file on 2007 the 11.0 becomes 12.0 and no other changes.

I can't think of any special references since all my work has been done on 2003 with few minor changes in 2007. This error drives me crazy since I can't do anything with the file. I will try this safe mode opening. I have never opened a file through VBA but will try.

Thanks

When you round trip a VBA file saved on an old version to a new, and back again reference problems can be introduced, even if you haven't done anything apart from save.

That occurs when the reference has been updated to the new version then not found in the old version's system. Whilst that can make some VBA stuff fail, normally not with the serious consequences you described. However the ref's you show this time look fine for the file when opened in 2003, indeed exactly what I'd expect. Actually I'm surprised you were able to remove two of the ref's.

The cosmetic changes you made in 2007 sound like formats, some of these may cause problems when reopened in 2003, though normally the compatibility checker will pick these up.

I'm a bit confused though, if I follow you are saying you've got problems with a file that's never even been opened in 2007. Also if I follow the exact same file opens fine in some 2003 systems but not in others.  Does the file include any non standard forms controls, any controls that you would have needed to add to the toolbox for example.

Regards,
Peter Thornton

Hi Peter,

Sorry to confuse you here - I am talking about 2 files.

First file - was used in 2003, then saved and more formatting work was done on 2007, after that this file crashed on my personal computer and one my coworker's as well. We can says this "round-trip" is the cause.

The second file is technically a copy of the first before it was opened on 2007. I re-did all of the work that I previously did on 2007 on my 2003 excel and that file is works fine on my computer but is crashing on some of my coworkers computers. This file was never opened on 2007.

Got me thinking though - I have loaded some custom toolbars on my excel that I am not sure all people within my company have. I don't know if I am going in the wrong direction here. Still I got a file now that I can open along with 2-3 other co-workers, but majority of the people can't and this file was not opened on 2007 but the error (at least the message) is exactly the same as the error I myself was getting when doing this 2003-2007 round trip.

Thanks

Curiouser and curiouser!

Do you have an original copy of the file that you neither modified in 2007 nor 2003, if so does that work on all machines.

Are there any Links ?

Any non standard controls that would have needed to be added to the Toolbox ?

Can you open the problematic files on the problematic machines in Safe Mode (see my first post) ?

Any references marked MISSING ?

Any Open event code, if so hold Shift when opening.

Custom Toolbars, do you mean "attached" to the workbook or created on demand. FWIW I really think best never to "attach" custom toolbars, instead create the custom toolbar in the workbook's open event and delete in the close. That said I can't think of any reason for a custom toolbar to cause the problems you described.

Regards,
Peter Thornton

Glad I got your curiosity! :)

The file I worked on from scratch over a year now. It was always done on 2003.

If you mean links to outside sources - no. There are hyperlinks within the file itself, but nothing fancy on that end.

I don't think any non-standard controls are added to the Toolbox.

I can open even the file that crashes on my computer by selecting Disable Macros. I tried the safe mode as well with the file that always crashes and besides the message that the macros are disabled in the beginning everything else is fine. So I assume all my coworkers would be able to open it with Safe mode as well.

No MISSING references. Unless it is hiding somewhere else, but I would assume the error message would let me know that.

No Open event code - that includes all of the tabs as well and no change event code.

The custom toolbars are widely used by my company and never caused many errors. But you are right, I don't think that is the error.

I would gladly send you the file, but it is full with company information. What is even more annoying is that I work in a different location from the rest of my coworkers that have issues with this file so I can't see what is going on there.

Before I did the changes with 2007, a month ago I had this issue - after my regular update on the file, the other person tried to open the file but got the same error. This was the first time when we faced that error. While waiting for him to hear from IT, I did some more updates to the file (didn't remove anything old and didn't add any code) and sent him the most recent copy. Suddenly it worked for him. Bizarre! 2 weeks later I did the updates on 2007 and I got the same error myself. For a year working with this file neither of us had any problems. It just recently started. I think it must be something not necessary related to this file, but rather with my excel, which was activated by another file and this file doesn't like it. Is that too crazy of an idea?

Thanks

If it weren't for one thing you mentioned I'd suspect the problem was most likely corruption in the VB project, based on the fact all appears fine when macros are disabled. Yet you said before the file opens fine in some systems but not in others, which suggests some sort of reference problem or possibly links.

As I can't see your workbook I suggest the best thing to do is rebuild it. Normally it shouldn't take very long, briefly -

If you have never had code behind the sheet modules simply select all sheets and copy to a new workbook (from the sheet tab context menu). If you have had code in the sheet modules, select the UsedRange of each sheet copy and paste to similarly named sheets in the new workbook.

Depending on how you copied you may have links back to the old workbook. Make a little macro to Replace the links in formulas with "". Chart series formulas too if applicable. There may be other stuff to look at like Names and Styles, and I've probably forgotten something!

In the VBE drag code modules from old to new, or perhaps better still Export and Import. Copy any ThisWorkbook / sheet module code from old to new

Suggest don't attach the custom toolbar if that's what you had before, create / delete each time on load / close.

Regards,
Peter Thornton

Well someone from work helped me figure this thing out. I am pretty happy and upset at the same time because the fix was minor! But still a little unclear to me why the error happened occasionally and not to all computers using 2003.

When the file is opened and crashes it takes you to the VBA screen without further notification. The Locals window has one of the modules includes by itself. When I double-clicked that module (and the rest of them) I saw that the code is there and didn't bother with it, but if I would have gone down I would have noticed that one of the custom functions is highlighted in yellow. Excel was trying to tell me what is the error but since it was a custom function it didn't know. And the function is very simple:

Function CellHasFormula(c)
    CellHasFormula = c.HasFormula
End Function

At this point all others that are reading this with similar problem shouldn't worry about my actual fix, but rather check your functions to make sure all are working properly. That is my advice :)

This function worked fine at the beginning but at one point I had it reference a range of cells rather than one cell. So for some reasons, when excel was auto-calculating it crashed (on opening) on some computers but not all.

The error was fixed when I modified the function definition as this: Function CellHasFormula(c as Range)

I knew it must be something very small and am glad my co-worker found it!

Thank you Peter for the support! I guess the DLL discussion sent us in a completely different direction but that happens sometimes when Excel doesn't know what error to display :)

Still slightly odd. As written the old CellHasFormula function shouldn't error, even if at one time the input arg' c had pointed to more than one cell. If all cells had or hadn't a formula the function would have returned True/False. If mixed cells it would have returned Null (to return as zero in the formula).

There probably was corruption related to the way the variable c is stored and referred to internally. I'm pretty sure that would have been fixed by rebuilding the project as suggested, however much better to have found it as you have done and fix by fully declaring the c (in affect giving it a new argument even if same name).  Only for curiosity, if you have an old version of the file, try exporting the code module, removing it, then reimporting it.

I trust you recall from my first post I was a little sceptical of it being a DLL problem :-)
Thanks for the feedback.

Regards,
Peter Thornton

Peter,

I exported the whole module, removed it and open the file again - no crash. Imported the module - crashed again. I even renamed "c" but still no luck.

The interesting thing is that the file that crashes on my computer was previously opened on 2007 and it crashes on all of co-workers computers, and the file that I didn't open on 2007, worked fine on mine and crashed on few of my coworkers computers have the same cause of error.

Excel is not perfect and fix my references, that is what I get out of this :)

Thanks for your help and wise thoughts! I am sure that if I could have shared the file you would have seen it long ago.

I had this problem, and i solved it by stopping the debugger when you open the sheet. an easy way to stop the debugger by pretending that you change some code in the VB code. eg. try to delete a line and return it back. 

this fixed my problem.

+1 for this finding.  I had the same problem, a user-defined function causing automation error / catastrophic failure.  Difference was (for me) Excel did NOT open the module that contained the "problem" function.  I had to go through my modules looking for the function highlighted yellow (and it was).  

Strange thing is, the function WORKS!  If I comment out the function, save, open, it opens fine.  Uncomment and run and it works fine.  Save, open, ERROR!  As long as the function is commented out, it saves and opens fine, and I can uncomment the function when I need to use it... which is a hassle.  I didn't even write the function, just found the essence of it on the internet, and modified for my use (see below).  

Sounds to me like there's something wrong with Excel that it corrupts user defined functions at random.  This is the 2nd time my workbook has gotten the automation error for this same function.  Last time was several months ago.  Everything was fine for me and the other 2 ppl using it up until this morning.  I blame Mercury Retrograde...

Public Function IsURLGood(url As String) As Boolean
    Dim request As New WinHttpRequest
    IsURLGood = False

On Error GoTo URL_Not_Found
    request.Open "GET", url
    request.send

    If request.Status = 200 Then IsURLGood = True

URL_Not_Found:
End Function

I have the exact same problem, in my case I have an Excel template with macros.

when I open the template in edit mode (rightclick + Open instead of New), no problem

when I doubleclick the file it opens as it should but gives the Automation Error - Catastrophic Failure, without an error code.

the vba editor opens, but I have to search for the module with the breakpoint.

Turns out it is a custom function that is run when the worksheet recalculates.

when I click the stop button I can see that the calculation continues and in the end when I go to the sheet using the custom function, all but one cell has the right value, and one shows an error.

recalculating runs without problems, the error only happens when I open the workbook.

How can we prevent this behaviour?