Excel Chart VBA Examples and Tutorials

Home/Excel VBA/Excel Chart VBA Examples and Tutorials

Excel charts are one of the awesome tools available to represent the data in rich visualized graphs. Here are the most frequently used Excel Chart VBA Examples and Tutorials. You can access chart objects, properties and dealing with the methods.
Excel Chart VBA Examples and Tutorials

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


Here are the top most Excel Chart VBA Examples and Tutorials, show you how to deal with chart axis, chart titles, background colors,chart data source, chart types, series and many other chart objects.

Excel Chart VBA Examples and Tutorials – Learning Path

Excel Chart VBA Examples and Tutorials – Creating Charts using Excel VBA:

We can create the chart using different methods in Excel VBA, following are the various Excel Chart VBA Examples and Tutorials to show you creating charts in Excel using VBA.

1. Adding New Chart for Selected Data using Sapes.AddChart Method in Excel VBA

The following Excel Chart VBA Examples works similarly when we select some data and click on charts from Insert Menu and to create a new chart. This will create basic chart in an existing worksheet.


Sub ExAddingNewChartforSelectedData_Sapes_AddChart_Method()
Range("C5:D7").Select
ActiveSheet.Shapes.AddChart.Select
End Sub

2. Adding New Chart for Selected Data using Charts.Add Method : Creating Chart Sheet in Excel VBA

The following Excel Chart VBA Examples method will add new chart into new worksheet by default. You can specify a location to embedded in a particular worksheet.


'Here is the other method to add charts using Chart Object. It will add a new chart for the selected data as new chart sheet.
Sub ExAddingNewChartforSelectedData_Charts_Add_Method_SheetChart()
Range("C5:D7").Select
Charts.Add
End Sub

3. Adding New Chart for Selected Data using Charts.Add Method : In Existing Sheet using Excel VBA

We can use the Charts.Add method to create a chart in existing worksheet. We can specify the position and location as shown below. This will create a new chart in a specific worksheet.


Sub ExAddingNewChartforSelectedData_Charts_Add_Method_InSheet()

Range("C5:D7").Select
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

End Sub

4. Difference between embedded Chart and Chart Sheet in Excel:

Both are similar except event handlers, Chart Sheets will have the event handlers,we can write event programming for Chart Sheets. And the other type embedded charts can not support the event handlers. We can write classes to handle the events for the embedded chart, but not recommended.

We have seen multiple methods to create charts, but we cant set the chart at particular position using the above codes. You can use the ChartObjects.Add method to specify the position of the chart.

5. Adding New Chart for Selected Data using ChartObjects.Add Method in Excel VBA

ChartObjects.Add method is the best method as it is very easy to play with the chart objects to change the settings.


Sub ExAddingNewChartforSelectedData_ChartObjects_Add_Method()
With ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
End With
End Sub

6. Assigning Charts to an Object in Excel VBA

Here is another Excel Chart VBA Examples with ChartObjects, here we will assign to an Object and play with that.


Sub ExAddingNewChartforSelectedData_ChartObjects_Object()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
cht.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
End Sub

7. Changing the Chart Position in Excel VBA

The following VBA example will show you how to change the chart position.

Sub ExAddingNewChartforSelectedData_Object_Position()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
cht.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
cht.Left = 350
cht.Width = 400
cht.Top = 30
cht.Height = 200
End Sub

8. Align Chart Object at a Particular Range or Cell in Excel VBA

You can set the top,left, height and width properties of a chart object to align in a particular position.


Sub AlignChartAtParticularRange()
' Chart Align

With ActiveSheet.ChartObjects(1)
.Left = Range("A6").Left
.Top = Range("A7").Top
.Width = Range("D6").Left
.Height = Range("D16").Top - Range("D6").Top
End With

End Sub

9. Use with statement while dealing with Charts and avoid the accessing the same object repeatedly in Excel VBA

If you are dealing with the same object, it is better to use with statement. It will make the program more clear to understand and executes faster.


Sub ExChartPostion_Object_Position()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Left = 350
.Width = 400
.Top = 30
.Height = 200
End With
End Sub

10. You can use ActiveChart Object to access the active chart in Excel VBA

Active chart is the chart which is currently selected or activated in your active sheet.


