AutoScaleFont
This decides whether the font size changes automatically when a chart is resized.
This can be used so the size of chart items is adjusted automatically to accomadate the size of the object.
AutoScaleFont = False
Selection.AutoScaleFont = False
Worksheet or Chart Sheet ?
A chart sheet is a chart object in its own right, whereas an embedded chart is contained by a chart object.
Each chart object is a member of the Shapes collection as well as a member of the ChartObjects collection.
The Shapes collection provides you with an alternative way to refer to embedded charts.
The macro recorder will generate code that uses the Shapes collection.
Every workbook has a Sheets collection that contains both worksheets and chart sheets.
There is also an additional Charts collection that contains just the chart sheets in a workbook.
Any charts that are embedded on the worksheet are not included in the Charts collection.
Charts embedded on a worksheet are contained on the ChartObjects collection.
If TypeName(ActiveSheet) = "Worksheet"
If TypeName(ActiveSheet) = "Chart Sheet"
The Charts collection only includes chart sheets (not charts embedded on a worksheet).
Charts embedded on a worksheet belong to the Worksheet.ChartObjects collection.
ChartObjects
If you select a chartobjects(2000) and there are not 2000 chart objects on the current worksheet then - does it default to 1 ??
ActiveSheet.ChartObjects(1).Chart.Shapes("Arrow1")
ActiveChart.ChartObjects("").Activate
Check the colours are correct for all charts including those with a single series.
Need to check that a chart axes actually exists before trying to modifying it incase the chart has no data.
Pie charts should always be displayed in descending order of size
Any zero values should be automatically removed from the data source.
Fix the last cumulative performance data to always be the last most recent month
Is it possible to add superscript to a chart label/title (so you don't have to manually insert a textbox)
It is possible to add VBA controls to "chart sheets" as well.
ActiveChart.CheckSpelling
DisplayUnitXxx properties
Adding and refreshing data
ActiveChart.Refresh
ActiveChart.Axes(xlAxisType.xlCategory).TickLabels.NumberFormat = "ddd"
ActiveChart.Axes(xlCategory).TickLabels.NumberFormatLinked = False
ActiveChart.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
ActiveChart.Axes(xlCategory).TickLabels.Alignment = xlCenter
ActiveChart.Axes(xlCategory).TickLabels.Offset = 100
ActiveChart.Axes(xlCategory).TickLabels.Font.Strikethrough = False
ActiveChart.Axes(xlCategory).TickLabels.Font.Superscript = False
ActiveChart.Axes(xlCategory).TickLabels.Font.Subscript = False
ActiveChart.Axes(xlCategory).TickLabels.AutoScaleFont = False
ActiveChart.Subtype = 2
Chart Sheets
This refers to the first chart sheet in the active workbook
Dim objChart As Chart
Set objChart = ActiveWorkbook.Charts(1)
Set objChart = Sheets(1).Charts(1)
Set objChart = ThisWorkbook.Charts(sWshName)
Set objChart = Sheets(sWshName)
Activating
It is possible to activate a chart on a chart sheet as well.
This will activate the chart on the first chart sheet of the active workbook
ActiveWorkbook.Charts(1).Activate
Sheets("Chart1").Activate
Notice that the Activate method applies to the chart object in this case.
Once a chart has been activated it can be referred to using the ActiveChart property (which returns a Chart object)
Dim objChart As Chart
objChart = ActiveChart
Object Model
Application
Workbook
Chart
A chart sheet is actually a chart object and has no ChartObject class.
The parent object of a chart on a Chart Sheet is the Workbook object.
If TypeName(objChart.Parent) = "Workbook"
When a chart has been given a specific name
Activesheet.ChartObjects("Chart_Name").Activate
Looping Through All Charts
All the chart sheets in a workbook
For Each objChart In ActiveWorkbook.Charts
Next objChart
Bug - Last Worksheet
Strangely enough Excel will not allow you to add a new chart after the last worksheet although you can move a chart after the last worksheet.
If you want to insert chart sheets after the last worksheet you can use the following code:
Dim objWorksheet As Worksheet
Dim objChart As Chart
Set objWorksheet = Sheets(Sheets.Count)
Set objChart = Charts.Add
objChart.Move After:=objWorksheet
Embedded Charts
A worksheet can contain any number of embedded charts.
This refers to the first embedded chart on the first worksheet in the active workbook
Dim objChart As Chart
objChart = ActiveWorkbook.Worksheets(1).ChartObjects(1).Chart
ActiveWorkbook.Worksheets(1).ChartObjects(1).Protect
Lets you view and print but you can't change
ActiveWorkbook.Worksheets(1).ChartObjects(1).UnProtect
Setting this property means that the chart object will be moved with the cells.
ChartObject.Placement = xlPlacement.xlMove
Activating
When you select an embedded chart you "activate" it. It is possible to activate a chart from code using the Activate method.
This will activate the first embedded chart on the first worksheet of the active workbook
ActiveWorkbook.Worksheets(1).ChartObjects(1).Activate
This will activate the first embedded chart on the active worksheet
ActiveSheet.ChartObjects(1).Activate
Notice that the Activate method applies to the actual Chart Object and not the Chart Object.
The following line of code will generate an error
ActiveWorkbook.Worksheets(1).ChartObjects(1).Chart.Activate
Object Model
Application
Workbook
Worksheet
ChartObject
Chart
The parent object of an embedded chart is the ChartObject object.
If TypeName(objChart.Parent) = "ChartObject"
Looping Through All Charts
All the embedded charts on a worksheet
For Each objChart In ActiveSheet.ChartObjects
Next objChart
ChartGroups Collection
This is a collection of all the ChartGroup objects
Each ChartGroup represents one or more series plotted in a chart with the same format.
A ChartGroup can contain one or more series.
ActiveChart.ChartGroups.Count
objChartGroup.AreaGroups
objChartGroup.BarGroups
objChartGroup.ColumnGroups
objChartGroup.DoughnutGroups
objChartGroup.LineGroups
objChartGroup.PieGroups
Page Setup
objChart.PageSetup
Activating
If you use the macro recorder to record chart related macros you will find that the code generated always activates the chart and then selects the objects that are being manipulated
This is not actually necessary. It is possible to modify a chart without actually selecting, or even activating the chart.
To modify a chart with VBA it is not necessary to activate the chart first.
If you reference the ActiveChart and a chart has not been activated then an error will be generated.
Once a chart is activated you can refer to it in your code as ActiveChart.
To modify a chart using VBA it is not necessary to activate it.
When you activate a chart contained in a ChartObject, the chart actually is contained in a window that is normally invisible.
To see an embedded chart in its own window, right click the chart object and select Chart Window on the shortcut menu.
The embedded chart remains on the worksheet but the chart also appears in its own floating window.
You can move and resize this window, but you cannot maximise it.
If you move the window you will notice that the embedded chart is still displayed in its original location.
Activating any other window makes the ChartObject window invisible again.
Determining whether a Chart is Activated
The following line of code will only work for chart sheets and not for embedded charts
If TypeName(Selection) = "Chart"
If TypeName(Selection) = "ChartObject"
Theonly way to check if a chart is active that will work for both embedded charts and chart sheets is to force an error.
Function ChartIsSelected() As Boolean
Dim sChartName As String
On Error GoTo AnError
ChartIsSelected = True
sChartName = ActiveChart.Name
Exit Function
AnError:
ChartIsSelected = False
End Function
ActiveChart.DeSelect
ActiveWorkbook.Charts
These are chart sheets (include a link to this page)
ActiveWorkbook.Charts("Chart 2").Activate
ActiveSheet.Charts
ActiveSheet.Charts(1).Activate
ActiveSheet.ChartObjects
ActiveSheet.ChartObjects(2).Activate
When a chart has been given a specific name
ActiveSheet.ChartObjects("Chart_Name").Activate
The chart object containing the selected chart ??
Private Sub ReturnChartObject() As ChartObject
Dim objChart As Chart
Dim objChartObject As ChartObject
objChart = ActiveChart
objChartObject = objChart.Parent
ReturnChartObject = objChartObject
End Sub
ActiveSheet.Shapes
= ActiveSheet.Shapes("Chart1") ?
Set objChart = objChartObject.Chart
Set objChart = ActiveSheet.ChartObjects("Chart 1").Chart
Problems if you try and change size from a modeless userform ??
Looping Through
Dim lcount As Long
For lcount = 1 to ActiveSheet.ChartObjects.Count
Set objChart = ActiveSheet.ChartObjects(lcount).Chart
Next lcount
Cell Under Chart
Range(ActiveChart.Parent.TopLeftCell.Address).Select
Interior
There are two ways you can assign a particular colour to the interior of a series.
You can either use the .ColorIndex property or the .Color property.
You should always use the Colorindex and never the Color
The reason for this is because the Color property is not accurate
If you assign a colour that is not on the colour palette then it will not be assigned
Excel will scan the colour palette and will assign a colour from the colour palette which it thinks is the closest match.
This is not a problem in 2007 as the chart colours can be completely independent from the active theme.
ColorIndex not Color
Another reason you should always use the colorindex property in 2003 is because the automatic conversion from colour to colorindex is not 100% accurate
For example if you add the colour RGB(69,85,96) to the colour palette and then assign this colour using RGB it will actually use colorindex (5) instead and not the colour you specified.
Fonts
There is not a single line of code that will change the font of the whole chart (ie for all the different chart elements)
objChart.ChartArea.Font.Size = 10
This does not appear in the intellisense
objChart.Legend.Font.Size = 10
objAxis.TickLabels.Font.Size = 10
objAxis.AxisTitle.Characters.Font.Size = 10
Moving and Resizing charts
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.Location = xlLocationAsNewSheet
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
ActiveSheet.Shapes("Chart 2").IncrementLeft -10.5
ActiveSheet.Shapes("Chart 2").IncrementTop -5#
Call MsgBox(ActiveChart.Parent)
Source Data
Once the chart has been created (either as an embedded chart or on a chart sheet) you then need to add the source data.
objChart.SetSourceData Source:=Sheets(1).Range("A1:D4"), _
PlotBy:=xlRowCol.xlColumns
When you create a chart using the "SetSourceData" method you must include the Plot By option. The default should be xlColumns although if it is not specified then the source range used will be the active cells.
ActiveChart.SetSourceData Source:=Sheets("Sheet 3").Range("D4:G20")
PlotBy
If you need to change the plotby of an existing chart this can be done using the PlotBy method
objChart.PlotBy = xlRowCol.xlRows
Deleting
ActiveChart.Delete - DOES NOT WORK
objChart.Delete - DOES NOT WORK
ActiveSheet.ChartObjects(Right(ActiveChart.Name, Len(ActiveChart.Name) - Len(ActiveSheet.Name) - 1)).Delete
Delete all the ChartObjects from a worksheet
ActiveSheet.ChartObjects.Delete
Delete all the chart sheets in a workbook
Application.DisplayAlerts = False
ActiveWorkbook.Charts.Delete
Application.DisplayAlerts = True
This deletes the chart sheet called Chart 2
ActiveWorkbook.Charts("Chart 2").Delete
Resizing
This works when any part of the chart is selcted
Sub Positioning()
With ActiveChart.Parent
.Height = 200
.Width = 200
.Top = 100
.Left = 100
End With
End Sub
Aligning and Sizing
A ChartObject class has standard positional and sizing properties.
The following code resizes all ChartObjects on Sheet1 so they match the dimensions of the ChartObject named Chart 1
It also arranges the ChartObjects so they appear on after the other along the left of the worksheet.
Sub ResizeArrange()
With ActiveSheet.ChartObjects("Chart 1")
W = .Width
H = .Height
End With
TopPos = 0
For Each objChart In ActiveSheet.ChartObjects
With objChart
.Width = W
.Height = H
.Left = 0
.Top = TopPos
End With
TopPos = TopPos + H
Next objChart
End Sub
Saving
export a chart
ActiveChart.Export "C:\Temp\FileName.bmp"
ActiveChart.Export "C:\temp\Temp.xls", "GIF"
Printing and displaying
ActiveChart.Printout
Protecting charts
ActiveChart.Protect Password:="psswd", DrawingObjects:=True, Contents:=True
ActiveChart.ProtectData = True
ActiveChart.ProtectFormatting = True
ActiveChart.ProtectSelection = True
ActiveSheet.ChartObjects(1).Chart.ProtectSelection = True
Dialog Boxes
Get a quick chart
Set dlgBuiltIn = Application.Dialogs(xlBuiltInDialog.xlDialogChartWizard)
Copy special chart
Set dlgBuiltIn = Application.Dialogs(xlBuiltInDialog.xlDialogCopyChart)
Screen copy of a chart
Set dlgBuiltIn = Application.Dialogs(xlBuiltInDialog.xlDialogCopyPicture)
Make current chart into a new custom chart type
Set dlgBuiltIn = Application.Dialogs(xlBuiltInDialog.xlDialogAddChartAutoformat)
Format Chart
Set dlgBuiltIn = Application.Dialogs(xlBuiltInDialog.xlDialogFormatChart)
datatable options
Set dlgBuiltIn = Application.Dialogs(xlBuiltInDialog.xlDialogChartOptionsDataTable)
format chart type
Set dlgBuiltIn = Application.Dialogs(xlBuiltInDialog.xlDialogFormatChartType
AutoFormat
This is legacy and can be ignored
This was replaced with user defined custom chart types in 2000 ??
Adds a custom chart autoformat to the list of available chart autoformats.
expression.AddChartAutoFormat(Chart, Name, Description)
Chart - Chart. A chart that contains the format that will be applied when the new chart autoformat is applied.
Name - The name of the autoformat.
Description - (Optional) A description of the custom autoformat.
This example adds a new autoformat based on Chart1.
Application.AddChartAutoFormat Chart:=Charts("Chart1"), Name:="Presentation Chart"
Application.AddChartAutoFormat name:="Presentation Chart"
Removes a custom chart autoformat from the list of available chart autoformats.
This example deletes the custom autoformat named "Presentation Chart."
Application.DeleteChartAutoFormat name:="Presentation Chart"
Axes
Dim objAxis As Excel.Axis
objChart.Axes(Type, Group)
Set objAxis = objChart.Axes(xlAxisType.xlCategory, xlAxisGroup.xlPrimary)
Set objAxis = objChart.Axes(xlAxisType.xlValue, xlAxisGroup.xlSecondary)
objAxis.Border.Color.ColorIndex = 1-56 | xlColorIndex.xlColorIndexAutomatic | xlColorIndex.xlColorIndexNone
objAxis.Border.LineStyle = xlContinuous
objAxis.Border.ThemeColour = xlThemeColor.xlThemeColorAccent5
objAxis.Border.Weight = xlBorderWeight.xlThick
objAxis.Border.Shadow = False
objAxis.Format.Line.ForeColor.RGB = lcolour
objAxis.Format.Line.BackColor.ObjectThemeColor =
objAxis.Format.Line.BackColor.SchemeColor =
objAxis.Format.Line.BackColor.Type = msoColorType
objAxis.Format.Line.Weight = 1.25
objAxis.Format.Line.Style = msoLineStyle
objAxis.Format.Line.Pattern = msoPatternType
objAxis.Format.Line.BeginArrowHeadStyle = msoArrowheadstyle
objAxis.Format.Line.EndArrowHeadStyle = msoArrowheadstyle.msoArrowheadStealth
objAxis.TickLabels.Font.Size = 10
objAxis.AxisTitle.Characters.Font.Size = 10
Y-Axis (Values)
With ActiveChart.Axes(xlAxisType.xlValues, xlAxisGroup.xlPrimary
.MinimumScaleIsAuto = True
.MaximumScale = 0.8
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
X-Axis (Timescale)
With ActiveChart.Axes(xlAxisType.xlCategory, xlAxisGroup.xlPrimary)
.MinimumScale = "01/01/2010"
.MaximumScale = "31/01/2010"
.BaseUnitIsAuto = False
.MajorUnit =
.MajorUnitScale = xlDays
.MinorUnit =
.MinorUnitScale = xlDays
.Crosses =
.AxisBetweenCategories =
.ReversePlotOrder =
End WIth
X-Axis (Category)
With ActiveChart.Axes(xlAxisType.xlCategory, xlAxisGroup.xlPrimary)
.Crosses = xlMaximum
.AxesBetweenCategories
.ReversePlotOrder
.TickLabelSpacing
.TickMarkSpacing
End With
.CategoryType = xlCategoryType.xlAutomaticScale
Secondary Axis
ActiveChart.SeriesCollection(1).AxisGroup = 2
Displaying Axis
ActiveChart.SetElement(msoElementPrimaryCategoryAxisShow)
Formatting Numbers / Values
Dim objAxis As Excel.Axis
objAxis.TickLabels.Font.Name = "Arial"
objAxis.TickLabels.Font.Size = 10
objAxis.TickLabels.Font.Bold = True
objAxis.TickLabels.Font.Italic = False
objAxis.TickLabels.Font.Background = xlBackground.xlBackgroundTransparent
Axes Titles
Which Axis ?
Set objAxis = objChart.Axes(xlCategory, xlPrimary)
Set objAxis = objChart.Axes(xlCategory, xlSecondary)
Set objAxis = objChart.Axes(xlValue, xlPrimary)
Set objAxis = objChart.Axes(xlValue, xlSecondary)
objAxis.HasTitle = True
Axis Text
objAxis.AxisTitle.Text = ""
objAxis.AxisTitle.Caption = ""
objAxis.AxisTitle.Characters.Text = ""
Font Formatting
objAxis.AxisTitle.AutoscaleFont = False
objAxis.AxisTitle.Characters.Font.Name = ""
objAxis.AxisTitle.Characters.Font.Size
objAxis.AxisTitle.Characters.Font.Color
objAxis.AxisTitle.Characters.Font.Bold = False
objAxis.AxisTitle.Characters.Font.Italic =
objAxis.AxisTitle.Characters.Font.FontStyle =
objAxis.AxisTitle.Characters.Font
Line Formatting
objAxis.AxisTitle.Fill
objAxis.AxisTitle.Interior
objAxis.AxisTitle.Shadow
objAxis.AxisTitle.Left
objAxis.AxisTitle.Top
objAxis.AxisTitle.Border
Chart Area
Activechart.ChartArea.Font.Size = 10
clear the chart area - only certain chart types though
ActiveChart.ChartArea.ClearFormats
Plot Area
clear the plot area
ActiveChart.PlotArea.ClearFormats
Series Collection
objSeries = objChart.SeriesCollection(1)
objSeries = objChart.SeriesCollection("Series 1")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.Add (Range(A4:B20), xlColumns)
ActiveChart.SeriesCollection(3).Name = "series_name"
ActiveChart.SeriesCollection(3).XValues = "=Sheet2!R1C2:R14C1"
ActiveChart.SeriesCollection(3).Values = "=Sheet2!R1C3:R14C3"
ActiveChart.SeriesCollection(3).Name = ActiveSheet.Range("D5")
ActiveChart.SeriesCollection(3).Values = Array(1,2,3,4,5,6,7,8)
ActiveChart.SeriesCollection(3).Values = vaArray
ActiveChart.SeriesCollection.Name = "=Sheet2!R4C3"
ActiveChart.SeriesCollection.Values = Range("B2:B10")
Area Charts
objSeries.Interior.ColorIndex = 17
Borders (line charts)
objSeries.Border.Weight = xlBorderWeight.xlMedium
objSeries.Border.LineStyle =xlLineStyle.xlContinuous
objSeries.Border.ColorIndex = 17 '2003 or earlier
objSeries.Border.Color =
Debug.Print ActiveSheet.ChartObjects("Chart 2").Chart.SeriesCollection(1).Border.LineStyle =
Series Properties
AxisGroup
BarShape
Border
ChartType
ErrorBars
Explosion
Fill
Formula
FormulaLocal
FormulaR1C1
FormulaR1C1Local
Has3DEffect
Interior
Name
PictureType
PictureUnit
PlotOrder
Shadow
Smooth
Type
Values
XValues
Series Methods
ApplyCustomType
ApplyDataLabels
ClearFormats
Copy
DataLabels
Delete
ErrorBar
Paste
Points
Select
Trendline
Legend
ActiveChart.Legend
LegendKey
Each LegendKey is a graphic that visually links a legend entry with its associated series in the chart.
The LegendKey is linked to the series so making changes to one will simultaneously change the other.
ActiveChart.Legend.LegendEntries(1).LegendKey.Format.Line.ForeColor.RGB = RGB(120,20,20)
This does not appear in the intellisense
objChart.Legend.Font.Size = 10
ActiveChart.Legend.Clear
If ActiveChart.DataTable.ShowLegendKey = True
If ActiveChart.HasLegend = True
Gridlines
objAxis.HasMajorGridlines
objAxis.HasMinorGridlines
objAxis.MajorGridlines.Border.Color =
objAxis.MajorGridlines.Border.Weight =
objAxis.MajorGridlines.Border.Linestyle =
objAxis.MajorGridlines.Format.Fill
objAxis.MajorGridlines.Format.Line
objAxis.MajorGridlines.Format.PictureFormat
objAxis.MajorGridlines.Format.TextFrame2
objAxis.MajorGridlines.Format.ThreeD
objAxis.MajorGridlines.Format.Glow
objAxis.MajorGridlines.Name
Data Points / Markers
A data marker represents a single data point.
Whether charts show the values over the data points
Application.ShowChartTipValue
Marker Fill Colour
objSeries.MarkerBackgroundColorIndex = xlColorIndex.xlColorIndexNone
objSeries.MarkerBackgroundColor = -2
lcolour = objSeries.MarkerBackgroundColor
objSeries.Points(2).
Marker Line Colour
objSeries.MarkerForegroundColorIndex = xlColorIndex.xlColorIndexNone
objSeries.MarkerForegroundColor = RGB(10,20,30)
Marker Options
objSeries.MarkerSize = 6
objSeries.MarkerStyle = xlMarkerStyle.xlMarkerStyle.Square
Data Labels
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent
Dim icount As Integer
For icount = 1 To ActiveChart.SeriesCollection.Count
ActiveChart.SeriesCollection(icount).DataLabels.Border.LineStyle = xlNone
ActiveChart.SeriesCollection(icount).DataLabels.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(icount).DataLabels.Position = xlDataLabelPosition.xlLabelPositionAbove
Next icount
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False
Trendlines
A trendline shows a trend or direction of data in an individual series
You can add multiple trendlines to a single series
Trendlines Collection
This contains all the Trendline objects for a single series
objChart.SeriesCollection(1).Trendlines(1).Type = xlTrendlineType.xlLinear
objChart.SeriesCollection(1).Trendlines.Add(Type:=xlTrendlineType.xlLinear, _
Forward:=0, _
Backward:=0, _
DisplayEquation:=False, _
DisplayRSquared:=False)
objChart.SeriesCollection(1).Trendlines(1).Delete
Hi Low Lines
High low lines connect the highest point with the lowest point in every category in the chart group.
Only 2D line groups can have high low lines
High low lines can only be switched on or off for all the points in a chart group
High low lines are displayed on a stock chart by default
These are available on the following chart types:
Line, Stacked Line, 100% stacked line
Line with markers, stacked line with markers, 100% stacked line with markers
Stock (HLC, OHLC, VHLC, VOHLC)
objChart.SetElement(msoElementLineHiLowLine)
Dim objhilowlines As Excel.HiLoLines
Set objhilowlines = objChart.ChartGroups(1).HiLoLines
objhilowlines.Format.Line.ForeColor.RGB = RGB(10,20,30)
objhilowlines.Border.Color = RGB(10,20,30)
If (objChart.ChartGroups(1).HasHighLowLines = True) Then
End If
Up Down Bars
These are displayed on the following chart types:
They are displayed on stock charts by default
Line, Stacked Line, 100% Stacked Line
Line with Markers, Stacked line with markers, 100% stacked line with markers
DropLines
Available on the following chart types:
3D line
Stock (HLC, OHLC, VHLC, VOHLC)
Lines - Series ??
3D
clear the walls and floor
ActiveChart.Walls.ClearFormats
ActiveChart.Floor.ClearFormats
ActiveChart.SeriesCollection(1).AxisGroup = xlPrimary
The following line of code generates an error when the chart is one of the following:
3D Clustered Column
3D Stacked Column
3D 100 Stacked Column
3D Column
(and the equivalent for Cone, Cylinder and Pyramid)
Error Bars
Sub Testing()
Dim oChart As Excel.Chart
Dim oSeriesCollection As Excel.SeriesCollection
Dim oSeries As Excel.Series
Set oChart = ActiveChart
Set oSeriesCollection = oChart.SeriesCollection
Set oSeries = oSeriesCollection(1)
Call oSeries.ErrorBar( _
Direction:=Excel.XlErrorBarDirection.xlY, _
Include:=Excel.XlErrorBarInclude.xlErrorBarIncludeBoth, _
Type:=Excel.XlErrorBarType.xlErrorBarTypeCustom, _
Amount:=10, _
MinusValues:=1)
End Sub
'Direction - the direction of the error bar
'Include - the parts to include
'Type - the type of the error bar
'Amount - the error amount
'MinusValue - the negative amount if your type is Custom
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext