UDF referring to named table errors when another workbook is active

Question

edited for clarity now that solution has been identified

I am a VBA newbie building a UDF. This UDF involves a number of vlookup functions that reference excel tables on other worksheets in the workbook, such as:

Twirecol = Application.WorksheetFunction.VLookup(i, Range("iterationtable"), 2, False)

The trouble is, if another workbook is active, when excel recalculates the formula returns a #VALUE error.

I see many solutions for how to reference OTHER workbooks and worksheets in VBA and UDFs, but I do not know how to scope these table objects appropriately, so that they stay focused on the workbook in which the UDF exists. Note that I am looking for a solution that does not depend on worksheet name or workbook file name or path, as all of these may change over time.

Here's my name manager for this workbook:Names Manager

Here is the entire UDF code:

Public Function voltagedrop(trenchlength As Integer, intlength As Integer) As String
Application.Volatile

Dim TLX As Integer
Dim ILX As Integer
Dim TVD As Single
Dim IVD As Single
Dim VD As Single
Dim Twirecol As Integer
Dim Iwirecol As Integer
Dim i As Integer


' Extended length variables account for extra length at end of strings
TLX = trenchlength + 10
ILX = intlength + 10

i = 0

Do
i = i + 1
    Twirecol = Application.WorksheetFunction.VLookup(i, Range("iterationtable"), 2, False)
    Iwirecol = Application.WorksheetFunction.VLookup(i, Range("iterationtable"), 3, False)

    ' Calculate voltage drops
    TVD = Application.WorksheetFunction.VLookup(TLX, Range("trenchtable"), Twirecol, False)
    IVD = Application.WorksheetFunction.VLookup(ILX, Range("inttable"), Iwirecol, False)
    VD = TVD + IVD

Loop Until VD < 0.025

VD = 100 * Round(VD, 4)
voltagedrop = Application.WorksheetFunction.VLookup(i, Range("iterationtable"), 4, False) & ": " & VD & "%"    

End Function

Solution (thanks @DavidZemens)

(*David's complete answer is below, this is my summary)

If this had been a traditional named range, rather than a table, I could have called the range like so:

Twirecol = Application.WorksheetFunction.VLookup(i, ThisWorkbook.Names("iterationtable").RefersToRange, 2, False)

But, because tables act differently than named ranges (despite showing up similarly in the Names Manager), I need to call the range like this:

Twirecol = Application.WorksheetFunction.VLookup(i, ThisWorkbook.Worksheets("Background Tables").ListObjects("iterationtable").Range, 2, False)

However, my ideal solution avoids naming the sheet at all, in case the sheet name changes in the future, so it was demonstrated that I could use the sheet CodeName instead (in my case sheet1):

Twirecol = Application.WorksheetFunction.VLookup(i, Sheet1.ListObjects("iterationtable").Range, 2, False)

I have identified the range directly in this sample code for simplicity, but per David's recommendation my final code did use a set a variable for the range.

Answer 1

I am hoping there is an elegant solution such as...

thisworkbook.range("iterationtable")

Although this does not work.

Range isn't a property of the Workbook class, it's a property of the Worksheet class.

However, named ranges are accessible via the Names collection. If the Name is scoped to the Workbook (which I think is the default), you should be able to access it via ThisWorkbook.Names("iterationtable").RefersToRange.

If the Name is scoped to a specific worksheet, then you'll ned to do ThisWorkbook.__WORKSHEET__.Names("iterationtable")... instead, where __WORKSHEET__ is the containing sheet.

---

The above was written on the assumption that this was a Name object, but upon review of Matt's screenshot, it becomes apparent that this isn't actually a Name, but rather a ListObject (table).:

While these appear in the Names Manager, and are similarly accessible to named ranges, i.e.,:

MsgBox ActiveSheet.Range("table1").Address ' etc...

They are not a member of the Names collection (they're actually a part of the ListObjects collection) and so attempting to call on them like below will raise a 1004 error:

MsgBox ActiveSheet.Names("table1").Address

To resolve this issue, you need to fully qualify the Range object, i.e.:

ThisWorkbook.Worksheets("Background Tables").Range("iterationtable")

Or:

ThisWorkbook.Worksheets("Background Tables").ListObjects("iterationtable").Range

The reason that Range("iterationtable") sometimes works (i.e., when it's the ActiveSheet is well-documented and normal, expected functionality of an improperly scoped identifier: Range refers always to whatever sheet is Active at runtime, unless explicitly scoped otherwise.

This is a good primer on how to avoid the infamous 1004 error, but it boils down to the above: scope your objects appropriately, and ideally use variables to represent them.

Dim wsTables as Worksheet
Dim iterTable As Range
Dim trenchTable as Range
Dim intTable as Range

Set wsTables = ThisWorkbook.Worksheets("Background Tables") 'Declare the worksheet
With wsTables
    'Assigns each table's Range to a Range object variable:
    Set iterTable = .ListObjects("iterationtable").Range
    Set trenchTable = .ListObjects("Trench Table").Range
    Set intTable = .ListObjects("Int Table").Range
End With

With Application.WorksheetFunction
    Do
        i = i + 1
        Twirecol = .VLookup(i, iterTable, 2, False)
        Iwirecol = .VLookup(i, iterTable, 3, False)
        ' Calculate voltage drops
        TVD = .VLookup(TLX, trenchTable, Twirecol, False)
        IVD = .VLookup(ILX, iterTable, Iwirecol, False)
        VD = TVD + IVD
    Loop Until VD < 0.025

    VD = 100 * Round(VD, 4)
    voltagedrop = .VLookup(i, iterTable, 4, False) & ": " & VD & "%"
End With

And finally:

including avoiding references to the sheet on which the named ranges are located.

OK, so if the user changes the worksheet name from "Background Tables" to anything else, the above code will still fail. There are some ways to prevent that, such as Locking the sheet for editing, and/or hiding the worksheet (assuming the user doesn't also need to input data to the sheet, etc.), or referring to the worksheet by its CodeName rather than its Name. This takes advantage of the fact that, when referred to by CodeName, the Worksheet is always implicitly a part of ThisWorkbook. To find the Sheet's CodeName, it's in parentheses in the Project pane:

In the above solution, you would change this line:

Set wsTables = ThisWorkbook.Worksheets("Background Tables") 'Declare the worksheet

To:

Set wsTables = Sheet2 '<~~ The CodeName goes here, modify as needed!

While the sheet's CodeName is read/write (meaning a savvy user could change it, they'd need to do it via VBA or manually through the VBE, so this seems very unlikely in most cases).

Answer 2

Or perhaps activate the correct workbook before hand

Set wb = ThisWorkbook
wb.Activate
TVD = Application.WorksheetFunction.VLookup(TLX, Range("trenchtable"), Twirecol, False)

Answer 3

Did you try making all of your calls to the workbook by using the workbook name and sheet? Using only "Range("whatever")" will cause the program to look at the active worksheet of whatever workbook is currently active.

Try changing the calls to this format:

Twirecol = Application.WorksheetFunction.VLookup(i, CorrectWorkbookName.CorrectWorksheetName.Range("iterationtable"), 2, False)

Where you change "CorrectWorkbookName" to the name of the book with the named range and change "CorrectWorksheetName" to the name of the sheet containing the named range.