UDF using Application.Range reference fails when another workbook is being opened-CodePudding

I'm using the UDF which relies on the ListObject name passed to it. A user hasn't 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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

You can use the Application.ThisCell inside your UDF function to get the current cell being called. From this information, you can extract the current Workbook or Worksheet.

This returns the calling worksheet:

Application.ThisCell.Worksheet

This returns the calling workbook:

Application.ThisCell.Workbook