|
Home > Charts > Advanced > Macros Macros for Excel ChartsUse 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 Note: For pivot chart macros, go to the Pivot Chart VBA page. |

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
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
If you create an Excel chart, then hide rows or columns in your worksheet, the hidden data might also disappear from your chart. By default, Excel charts do not display the data in hidden rows and columns.

This video shows how to change an Excel chart's settings (manually), so all the data will appear in the chart, even if some of the data rows or data columns are hidden.
To make this change quickly, for multiple charts, you can use the macro shown below video.
There are two macros shown below -
-- VisDataOnlyOnALLSheets
-- VisDataOnlyOffALLSheets
There are details steps on the Show Hidden Data in Excel Chart page, and macros to change the hidden data settings for Sparkline charts too.
Copy this macro code, and paste it into a regular code module in your workbook. Later, run this macro to change the settings for all charts, on all worksheets in the active workbook.
The macros, and buttons to run them are also in the Hidden Data in Charts sample file that you can download, at the bottom of this page.
Sub VisDataOnlyOnALLSheets()
'only visible data in chart
'all charts - all worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim ch As ChartObject
Set wb = ActiveWorkbook
On Error Resume Next
For Each ws In wb.Worksheets
If ws.ChartObjects.Count > 0 Then
For Each ch In ws.ChartObjects
ch.Chart.PlotVisibleOnly = True
Next ch
Else
MsgBox "No charts on this sheet"
End If
Next ws
End Sub
'=================================
Sub VisDataOnlyOffALLSheets()
'include hidden data in chart
'all charts - all worksheets
Dim wb As Workbook
Dim ws As Worksheet
Dim ch As ChartObject
Set wb = ActiveWorkbook
On Error Resume Next
For Each ws In wb.Worksheets
If ws.ChartObjects.Count > 0 Then
For Each ch In ws.ChartObjects
ch.Chart.PlotVisibleOnly = False
Next ch
Else
MsgBox "No charts on this sheet"
End If
Next ws
End Sub
The following code will remove both the X and Y error bars, for the first chart on the active worksheet.

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
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
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
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:
We need to send another invoice to Fran -- her pie slice is large, and the orange colour shows a mid-range percentage invoiced

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

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.
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
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
Last updated: September 17, 2024 3:40 PM