Lesson 24: Creating Charts and Graphs in Excel VBA 365

❮ Previous Next Lesson ❯

In Excel VBA 365, we can automate the creation of professional charts and graphs to visualize spreadsheet data. This powerful feature saves time and ensures consistency in your reports and dashboards.

Pro Tip: Charts created with VBA can be updated automatically when source data changes, making them perfect for dynamic reports.

24.1 Creating a Pie Chart

Pie charts are excellent for showing proportional relationships. Here's how to create one with VBA:

Step-by-Step Process:

Prepare your data range in the worksheet
Name the range (right-click > Define Name) for easier reference
Insert a command button to trigger the chart creation

The complete VBA code to create a 3D pie chart:

Private Sub CommandButton1_Click()
  'Add and position the chart
  ActiveSheet.Shapes.AddChart.Select
  ActiveSheet.Shapes(1).Top = 10
  ActiveSheet.Shapes(1).Left = 10
  
  'Set chart type and data source
  ActiveChart.ChartType = xl3DPie
  ActiveChart.PlotArea.Select
  ActiveChart.SetSourceData Source:=Range("MyChart")
  
  'Add and format chart title
  ActiveChart.HasTitle = True
  ActiveChart.ChartTitle.Text = "Sales Distribution"
  ActiveChart.ChartTitle.Font.Size = 14
  ActiveChart.ChartTitle.Font.Bold = True
End Sub

3D Pie Chart created with Excel VBA — Figure 24.1: Professional 3D Pie Chart created with VBA

Creating Exploded Pie Charts

To emphasize specific segments, use the exploded pie chart variant:

'Change this line in the previous code
ActiveChart.ChartType = xl3DPieExploded

Exploded 3D Pie Chart — Figure 24.2: Exploded Pie Chart highlights individual segments

24.2 Creating a Bar Chart

Bar charts (or column charts) are ideal for comparing values across categories. Here's the VBA code to create one:

Private Sub CommandButton2_Click()
  'Add and position the chart
  ActiveSheet.Shapes.AddChart.Select
  ActiveSheet.Shapes(1).Top = 10
  ActiveSheet.Shapes(1).Left = 10
  
  'Set chart type and data source
  ActiveChart.ChartType = xl3DColumn
  ActiveChart.PlotArea.Select
  ActiveChart.SetSourceData Source:=Range("MyChart")
  
  'Add chart title
  ActiveChart.HasTitle = True
  ActiveChart.ChartTitle.Text = "Quarterly Sales Report"
  
  'Format axes
  ActiveChart.Axes(xlCategory).HasTitle = True
  ActiveChart.Axes(xlCategory).AxisTitle.Text = "Products"
  ActiveChart.Axes(xlValue).HasTitle = True
  ActiveChart.Axes(xlValue).AxisTitle.Text = "Sales ($)"
End Sub

3D Bar Chart created with VBA — Figure 24.3: 3D Bar Chart comparing values across categories

Excel VBA Chart Types

Excel VBA 365 supports numerous chart types. Here are the most commonly used ones:

Table 24.1: Common ChartType Properties in Excel VBA
Property	Chart Type	Best For
xlArea	Area Chart	Showing magnitude over time
xlBar	Bar Chart	Comparing values across categories
xlColumn	Column Chart	Vertical bar comparisons
xlLine	Line Chart	Trends over time
xl3DLine	3D Line Chart	Multi-series trends
xlPie	Pie Chart	Proportional relationships
xlXYScatter	Scatter Plot	Correlation between variables
xl3DArea	3D Area Chart	Volume representation
xl3DBar	3D Bar Chart	Enhanced visual comparisons
xl3DColumn	3D Column Chart	Dramatic value comparisons

For a complete list of ChartType properties, refer to the official Microsoft documentation.

24.3 Creating a Line Chart

Line charts are perfect for showing trends over time. Here's how to create one with VBA:

Private Sub CommandButton3_Click()
  'Add and position the chart
  ActiveSheet.Shapes.AddChart.Select
  ActiveSheet.Shapes(1).Top = 10
  ActiveSheet.Shapes(1).Left = 10
  
  'Set chart type and data source
  ActiveChart.ChartType = xlLine
  ActiveChart.PlotArea.Select
  ActiveChart.SetSourceData Source:=Range("MyChart")
  
  'Add chart title
  ActiveChart.HasTitle = True
  ActiveChart.ChartTitle.Text = "Monthly Sales Trend"
  
  'Format line style
  ActiveChart.SeriesCollection(1).Format.Line.Weight = 2.5
  ActiveChart.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 0, 255)
  
  'Add data labels
  ActiveChart.SeriesCollection(1).HasDataLabels = True
  ActiveChart.SeriesCollection(1).DataLabels.NumberFormat = "$#,##0"
End Sub

Line Chart showing trends — Figure 24.4: Line Chart visualizing trends over time

24.4 Advanced Chart Customization

Excel VBA allows extensive customization of your charts. Here are some useful techniques:

Adding a Legend

'Position the legend
ActiveChart.Legend.Position = xlLegendPositionBottom

'Format legend text
ActiveChart.Legend.Font.Size = 10
ActiveChart.Legend.Font.Bold = True

Changing Chart Colors

'Change series color
ActiveChart.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)

'Apply gradient fill to chart area
With ActiveChart.ChartArea.Format.Fill
    .ForeColor.RGB = RGB(255, 255, 255)
    .TwoColorGradient msoGradientHorizontal, 1
End With

Deleting Charts

To clean up your worksheets, use this code to delete all charts:

'Delete all charts on Sheet1
Worksheets("Sheet1").ChartObjects.Delete

'Delete specific chart by name
Worksheets("Sheet1").ChartObjects("Chart 1").Delete

Best Practices for VBA Charting

Always name your data ranges for easier reference
Include error handling for missing data
Use variables to store chart objects for cleaner code
Add comments to explain complex formatting
Consider creating chart templates for consistent styling

Summary: Key Takeaways

Chart Creation: Use Shapes.AddChart method to create charts programmatically
Chart Types: Excel VBA supports numerous chart types (pie, bar, line, etc.) through ChartType property
Data Binding: Connect charts to data using SetSourceData method with named ranges
Customization: Extensive formatting options available for titles, colors, legends, and more
Automation: VBA charts can be dynamically updated when source data changes
Cleanup: Use ChartObjects.Delete to remove charts from worksheets

🔗 Related Resources

❮ Previous Lesson Next Lesson ❯

Contact: Facebook Page