Dynamic Sorting With a UDF
Coincidentally, this post relates to two previous posts — which happen to be unrelated to each other:
- Dynamic Text Sorting - An array formula that sorts data in a range as it's entered.
- Modifying Shapes (and Charts) With UDFs - A user-defined function that modifies objects on a worksheet.
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:
' Excel 2007 only
' Order: 1=ascending, 2=descending
If IsMissing(order) Then order = 1
With rngToSort.Parent.Sort
.SortFields.Clear
.SortFields.Add Key:=rngToSort.Cells(1, 2), order:=order
.SetRange rngToSort
.Header = xlYes
.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…
- Clears the SortFields (which is a collection)
- 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.
- Gets the range to be sorted, using the SetRange method.
- Specifies that the range has a header row, via the the Header property.
- Does the sorting, via the Apply method
- 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?
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
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!
Would someone advise where this benefits over the use of:
If Target.Column = 2 Then
Cells(1).CurrentRegion.Sort Key1:=Columns(2), Header:=xlYes
End If
in the Worksheet_Cange event ?
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.
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.
John,
Why not use constants xlAscending and xlDescending in the function code?
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.
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.
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
[...] 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 [...]