Tables interfere with VBA range variables depending on scope

Question

An Excel file includes VBA-coded user-defined functions (UDFs) that are deployed in tables (VBA listobjects). Now, for reasons that escape me, if the UDF module contains Range variables that are declared outside the scope of any sub or function, I get a very dramatic warning when the file is opened: "Automatic error -- Catastrophic failure".

"Catastrophic" seems like an exaggeration because after the warning is dismissed, the file seems to work correctly. But I would still like to understand what the issue is. I have managed to replicate the issue with an MVC example as follows. I'm running Excel 2016 (updated) on Windows 10.

There are two tables (i.e. VBA listobjects): Table 1 lists "items" and Table 2 lists "item features" (both tables were generated by selecting the data and clicking Table on the Insert tab). Table 2 has a UDF called ITEM_NAME() in the field Item_Name that returns the item name as a function of the item ID, see the screenshot:

The function ITEM_NAME() is essentially a wrapper around the regular worksheet functions INDEX and MATCH, as in the following code:

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.
    Set mrngItemNumber = Sheets(1).Range("A4:A6")
    Set mrngItemName = Sheets(1).Range("B4:B6")
    ITEM_NAME = Application.WorksheetFunction.Index(mrngItemName, _
    Application.WorksheetFunction.Match(varItemNumber, mrngItemNumber))
End Function

So, to repeat, with this setup I get the Automation error when the file is opened. But the error disappears when I do any of the following:

1. Move the declarations into the scope of the function. This solution is not attractive since it requires many more lines of code, one for each UDF, and there are many.

2. Change the variable type from Range to something else, for example Integer (so the function will obviously not work).

3. Convert Table 2 to an ordinary range (i.e. remove the table). This is also an inconvenient solution since I really want to use the Table features for other purposes in my code.

4. Remove the function ITEM_NAME() from Table 2. (Obviously no attractive option..)

What's going on? Why do I get the error message? And why does the file still seem to work properly despite the warning? Is there a workaround that I've missed?

I suspect it might have something to do with how sheet objects and listobjects interact, but not sure. A possible hint is provided in this answer to another question:

If you want to reference a table without using the sheet, you can use a hack Application.Range(ListObjectName).ListObject.

NOTE: This hack relies on the fact that Excel always creates a named range for the table's DataBodyRange with the same name as the table.

Similar problems have been reported elsewhere (at Stackoverflow and Microsoft Technet), but not with this particular flavor. Suggested solutions include checking for broken references or other processes running in the background, and I've done that to no avail. I can also add that it makes no difference whether the function ITEM_NAME is entered after Table 2 is created rather than before; the only difference is that it uses structured references in that case (as in the screenshot above).

UPDATE: Inspired by @SJR's comments below I tried the following variation of the code, where a ListObject variable is declared to store the table "Items". Note that the Range declarations are now inside the scope of the function, and that only the ListObject declaration is outside. This also generates the same Automation error!

Option Explicit

Dim mloItems As ListObject

Public Function ITEM_NAME(varItemNumber As Variant) As String
' Returns Item Name as a function of Item Number.
    Dim rngItemNumber As Range
    Dim rngItemName As Range
    Set mloItems = Sheet1.ListObjects("Items")
    Set rngItemNumber = mloItems.ListColumns(1).DataBodyRange
    Set rngItemName = mloItems.ListColumns(2).DataBodyRange
    ITEM_NAME = Application.WorksheetFunction.Index(rngItemName, _
    Application.WorksheetFunction.Match(varItemNumber, rngItemNumber))
End Function

UPDATE 2: The problem now seems to be solved, but I'm not much wiser as to what actually caused it. Since no one could replicate (not even friends of mine who opened the same file on different systems), I began to think that it was a local issue. I tried repairing Excel and then even reinstalled the complete Office package from scratch. But the issue still persisted, both with my MCV files used to create the example above and the original file where I discovered the problem.

I decided to try to create a new version of the MCV example where, inspired by AndrewD's answer below, I used .ListObjects() to set the range instead of using .Range(). This did indeed work. I will probably adapt that solution for my work (but see my comments under AndrewD's question explaining why I might prefer .Range().)

In order to double check that this solution worked, I set about to create two new files, one to replicate my own example as described above, and one where the only difference would be the switch to ListObjects(). In the process, I noted that I had actually indented the Range declarations at the beginning of the code in my original file, like so:

Option Explicit

    Dim mrngItemNumber As Range
    Dim mrngItemName As Range

Public Function ITEM_NAME(...

Without thinking much about this, I created the new file but without indentation. So that would be an exact copy of the previous file (and the given example above), but without indentation. But behold, with this file I could not replicate the Automation error! After inspecting both files I noted that the only difference was indeed indentation, so I put the indentation back again in the new file expecting it to generate the Automation error again. But the problem did not reappear. So then I then removed the indentation from the first file (used to create the example above), and now the Automation error disappeared from that file as well. Armed with this observation, I went back to my real file where I first discovered the issue and simply removed the indentation there too. And it worked.

So to summarize, after removing the indentation of the Range declarations I fail to recreate the Automation error in any of the three files that had generated it before. And moreover, the problem does not reappear even if I put the indentation back in place again. But I still don't understand why.

Thanks everyone who took time to look at this and shared valuable ideas.

Answer 1

OK. This workaround should work.

If When it does, there are a few issues and caveats to address.

I'll also post explanations.

Install the code in the ThisWorkbook module.

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

  Dim rngCell As Range

  For Each rngCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    With rngCell
      If .FormulaR1C1 Like "*ITEM_NAME*" _
      And Left$(.FormulaR1C1, 4) <> "=T(""" _
      Then
        .Value = "=T(""" & .FormulaR1C1 & """)"
      End If
    End With
  Next rngCell

End Sub

Private Sub Workbook_Open()

  Dim rngCell As Range

  For Each rngCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    With rngCell
      If .FormulaR1C1 Like "*ITEM_NAME*" _
      And Left$(.FormulaR1C1, 4) = "=T(""" _
      Then
        .FormulaR1C1 = .Value
      End If
    End With
  Next rngCell

End Sub

Answer 2

On a purely code level, why declare modular-level variables to store the ranges when you set them every single time? If you were caching the references and only setting them if Nothing I could understand...but then you would use a Static to reduce the scope.

My preference would be to not bother with the modular (or local/static) variables, replace the Worksheet.Name reference with Worksheet.CodeName (less likely to be changed and, if you compile after a rename you get an error) and refer to the table ranges via the ListObject and ListColumns (in case the table size changes).

' Returns the item name for the requested item ID.
Public Function ITEM_NAME(ByVal ItemID As Variant) As String
    ITEM_NAME = Application.WorksheetFunction.Index( _
                      Sheet1.ListObjects("Table1").ListColumns("Item_name").DataBodyRange _
                    , Application.WorksheetFunction.Match( _
                          ItemID _
                        , Sheet1.ListObjects("Table1").ListColumns("Item_ID").DataBodyRange _
                        ) _
                    )
End Function

But the most robust solution would be to avoid a UDF and use =INDEX(Table1[Item_name],MATCH([@[Item_ID]],Table1[Item_ID]‌​)) (VLOOKUP may be slightly faster but INDEX+MATCH is more robust).