none
Stacked Chart RRS feed

  • Question

  • Dear all,

    I am trying to make a stacked columns chart using vba.

    My problem is that, despite my best efforts, it doesn't work.

    Here is my code;

    'Dump the data in a sheet
    
    ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = "Temporary"
    ThisWorkbook.Sheets("Temporary").Range("B2").Resize(UBound(graph_data), UBound(graph_data, 2)).Value = graph_data
    ThisWorkbook.Sheets("Temporary").Range("A2").Resize(UBound(graph_labels_down), 1).Value = Application.Transpose(graph_labels_down)
    
    ThisWorkbook.Sheets("Temporary").Range("B1").Resize(1, UBound(Category)).Value = Category
    
    ''Chart
    
    With ThisWorkbook.Worksheets("Update").ChartObjects.Add(Left:=375, Width:=475, Top:=topOfChart, Height:=325)
            .SetSourceData Source:=ThisWorkbook.Sheets("Temporary").Range(ThisWorkbook.Worksheets("Temporary").Cells(1, 1) _
        , ThisWorkbook.Worksheets("Temporary").Cells(UBound(graph_data) + 1, UBound(graph_data, 2) + 1))
            .Chart.ChartType = xlColumnStacked
            .Chart.HasTitle = True
            .Chart.ChartTitle.Text = passedChartTitle & " Sales"
    End With
    

    What I do is that I dump the data into a Temporary worksheet called "Temporary" (This is because it seems rather hard to pass the array directly to the graph; it that is not the case please tell me). Then I select the range to plot the graph on an other worksheet. The ThisWorkbook is used because I am opening an other workbook from which I am taking the data.

    I seem to have problems on the line saying;

    .SetSourceData Source:=ThisWorkbook.Sheets("Temporary").Range(ThisWorkbook.Worksheets("Temporary").Cells(1, 1) _ , ThisWorkbook.Worksheets("Temporary").Cells(UBound(graph_data) + 1, UBound(graph_data, 2) + 1))

    Could someone help me?

    Thanks in advance

    Best regards,

    Shutzi


    • Edited by shutzi Wednesday, March 27, 2013 3:27 PM
    Wednesday, March 27, 2013 3:18 PM

Answers

  • Try

            .Chart.SetSourceData Source:=ThisWorkbook.Sheets("Temporary").Range(ThisWorkbook.Worksheets("Temporary").Cells(1, 1) _
                , ThisWorkbook.Worksheets("Temporary").Cells(UBound(graph_data) + 1, UBound(graph_data, 2) + 1))
    
    


    Regards, Hans Vogelaar

    • Marked as answer by shutzi Wednesday, March 27, 2013 3:50 PM
    Wednesday, March 27, 2013 3:40 PM
  • You have to create the chart on a sheet, but once you have done so, you can export it to a .bmp, .jpg, .gif or .png file:

        .Chart.Export FileName:="C:\ExportedCharts\MyChart.png", FilterName:="PNG"

    If you use SetSourceData, you must specify a range.

    If you add series one by one, you can specify the XValues and Values properties of each series as an array:

        Dim ser As Series
        Set ser = .Chart.SeriesCollection.NewSeries
        ser.XValues = Array1
        ser.Values = Array2
        ser.Name = "MyData"

    Here, Array1 and Array2 are one-dimensional arrays.

    Regards, Hans Vogelaar

    • Marked as answer by shutzi Wednesday, March 27, 2013 4:46 PM
    Wednesday, March 27, 2013 4:11 PM

All replies

  • Try

            .Chart.SetSourceData Source:=ThisWorkbook.Sheets("Temporary").Range(ThisWorkbook.Worksheets("Temporary").Cells(1, 1) _
                , ThisWorkbook.Worksheets("Temporary").Cells(UBound(graph_data) + 1, UBound(graph_data, 2) + 1))
    
    


    Regards, Hans Vogelaar

    • Marked as answer by shutzi Wednesday, March 27, 2013 3:50 PM
    Wednesday, March 27, 2013 3:40 PM
  • I feel slightly ashamed. Thank you!

    Is there a way to make this chart not dependent on the data on tab "Temporary" (because I will delete this tab). i.e is there a way to save this chart as a picture?

    In addition, is there a way not to have to dump the whole data into a sheet? In other words is there a way to make this chart from an array?

    Again, Thank you!

    Wednesday, March 27, 2013 3:50 PM
  • You have to create the chart on a sheet, but once you have done so, you can export it to a .bmp, .jpg, .gif or .png file:

        .Chart.Export FileName:="C:\ExportedCharts\MyChart.png", FilterName:="PNG"

    If you use SetSourceData, you must specify a range.

    If you add series one by one, you can specify the XValues and Values properties of each series as an array:

        Dim ser As Series
        Set ser = .Chart.SeriesCollection.NewSeries
        ser.XValues = Array1
        ser.Values = Array2
        ser.Name = "MyData"

    Here, Array1 and Array2 are one-dimensional arrays.

    Regards, Hans Vogelaar

    • Marked as answer by shutzi Wednesday, March 27, 2013 4:46 PM
    Wednesday, March 27, 2013 4:11 PM
  • Thank you!
    Wednesday, March 27, 2013 4:47 PM