How to create a chart in VBA excel

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to create a chart in VBA excel
Create a chart using Visual Basic for Applications in Excel. (businessman and chart image by Kit Wai Chan from Fotolia.com)

Knowing how to dynamically create charts with Visual Basic for Applications can save time when analysing large amounts of data. Microsoft Excel is commonly used to collect data similar to an Access database. Excel offers different types of charts such as pie, line, column, and other formats. Column charts are often used in Excel to graphically display information. It is not difficult to create charts using VBA to present your data clearly and more efficiently.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

    Open Microsoft Excel 2007 and click on the "Developer" tab. Select "Visual Basic and type "Sub createColumnChart()" and press "Enter" to create a new sub.

  2. 2

    Type "Dim my Chart As Chart

    Range("A1").Select
    
    ActiveCell.Value = "Chart Data 1"
    
    Range("A2").Select
    
    ActiveCell.Value = "1"
    
    Range("A3").Select
    
    ActiveCell.Value = "2"
    
    Range("A4").Select
    
    ActiveCell.Value = "3"
    
    Range("A5").Select
    
    ActiveCell.Value = "4"
    
    Range("B1").Select
    
    ActiveCell.Value = "Chart Data 2"
    
    Range("B2").Select
    
    ActiveCell.Value = "5"
    
    Range("B3").Select
    
    ActiveCell.Value = "6"
    
    Range("B4").Select
    
    ActiveCell.Value = "7"
    
    Range("B5").Select
    
    ActiveCell.Value = "8"
    
    Range("B6").Select" to declare the chart variable and add some data for your chart.
    
  3. 3

    Type "Set my Chart = Charts.Add" to add a new chart.

    Type "With my Chart

      .Name = "Chart Data"
    
      .ChartType = xlColumnClustered
    
      .SetSourceData Source:=Sheets("Sheet1").Range("A1:B5"), _
    
         PlotBy:=xlRows
    
      .HasTitle = True
    
      .ChartTitle.Text = "=Sheet1!R1C2"
    
      .Axes(xlCategory, xlPrimary).HasTitle = True
    
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Chart Data 1"
    
      .Axes(xlValue, xlPrimary).HasTitle = True
    
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Chart Data 2"
    

    End With" to set chart properties.

  4. 4

    Type "End Sub" if needed. Execute your new sub.

Tips and warnings

  • Your new sub should look like this:
  • "Sub createColumnChart()
  • Dim my Chart As Chart
  • Range("A1").Select
  • ActiveCell.Value = "Chart Data 1"
  • Range("A2").Select
  • ActiveCell.Value = "1"
  • Range("A3").Select
  • ActiveCell.Value = "2"
  • Range("A4").Select
  • ActiveCell.Value = "3"
  • Range("A5").Select
  • ActiveCell.Value = "4"
  • Range("B1").Select
  • ActiveCell.Value = "Chart Data 2"
  • Range("B2").Select
  • ActiveCell.Value = "5"
  • Range("B3").Select
  • ActiveCell.Value = "6"
  • Range("B4").Select
  • ActiveCell.Value = "7"
  • Range("B5").Select
  • ActiveCell.Value = "8"
  • Range("B6").Select
  • Set my Chart = Charts.Add
  • With my Chart
  • .Name = "Chart Data"
  • .ChartType = xlColumnClustered
  • .SetSourceData Source:=Sheets("Sheet1").Range("A1:B5"), _
  • PlotBy:=xlRows
  • .HasTitle = True
  • .ChartTitle.Text = "=Sheet1!R1C2"
  • .Axes(xlCategory, xlPrimary).HasTitle = True
  • .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Chart Data 1"
  • .Axes(xlValue, xlPrimary).HasTitle = True
  • .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Chart Data 2"
  • End With
  • End Sub"

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.