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"


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(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


ActieChart.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)


objChart.PageSetup ???






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



Copying


More Copying Pages

Excel > Cells & Ranges > VBA Code > Copying
Excel > Illustrations > VBA Code > Copying
Word > Text > VBA Code > Copying


Chart Object

this will copy the active embedded chart as a picture

ActiveChart.CopyPicture Appearance:=xlPictureAppearance.xlScreen, _ 
                        Format:=xlPictureAppearance.xlPicture, _
                        Size:=xlCopyPictureFormat.xlScreen

Appearance - Specifies how the picture should be copied
Format - The format of the picture
Size - The size of the picture when the object is a chart on a chart sheet


Range Object


Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture 
objRange.CopyPicture Appearance:=xlScreen, Format:=xlPicture
ActiveChart.CopyPicture Appearance:=xlprinter, Size:=xlScreen, Format:=xlPicture

Appearance -
Format -



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




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 





Creating

Charts.Add

This will create a new chart on a chart sheet
When you create a chart using the Add method of the Charts collection the chart is created on a chart sheet

ThisWorkbook.Charts.Add 
Charts.Add
Charts.Add Before:=objWorksheet




ChartObjects.Add

When you create a new embedded chart you are adding the chart to the ChartObjects collection for that particular worksheet.
This creates a chart of whatever chart type you have set as the default.
This method returns an Excel.ChartObject

ChartObject.Add(Left, Top, Width, Height) 

Left - the number of pointd from the upper left corner of cell A1
Top - the number of points from the ??
Width - The width of the embedded chart
Height - The height of the embedded chart


This will create a new embedded chart on the active worksheet

Set objChartObject = ChartObjects(20,20,20,20) 


There is no Charts collection for a worksheet.

ChartObjects.Add(before,after,count) 
ActiveSheet.ChartObjects.Add(left,top,width,height)
ActiveSheet.ChartObjects.Add(50,40,40,50)


You can then use the Location method to move to the chart to an embedded chart on a particular worksheet


The following line of code moves an embedded chart back to a chart sheet

Sheets("Sheet1").ChartObjects(1).Chart.Location Where:=xlLocationAsNewSheet, "MyChart" 



Shapes.AddChart

Activesheet.Shapes.AddChart() 



Always work with a Chart Object

Whenever you are working with charts you want to refer to a chart object not a chartobject object.

Set objChart = objChartObject.Chart 



Selecting

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 



Formatting

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 =
objAxis.Border.LineStyle = xlContinuous
objAxis.Border.ThemeColour =
objAxis.Border.Weight =
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 =
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






© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext