Search Contextures Sites
Custom Search

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

 

Excel Charts - VBA

Code samples for working with charts by VBA programming.

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

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

Add Error Bars

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

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

 

__

 

Privacy Policy

 

Contextures Inc., Copyright 2016
All rights reserved.

 

Last updated: August 24, 2015 4:30 PM