CODEWITHSUNDEEP
excelvbaexcel-formula
Thomas Breakell
Thomas Breakell

Reputation: 61

Method to use a UDF to return a Range as String to be used in Lookup Functions

For my problem, I have a UDF which accepts a string and using select case it then returns the string reference to the specified array. The return string is a reference to a sheet and range array in the following form:

'WorksheetExample'!$B$1:$E$44

This UDF is nested within the VLookup/Match in my table to give the correct reference for that case. 

Public Function EquipCatRNG(tag As String, tagOrCatRef As String)

'Hardcoded Category Ranges for Matches
Dim tagRef, catRef As String
Dim subfix As String
subfix = Left(tag, 1)
Select Case subfix
    Case "E"
    'Range for heat exchanger info, 'Heat Exchangers Template (EDS)'!$B$1:$J$53
    tagRef = "'Heat Exchangers Template (EDS)'!$B$1:$J$1"
    catRef = "'Heat Exchangers Template (EDS)'!$B$1:$B$53"
    Case "P"
        If InStr(tag, "Drive") > 0 Then
        tagRef = "'Pumps And Drives Template (EDS)'!$B$44:$G$44"
        catRef = "'Pumps And Drives Template (EDS)'!$B$44:$B$68"
        Else
        tagRef = "'Pumps And Drives Template (EDS)'!$B$1:$G$1"
        catRef = "'Pumps And Drives Template (EDS)'!$B$1:$B$42"
        End If
    Case "T"
        If InStr(tag, "Internals") > 0 Then
        tagRef = "'Towers Template (EDS)'!$B$46:$E$46"
        catRef = "'Towers Template (EDS)'!$B$46:$B$68"
        Else
        tagRef = "'Towers Template (EDS)'!$B$1:$E$1"
        catRef = "'Towers Template (EDS)'!$B$1:$B$44"
        End If
    Case "R"
        tagRef = "'Reactors Template (EDS)'!$B$1:$C$1"
        catRef = "'Reactors Template (EDS)'!$B$1:$B$61"
    Case "H"
        tagRef = "'Heaters Template (EDS)'!$B$1:$C$1"
        catRef = "'Heaters Template (EDS)'!$B$1:$B$45"
    Case "V"
        If InStr(tag, "Internals") > 0 Then
        tagRef = "'Vessels Template (EDS)'!$B$45:$F$45"
        catRef = "'Vessels Template (EDS)'!$B$45:$B$64"
        Else
        tagRef = "'Vessels Template (EDS)'!$B$1:$F$1"
        catRef = "'Vessels Template (EDS)'!$B$1:$B$43"
        End If
End Select
    tagOrCatRef = LCase(tagOrCatRef)
    If tagOrCatRef = "tag" Then
        EquipCatRNG = tagRef
    Else
        EquipCatRNG = catRef
    End If

End Function

Any help is appreciated, I would also be fine with passing the Range directly from the UDF instead of returning a string and then performing some operation to utilize it in the formula. Thank you.

EDIT for Clarity: This function passes the String successfully but it is not working as a valid reference in the Vlookup/Match functions in my table. I need to know how I either: 1. Perform a formula operation on the returned string post-UDF so that the Vlookup accepts it as a valid reference? 2. Pass it initially as a range?

Upvotes: 0

Views: 178

Answers (1)

user11121185
user11121185

Reputation:

To use the UDF 'in-line' inside another worksheet function, you need to Set the UDF to the range.

Public Function EquipCatRNG(tag As String, tagOrCatRef As String) as range

'Hardcoded Category Ranges for Matches
Dim tagRef As range, catRef As range
Dim subfix As String
subfix = Left(tag, 1)
Select Case subfix
    Case "E"
        'Range for heat exchanger info, 'Heat Exchangers Template (EDS)'!$B$1:$J$53
        Set tagRef = worksheets("Heat Exchangers Template (EDS)").Range("B1:J1")
        Set catRef = worksheets("Heat Exchangers Template (EDS)").Range("B1:B53")
    Case "P"
        If InStr(tag, "Drive") > 0 Then
          Set tagRef = worksheets("Pumps And Drives Template (EDS)").Range("B44:G44")
          Set catRef = worksheets("Pumps And Drives Template (EDS)").Range("B44:B68")
        Else
          Set tagRef = worksheets("Pumps And Drives Template (EDS)").Range("B1:G1")
          Set catRef = worksheets("Pumps And Drives Template (EDS)").Range("B1:B42")
        End If
    Case "T"
        'etc, etc, etc
         ...

End Select

    tagOrCatRef = LCase(tagOrCatRef)
    If tagOrCatRef = "tag" Then
        Set EquipCatRNG = tagRef
    Else
        Set EquipCatRNG = catRef
    End If

End Function

Used on a worksheet, this would look like,

=MATCH("abc", EquipCatRNG("E", "Tag"), 0)

Upvotes: 2

Related Questions