Naughty Sorts

Continuing the theme of naughty code which can cause Excel XML corruption, let’s have a look at some code which sorts some data in a range but then causes XML errors when the file is saved and re-opened. Here’s my sheet:

BeforeSort

And here is some VBA code generated by the macro recorder which sorts it descending by ColA:

Sub Macro1()
    '
    ' Macro1 Macro
    '
    '

    Range("A1:C6").Select

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("A2:A6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

It runs, it works and I’m happy:

AfterSort1

Next I decide to make a couple of small modifications because I want to sort by three columns instead of one. Being a copy and paste cowboy, I copy the code which adds the SortField and update it so it applies to the 2nd and 3rd columns.

Sub Macro1()
    '
    ' Macro1 Macro
    '
    '

    Range("A1:C6").Select

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("A2:A6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("B2:B6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("B2:B6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

It also runs without an error so I happily save and close the file and go and make myself a nice cup to tea:

AfterSort2

When I return to my desk and open the workbook what do I get? A faceful of error messages. Thanks Excel.

Error1

Error2

Error3

So what was my mistake here? Well, actually there were two.

Firstly, the bit of code which caused the XML error is the duplicate SortField with Key range B2:B6. I didn’t edit it properly when I copied and pasted it.

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("B2:B6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
        Key:=Range("B2:B6"), _
        SortOn:=xlSortOnValues, _
        Order:=xlDescending, _
        DataOption:=xlSortNormal

Secondly, I didn’t test it properly: I should’ve tried it out on sample data where the third SortField would’ve come into visual effect.

Those whoopsies aside, the macro recorder itself tends to generate dodgy code. In this case it didn’t properly qualify the SortField key ranges and the Sort range. Here’s the corrected version:

Sub Macro1()

    Dim wksTarget As Worksheet

    Set wksTarget = Worksheets("Sheet1")

    With wksTarget.Sort

        With .SortFields

            .Clear

            .Add _
                Key:=wksTarget.Range("A2:A6"), _
                SortOn:=xlSortOnValues, _
                Order:=xlDescending, _
                DataOption:=xlSortNormal

            .Add _
                Key:=wksTarget.Range("B2:B6"), _
                SortOn:=xlSortOnValues, _
                Order:=xlDescending, _
                DataOption:=xlSortNormal

            .Add _
                Key:=wksTarget.Range("C2:C6"), _
                SortOn:=xlSortOnValues, _
                Order:=xlDescending, _
                DataOption:=xlSortNormal

        End With

        .SetRange wksTarget.Range("A1:C6")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub
Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s