Sorting and subsorting a variant array

Question

I would like to code the following in VBA, a function of signature

Public Function MySort( v as Variant) as Variant

that takes a variant v having names in its first columns and notes in its second, checks if it has two columns, throws an error if it hasn't, and if it has two rows, does the following : it sorts v's rows according to v's second column in decreasing, and then, inside a group of same note, sorts it alphabetically. For instance, the function MySort will send the variant

to the variant

^{from teh OP's comment below}

I have tried this, without success :

Public Function MySort(r As Range) As Range
    Dim r1 As Range
    Dim r2 As Range

    Set r1 = r.Columns(1)
    Set r2 = r.Columns(2)

    r.Sort Key1:=r1, Order1:=xlDescending, DataOption1:=xlSortNormal
    r.Sort Key2:=r2, Order2:=xlAscending, DataOption2:=xlSortNormal

    Tri = r 
End Function

I'd recommend using the macro recorder, and create a macro to sort by column B Desc + col A Asc. Then look at the code. Then slightly modify that code into your routine ;) It should be fairly basic. If you still have trouble, drop a note here with what's giving you a head ache. — Ditto, Apr 17 '15 at 20:07
You might need to improve on your example, because your code simply shows a sort on the first column which is very easy to do using @Ditto's suggestion. — FreeMan, Apr 17 '15 at 20:18
@Ditto Thanks a lot. Now, I could indeed apply your advise, and in this case, it would be easier that my function takes two parameter, column A and column B, but I would really like it to take a variant. I know that I could convert a range to variant, but how can I extract column1 and column 2 elegantly from the input variant then ? — EricFlorentNoube, Apr 17 '15 at 20:21
@EricFlorentNoube, why take a variant? You could pass in `v as Range` and then use `v.Columns(1)` and `v.Columns(2)` to process the columns. Or you can do `v.Cells(row,col)` if you want to deal with them together. — Byron Wall, Apr 17 '15 at 20:27
I have tried this, without success : `Public Function MySort(r As Range) As Range Dim r1 As Range Dim r2 As Range Set r1 = r.Columns(1) Set r2 = r.Columns(2) r.Sort Key1:=r1, Order1:=xlDescending, DataOption1:=xlSortNormal r.Sort Key2:=r2, Order2:=xlAscending, DataOption2:=xlSortNormal Tri = r End Function` — EricFlorentNoube, Apr 17 '15 at 20:53
VBA Functions do not modify the values or structure on a worksheet. A function *could* be used to sort a variant array. — , Apr 17 '15 at 23:50
Check the following link: [Chip Pearson - Sorting Array In VBA](http://www.cpearson.com/excel/SortingArrays.aspx) — Darren Bartrup-Cook, Apr 20 '15 at 16:28

score 0 · Answer 1 · answered Apr 18 '15 at 00:05

VBA Functions do not modify the values or structure on a worksheet other than the cell that they reside in. A function could be used to sort a variant array and pass the reordered results back to a sub which returns the values back into the worksheet but you are better off just using a sub to perform the sort unless you have some custom sort criteria that cannot be handled conventionally.

Sub sort_A1CR(Optional ws As Variant) 'expect a worksheet object, not a worksheet name

    If IsMissing(ws) Then Set ws = ActiveSheet
    With ws.Cells(1, 1).CurrentRegion
        .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                    Key2:=.Columns(2), Order2:=xlDescending, _
                    Orientation:=xlTopToBottom, Header:=xlYes
    End With

End Sub

Your sample code contradicted your results. This sorts with column A as the primary key in an ascending order. If duplicate values are found in column A then column B is used as the sub-sort in a descending manner.

Sorting and subsorting a variant array

1 Answers1