Using VBA to Create Charts in Excel

The VBA code below creates a clustered column chart for each variable. The code is dynamic and will accommodate adding variables (columns of data) and adding trials (rows of data) to the worksheet. Each chart will be 500 pixels wide by 250 pixels tall. Each chart will be 310 pixels from the left edge of the worksheet. The first chart is 10 pixels from the top edge of the worksheet and each chart after that will be 10 pixels from the chart above it. All of these parameters can be adjusted as you see fit.

Microsoft Excel - Andon Excel - Creating charts in Excel from data tables in spreadsheets.

Sub CreateGraph()

Dim SH1 As Worksheet

Set SH1 = ThisWorkbook.Worksheets("Sheet1")

Dim LR_SH1 As Integer

Dim LC_SH1 As Integer

Dim C_Chart As ChartObject

Dim Count1 As Integer

' Identifying the last row of data and the last column of variables.

LR_SH1 = SH1.Cells(Rows.Count, "A").End(xlUp).Row

LC_SH1 = SH1.Cells(1, Columns.Count).End(xlToLeft).Column

Count1 = 0

' Deleting all existing charts on Sheet1

On Error Resume Next

SH1.ChartObjects.Delete

' Creating a chart for each column starting at column "C".

For A = 3 To LC_SH1

With SH1.ChartObjects.Add(Left:=310, Width:=500, Top:=10 + 260 * Count1, Height:=250)

With .Chart.SeriesCollection.NewSeries

.ChartType = xlColumnClustered

.Values = SH1.Range(SH1.Cells(2, A), SH1.Cells(LR_SH1, A))

.XValues = SH1.Range(SH1.Cells(2, 1), SH1.Cells(LR_SH1, 1))

.Name = SH1.Cells(1, A).Value

End With

.Chart.SetElement (msoElementChartTitleAboveChart)

.Chart.ChartTitle.Text = SH1.Cells(1, A).Value

.Chart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)

.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Trial"

.Chart.SetElement (msoElementPrimaryValueAxisTitleRotated)

End With

Count1 = Count1 + 1

Next A

End Sub