# Dynamic Sorting With a UDF

Coincidentally, this post relates to two previous posts — which happen to be unrelated to each other:

Excel 2007 has a new Sort object. I discovered that this object does its thing even when it’s called by a UDF function used in a formula. In other words, yet another example of a UDF that changes the spreadsheet.

The figure below shows an example. The data in columns A and B is sorted immediately (by column B) whenever a change is made within the range A1:B50. So, if I change Anne’s score to 71, her row would move down between Paul and Oscar’s data. Cell D2 contains a formula that uses the SortRange function:

Function SortRange(rngToSort As Range, Optional order)
‘   Excel 2007 only
‘   Order: 1=ascending, 2=descending
If IsMissing(order) Then order = 1
With rngToSort.Parent.Sort
.SortFields.Clear
.SetRange rngToSort
.Apply
SortRange = .Rng.Address(False, False) ‘Range actually sorted
End With
End Function

This function takes two arguments: The range to sort, and the sort order (an optional argument). The Sort object is a member of the Worksheet, so I use the Parent property to get that object, and enclose all of the object manipulations inside of a With-End With structure.

The function does this…

1. Clears the SortFields (which is a collection)
2. Adds a key. In this case, the sort key is the second column of the data, and it’s sorted in the order specified by the second argument.
3. Gets the range to be sorted, using the SetRange method.
4. Specifies that the range has a header row, via the the Header property.
5. Does the sorting, via the Apply method
6. Gets the address of the range that was actually sorted, and assigns it to the function (this is the text returned by the formula). The function is passing a 50-row range, but it only has 13 rows of data.

You can accomplish the same effect with a WorksheetChange event, but this technique allows the user to specify some options without modifying the code. The function, of course, could include more arguments that control how the sorting is done.

Practical applications, anyone?

Posted in Uncategorized

## 21 thoughts on “Dynamic Sorting With a UDF”

1. sam says:

Hello John,
Great way to ensure you have a sorted table always…Would the code work on a dynamic range
(Dont have 007…so no ways of testing it)

Regards
Sam

2. Ross says:

Not sure if this is a good or a bad thing! Looks like it might be an over sight in the coding? I tend to agree with Tusha Merta on the issue, aviod it!

3. Andrew Hunt says:

Would someone advise where this benefits over the use of:

If Target.Column = 2 Then
End If

in the Worksheet_Cange event ?

4. John Walkenbach says:

Andrew, the only benefit that I see is that (by setting up the function arguments) it’s possible for a user to change the search parameters without having to know how to edit the VBA code.

5. Andrew Hunt says:

John

I would be more than concerned that a User would make an entry on line 14 and forget to change B13 to B14, whereas my code would do this automatically. There is also the possibility that some users would not think that the A1:B13 ref. shouldn’t be there and just delete it.

Thanks for the clarification however.

6. Alex J says:

John,
Why not use constants xlAscending and xlDescending in the function code?

7. fzz says:

This could be lots of fun if it bypasses circular recalc detection. Yet another way for udfs to cause infinite loops. Just what everyone needs!

So much for Microsoft claims that udfs *can’t* modify the Excel environment.

8. jkpieterse says:

Hi John,

Does it work if the referred cells are in a table and you’ve used the new structured referencing i.s.o. cell addresses?

IMO this is a bug though, I wouldn’t want this to happen.

9. Mike Rosenblum says:

For some reason I could not get his version to work… Is it possible that this was an exception to Excel’s worksheet function calculation mode protection that has now been fixed by Microsoft? Or am I not doing something correctly?

I’m using Excel 2007 12.0.6300.5000 SP1 MSO (12.0.6213.1000).

Any thoughts? Has SP1 closed this hole in the worksheet function calculation mode protection, or am I simply doing something wrong?

Just curious…

Mike

10. […] Walkenbach has an article at Daily-Dose-of Excel on Dynamic Sorting with a UDF, which whilst intersting has the disadvantages that it doesn’t work in Excel 2007, and is not […]

11. Bill Ghauri says:

I’m trying to develop a UDF that uses the Quick Sort routine to return the value of a specified position within the sorted array. To illustrate, let’s say I have values of 350, 500, 125, 625, 325, and 700 in rows A1:A6 respectively. Now my function needs to read this range, sort it in ascending order (using Quick Sort), then return the “position” specified in my function. So for example, the third position would return a value of 350 because that would be the third position in the sorted list. When I deploy this function, I will be sorting lists of hundreds of numbers, so I want to use Quick Sort because of the speed advantage. Also, I will ultimately incorporate the Select Case… construct to analyze multiple nth positions within the sorted array, but for clarity’s sake, I’ve left that out for now. So far this is what my function looks like:

Function PositionValue(VolRng As Range)
Dim First, Last As Long
First = Application.WorksheetFunction.min(VolRng)
Last = Application.WorksheetFunction.max(VolRng)
Call Quick_Sort(VolRng, First, Last)
PositionValue = VolRng(3, 1)’ this will return the third position within the sorted array
End Function

The Quick Sort routine I’m using is as follows:

