Is there some manual I didn't read that shows the VBA way to return the k-th largest number from a range - ignoring duplicates? I have no idea why this is not just the default way the worksheet function RANK works.... This is part of an integer solver problem, which takes ages to run as it is, and this function will be called a large number of times - I don't want to makes things slower than they need to be.
I thought about using the unique-key facility of a collection (load my range into a collection, let the collection throw away the duplicates via key then use RANK, or LARGE or whatever)...but that seemed like overkill.
Below is an obvious recursive method...but with all those references to worksheet functions, etc, I think I'm missing some really obvious VBA/Excel function/functionality? This just seems too tragic for such a simple task, is there a "better" way to do this, where better = "not using recursive worksheet function calls - or creating, filling, copying, destroying collections on each function call"?
Public Function getRank(ByRef r As range, k As Integer) As Double
If (k < 1) Then Exit Function
If (k = 1) Then
getRank = Application.WorksheetFunction.Max(r)
Exit Function
End If
getRank = Application.WorksheetFunction.Large(r, Application.WorksheetFunction. _
CountIf(r, ">=" & getRank(r, k - 1)) + 1)
End Function
My r is a column with this data in it, as a test.
-100%
50%
-100%
-100%
-100%
-100%
-200%
-100%
-200%
-100%
-200%
-100%
-200%
-200%
-100%
-100%
60%
-200%
-100%
50%
-200%
-200%
-200%
60%
60%
-100%
-200%
-100%
-100%
-200%
-200%
60%
60%
-200%
To gain optimal speed, consider separating the functionality into two parts:
The event macro would create a public static array from the column values, discarding duplicates and sorting the uniques. The macro would only run initially and when the column changes.
The UDF then only needs to index into the created array based on the index in its header.