Automation Error - Catastrophic Failure EXCEL VBA

Question

I have a workbook which is throwing this error on opening. When it does and I open the VBA module, the current line is the definition of a sub. But the only option is to kill the whole Excel process.

I've got custom document properties, I've got embedded combo-box controls, I have no clue what it might be, and Excel isn't helping.

However, when I open the same file on another computer - it doesn't throw the error.

Does anyone have any experience or advice with this kind of error?

Here's the Open code, but the 'Show Next Statement' command doesn't point here when the error occurs:

````

Private Sub Workbook_Open()
    Dim ans


    If Range("currentstatus") Like "*Ready for Year-End Preparation*" Then
        ans = MsgBox("This workbook is ready for Year-End Preparation" & vbCrLf & "Would you like to begin?", vbYesNo)

        If ans = vbYes Then
            Range("Phase") = "Year-End"
            SheetsSet 3
        End If
    End If

    'Exit Sub

    If Range("Phase") = "Commissions" Then

        If Range("currentstatus") Like "*RVP/Dept Head Approved*" Then
            ans = MsgBox("Commissions have been approved for " & Range("applicablemonth") & vbCrLf & "Would you like to enter data for the new period?", vbYesNo + vbQuestion)
            If ans = vbYes Then


                Range("ApplicableMonth") = Format(DateAdd("m", 1, CVDate(Range("applicablemonth"))), "YYYY-MM")
                Range("CurrentStatus") = "Ready for Data Entry for " & Range("ApplicableMonth")

                ' now reset the summary page
                Prot False, "Commission Form Summary"
                Range("SalesPersonComplete") = Range("Summary")
                Range("RVPComplete") = ""
                Range("BrMgrComplete") = ""
                Prot True, "Commission Form Summary"

                Sheets("Menu").Select
                '                MsgBox "Begin."
            End If
        End If


    End If

End Sub

What sheet is Range("currentstatus") in? What sheet are the other ranges in? if you save the file not on the expected sheet it will cause problems. You should get used to explicitly refering to the sheet of the range you want to work with this way you eliminate all ambiguity. For example if Range("Phase") is on Sheet1 use `Sheet1.Range("Phase")`, Also, if you press F8 it will step through the code line by line to the line that actually throws the error — paul bica, Aug 08 '15 at 02:27
Thank you very much ! I shall try that, although I believe these ranges have Workbook scope. — George W, Aug 08 '15 at 02:50
Thanks for the advice. Right now I've found a temporary solution so I'm waiting for the problem to recur. The solution doesn't make sense but here it is: When the error occurs I click 'debug'. Then I give the password for the project and it opens to a function definition for a function which I happen not to need, close the project, and things are back to normal. ?! Seems very weird, but I'll take it. — George W, Aug 08 '15 at 14:43
Any improvement in your solution? This started to annoy me too! — Felipe, Aug 03 '17 at 15:06

score 4 · Answer 1 · answered Mar 22 '16 at 20:32

4

I had this message earlier today and it was due to another instance of Excel being open as a background process (the background process had previously opened the file in question, so it must have been something to do with that). Once I closed the other instance the problem disappeared.

It might be worth checking 'Task Manager' > 'Background processes' to see if that's the case.

answered Mar 22 '16 at 20:32

Chris Mack

5,148
2
12
29

Thanks for the suggestion. Since my fix, the error has not recurred. – George W May 06 '16 at 19:27
Which version of Excel you use? I started using 2016 and now this problem is annoying me! – Felipe Jun 29 '17 at 02:46
I know this is an old response, but this just happened to me after trying to open the VBA editor in Excel; looking for answers, I came across yours. Looked at my background processes--no instance of Excel is even listed there. I decided to close the VBA window, and reopen, and the error did not occur again. Seems it's still a mystery! – d4rk_1nf1n1ty Jun 18 '19 at 18:15

score 1 · Accepted Answer · answered Sep 13 '21 at 08:50

This sounds like a Voodoo procedure, but what helps when I got this error is to edit any of the VBA code (for example in some module add a linebreak and remove it) and then save the workbook. Maybe it's some kind of caching issue in my case but I thought it might help some of you too.

score -1 · Answer 3 · answered Jun 20 '17 at 18:34

-1

Double-check your file extension. Excel spreadsheets with macros embedded need a *.xlsm extension, not *.xls.

Total 'for-dummies' answer, but I just made this mistake myself.

answered Jun 20 '17 at 18:34

NathanDetr0it

11

Automation Error - Catastrophic Failure EXCEL VBA

3 Answers3

Linked