Accessing Named Range In UDF

[djt76010]

I have written this function in a macro enabled spreadsheet and it works as expected if I pass in the named range "TESTRANGE" that refers to a range in that spreadsheet. When I save that spreadsheet as an .xlam file and add it as an add-in the function no longer works. Is there some adjustment I need to make in order to access a named range in the .xlam file?

Function TEST_1(Number, DATA)

    Dim DATA_RANGE As Variant
    
    DATA_RANGE = Range(DATA).Value
          
    TEST_1 = DATA_RANGE(Number, 1)

End Function

Thanks in advance

 

[mikerickson]

Wouldn't ThisWorkbook refer to the Add-In's workbook rather than the one holding the formula?

 

[xenou]

Yes, I *think* that's what the OP wants ... i.e., storing data in named ranges in the xlam. Though I could have it backwards, in which case the penultimate solution is best.

 

[djt76010]

Thanks guys. That did the trick.