Issue with creating stacked column chart

2
votes

I am trying to create a stacked column chart using excel vba.input data

Following mentioned below is my excel vba code for generating the stacked column chart for the corresponding input data.

      Sub to_draw_chart()
         ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
         ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$P$4")
         ActiveChart.Axes(xlValue).Select
         ActiveChart.Axes(xlValue).MaximumScale = 1000
         ActiveChart.Axes(xlValue).MajorUnit = 250

         ActiveChart.ChartArea.Select
         ActiveChart.Axes(xlCategory).Select

         ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
         ActiveChart.Axes(xlCategory).CategoryType = xlAutomatic
         Application.CommandBars("Format Object").Visible = False
         ActiveChart.FullSeriesCollection(2).Select
         ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
         Selection.Format.Fill.Visible = msoFalse
         ActiveChart.FullSeriesCollection(5).Select
         Selection.Format.Fill.Visible = msoFalse
         ActiveChart.FullSeriesCollection(12).Select
         Selection.Format.Fill.Visible = msoFalse
         ActiveChart.FullSeriesCollection(15).Select
         Selection.Format.Fill.Visible = msoFalse

         ActiveChart.ChartArea.Select
         ActiveChart.ChartTitle.Select
         ActiveChart.ChartTitle.Text = "Chart "
         Selection.Format.TextFrame2.TextRange.Characters.Text = "Chart "
      End Sub

outputExpected_VS_output_getting

But when i run this macro, the output i am getting is different. the problem is in the x axis. my x axis should be D0, D1,D2(as you can see in image "Output Expected". But it is different. i have also attached the output i am getting when i run the vba code (Second image).

I am not understanding why my x axis is getting changed which is indeed affecting the code and the output graph.

Manually when i do without using code then i am getting the correct output.

where am i going wrong?

2
vbaexcel
see my answer below (another option to handle the Chart object) - Shai Rado
yes, it worked. thank you very much. - user28
then please upvote and mark as answer - Shai Rado

2 Answers

0
votes

You need to Switch Row/Column by adding ActiveChart.PlotBy = xlColumns. The following should solve the issue.

  Sub to_draw_chart()
     ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
     ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$P$4")
     ActiveChart.PlotBy = xlColumns
     ActiveChart.Axes(xlValue).Select
     ActiveChart.Axes(xlValue).MaximumScale = 1000
     ActiveChart.Axes(xlValue).MajorUnit = 250

     ActiveChart.ChartArea.Select
     ActiveChart.Axes(xlCategory).Select

     ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
     ActiveChart.Axes(xlCategory).CategoryType = xlAutomatic
     Application.CommandBars("Format Object").Visible = False
     ActiveChart.FullSeriesCollection(2).Select
     ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
     Selection.Format.Fill.Visible = msoFalse
     ActiveChart.FullSeriesCollection(5).Select
     Selection.Format.Fill.Visible = msoFalse
     ActiveChart.FullSeriesCollection(12).Select
     Selection.Format.Fill.Visible = msoFalse
     ActiveChart.FullSeriesCollection(15).Select
     Selection.Format.Fill.Visible = msoFalse

     ActiveChart.ChartArea.Select
     ActiveChart.ChartTitle.Select
     ActiveChart.ChartTitle.Text = "Chart "
     Selection.Format.TextFrame2.TextRange.Characters.Text = "Chart "
  End Sub
1
votes

Not sure what is your desired final chart with the code supplied, since I didn't use some of your code lines.

There is another option, a "cleaner" and more efficient way to handle charts (there is no need to select them all time, I think you did it with the MACRO recorder).

Anyway, see my code below, it gave me the exact result like with the manual step (just like in your attached images).

Option Explicit

Sub to_draw_chart()

Dim Sht1                   As Worksheet

' modify to your sheet name
Set Sht1 = ThisWorkbook.Sheets("Sheet1")

' change Left, Top, Width , Height according to your needs
Sht1.Shapes.AddChart(xlColumnStacked, 200, 200, 500, 500).Select

With ActiveChart
    .SetSourceData Source:=Range("Sheet2!$A$1:$P$4")
    .Axes(xlValue).MaximumScale = 1000
    .Axes(xlValue).MajorUnit = 250

    .HasTitle = True
    .ChartTitle.Text = "Chart "
    .ChartTitle.Format.TextFrame2.TextRange.Characters.Text = "Chart "

    .Axes(xlCategory).CategoryType = xlCategoryScale
    .Axes(xlCategory).CategoryType = xlAutomatic

' not sure what is the purpose with the lines below ?      
'         Application.CommandBars("Format Object").Visible = False
'         ActiveChart.FullSeriesCollection(2).Select
'         ActiveChart.Axes(xlCategory).CategoryType = xlCategoryScale
'         Selection.Format.Fill.Visible = msoFalse
'         ActiveChart.FullSeriesCollection(5).Select
'         Selection.Format.Fill.Visible = msoFalse
'         ActiveChart.FullSeriesCollection(12).Select
'         Selection.Format.Fill.Visible = msoFalse
'         ActiveChart.FullSeriesCollection(15).Select
'         Selection.Format.Fill.Visible = msoFalse
End With   

End Sub