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.

What's the rest of the function? Although yes, your guess at the end is right. You should add the worksheet (and/or workbook if you have multiple open) before the range, i.e. `...Vlookup(i, worksheets("Sheet1").Range("iterationtable"),...` — BruceWayne, May 15 '17 at 15:58
Thanks @BruceWayne. Edited the original post to include the entire code. Works great when workbook is active. ...thisworkbook.range("iterationtable") does not work... rather, it gives me a "compile error: method or data member not found". — Matt Cohen-Price, May 15 '17 at 16:26
Also, in regards to at the worksheet name, I'm attempting to keep this function as generic as possible so future modifications by coworkers less familiar with VBA don't accidentally break it, including avoiding references to the sheet on which the named ranges are located. Is there a reason to identify the worksheet, considering that the range is named? — Matt Cohen-Price, May 15 '17 at 16:36
are the named ranges, workbook or worksheet scope? Look in the name manager. If they are workbook scope it should work, if not then you will need to name the sheet. — Scott Craner, May 15 '17 at 16:52
Please specify whether the name is scoped to worksheet or workbook. This will probably make a difference. Please also update with any additional troubleshooting you've done, and a screenshot of your "Names Manager" showing clearly that such a name does exist, and to what it refers. — David Zemens, May 15 '17 at 20:47
in your troubleshooting attempt, do `Msgbox range1.Address` -- .value would return an array and likely raise a Type Mismatch error. — David Zemens, May 16 '17 at 02:24
Note also that WorksheetFunction.Vlookup will raise an error if value not found. is that possibly the problem? — David Zemens, May 16 '17 at 02:25
@davidzemens awesome!! great catch. I won't have time to implement this until later today or tomorrow but I will ASAP and confirm that it works. I imagine that it will. — Matt Cohen-Price, May 16 '17 at 14:28
@davidzemens, thanks a bunch. new code implemented; seems to work well. See two specific notes in my comment on your solution post. — Matt Cohen-Price, May 17 '17 at 01:44

score 1 · Accepted Answer · edited May 23 '17 at 12:18

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).

Thanks David, this is exactly what I am looking for. However, I am struggling to implement it. When I replace my `range("iterationtable")` with `thisworkbook.names("iterationtable").RefersToRange` I get a VALUE error. I tried making a test macro to see if the problem was somewhere else: `Dim range1 as range` // `set range1 = thisworkbook.names("iterationtable").ReferstoRange` // `msgbox range1` but this failed too. I imagine I am overlooking something stupid? — Matt Cohen-Price, May 15 '17 at 20:33
Please update your questiont o include the troubleshooting you've attempted. It's difficult to read code in comments, and further when you put it in the comments the only person likely to see the additional detail is me, which limits (significantly) the number of people who might otherwise be able to assist you. — David Zemens, May 15 '17 at 20:45
If this is failing in a test procedure, then it would seem likely that such a range does not exist in `ThisWorkbook` object. — David Zemens, May 15 '17 at 20:46
Absolutely. In the form in which I originally posted it, It works very well when the workbook is active in a 15x20 matrix of cells that calculates voltage drop over common possible combination wire runs. The only issue with it is that it errors when excel recalculates while another workbook is active. — Matt Cohen-Price, May 16 '17 at 01:54
thanks for your help. Your solution worked well, & I learned a few good habits re setting variables along the way. Two things: (1) in your answer you say that the `CodeName` is the part in parentheses, but it seems that actually it is the part **before the parentheses**. (2) Just identifying the codename `sheet1` works perfectly, but I am curious why this works when another workbook is active, considering essentially every workbook contains a worksheet with a codename of `sheet1`. Why do you not have to specify the workbook, as in `ThisWorkbook.Worksheets("Background Tables")`? — Matt Cohen-Price, May 17 '17 at 01:40
see the link which explains -- I think the answer to "why" it works that way is simply because that's how it's designed to work: when referring to Codename, it's implicitly ThisWorkbook. I would guess that's because CodeName identifiers aren't part of the Globals namespace, whereas if you did `Worksheets(1)` that is implicitly ActiveWorkbook, which might not be ThisWorkbook, depending on which book is Active. — David Zemens, May 17 '17 at 01:52
yes I think I was mistaken on the parentheses, CodeName is not in parens. one more caveat: CodeName can be changed, but only via VBA or VBE so it's unlikely the users will mistakenly do this. if the answer solves the problem, do consider marking it as accepted :) — David Zemens, May 17 '17 at 02:01

score 0 · Answer 2 · answered May 15 '17 at 16:52

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.

This is true only if the named range is in the worksheet scope. If the named range is in the workbook scope then one should not name the sheet. — Scott Craner, May 15 '17 at 16:53

score 0 · Answer 3 · answered May 15 '17 at 17:38

0

Or perhaps activate the correct workbook before hand

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

answered May 15 '17 at 17:38

John Muggins

1,198
1
6
12

Thanks John, the named range is scoped to the workbook and I am trying to avoid naming the workbook or worksheet in the UDF in case either see name changes in the future. And the goal is specifically to keep the UDF working when the workbook is not active--in other words, when I open a second document on another monitor and recalculate something, I need this UDF to not recalculate with a VALUE error. – Matt Cohen-Price May 15 '17 at 20:38
https://social.msdn.microsoft.com/Forums/office/en-US/1c96dd88-84f3-415e-b531-8ea2d39d093a/udfs-that-work-in-all-open-workbooks?forum=exceldev – John Muggins May 16 '17 at 14:54

UDF referring to named table errors when another workbook is active

3 Answers3