How to sort Legend of a chart in VBA

问题:

I was trying to sort my legend and could not find any reliable answer. Here, Reordering Chart Data Series in Excel, you can find out how to do it manually. Also, a code was posted there, which was not working for me and it was too complicated for a task like this. Basically, we are trying to automate what is shown in the following picture.

Please propose your solutions or look-up my answer if you have the same problem.

回答1:

This code gets the series names, put them into an array, sort the array and based on that defines the plotting order which will give the desired output. You can change ActiveChart to chart name to be more explicit. Feel free to apply any improvement.

Sub Sorting_Legend()


    Dim Arr()
    ReDim Arr(1 To ActiveChart.FullSeriesCollection.Count)

        'Assigning Series names to an array
        For i = LBound(Arr) To UBound(Arr)
        Arr(i) = ActiveChart.FullSeriesCollection(i).Name
        Next i

        'Bubble-Sort (Sort the array in increasing order)
        For r1 = LBound(Arr) To UBound(Arr)
            rval = Arr(r1)
                For r2 = LBound(Arr) To UBound(Arr)
                    If Arr(r2) > rval Then 'Change ">" to "<" to make it decreasing
                        Arr(r1) = Arr(r2)
                        Arr(r2) = rval
                        rval = Arr(r1)
                    End If
                Next r2
        Next r1

    'Defining the PlotOrder
    For i = LBound(Arr) To UBound(Arr)
    ActiveChart.FullSeriesCollection(Arr(i)).PlotOrder = i
    Next i

End Sub

Excel treats name of the series as text. So you would encounter the problem that instead of having 1,2,3,... you end up getting 1,10,11,...,19,2,20,.... In that case, convert the array to number. There are questions here, like convert an array to number, that would do the job. You can also simply compare Cdbl of each element with the one of the other. (i.e. If Cdbl(Arr(r2)) > Cdbl(rval) Then ...)