如何在VBA中对图表图例进行排序 [英] How to sort Legend of a chart in VBA

问题描述

我试图对图例进行排序，但找不到可靠的答案。在这里，，即可完成工作。您还可以简单地将每个元素的 Cdbl 与另一个元素进行比较。 （即如果Cdbl（Arr（r2））> Cdbl（rval）然后... ）

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.

I am posting this to document my solution to this problem. Please propose your answers or look-up mine if you have the same question.

解决方案

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 ...)

这篇关于如何在VBA中对图表图例进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！