This video demonstrates how to write a VBA macro that will create a Chart in Excel. The VBA code can be found below the video.
1) How to create a chart object on the same sheet as its data
2) How to create a chart on a chart sheet
3) How to position a chart on a worksheet
4) How to size a chart’s width and height
5) How to apply a chart style
6) How to add data labels
7) How to turn gridlines off
8) How to position the chart legend
9) How to axis labels visible/invisible
10) How to axis titles
11) How to set a colour for each series in your chart
12) How to format the background colour of the chart area
VBA Code to Create Chart as Object on Worksheet
Sub CreateChartObject()
Dim ws As Worksheet
Dim ch As Chart
Dim dt As Range
Set ws = ActiveSheet
Set dt = Range("Sales_Data[#All]")
Set ch = ws.Shapes.AddChart2(Style:=280, Width:=300, Height:=500, _
Left:=Range("F1").Left, Top:=Range("F1").Top).Chart
With ch
.SetSourceData Source:=dt
.ChartType = xlBarClustered
.ChartTitle.Text = "Sales by Year"
.SetElement msoElementDataLabelOutSideEnd
.SetElement msoElementPrimaryValueGridLinesNone
.SetElement msoElementLegendTop
.SetElement msoElementPrimaryValueAxisNone
.SetElement msoElementPrimaryCategoryAxisTitleBelowAxis
.Axes(xlCategory).AxisTitle.Text = "Region"
.SeriesCollection("Sales 2016").Interior.Color = RGB(255, 0, 0)
.SeriesCollection("Sales 2017").Interior.Color = RGB(100, 0, 0)
.SeriesCollection("Sales 2018").Interior.Color = RGB(50, 0, 0)
.ChartArea.Format.Fill.ForeColor.RGB = RGB(221, 217, 185)
End With
End Sub
VBA Code to Create Chart on Chart Sheet
Sub CreatingChartOnChartSheet()
Dim ch As Chart
Dim dt As Range
Set dt = Range("Sales_Data[#All]")
Set ch = Charts.Add2
With ch
.SetSourceData Source:=dt
.ChartType = xlBarClustered
.ChartTitle.Text = "Sales by Year"
.SetElement msoElementDataLabelOutSideEnd
.SetElement msoElementPrimaryValueGridLinesNone
.SetElement msoElementLegendTop
.SetElement msoElementPrimaryValueAxisNone
.SetElement msoElementPrimaryCategoryAxisTitleBelowAxis
.Axes(xlCategory).AxisTitle.Text = "Region"
.SeriesCollection("Sales 2016").Interior.Color = RGB(255, 0, 0)
.SeriesCollection("Sales 2017").Interior.Color = RGB(100, 0, 0)
.SeriesCollection("Sales 2018").Interior.Color = RGB(50, 0, 0)
.ChartArea.Format.Fill.ForeColor.RGB = RGB(221, 217, 185)
.Name = "Sales Chart"
End With
End Sub