0

I'm using the UDF which relies on the ListObject name passed to it. A user doesn't have to know where a referenced table (ListObject) is placed in the workbook, since it is rather big and complex workbook. For this reason, the UDF refers to the table by its name, omitting the worksheet's name, as follows:

Set Obj = Application.Range(TableName).ListObject

This works perfectly as long as the code is in the active workbook. It doesn't work, if another workbook is activated. For some reason, when another workbook is being opened, the UDF's VBA code is being fired, and it fails resulting in #VALUE outputs all the way across my workbook. I have tried multiple ways to reference a list object explicitly, and the only working one seems to be like

WorkbookObject.WorksheetObject.ListObject(TableName)

However, as I've mentioned above, I'm trying to avoid using a Worksheet reference.

Is it ever possible to reference a list object in non-active workbook through an explicit reference, without a need to use a Worksheet reference?

If not, what is the best way to prevent a UDF from unnecessary runs when another workbooks are being opened? I've tried to force re-calculating the UDF using Application.Volatile method, but due to the size of the workbook, it slows it down dramatically. Any help are highly appreciated.

Clarification based on the @BigBen comment Because most part of the workbook acts behind the scene, the table's name isn't passed on as an explicit value, instead the UDF takes user inputs (strings) and decides on the name of the table to call based on these inputs.

Edit

Thanks all. I've now resolved the issue, based on the Tim Williams's suggestion as follows:

    Dim ws As Worksheet, LObj As ListObject
    If ActiveWorkbook Is ThisWorkbook Then
        Set Obj = Application.Range(Tabl).ListObject
    Else
        For Each ws In ThisWorkbook.Worksheets
            For Each LObj In ws.ListObjects
               If LObj.Name = Tabl Then Set Obj = LObj: Exit For
            Next
        Next
    End If

Excuse me for being so stupid before.

Improve this question
5
  • Why not just make it a ListObject parameter and pass the actual table, not the table name? Commented Dec 28, 2021 at 14:13
  • @BigBen Actually, it's slightly more complex than described, because the table's name isn't passed on as an explicit value, instead the UDF takes user inputs (strings) and decides on the name of the table to call based on these inputs. Commented Dec 28, 2021 at 18:56
  • 1
    Re the UDF takes user inputs (strings) and decides on the name of the table, you'll also need to derive which workbook the table is in, and reference that (BTW Application.Range always refers to the Active Workbook/Sheet). If the table is always in the WB containing the code, then it's ThisWorkbook. If not, and the UDF call is always in the same WB as the Table, then it's ThisCell.Workbook.Parent Commented Dec 28, 2021 at 20:28
  • That should of read ThisCell.Worksheet.Parent Commented Dec 28, 2021 at 20:45
  • If the inputs are are strings you will have to make the UDF volatile or suffer from the UDF not recalcing when it should. Also you will have to cycle thru all the worksheets in all the open workbooks looking for the name of the table and hope that there is only 1 table with that name, or prompt for clarification if more than one exists. Commented Dec 28, 2021 at 21:33

2 Answers 2

Reset to default
2

As BigBen says I would recommend passing the Table itself as a parameter to the UDF. This solves problems like volatility, changing active worksheet. book etc.

=SumTable(Table1)

The parameter should be defined as a Range. If you need to actually get the ListObject you can get it from the Range

Function SumTable(MyTable As Range)
Dim oBj As ListObject
Set oBj = MyTable.ListObject
SumTable = oBj.Name
End Function
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. This would have solved the issue in most cases, but this one is slightly different. I've now clarified the issue in question.
1

You can create a function to return the required listobject from ThisWorkbook, based on the list's name name:

Set Obj = GetListObject(TableName)

Function:

Function GetListObject(loName As String) As ListObject
    Dim ws As Worksheet, lo As ListObject
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            If lo.Name = loName Then
                Set GetListObject = lo
                Exit Function
            End If
        Next lo
    Next ws
End Function
Improve this answer

1 Comment

I'm wondering why I didn't think of that obvious solution before. Iterating a short collection of worksheets is for sure better than waiting for 1 minute each time while a volatile UDF is recalculating. And it only have to be called upon failure of the default method.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.