Private Sub Quick_Sort(ByRef SortArray As Variant, ByVal First As Long, ByVal Last As Long)
Dim Low As Long, High As Long
Dim Temp As Variant, List_Separator As Variant
Low = First
High = Last
List_Separator = SortArray((First + Last) / 2)
Do
Do While (SortArray(Low) List_Separator)
High = High – 1
Loop
If (Low <= High) Then
Temp = SortArray(Low)
SortArray(Low) = SortArray(High)
SortArray(High) = Temp
Low = Low + 1
High = High – 1
End If
Loop While (Low <= High)
If (First < High) Then Quick_Sort SortArray, First, High
If (Low < Last) Then Quick_Sort SortArray, Low, Last
End Sub

I’m not sure if using Quick Sort is overkill for this and there is a simpler way. Any help to get this working would be very much appreciated. Thanks.

Best regards,
Bill Ghauri

12. Dick Kusleika says:
Function PositionValue(VolRng As Range, Optional lPos As Long = 1)

Dim vArray As Variant

vArray = VolRng.Value
QuickSort vArray, LBound(vArray, 1), UBound(vArray, 1)

PositionValue = vArray(lPos, 1)

End Function

Public Sub QuickSort(vArray As Variant, inLow As Long, inHigh As Long)

Dim vPivot As Variant
Dim vSwap As Variant
Dim lLow As Long
Dim lHigh As Long

lLow = inLow
lHigh = inHigh

vPivot = vArray((inLow + inHigh) 2, 1)

Do While lLow <= lHigh

Do While vArray(lLow, 1) < vPivot And lLow < inHigh
lLow = lLow + 1
Loop

Do While vPivot < vArray(lHigh, 1) And lHigh > inLow
lHigh = lHigh – 1
Loop

If (lLow <= lHigh) Then
vSwap = vArray(lLow, 1)
vArray(lLow, 1) = vArray(lHigh, 1)
vArray(lHigh, 1) = vSwap
lLow = lLow + 1
lHigh = lHigh – 1
End If

Loop

If (inLow < lHigh) Then QuickSort vArray, inLow, lHigh
If (lLow < inHigh) Then QuickSort vArray, lLow, inHigh

End Sub

The low and high that you pass in are the bounds of the array, not the minimum and maximum values. Also, the range array of values is two dimensional, so all your array references needed a second dimension. Also, you are pulling the 3rd value from the range which ignores the sorting that was done.

13. Bill Ghauri says:

@ Dick,

I kind of boggles my mind how you experts can figure this stuff out so fast! You solution works great! Many thanks.

Best regards,
Bill Ghauri

14. Jon Peltier says:

What’s wrong with =LARGE(array,k)?

15. Bill Ghauri says:

@ Jon,

I wanted to use a UDF that I could call from another sub, but I suppose the LARGE function would work too. I did not try it. In my model I had been been using a sub to copy/sort the values on a worksheet. It worked, but I needed to steamline the model as much as possible and I figured a UDF was the best route. Btw, this is how my final function looks with the Select Case construct. It works great.

Function Vol_ID(Vol_0 As Long, volRng As Range)
Dim vArray As Variant
vArray = volRng.Value
QuickSort vArray, LBound(vArray, 1), UBound(vArray, 1)
Select Case True
Case Is = Vol_0 vArray(5, 1) And Vol_0 vArray(17, 1) And Vol_0 vArray(29, 1) And Vol_0 vArray(41, 1) And Vol_0 vArray(53, 1)
Vol_ID = “EXTM”
End Select
End Function

Best regards,
Bill Ghauri

16. Bill Ghauri says:

I must have messed up posting because it cut out some of my code.

17. Bill Ghauri says:

Maybe this time…

Function Vol_ID(Vol_0 As Long, volRng As Range)
Dim vArray As Variant
vArray = volRng.Value
QuickSort vArray, LBound(vArray, 1), UBound(vArray, 1)
Select Case True
Case Is = Vol_0 vArray(5, 1) And Vol_0 vArray(17, 1) And Vol_0 vArray(29, 1) And Vol_0 vArray(41, 1) And Vol_0 vArray(53, 1)
Vol_ID = “EXTM”
End Select
End Function

18. Bill Ghauri says:

One more time. If this doesn’t work, my apologies for mucking up this thread.

Function Vol_ID(Vol_0 As Long, volRng As Range)
Dim vArray As Variant
vArray = volRng.Value
QuickSort vArray, LBound(vArray, 1), UBound(vArray, 1)
Select Case True
Case Is = Vol_0 vArray(5, 1) And Vol_0 vArray(17, 1) And Vol_0 vArray(29, 1) And Vol_0 vArray(41, 1) And Vol_0 vArray(53, 1)
Vol_ID = “EXTM”
End Select
End Function

19. Dick Kusleika says:

Bill, put your code in code tags. See the instructions just above the comment box. This will preserve the code and format it correctly.

20. Jon says:

I have a range \$A\$5:\$K\$26. I want to sort on the J column, how do I modify the code to do this?

21. Jon says:

Nevermind, I changed the .Cell(5, 10) and that worked. I had inadvertently pasted this function into two different spots and was modifying the wrong one.

Posting code? Use <pre> tags for VBA and <code> tags for inline.