Contextures

Home > Charts > Advanced > Macros

Macros for Excel Charts

Use these sample macros to work with Excel charts. For example, export a chart as a picture, apply colour to pie chart slices, or delete all charts

client invoice data pie chart

Export Picture of Active Chart

The following code will export a picture of the active chart, in gif format. Change the file name and path to suit your needs.

Sub ExportChartGIF()
ActiveChart.Export filename:="C:\Data\ExpChart2K.gif", _
  FilterName:="GIF"
End Sub

Delete All Charts in Workbook

Use this sample code carefully! It deletes all the charts in the active workbook, and is helpful if you want to clear things out and start fresh.

Sub AllChartsDelete()
On Error GoTo ExitChart
Dim ws As Worksheet
Dim chObj As ChartObject
  Application.DisplayAlerts = False
  'delete embedded charts
  For Each ws In ActiveWorkbook.Worksheets
    For Each chObj In ws.ChartObjects
       chObj.Delete
    Next chObj
  Next ws
  'delete the chart sheets
  ActiveWorkbook.Charts.Delete

ExitChart:
  Application.DisplayAlerts = True
  Exit Sub
End Sub

Remove Error Bars go to top

The following code will remove both the X and Y error bars, for the first chart on the active worksheet.

remove error bars

Sub RemoveErrorBars()

Dim ch As ChartObject
Dim ser As Series
  Set ch = ActiveSheet.ChartObjects(1)
  Set ser = ch.Chart.SeriesCollection(1)
   ser.ErrorBar Direction:=xlX, _
        Include:=xlErrorBarIncludeNone, Type:=xlStError
   ser.ErrorBar Direction:=xlY, _
        Include:=xlErrorBarIncludeNone, Type:=xlStError

End Sub

Add Error Bars go to top

The following code will add both the X and Y error bars, for the first chart on the active worksheet.

The Y error bars will have a fixed value of 2, and the X error bars will be set for 10%.

Sub AddErrorBars()


Dim ch As ChartObject
Dim ser As Series

Set ch = ActiveSheet.ChartObjects(1)
Set ser = ch.Chart.SeriesCollection(1)
  ser.ErrorBar Direction:=xlY, _
     Include:=xlErrorBarIncludeBoth, _
     Type:=xlErrorBarTypeFixedValue, _
     Amount:=2
  ser.ErrorBar Direction:=xlX, _
     Include:=xlErrorBarIncludeBoth, _
     Type:=xlErrorBarTypePercent, _
     Amount:=10

End Sub

Add or Remove Error Bars go to top

The following code will add the X error bars, and remove Y error bars, for the first chart on the active worksheet.

The X error bars will be set for 10%.

Sub SetErrorBars()

Dim ch As ChartObject
Dim ser As Series

Set ch = ActiveSheet.ChartObjects(1)
Set ser = ch.Chart.SeriesCollection(1)

ser.ErrorBar Direction:=xlX, _
    Include:=xlErrorBarIncludeBoth, _
    Type:=xlErrorBarTypePercent, _
    Amount:=10
ser.ErrorBar Direction:=xlY, _
    Include:=xlErrorBarIncludeNone, _
    Type:=xlStError

End Sub

Pie Chart Colour Macro

This macro uses conditional formatting to colour the slices in an Excel pie chart.

In the pie chart, the slice size and colour gives us information about client orders and invoices:

  • slice size based on the client's order amount
  • slice colour based on a colour scale for the % invoiced

We need to send another invoice to Fran -- her pie slice is large, and the orange colour shows a mid-range percentage invoiced

client invoice data pie chart

Pie Chart Data

Here’s a screen shot of the sample data for this pie cahrt.

  • Client names in column B
  • Client order totals in column C
  • Total invoiced amounts in column D
  • Formulas in column E calculate percent invoiced
  • Conditional formatting colour scale in column E - green highest, red lowest

client invoice data with conditional formatting

Pie Chart Colour Match Macro

There's no built-in way to match a pie slice colour to a cell's condtional formatting colour, so I wrote a macro to do the job.

  • The macro gets the location of the source data, based on the formula for the chart series.
  • Then, the macro gets the conditional formatting color from the 3rd column to the right of the source data’s start column, by using the DisplayFormat property for the cell
  • That color is used to colour the applicable pie chart slice

Macro VBA Code

Here is the VBA code, and it's also available in the sample file you can download. The sample file also has a bar chart on the worksheet, and the macro colours its bars too.

Sub ColorChartDataPoints()
'colour data point based on
'value in rank column
Dim ws As Worksheet
Dim ch As ChartObject
Dim ser As Series
Dim dp As Point
Dim ptnum As Long
Dim rngSD01 As Range
Dim strF  As String
Dim strRng  As String
Dim CharStart As Long
Dim CharEnd As Long
Dim ColOff As Long
Dim PtColor As Long

ColOff = 3  'offset to Rank column
Set ws = ActiveSheet

For Each ch In ws.ChartObjects
  Set ser = ch.Chart.FullSeriesCollection(1)
  strF = ch.Chart.SeriesCollection(1).Formula
   
  CharStart = InStr(1, strF, ",")
  CharEnd = InStr(InStr(1, strF, ",") _
        + 1, strF, ",")
  strRng = Mid(strF, CharStart + 1, _
         CharEnd - CharStart - 1)
  Set rngSD01 = ws.Range(strRng)
  ptnum = 1
  
  For Each dp In ser.Points
    PtColor = rngSD01.Cells(ptnum, 1) _
        .Offset(0, ColOff).DisplayFormat.Interior.Color
    dp.Format.Fill.ForeColor.RGB = PtColor
    ptnum = ptnum + 1
  Next dp
Next ch

End Sub

Running the Macro

The macro runs automatically, if any changes are made in the named range, "ClientAmts". The following event code is on the worksheet code module for the sheet named Chart

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ClientAmts")) Is Nothing Then
  ColorChartDataPoints
End If
End Sub

Download Sample File

Chart Colour Based on Rank -- Get the sample Excel Chart Colour Macro workbook, with VBA macro that changes the pie chart and bar chart colours, based on rank. The zipped file is in xlsm format, and contains macros

More Tutorials

Show Hidden Data in Charts

Interactive Chart - Totals

Cluster Stack Chart

Pie Charts

Charts, Line-Column 2 Axes

Charts, Panel

Charts, Waterfall

VBA Code, Copy to a workbook

 

About Debra

 

Last updated: November 5, 2022 11:55 AM