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

 

[djt76010]

Bump

 

[xenou]

What's the purpose of your function?
ξ

 

[djt76010]

This is just simplified code to try and figure out why my methodology was not working.

 

[xenou]

I makes a difference if you want to use the range from the sheet where the UDF is, or some kind of stored or temporarily stored value in the xlam itself. What's DATA supposed to be in this case - is it coming from the sheet where the UDF is, or is it something stored in the xlam itself?

 

[djt76010]

Oops . . . I guess I oversimplified my example. DATA refers to a named range saved inside the xlam file. The xlam file has many different named range arrays containing numbers. I need the user to be able to pass a named range into the UDF for other calculations.

I makes a difference if you want to use the range from the sheet where the UDF is, or some kind of stored or temporarily stored value in the xlam itself. What's DATA supposed to be in this case - is it coming from the sheet where the UDF is, or is it something stored in the xlam itself?

 

[xenou]

Try:

Function TEST_1(Number, DATA)

    Dim DATA_RANGE As Variant
    
    DATA_RANGE = [COLOR="Red"]ThisWorkbook.[/COLOR]Range(DATA).Value
          
    TEST_1 = DATA_RANGE(Number, 1)

End Function

 

[djt76010]

That makes sense that you have to tell it where to look for the named range, but I am getting a compile error ("Method or data member not found"). Is it necessary to declare the sheet as well?

Try:

Function TEST_1(Number, DATA)

    Dim DATA_RANGE As Variant
    
    DATA_RANGE = [COLOR="Red"]ThisWorkbook.[/COLOR]Range(DATA).Value
          
    TEST_1 = DATA_RANGE(Number, 1)

End Function

 

[mikerickson]

It looks like you are passing the name as a string and call it with formulas like
=TEST_1(2, "namedRange")

One could declare Data a range value and use formulas like =TEST_1a(2, namedRange)

Function TEST_1a(Number As Long, DATA As Range) As Variant

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

End Function

If you have to pass DATA as a string,

Function TEST_1(Number As Long, DATA as String) As Variant

    Dim DATA_RANGE As Variant
    
    With Application.Caller.Parent.Parent
        DATA_RANGE = ThisWorkbook.Names(DATA).RefersToRange.Value
    End With

    TEST_1 = DATA_RANGE(Number, 1)

End Function

 

[xenou]

If that doesn't work, try:

Public Function TEST_1(ByVal num As Long, DAT As String)

    TEST_1 = ThisWorkbook.Names(DAT).RefersToRange.Cells(num, 1)

End Function

I've gotten confused again if we're in the xlam or the workbook that's employing the function.