Sub ExChartPostion_ActiveChart()
ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300).Activate
With ActiveChart
.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Parent.Left = 350
.Parent.Width = 400
.Parent.Top = 30
.Parent.Height = 200
End With
End Sub

Top

Excel Chart VBA Examples and Tutorials – Setting Chart Types using Excel VBA:

We have verity of chart in Excel, we can use VBA to change and set the suitable chart type based on the data which we want to present. Below are the Excel Chart VBA Examples and Tutorials to change the chart type.

We can use Chart.Type property to set the chart type, here we can pass Excel chart constants or chart enumerations to set the chart type. Please refer the following table to understand the excel constants and enumerations.

11. Example to Change Chart type using Excel Chart Enumerations in Excel VBA

This Excel Chart VBA Example will use 1 as excel enumeration to plot the Aria Chart. Please check here list of enumerations available for Excel VBA Charting


Sub Ex_ChartType_Enumeration()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = 1 ' for aria chart
End With
End Sub

12. Example to Change Chart type using Excel Chart Constants in VBA

This Excel Chart VBA Example will use xlArea as excel constant to plot the Aria Chart. Please check here for list of enumerations available in Excel VBA Charting


Sub Ex_ChartType_xlConstant()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlArea
End With
End Sub

xlConstants is recommended than Excel Enumeration, as it is easy to understand and remember. Following are frequently used chart type examples:

13. Example to set the type as a Pie Chart in Excel VBA

The following VBA code using xlPie constant to plot the Pie chart. Please check here for list of enumerations available in Excel VBA Charting


Sub Ex_ChartType_Pie_Chart()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlPie
End With
End Sub

14. Example to set the chart type as a Line Chart in Excel VBA

The following VBA code using xlLine constant to plot the Pie chart. Please check here for list of enumerations available in Excel VBA Charting


Sub Ex_ChartType_Line_Chart()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlLine
End With
End Sub

15. Example to set the chart type as a Bar Chart in Excel VBA

The following VBA code using xlBar constant to plot the Pie chart. Please check here for list of enumerations available in Excel VBA Charting


Sub Ex_ChartType_Bar_Chart()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlBar
End With
End Sub

16. Example to set the chart type as a XYScatter Chart in Excel VBA

The following code using xlXYScatter constant to plot the Pie chart. Please check here for list of enumerations available in Excel VBA Charting


Sub Ex_ChartType_XYScatter_Chart()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlXYScatter
End With
End Sub

Here is the complete list of Excel Chart Types, Chart Enumerations and Chart Constants:

Top

Excel Chart VBA Examples and Tutorials – Formatting Chart Objects using Excel VBA:

Below are Excel Chart VBA Examples to show you how to change background colors of charts, series and changing the different properties of charts like Chart Legends, Line Styles, Number Formatting. You can also find the examples on Chart Axis and Chart Axes Setting and Number Formats.

17. Changing Chart Background Color – Chart Aria Interior Color in Excel VBA

The following VBA code will change the background color of the Excel Chart.


Sub Ex_ChartAriaInteriorColor()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.ChartArea.Interior.ColorIndex = 3
End With
End Sub

18. Changing PlotAria Background Color – PlotAria Interior Color in Excel VBA

The following code will change the background color of Plot Area in Excel VBA.


Sub Ex_PlotAriaInteriorColor()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.PlotArea.Interior.ColorIndex = 5
End With
End Sub

19.Changing Chart Series Background Color – Series Interior Color in Excel VBA

The following code is for changing the background color of a series using Excel VBA.

Sub Ex_SeriesInteriorColor()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = rgbRed
.Chart.SeriesCollection(2).Interior.ColorIndex = 5

End With
End Sub

20. Changing Chart Series Marker Style in Excel VBA

Here is the code to change the series marker style using Excel VBA, you can change to circle, diamond, square,etc. Check the excel constants and enumerations for more options available in excel vba.


Sub Ex_ChangingMarkerStyle()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlLine
.Chart.SeriesCollection(1).MarkerStyle = 7
End With
End Sub

21. Changing Chart Series Line Style in Excel VBA

Here is the code to change the line color using Excel VBA, it will change the line style from solid to dash. Check the excel constants for more options.


Sub Ex_ChangingLineStyle()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlLine
.Chart.SeriesCollection(1).Border.LineStyle = xlDash
End With
End Sub

