VBA make named range arguments to a custom function refer to both a single cell and a range of cells

Question

The percentrank function can take the same named range for both of its agruments like:

=percentrank(inputrange,inputrange)

It treats the first argument as a range of cells for calculating percent cutoffs, and uses the second argument to return a single cell's value to rank amongst the cutoffs. The single cell is determined by the row in which the function is entered/called from.

I want to recreate this functionality and use the second range reference to find a single cells value (based on the row the function is entered into). Here's what I have:

Public Function QUARTILE_RANK(DataRange As Range, RefCell As Range)

If RefCell <> vbNullString Then

    q1 = Application.WorksheetFunction.Quartile(DataRange, 1)
    q2 = Application.WorksheetFunction.Quartile(DataRange, 2)
    q3 = Application.WorksheetFunction.Quartile(DataRange, 3)
    q4 = Application.WorksheetFunction.Quartile(DataRange, 4)

    If (RefCell <= q1) Then QUARTILE_RANK = 1
    If (RefCell > q1) And (RefCell <= q2) Then QUARTILE_RANK = 2
    If (RefCell > q2) And (RefCell <= q3) Then QUARTILE_RANK = 3
    If (RefCell > q3) Then QUARTILE_RANK = 4
Else
    QUARTILE_RANK = vbNullString
End If

End Function

If I pass it the same named range for both arguments, it sees both ranges as arrays of cells. I want it to treat the first argument that way, but find a single cell value using the second argument. That is, I want it to mirror the functionality of the PERCENTRANK function.

EDIT:

Sean Chesire's input helped me get it working. I'd welcome any suggestions for improvement as I'm sure this same function has been written by others.

Here's the final form:

Public Function QUARTILE_RANK(DataRange As Range, RefRange As Range)

Dim refCell As Range

If RefRange.Rows.Count > 1 Then
    Set refCell = RefRange.Cells(Application.Caller.Row, 1)
Else
    Set refCell = RefRange ' maybe they only passed a cell reference
End If

If refCell <> vbNullString Then

    q1 = Application.WorksheetFunction.Quartile(DataRange, 1)
    q2 = Application.WorksheetFunction.Quartile(DataRange, 2)
    q3 = Application.WorksheetFunction.Quartile(DataRange, 3)
    q4 = Application.WorksheetFunction.Quartile(DataRange, 4)

    If (refCell <= q1) Then QUARTILE_RANK = 1
    If (refCell > q1) And (refCell <= q2) Then QUARTILE_RANK = 2
    If (refCell > q2) And (refCell <= q3) Then QUARTILE_RANK = 3
    If (refCell > q3) Then QUARTILE_RANK = 4
Else
    QUARTILE_RANK = vbNullString
End If

End Function

SeanC SeanC · Accepted Answer · 2012-07-30T19:55:55

this will tell you which cell the formula was called from:

Function testit() As String
testit = Application.Caller.Parent.Parent.Name & " " & Application.Caller.Parent.Name & " " & Application.Caller.Address
End Function

You should then be able to test the ranges given, and work out which value should be referenced in the other array

VBA make named range arguments to a custom function refer to both a single cell and a range of cells

2 Answers