Tables interfere with VBA range variables depending on volume

Question

An Excel file contains VBA-encoded user-defined functions (UDFs) that are deployed in tables (VBA listobjects). Now, for reasons that avoid me, if the UDF module contains range variables that are declared outside the scope of any subfunction or function, I get a very sharp warning when I open the file: "Automatic error - catastrophic failure."

"Catastrophic" seems like an exaggeration, because after the warning is rejected, the file seems to be working correctly. But I still would like to understand what the problem is. I was able to reproduce the problem with the MVC example as follows. I am running Excel 2016 (updated) on Windows 10.

There are two tables (for example, VBA listobjects): Table 1 contains lists of “elements” and Table 2 lists of “element functions” (both tables were generated by selecting data and click Table on the Insert tab). Table 2 has a UDF called ITEM_NAME() in the Item_Name field, which returns the item name as a function of the item identifier, see screenshot:

The ITEM_NAME() function is essentially a wrapper around the usual INDEX and MATCH sheet functions, 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 setting, I get an Automation error when opening a file. But the error disappears when I do one of the following:

• Move the ads to the scope of the function. This solution is not attractive, since it requires much more lines of code, one for each UDF, and there are many.

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

• Convert table 2 to the normal range (i.e. delete the table). This is also an inconvenient solution, since I really want to use the Table functions for other purposes in my code.

• Remove the ITEM_NAME() function from table 2. (Obviously, there is no attractive option.)

What's happening? Why am I getting an error message? And why is the file still working fine despite the warning? Is there a workaround that I missed?

I suspect this may have something to do with how sheet objects and object lists interact, but are not sure. A possible hint is given in this answer to another question:

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

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

