How to change Orientation of bars in Excel Bar Chart created with VBA

Question

I am creating a chart, in a form, using VBA Excel 2010. I have configured excel to use Chartspace and the chart is created using dynamic data correctly, but the presentation is not what I am looking for, but can't figure how to change it. Please see the section of code:

Private Sub UserForm_Initialize()
Dim row_count As Integer
Dim n As Long
Dim chart_data As Worksheet

Set chart_data = Worksheets("Sheet3")
row_count = chart_data.UsedRange.Rows.Count

Dim varCats()
Dim varVals()

ReDim varCats(row_count)
ReDim varVals(row_count)

'Set c = ChartSpace1.Constants
Set mychart = ChartSpace1.Charts.Add
mychart.Type = xlColumnClustered '51 'chChartTypeBarClustered 'c.chChartTypeBarClustered

For n = 2 To row_count
    varCats(n) = ActiveWorkbook.Sheets("Sheet3").Range("A" & n).Value
    varVals(n) = ActiveWorkbook.Sheets("Sheet3").Range("T" & n).Value
Next n

mychart.SeriesCollection.Add
With mychart.SeriesCollection(0)
    .SetData chDimSeriesNames, chDataLiteral, "QAR Score"
    .SetData chDimCategories, chDataLiteral, varCats
    .SetData chDimValues, chDataLiteral, varVals
End With

End Sub

The bar chart is showing the bars horizontally and not vertically. So where I thought my variable "varCats" would go to the X-Axis is not but rather the Y-axis.

I know this is going to be a simple response from the community, but I have yet to find it through my searching.

Thanks, C

Image from the answer provide: enter image description here

Desired Results: enter image description here

Which version of Excel, and which version of OWC are you using? — Rory
Microsoft Office Chart 11.0 is enabled from "Additional Controls." Excel 2010. — cquadrini

David Zemens David Zemens · Accepted Answer · 2014-05-21T16:21:37

Revised to work with ChartSpace objects in UserForm

Private Sub UserForm_Initialize()
    Dim row_count As Integer
    Dim n As Long
    Dim chart_data As Worksheet
    Dim srs As ChSeries
    Dim myChart As ChChart

    Set chart_data = Worksheets("Sheet3")
    row_count = chart_data.UsedRange.Rows.Count

    ReDim varCats(1 To row_count)
    ReDim varVals(1 To row_count)

    varCats = Application.Transpose(chart_data.Range("A2:A" & row_count).Value)
    varVals = Application.Transpose(chart_data.Range("T2:B" & row_count).Value)

    'Set c = ChartSpace1.Constants
    Set myChart = ChartSpace1.Charts.Add
        myChart.Type = chChartTypeColumnClustered
    Set srs = myChart.SeriesCollection.Add
        With srs
            .SetData chDimSeriesNames, chDataLiteral, "QAR Score"
            .SetData chDimCategories, chDataLiteral, varCats
            .SetData chDimValues, chDataLiteral, varVals
        End With



End Sub

How to change Orientation of bars in Excel Bar Chart created with VBA

1 Answers