Creating a stacked bar chart in Excel using VBA

[B416]

Hello,

I am trying to create a stacked bar chart in Excel, using VBA.

I have a subroutine that creates the data on my "chart" worksheet in this range A1:B6 (includes titles).

I used the macro recorder to obtain the code below, which I pasted into my subroutine, after the data has been generated.

Unfortunately, when I try to run it, I get a Run-time error '1004' Application-defined or object-defined error.

I've tried looking up what that means, but I still don't really understand what's happening.

In the code below, the debugger is highlighting this line:

.DisplayUnit = xlNone


Any help/suggestions would certainly be appreciated. Thank you.

-----------------

Range("A1:B6").Select
Charts.Add
ActiveChart.ChartType = xlColumnStacked100
ActiveChart.SetSourceData Source:=Sheets("chart").Range("A1:B6"), PlotBy:= _
xlRows
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Segment ranking"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Segment ranking"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = True
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.ChartArea.Select
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = False
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic

 

[Andrew Poulsom]

According to VBA Help the valid values for DisplayUnit are:

xlHundredMillions
xlHundredThousands
xlMillions
xlTenThousands
xlThousands
xlHundreds
xlMillionMillions
xlTenMillions
xlThousandMillions

 

[B416]

Thank you for the quick response.

I've tried replacing "xlNone" with each of those values, respectively, but I am still getting the same error and the same line in the code is highlighted by the debugger.

Is there anything else that could be causing the error?

Thanks.

 

[Andrew Poulsom]

Why don't you just remove it?

 

[B416]

Brilliant! It worked. Thank you again for your help.