22. Changing Chart Series Border Color in Excel VBA

Here is the code for changing series borders in Excel VBA.


Sub Ex_ChangingBorderColor()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlBar
.Chart.SeriesCollection(1).Border.ColorIndex = 3
End With
End Sub

23. Change Chart Axis NumberFormat in Excel VBA

This code will change the chart axis number format using excel vba.


Sub Ex_ChangeAxisNumberFormat()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlLine
.Chart.Axes(xlValue).TickLabels.NumberFormat = "0.00"
End With
End Sub

24. Formatting Axis Labels: Changing Axis Font to Bold using Excel VBA

The following example is for formating Axis labels using Excel VBA.


Sub Ex_ChangeAxisFormatFontBold()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlLine
.Chart.Axes(xlCategory).TickLabels.Font.FontStyle = "Bold"
End With
End Sub

25. Two Y-axes Left and Right of Charts(Primary Axis and Secondary Axis) using Excel VBA

This code will set the series 2 into secondary Axis using Excel VBA.


Sub Ex_ChangeAxistoSecondary()
Dim cht As Object
Set cht = ActiveSheet.ChartObjects.Add(Left:=300, Width:=300, Top:=10, Height:=300)
With cht
.Chart.SetSourceData Source:=Sheets("Temp").Range("C5:D7")
.Chart.Type = xlLine
.Chart.SeriesCollection(2).AxisGroup = 2
End With
End Sub

Top

Excel Chart VBA Examples and Tutorials – Chart Collection in Excel VBA

You can use ChartObject Collection to loop through the all charts in worksheet or workbook using Excel VBA. And do whatever you want to do with that particular chart. Here are Excel Chart VBA Examples to deal with Charts using VBA.

26. Set equal widths and heights for all charts available in a Worksheet using Excel VBA

Following is the Excel VBA code to change the chart width and height.


Sub Ex_ChartCollections_Change_widths_heights()
Dim cht As Object
For Each cht In ActiveSheet.ChartObjects
cht.Width = 400
cht.Height = 200
Next

End Sub

27. Delete All Charts in a Worksheet using Excel VBA

Following is the Excel VBA example to delete all charts in worksheet.


Sub Ex_DeleteAllCharts()
Dim cht As Object
For Each cht In ActiveSheet.ChartObjects
cht.Delete
Next

End Sub

Top

Excel Chart VBA Examples and Tutorials – Other useful Examples and tutorials on Excel VBA Charting

28. Set Chart Data Source using Excel VBA

Below is the Excel Chart VBA Example to set the chart data source. You can set it by using .SetSourceData Source property of a chart


Sub Ex_ChartDataSource()

Dim cht As Chart

'Add new chart
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
'Specify source data and orientation
.SetSourceData Source:=Sheet1.Range("A1:C5")
End With

End Sub

29. Swap or Switch Rows and Columns in Excel Charts using VBA

Here is the excel VBA code to swap the Rows to Columns.

Sub Ex_SwitchRowsColumns()

Dim cht As Chart

'Add new chart
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
'Specify source data and orientation
.SetSourceData Source:=Sheets("Temp").Range("C5:D7"), PlotBy:=xlRows ' you can use xlColumns to swith it
End With

End Sub

30. Set Chart Data Labels and Legends using Excel VBA

You can set Chart Data Labels and Legends by using SetElement property in Excl VBA


Sub Ex_AddDataLabels()

Dim cht As Chart

'Add new chart
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
'Specify source data and orientation
.SetSourceData Source:=Sheet1.Range("A1:B5"), PlotBy:=xlColumns
'Set Chart type
.ChartType = xlPie
'set data label at center
.SetElement (msoElementDataLabelCenter)
'set legend at bottom
.SetElement (msoElementLegendBottom)
End With
End Sub

31. Changing Axis Titles of a Chart in Excel VBA

Following is the code to change the chart Axis titles using Excel VBA..


Sub Ex_ChangeAxisTitles()
activechart.chartobjects(1).activate
ActiveChart.Axes(xlCategory).HasTitle = True
ActiveChart.Axes(xlCategory).AxisTitle.Text = "Quarter"

ActiveChart.Axes(xlValue).HasTitle = True
ActiveChart.Axes(xlValue).AxisTitle.Text = "Sales"

End Sub

32. Change Titles of a Chart using Excel VBA