Similar issues have been reported elsewhere (at https://stackoverflow.com/a/26825 / ... and Microsoft Technet ), but not with this particular taste. Suggested solutions include checking for broken links or other processes running in the background, and I did it to no avail. I can also add that it doesn't matter if the Item_Name function is Item_Name after creating table 2, than before; the only difference is that in this case it uses structured links (as in the screenshot above).

UPDATE: Inspired by @SJR's comments below, I tried the following code variant, which declared a ListObject variable to store the Elements table. Note that range declarations now fall within the scope of the function and that only the ListObject declaration is outside. It 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: Now the problem is resolved, but I'm not much wiser about what actually caused it. Since no one could replicate (even my friends who opened the same file on different systems), I began to think that this was a local question. I tried to restore Excel, and then even reinstalled the full Office suite from scratch. But the problem still persisted, both with my MCV files used to create the example above, and with the source file where I found the problem.

I decided to try creating a new version of the MCV example, where, inspired by AndrewD below , I used .ListObjects() to set the range instead of using .Range() . It really worked. I can probably adapt this solution for my work (but see my comments in the AndrewD question explaining why I prefer .Range() .)

To check if this solution works, I decided to create two new files, one of which will replicate my own example, as described above, and one where the only difference will be the transition to ListObjects() . In this process, I noticed that I was actually backing away from Range declarations at the beginning of the code in my source file, for example:

 Option Explicit Dim mrngItemNumber As Range Dim mrngItemName As Range Public Function ITEM_NAME(... 

Without thinking about it, I created a new file, but without indentation. Thus, it will be an exact copy of the previous file (and the above example), but without indentation. But now, with this file, I could not replicate the Automation error! After checking both files, I noticed that the only difference was really indentation, so I again indent the new file, expecting it to generate an Automation error again. But the problem did not appear again. So, then I removed the indent from the first file (used to create the example above), and now the auto-disappear error also disappeared from this file. Armed with this observation, I returned to my real file, where I first discovered the problem and simply deleted the indentation there. And it worked.

So, to summarize, after removing the indent of the Range declaration, I cannot recreate the Automation error in any of the three files generated earlier. And besides, the problem does not appear again, even if I reinsert the indent. But I still don’t understand why.

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

Answer 1

Declaring module level variables simply to store two lines in each UDF, which would otherwise be required, is really bad coding practice. However, if this is your thinking, why not go all the way and save four lines in UDF, while avoiding installing in each of them!

You can do this using pseudo-constant functions, as shown in the following code:

 Option Explicit Private Function rng_ItemNumber() As Range Set rng_ItemNumber = Sheet1.Range("A4:A6") End Function Private Function rng_ItemName() As Range Set rng_ItemName = Sheet1.Range("B4:B6") End Function Public Function ITEM_NAME(varItemNumber As Variant) As String ' Returns Item Name as a function of Item Number. With Application.WorksheetFunction ITEM_NAME = .Index(rng_ItemName, .Match(varItemNumber, rng_ItemNumber)) End With End Function 

Cost, of course, is the invoice of a function call.

---

If you plan to use the ListObject class for the final design, then why not use it now, and use dynamic named ranges (in this case, hard-coded ranges are used, so it actually works as it is - they should be replaced with named ranges) :

 Option Explicit Private Function str_Table1() As String Static sstrTable1 As String If sstrTable1 = vbNullString Then sstrTable1 = Sheet1.Range("A4:B6").ListObject.Name End If str_Table1 = sstrTable1 End Function Private Function str_ItemNumber() As String Static sstrItemNumber As String If sstrItemNumber = vbNullString Then sstrItemNumber = Sheet1.Range("A4:A6").Offset(-1).Resize(1).Value2 End If str_ItemNumber = sstrItemNumber End Function Private Function str_ItemName() As String Static sstrItemName As String If sstrItemName = vbNullString Then sstrItemName = Sheet1.Range("B4:B6").Offset(-1).Resize(1).Value2 End If str_ItemName = sstrItemName End Function Public Function ITEM_NAME(varItemNumber As Variant) As String 'Returns Item Name as a function of Item Number. Dim ƒ As WorksheetFunction: Set ƒ = WorksheetFunction With Sheet1.ListObjects(str_Table1) ITEM_NAME _ = ƒ.Index _ ( _ .ListColumns(str_ItemName).DataBodyRange _ , ƒ.Match(varItemNumber, .ListColumns(str_ItemNumber).DataBodyRange) _ ) End With End Function 

Once the logic / construction is ready, you can replace functions using constants at the module level with the same name if the speed is critical, and you need to return the service data of the function call. Otherwise, you can leave it as it is.

Note that using static variables is not required, but should reduce the execution time. (Static variables could also be used in the first example, but I left them to be short.)

There is probably no need to extract table names into pseudo-constants, but I did this for the sake of completeness.

---

EDIT: (v2)

Following Egalth, two brilliant sentences lead to the following code, which eliminates the need for named ranges, or even hardcoded cell addresses , in general, because we use the built-in ListObject table dynamism by itself.

I also changed the parameter name to match * the corresponding column header name, so when the user press Ctrl + Shift + A a hint about which column to use. (This tip and, if required, more information on how to add tooltips for Intellisense tools and / or get the description that appears in the Function Arguments dialog box can be seen here.)

 Option Explicit Private Function str_Table1() As String Static sstrTable1 As String If sstrTable1 = vbNullString Then sstrTable1 = Sheet1.ListObjects(1).Name ' or .ListObjects("Table1").Name str_Table1 = sstrTable1 End Function Private Function str_ItemNumber() As String Static sstrItemNumber As String If sstrItemNumber = vbNullString Then sstrItemNumber = Sheet1.ListObjects(str_Table1).HeaderRowRange(1).Value2 End If str_ItemNumber = sstrItemNumber End Function Private Function str_ItemName() As String Static sstrItemName As String If sstrItemName = vbNullString Then sstrItemName = Sheet1.ListObjects(str_Table1).HeaderRowRange(2).Value2 End If str_ItemName = sstrItemName End Function Public Function ITEM_NAME(ByRef Item_ID As Variant) As String 'Returns Item Name as a function of Item Number. Dim ƒ As WorksheetFunction: Set ƒ = WorksheetFunction With Sheet1.ListObjects(str_Table1) ITEM_NAME _ = ƒ.Index _ ( _ .ListColumns(str_ItemName).DataBodyRange _ , ƒ.Match(Item_ID, .ListColumns(str_ItemNumber).DataBodyRange) _ ) End With End Function 

Pay attention to using .Value2 . I have always used .Value2 since I learned about performance drag and drop and other issues caused by implicit type conversions performed when using .Value (or if you rely on it as the default property).

_{* Be sure to update the column heading names in the code when the project logic / project is completed.}

---

EDIT: (reboot)

After re-reading my own comments on your Question post, I noted this one :

In the end, I could take this approach, but I'm still in the process of designing and moving the columns around a lot, so the index number can also change

While the last example above allows you to dynamically change header names, moving / pasting columns changes indexes, requiring code changes.

It looks like we're back to using named ranges. However, this time we only need static ones pointing to the column headers.

It also turns out that for this new case, static variables are the bad idea at the design stage. Since column indexes are cached, inserting a new column breaks the UDF until the project is reset.

I also included an abridged version of the table link without the table from the quote in your posted Question:

 Option Explicit Private Function str_Table1() As String str_Table1 = Sheet1.ListObjects(1).Name End Function Private Function str_ItemNumber() As String With Range(str_Table1).ListObject str_ItemNumber = .HeaderRowRange(.Parent.Range("A3").Column - .HeaderRowRange.Column + 1).Value2 End With End Function Private Function str_ItemName() As String With Range(str_Table1).ListObject str_ItemName = .HeaderRowRange(.Parent.Range("B3").Column - .HeaderRowRange.Column + 1).Value2 End With End Function Public Function ITEM_NAME(ByRef Item_ID As Variant) As String 'Returns Item Name as a function of Item Number. Dim ƒ As WorksheetFunction: Set ƒ = WorksheetFunction With Range(str_Table1).ListObject ITEM_NAME _ = ƒ.Index _ ( _ .ListColumns(str_ItemName).DataBodyRange _ , ƒ.Match(Item_ID, .ListColumns(str_ItemNumber).DataBodyRange) _ ) End With End Function 

Please note that you cannot use Item_name for one of the named ranges, as this is the same as UDF (the case is ignored). I suggest using a bottom underscore, such as Item_name_ , for your named ranges.

---

All of the above methods also resolved the original question that you had. I am waiting for the latest pieces of information to give a reasonable assumption about why this problem arose in the first place.

Answer 2

OK This workaround should work.

If If so, there are several issues and reservations.

I will also post explanations.

Install the code in ThisWorkbook .

The 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 3

At the pure code level, why declare modular level variables to store ranges when you set them each time? If you cached links and only installed them, if you didn’t understand anything ... but then you would use Static to reduce the area.

My preference would be to not worry about modular (or local / static) variables, replace the Worksheet.Name link with Worksheet.CodeName (less likely to change and if you compile after renaming, you will get an error message) and refer to ranges of tables through ListObject and ListColumns (in case of resizing the table).

 ' 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 reliable solution would be to avoid UDF and use =INDEX(Table1[Item_name],MATCH([@[Item_ID]],Table1[Item_ID]‌​)) (VLOOKUP may be a little faster, but INDEX + MATCH is more reliable).