We can create charts using Excel VBA quickly and easily. Creating charts can be the starting point for creating dashboards. There are two types of charts in MS Excel: one type of chart is created on its own worksheet and is known as a chart object and the other type of chart is an embedded chart in the worksheet next to the data and is called a chartobject. Chart sheets are defined as members of the workbook charts collection. A chart embedded in a worksheet is a member of the worksheet chartobjects collection. This may sound a little confusing but as you work with the sample VBA codes you will understand this concept quickly.
Below is the code for creating a column chart on a separate chart sheet using VBA:

Sub createmychart()
Dim chart1 As Chart
Set chart1 = Charts.Add
chart1.SetSourceData Source:=Worksheets(“sheet1”).Range(“A1”).CurrentRegion, PlotBy:=xlColumns
chart1.ChartType = xlColumnClustered
End Sub

Watch the Excel training video:

Further reading:
Creating Charts in Excel 2003 Using Visual Basic for Applications Code