Following is the code to change the chart titles using Excel VBA..


Sub Ex_ChangeChartTitles()
ActiveSheet.ChartObjects(1).Activate
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Overal Summary"

End Sub

33. Send an Excel Chart to an Outlook email using VBA

Download Example File:
ANALYSIS TABS – SendExcelChartToOutLook.xlsm

Following is the code to Send an Excel Chart to an Outlook email using VBA.


Sub SendChartThroughMail()
'Add refernce to Microsoft Outlook object Library

Dim olMail As MailItem
Dim objOL As Object
Dim sImgPath As String

Dim sHi As String
Dim sBody As String
Dim sThanks As String

' Saving chart as image
sImgPath = ThisWorkbook.Path & "\Temp_" & Format(Now(), "DD_MM_YY_HH_MM_SS") & ".bmp"
Sheets("Sheet1").ChartObjects(1).Chart.Export sImgPath

'creating html body with image
sHi = "<font size='3' color='black'>" & "Hi," & "<br> <br>" & "Here is the required solution: " & "<br> <br> </font>"

sBody = "<p align='Left'><img src=""cid:" & Mid(sImgPath, InStrRev(sImgPath, "\") + 1) & """ width=400 height=300 > <br> <br>"

sThanks = "<font size='3'>" & "Many thanks - ANALYSISTABS.COM <br>The Complete Reference For Analyst <br> website:<A HREF=""https://www.analysistabs.com""> analysistabs.com</A>" & "<br> <br> </font>"

'sending the email

Set objOL = CreateObject("Outlook.Application")
Set olMail = objOL.CreateItem(olMailItem)

With olMail
.To = "youremail@orgdomain.com"
.Subject = "ANALYSISTABS.COM: Test Mail with chart"
.Attachments.Add sImgPath
.HTMLBody = sHi & sBody & sThanks
.Display
End With

'Delete the saved chart
Kill sImgPath

'Free-up the objects
Set olMail = Nothing
Set olApp = Nothing

End Sub

Top

List of Excel Charting Constants and Enumerations for VBA

Chart Types, Constants and Enumerations
CHART TYPE VBA CONSTANT VALUE

AREA Charts

AREA xlArea 1
STACKED AREA xlAreaStacked 76
100% STACKED AREA xlAreaStacked100 77
3D AREA xl3DArea -4098
3D STACKED AREA xl3DAreaStacked 78
3D 100% STACKED AREA xl3DAreaStacked100 79

BAR Charts

3D CLUSTERED BAR xl3DBarClustered 60
3D STACKED BAR xl3DBarStacked 61
3D 100% STACKED BAR xl3DBarStacked100 62
CLUSTERED BAR xlBarClustered 57
STACKED BAR xlBarStacked 58
100% STACKED BAR xlBarStacked100 59
CLUSTERED CONE BAR xlConeBarClustered 102
STACKED CONE BAR xlConeBarStacked 103
100% STACKED CONE BAR xlConeBarStacked100 104
CLUSTERED CYLINDER BAR xlCylinderBarClustered 95
STACKED CYLINDER BAR xlCylinderBarStacked 96
100% STACKED CYLINDER BAR xlCylinderBarStacked100 97
CLUSTERED PYRAMID BAR xlPyramidBarClustered 109
STACKED PYRAMID BAR xlPyramidBarStacked 110
100% STACKED PYRAMID BAR xlPyramidBarStacked100 111
BUBBLE Charts    
3D BUBBLE, BUBBLE WITH 3D EFFECTS xlBubble3DEffect 87
BUBBLE xlBubble 15

COLUMN Charts

3D CLUSTERED COLUMN xl3DColumnClustered 54
3D COLUMN xl3DColumn -4100
3D CONE COLUMN xlConeCol 105
3D CYLINDER COLUMN xlCylinderCol 98
3D PYRAMID COLUMN xlPyramidCol 112
3D STACKED COLUMN xl3DColumnStacked 55
3D 100% STACKED COLUMN xl3DColumnStacked100 56
CLUSTERED COLUMN xlColumnClustered 51
STACKED COLUMN xlColumnStacked 52
100% STACKED COLUMN xlColumnStacked100 53
CLUSTERED CONE COLUMN xlConeColClustered 99
STACKED CONE COLUMN xlConeColStacked 100
100% STACKED CONE COLUMN xlConeColStacked100 101
CLUSTERED CYLINDER COLUMN xlCylinderColClustered 92
STACKED CYLINDER COLUMN xlCylinderColStacked 93
100% STACKED CYLINDER COLUMN xlCylinderColStacked100 94
CLUSTERED PYRAMID COLUMN xlPyramidColClustered 106
STACKED PYRAMID COLUMN xlPyramidColStacked 107
100% STACKED PYRAMID COLUMN xlPyramidColStacked100 108

DOUGHNUT Charts

DOUGHNUT xlDoughnut -4120
EXPLODED DOUGHNUT xlDoughnutExploded 80

LINE Charts

3D LINE xl3DLine -4101
LINE xlLine 4
LINE WITH MARKERS xlLineMarkers 65
STACKED LINE xlLineStacked 63
100% STACKED LINE xlLineStacked100 64
STACKED LINE WITH MARKERS xlLineMarkersStacked 66
100% STACKED LINE WITH MARKERS xlLineMarkersStacked100 67

PIE Charts

3D PIE xl3DPie -4102
3D EXPLODED PIE xl3DPieExploded 70
BAR OF PIE xlBarOfPie 71
EXPLODED PIE xlPieExploded 69
PIE xlPie 5
PIE OF PIE xlPieOfPie 68

RADAR Charts

RADAR xlRadar -4151
FILLED RADAR xlRadarFilled 82
RADAR WITH DATA MARKERS xlRadarMarkers 81

SCATTER Charts

SCATTER xlXYScatter -4169
SCATTER WITH LINES xlXYScatterLines 74
SCATTER WITH LINES AND NO DATA MARKERS xlXYScatterLinesNoMarkers 75
SCATTER WITH SMOOTH LINES xlXYScatterSmooth 72
SCATTER WITH SMOOTH LINES AND NO DATA MARKERS xlXYScatterSmoothNoMarkers 73

STOCK Charts

STOCK HLC (HIGH-LOW-CLOSE) xlStockHLC 88
STOCK OHLC (OPEN-HIGH-LOW-CLOSE) xlStockOHLC 89
STOCK VHLC (VOLUME-HIGH-LOW-CLOSE) xlStockVHLC 90
STOCK VOHLC (VOLUME-OPEN-HIGH-LOW-CLOSE) xlStockVOHLC 91

SURFACE Charts

3D SURFACE xlSurface 83
3D SURFACE WIREFRAME xlSurfaceWireframe 84
SURFACE TOP VIEW xlSurfaceTopView 85
SURFACE TOP VIEW WIREFRAME xlSurfaceTopViewWireframe 86

Marker Styles, Constants and Enumerations
Marker Styles Name Value
Automatic markers xlMarkerStyleAutomatic -4105
Circular markers xlMarkerStyleCircle 8
Long bar markers xlMarkerStyleDash -4115
Diamond-shaped markers xlMarkerStyleDiamond 2
Short bar markers xlMarkerStyleDot -4118
No markers xlMarkerStyleNone -4142
Picture markers xlMarkerStylePicture -4147
Square markers with a plus sign xlMarkerStylePlus 9
Square markers xlMarkerStyleSquare 1
Square markers with an asterisk xlMarkerStyleStar 5
Triangular markers xlMarkerStyleTriangle 3
Square markers with an X xlMarkerStyleX -4168
Line Styles, Constants and Enumerations
Line Style Value
xlContinuous 1
xlDash -4115
xlDashDot 4
xlDashDotDot 5
xlDot -4118
xlDouble -4119
xlLineStyleNone -4142
xlSlantDashDot 13

Example file to Download

You can download the example file and have a look into the working codes.
ANALYSISTABS- Chart VBA Examples

Reference:
MSDN

LIMITED TIME OFFER
By |September 8th, 2013|Excel VBA|23 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

23 Comments

  1. Tim December 18, 2013 at 11:33 PM - Reply

    HI there PNRao,

    This is a really great reference – in the past, I’ve beat around the object browser as well. I use MS Graph in an MS Access application (currently A2007 in process of being upgraded from A2003). I have numerous controls to do some of the tasks you’ve identified here and others as well. I also have a button for the very few advanced users UI have to be able to actually open MS Graph in a separate window and do formatting there. What I’m trying to figure out how to do is enumerate through ALL properties of a chart and save them as an array or tab delimited string in a database (Oracle in this case) field.

    Is there anyway to do this? I’ve done lots of the sorts of enumeration that you show show excellently here. What I would very much like to do is to be able to enumerate all formatting properties of a chart to be able to store it (above) and retrieve.

    Do you have any ideas? Thanks in advance,

    Tim

    • PNRao December 19, 2013 at 11:30 AM - Reply

      Hi Tim,
      It is good idea to store all properties as enumerations in a field and draw the charts based on the requirement. You can consider the following things to save in the data base.
      1. Chart Type
      2. Data Source
      3. Axes (Primary/Secondary)
      4. Border
      5. Number Format
      6. Legend Alignment
      7. Data Labels
      8. Chart title

      You can make all the above properties as dynamic and the other things like Colors and minimum and maximum axis should be automatic.

      And you can provide UI to format the charts using drop-down lists and Text boxes. For example, you can show different chart types in drop-down and chart type can be changed based on the user selection. You can provide text box to enter the chart title, it should save in your database for chart titles enumeration to reflect on your chart.

      Hope this helps, let me know if you need any help on this.

      Thanks
      PNRao!

  2. kamran February 28, 2014 at 3:44 PM - Reply

    One of the best Tutorial for VBA macro.

  3. Antoni April 23, 2014 at 9:02 PM - Reply

    Hi,

    My boss has asked me to do many graphics chars, i love the example and explanations that have left at this web, I would like to do what our friend says PNRao, please have you any example of how to do it?

    Thank you very much.
    Antoni.

    • PNRao April 24, 2014 at 8:48 PM - Reply

      Hi Antoni,

      We can automate charting using VBA. We have provided some examples in download page. You can download and see the examples, let us know if you need any help.

      Thanks-PNRao!

  4. Antoni April 25, 2014 at 8:15 PM - Reply

    Hi PNRao,

    I can’t found examples in the Download section, can you help me please?, or can you send me a links?

    Thank you.

    • PNRao April 26, 2014 at 12:08 AM - Reply

      Hi Antoni,
      I have added the example file at end of the post. Please download and see the code.

      Thanks-PNRao!

  5. Sam May 4, 2014 at 9:32 AM - Reply

    Hi,
    I am having a task, in which i need to copy chart from one excel sheet to another excel sheet.
    So can you please help me in this.
    Thanks,
    Sam

    • PNRao May 4, 2014 at 11:40 AM - Reply

      Hi Sam,

      You can use the Copy method as shown below:
      [vb]
      Sub sbVBA_Chart_Copy()
      Sheet1.ChartObjects("Chart 1").Copy ‘Sheet name and Chart Object name to be copied
      Sheet2.Activate ‘Activate your destination Sheet
      Range("G1").Select ‘Select your destination cell
      ActiveSheet.Paste ‘Now Paste
      End Sub
      [/vb]

      Thanks-PNRao!

  6. Ross December 29, 2014 at 9:02 PM - Reply

    Hi PNRao,

    Thanks for the above it’s really helpful, although I’m still stuck!

    Is there a way to so that when making a graph it has a certain destination within the sheet so it’s not pasted over my data?

    Thank you!!

  7. เสื้อคู่ May 25, 2015 at 3:43 PM - Reply

    Hi, this weekend is pleasant in favor of me, since this moment i am reading
    this great informative post here at my residence.

  8. Nkhoma Kenneth October 4, 2015 at 1:27 PM - Reply

    Thanks very much for the tutorial, it is a great one indeed and very helpful.

    Thanks to you, i have learn’t something.

  9. Richard December 1, 2015 at 4:59 PM - Reply

    Hi Guys
    Great info – Thanks

    I’m trying to automate some graph formatting, and whilst I’ve worked out how to change the range for a xlTimeScale based graph, I want an easy way for the user to define which graphs need to be scaled to current month rather than all year.

    I thought of putting a text string into the Alt Text Description box (found under / but how do I read that field from a macro?

    If it were a shape I could use something like shapeDesc = sheet(1).Shapes(1).AlternativeText but that doesn’t work for ChartObjects.

    Any ideas?
    Thanks
    Richard

    Always learning … 🙂

  10. Richard December 1, 2015 at 7:44 PM - Reply

    Hah! Got it guys.
    I’ve decided to scroll through each sheet. In each sheet scroll through each Shape. If the Shape.Type = msoChart Then I can pick up the Alternative Text and act accordingly.

    Code extract:
    For lShtCtr = 1 To ThisWorkbook.Sheets.Count
    Debug.Print “Checking worksheet ” & lShtCtr & ” ” & Sheets(lShtCtr).Name & “. Detected ” & ThisWorkbook.Sheets(lShtCtr).Shapes.Count & ” shapes.”
    For lShapeCtr = 1 To ThisWorkbook.Sheets(lShtCtr).Shapes.Count
    If ThisWorkbook.Sheets(lShtCtr).Shapes(lShapeCtr).Type = msoChart Then
    myText = ThisWorkbook.Sheets(lShtCtr).Shapes(lShapeCtr).AlternativeText
    Debug.Print “Alt Text in ” & ThisWorkbook.Sheets(lShtCtr).Shapes(lShapeCtr).Name & ” =:” & myText
    If InStr(1, myText, “#AutoScale”, vbTextCompare) > 0 Then
    Debug.Print “Scaling axes on ” & ThisWorkbook.Sheets(lShtCtr).Shapes(lShapeCtr).Name
    Call ScaleAxes(ThisWorkbook.Sheets(lShtCtr), ThisWorkbook.Sheets(lShtCtr).Shapes(lShapeCtr).Name)
    End If ‘myText contains #AutoScale
    End If ‘Shape.Type = msoChart
    Next lShapeCtr
    Next lShtCtr

    The debug.print allow you to see what is going on. I’ve used the magic key phrase ‘#AutoScale’ as my command

    Thanks
    Richard

    Always learning 🙂

  11. Krishnaprasad Menon January 2, 2016 at 10:45 AM - Reply

    Very nice tutorial.. Chart creating example file is not available on download… Kindly make it available

    • PNRao January 2, 2016 at 11:38 PM - Reply

      Thanks for your valuable feedback and reporting an issue, we have fixed it.

      Thanks-PNRao!

  12. HS March 19, 2016 at 5:12 PM - Reply

    Very quickly this site will be famous amid all blogging and site-building
    users, due to it’s good posts

  13. chris May 9, 2016 at 1:05 AM - Reply

    i want to explode on a pie chart the piece with the max value. How can i do this? Also what if I have on a pie two pieces with the same max value? Thank you.

  14. Nagaraju July 1, 2016 at 12:17 PM - Reply

    Hi All,

    I’m trying to open website through Vba code….It was done…I need small information that if need code how to search search any information in google and display.

    Regards,
    Nagu

  15. Salty August 25, 2016 at 11:12 PM - Reply

    Hi
    This comment relates to your Example 13, above, but may apply to all of the Chart.Type examples.
    Using Excel 2016 on Windows 8, I had to change one line as follows:
    From > Chart.Type = xlPie
    To > Chart.Chart.Type = xlPie

  16. Salty August 29, 2016 at 9:01 AM - Reply

    Hi
    Sorry, I sent you a typo in my August 25 2016 comment. It should have said:
    It should have said
    To > Chart.ChartType = xlPie

  17. Raspreet Singh Jaggi October 21, 2016 at 3:22 PM - Reply

    Your codes are very much helpful Mr.PNRao.

  18. Dwayne November 29, 2016 at 10:01 AM - Reply

    Hi all,
    That was quite a read! I’ve come away with many ideas for future spreadsheets..

    I do have one quick query, I’m using the following script to change the data range for a chart I’m using;

    Sub RECPLINE()
    ActiveSheet.ChartObjects(“Chart 1”).Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SetSourceData Source:=Range(“=Data!$AR$3:$AW$31”)
    End Sub

    Instead of having 6 charts crammed into one screen, I aim to have one chart and 6 buttons to change the data range in the chart.
    Everything works fine, but I have formatted the visual aspects of the chart to be more appealing. After saving and re-opening the spreadsheet, the first chart has kept the formatting, but all the other charts adopt the standard formatting when the data range is changed..

    Is there any way I can force the existing visual changes, or will I have to code the changes into the VBA script each time I change the data range?

    Any help would be greatly appreciated!

    Cheers,
    Dwayne.

Leave A Comment