Contextures

Macros for Excel Charts

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

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

Related Tutorials

Interactive Chart - Totals

Excel Charting Add-in

Charts, Line-Column 2 Axes

Charts, Panel

Charts, Waterfall

VBA Code, Copy to a workbook

Search Contextures Sites

 

Excel Tools Add-in

 

Free Pivot Table Tools

 

 

excel chart tools by peltier tech

 

 

 

Pivot Power Premium

 

Excel Data Entry Popup List

 

Excel UserForms for Data Entry

 

Last updated: October 25, 2016 7:47 PM
Contextures RSS Feed