Search code examples
vbaexcelsortinggraphing

Excel VBA: Create a clustered column chart that sorts based on value not series?


Here is my issue: I have a user-specified number of data sets that I want to plot on a clustered column graph. I am creating the chart in visual basic and I am adding the data sets as separate series, so that they are distinguishable by color and have different titles on the legend:

ActiveWorkbook.Charts.Add 'all of this just adds a new chart
ActiveChart.ChartArea.Select
With ActiveChart
    .ChartType = xlColumnClustered
    .HasTitle = True
    .ChartTitle.Text = "Ordered Distribution Graph"
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Item"
    .Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Total"
    .Legend.Position = xlLegendPositionBottom
End With

ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
ActiveSheet.Name = "Distribution Chart"

For j = 0 To UBound(chartLabels) 'here is where I handle the data based on global variables
    If IsEmpty(chartLabels(j)) Then Exit For
    Erase xval
    Erase yval
    ReDim Preserve xval(0 To 0)
    ReDim Preserve yval(0 To 0)
    xval(0) = chartData(0, j, 0)
    yval(0) = chartData(2, j, 0)

    For i = 0 To UBound(chartData, 3) - 1
        If Not IsEmpty(chartData(2, j, i + 1)) Then
            ReDim Preserve xval(0 To i + 1)
            ReDim Preserve yval(0 To i + 1)
            xval(i + 1) = chartData(0, j, i + 1)
            yval(i + 1) = chartData(2, j, i + 1)
        End If
    Next

    Call bubblesortData(j, UBound(xval)) 'separate sort function

    ActiveChart.SeriesCollection.NewSeries 'plots each series
    ActiveChart.SeriesCollection(j + 1).XValues = xval
    ActiveChart.SeriesCollection(j + 1).Values = yval
    ActiveChart.SeriesCollection(j + 1).Name = main.chartLabels(j)
    ActiveChart.ChartGroups(1).GapWidth = 10
    ActiveChart.ChartGroups(1).Overlap = -10
Next

Sheets(ActiveWorkbook.Sheets.count).Activate

Currently, each set of data is sorted using the bubblesortData(setNumber, numberOfDataPoints) subroutine (xval and yval are global arrays):

Sub bubblesortLosses(b As Variant, tot As Variant)
Dim changed As Integer, temp As Variant

Do
changed = 0
    For i = 0 To tot - 1
    If Not IsEmpty(xval(i)) Then
        If yval(i) > yval(i + 1) Then
            temp = xval(i)
            xval(i) = xval(i + 1)
            xval(i + 1) = temp
            temp = yval(i)
            yval(i) = yval(i + 1)
            yval(i + 1) = temp
            changed = 1
        End If
    End If
    Next

Loop Until changed = 0
End Sub

This is working fine, but results in something like this:

ExampleChart

Each set is ordered due to my sort, but I would like all of the data to be sorted based on y-axis value. I cannot think of a way to accomplish this while also keeping the data separated by series. Is there a way to display x-axis values based on corresponding y-axis value and not based on series position??


Solution

  • After a lot of searching, I found a combination of solutions that worked for me, mostly drawing information from this link: http://peltiertech.com/chart-with-a-dual-category-axis/

    ...As well as from a variety of StackOverflow posts indicating that doing this is impossible programmatically and must be done through the worksheet, which has worked for me. I filled in worksheet cells like was done in the above link, except with visual basic. Then, after the data was graphed, I hid the worksheet. This works for me because the worksheets are cleared every time the user starts over with a new data set. Here is my code:

    Sub Distribution()
    Dim runningTotal, seriesNumber, sheetName
    
    seriesNumber = 1
    runningTotal = 2
    
    currDist = currDist + 1
    sheetName = "DistData" + CStr(currDist)
    
    ActiveWorkbook.Sheets.Add
    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
    ActiveSheet.Name = sheetName
    ActiveSheet.Visible = True
    
    For j = 0 To UBound(chartLabels)
        If IsEmpty(chartLabels(j)) Then Exit For
        Erase xval
        Erase yval
        ReDim Preserve xval(0 To 0)
        ReDim Preserve yval(0 To 0)
        xval(0) = chartData(0, j, 0)
        yval(0) = chartData(2, j, 0)
    
        For i = 0 To UBound(chartData, 3) - 1
            If Not IsEmpty(chartData(2, j, i + 1)) Then
                ReDim Preserve xval(0 To i + 1)
                ReDim Preserve yval(0 To i + 1)
                xval(i + 1) = chartData(0, j, i + 1)
                yval(i + 1) = chartData(2, j, i + 1)
            End If
        Next
    
        Call bubblesortLosses(j, UBound(xval))
    
        Sheets(sheetName).Select
    
        Cells(1, seriesNumber + 2) = chartLabels(j)
        Cells(runningTotal, 1) = chartLabels(j)
    
        For k = 0 To UBound(xval)
            Cells(runningTotal, 2) = xval(k)
            Cells(runningTotal, seriesNumber + 2) = yval(k)
            runningTotal = runningTotal + 1
        Next
    
        seriesNumber = seriesNumber + 1
    
    Next
    
    ActiveWorkbook.Charts.Add
    ActiveChart.ChartArea.Select
    With ActiveChart
        .ChartType = xlColumnStacked
        .HasTitle = True
        .ChartTitle.Text = "Ordered Distribution Graph"
        .Axes(xlCategory).TickLabels.MultiLevel = True
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Characters.Text = "Item"
        .Axes(xlCategory).CategoryType = xlCategoryScale
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Total"
        .Legend.Position = xlLegendPositionBottom
    End With
    
    ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
    ActiveSheet.Name = "Distribution " + CStr(currDist)
    
    ActiveChart.ChartGroups(1).GapWidth = 10
    ActiveChart.ChartGroups(1).Overlap = 100
    
    Sheets(sheetName).Visible = False
    Sheets(ActiveWorkbook.Sheets.count).Activate
    
    End Sub
    

    The bubblesort subroutine is the same as was used in the question. The final result for one of my test runs is here:

    enter image description here

    Item number is listed, but the category labels are cut out of the picture due to confidentiality. They would read similar to "Series 1", "Series 2", and "Series 3"