This question is about a peculiar occurence of Excel's somewhat mysterious error message "Automation Error -- Catastrophic Failure". It occurs when I open a specific Excel file that has VBA code where some range variables are declared at the modular level, outside of any sub or function. Details follow.
UPDATE: After further troubleshooting I think I have isolated the problem. I might post a new question since a complete update would probably be too confusing. END UPDATE
UPDATE 2: Now I've have posted a new question with some additional clarification.
I have an Excel file with VBA code used to track inventory. On one sheet I have a table with merchendise items with several fields such as item number, item name, etc. I have written several user-defined functions that return various item properties as a function of the item number. These UDFs are based on a combination of the regular worksheet functions INDEX and MATCH.
In particular, I have a function called ITEM_NAME() as illustrated in the screenshot (table minimized for simplicity):
The code for ITEM_NAME() follows; note that the two Range variables are declared outside the scope of the function (the function ITEM_NUMBER_EXISTS called by ITEM_NAME basically checks the validity of the provided argument, but the code is not shown here):
Option Explicit
Dim mrngItemNumber As Range
Dim mrngItemName As Range
Public Function ITEM_NAME(varItemNumber As Variant) As String
' Returns Item Name as a function of Item Number.
' Return value error if argument is invalid:
If Not ITEM_NUMBER_EXISTS(varItemNumber) Then
ITEM_NAME = CVErr(xlErrNA)
Exit Function
End If
' The rest is executed only if the argument is a valid item number.
Set mrngItemNumber = Range("A4:A6")
Set mrngItemName = Range("B4:B6")
ITEM_NAME = Application.WorksheetFunction.Index(mrngItemName, _
Application.WorksheetFunction.Match(varItemNumber, mrngItemNumber))
End Function
Since I have quite a lot of functions (not shown here) in the same module, I prefer to declare all the range variables as modular variables in the beginning of the code, as in the example above, and then assign them with the Set keyword inside the respective functions (and avoid having to declare them separately in each function).
Now, with the code written as in this example, I get the annoying error message when the file is opened. But the error disappears if I do any of the following:
1) change the variable type of mrngItemNumber and mrngItemName from Range to something else, such as Integer (although this obviously destroys the functions); or
2) move the declarations into the scope of the function ITEM_NAME.
After the error message appears, I can't really detect any actual problems with the file, it seems to be working normally.
To add to the confusion, I fail to replicate the issue with my minimal example above. But the problem has now occured with two different files: The first time I just suspected it was a compatibility issue since the original file was created on Office for Mac 2008, but the second time I'm working with a file that was built from scratch in Excel 2016 on Windows.
I've seen similar problems reported elsewhere (at Stackoverflow and Microsoft Technet), but none with this particular flavor. Solutions suggested include checking that external references are working, and that no Excel process is running in the background. I have checked for both these issues but they don't seem to be connected to what I am experiencing. I've also checked that I have the latest update of Excel. (The file has one external reference (to personal.xlsb), and it is working as normal.)
I can't figure out why I get this error?
