CREATE CHARTS USING VBA IN EXCEL

INTRODUCTION

Charts are a graphic representation of any tabular data which helps us to look over the trend, values , comparison and many other aspects easily.

The tables contain a lot of information but we need to study them closely and have to look at the figures repetitively. But after we have made its graphical representation which is known as a GRAPH or CHART, we can find out the comparison without looking over the data.

Charts or Graphs are used a lot in data analysis which is why it is one of the very important parts of the MICROSOFT EXCEL which is the top software for data analysis.

In addition to this, this gives nice visual feel to our reports or presentation.

In this article we’d see how to create a chart using VBA in excel.

If you want to learn the basics of the charts, KINDLY CLICK HERE.

TYPES OF CHARTS IN EXCEL

No, we are not talking about the chart types like line chart , pie or others ; but we are talking about the CHART TYPES based on the location.

The charts can be used at two different locations in EXCEL.

1. The embedded chart- which is a simple chart which is placed just near the data inside a worksheet only. Mostly by default we use this type of chart.

2. The next type of chart is, a chart on the CHART SHEET. There are two types of sheets in excel.

• WORKSHEET
• CHARTSHEET

Chartsheet contain a single chart usually and is a complete sheet just like we have worksheets.

CREATING FIRST EMBEDDED CHART USING VBA

Charts are a part of the SHAPES OBJECT so we can use the ADDCHART2 method of the SHAPES object to create an embedded chart.

Let us check out the different parameters of this method. 

AddChart2 can take upto seven arguments. 

AddChart2 (Style, XlChartType, Left, Top, Width, Height, NewLayout)

expression A variable that represents a Shapes object.

Parameters

Name	Required/Optional	Data type	Description
Style	Optional	Variant	A number is used. Use -1 for default. Type of Chart, the one which we choose after we have chosen the major type.
XlChartType	Optional	Variant	The type of chart.( Line, Pie, Column etc.)
Left	Optional	Variant	The position, in points, of the left edge of the chart, relative to the anchor. (Distance from left screen)
Top	Optional	Variant	The position, in points, of the top edge of the chart, relative to the anchor. (Distance from the top of worksheet)
Width	Optional	Variant	The width, in points, of the chart.
Height	Optional	Variant	The height, in points, of the chart.
NewLayout	Optional	Variant	If NewLayout is True, the chart is inserted by using the new dynamic formatting rules (Title is on, and Legend is on only if there are multiple series).

xlChart has the following values. We can use the name as well as value. Kindly read them once.

Specifies the chart type.

Name	Value	Description
xl3DArea	-4098	3D Area.
xl3DAreaStacked	78	3D Stacked Area.
xl3DAreaStacked100	79	100% Stacked Area.
xl3DBarClustered	60	3D Clustered Bar.
xl3DBarStacked	61	3D Stacked Bar.
xl3DBarStacked100	62	3D 100% Stacked Bar.
xl3DColumn	-4100	3D Column.
xl3DColumnClustered	54	3D Clustered Column.
xl3DColumnStacked	55	3D Stacked Column.
xl3DColumnStacked100	56	3D 100% Stacked Column.
xl3DLine	-4101	3D Line.
xl3DPie	-4102	3D Pie.
xl3DPieExploded	70	Exploded 3D Pie.
xlArea	1	Area
xlAreaStacked	76	Stacked Area.
xlAreaStacked100	77	100% Stacked Area.
xlBarClustered	57	Clustered Bar.
xlBarOfPie	71	Bar of Pie.
xlBarStacked	58	Stacked Bar.
xlBarStacked100	59	100% Stacked Bar.
xlBubble	15	Bubble.
xlBubble3DEffect	87	Bubble with 3D effects.
xlColumnClustered	51	Clustered Column.
xlColumnStacked	52	Stacked Column.
xlColumnStacked100	53	100% Stacked Column.
xlConeBarClustered	102	Clustered Cone Bar.
xlConeBarStacked	103	Stacked Cone Bar.
xlConeBarStacked100	104	100% Stacked Cone Bar.
xlConeCol	105	3D Cone Column.
xlConeColClustered	99	Clustered Cone Column.
xlConeColStacked	100	Stacked Cone Column.
xlConeColStacked100	101	100% Stacked Cone Column.
xlCylinderBarClustered	95	Clustered Cylinder Bar.
xlCylinderBarStacked	96	Stacked Cylinder Bar.
xlCylinderBarStacked100	97	100% Stacked Cylinder Bar.
xlCylinderCol	98	3D Cylinder Column.
xlCylinderColClustered	92	Clustered Cone Column.
xlCylinderColStacked	93	Stacked Cone Column.
xlCylinderColStacked100	94	100% Stacked Cylinder Column.
xlDoughnut	-4120	Doughnut.
xlDoughnutExploded	80	Exploded Doughnut.
xlLine	4	Line.
xlLineMarkers	65	Line with Markers.
xlLineMarkersStacked	66	Stacked Line with Markers.
xlLineMarkersStacked100	67	100% Stacked Line with Markers.
xlLineStacked	63	Stacked Line.
xlLineStacked100	64	100% Stacked Line.
xlPie	5	Pie.
xlPieExploded	69	Exploded Pie.
xlPieOfPie	68	Pie of Pie.
xlPyramidBarClustered	109	Clustered Pyramid Bar.
xlPyramidBarStacked	110	Stacked Pyramid Bar.
xlPyramidBarStacked100	111	100% Stacked Pyramid Bar.
xlPyramidCol	112	3D Pyramid Column.
xlPyramidColClustered	106	Clustered Pyramid Column.
xlPyramidColStacked	107	Stacked Pyramid Column.
xlPyramidColStacked100	108	100% Stacked Pyramid Column.
xlRadar	-4151	Radar.
xlRadarFilled	82	Filled Radar.
xlRadarMarkers	81	Radar with Data Markers.
xlRegionMap	140	Map chart.
xlStockHLC	88	High-Low-Close.
xlStockOHLC	89	Open-High-Low-Close.
xlStockVHLC	90	Volume-High-Low-Close.
xlStockVOHLC	91	Volume-Open-High-Low-Close.
xlSurface	83	3D Surface.
xlSurfaceTopView	85	Surface (Top View).
xlSurfaceTopViewWireframe	86	Surface (Top View wireframe).
xlSurfaceWireframe	84	3D Surface (wireframe).
xlXYScatter	-4169	Scatter.
xlXYScatterLines	74	Scatter with Lines.
xlXYScatterLinesNoMarkers	75	Scatter with Lines and No Data Markers.
xlXYScatterSmooth	72	Scatter with Smoothed Lines.
xlXYScatterSmoothNoMarkers	73	Scatter with Smoothed Lines and No Data Markers.

Let us create our first embedded chart using VBA but without the data. The statement goes like thisActiveSheet.Shapes.AddChart2 201, xlColumnClustered, 50, 60, 300, 200, 5 )

OBJECTIVE

CREATE A SIMPLE COLUMN CLUSTERED CHART FOR THE GIVEN DATA USING VBA

Let us create an embedded chart using the above mentioned function.

The given data is

DAY	PEOPLE
1	5
2	6
3	7
4	8

PLANNING THE SOLUTION

We’ll select the data and create the chart on the active sheet.The code is given below.

CODE

'GYANKOSH.NET
'CREATING SIMPLE CHART USING VBA

Sub createsimpleChart()

Dim MyChart As Chart 'DEFINING THE CHART

Range("d2", "e6").Select 'SELECTING THE DATA

Set MyChart = ActiveSheet.Shapes.AddChart2( _
201, xlclusteredcolumn, 100, 100, 500, 500, True).Chart 'Setting the chart

End Sub

EXPLANATION

The process is pretty simple.

We declare a chart variable.

Range is selected as per the data availability and it is selected by .SELECT method.

The next line, creates the chart . We use all the parameters but it is not necessary. We can

choose any number of options and rest will go to default.

The output is shown below.

RUNNING THE CODE AND OUTPUT

The code is shown which is already discussed above.

• A button has been placed in the sheet. Click right and assign macro to the button . The macro is named by the name of the sub procedure.
• Click the button and the chart is created.
• The style is defined by first argument i.e. 201.
• The next type of chart is because of the second argument clustered column.
•  The chart title and series names are given because of the last argument which is true or 1.
• The size and distances from the screen are as per in the formula

CREATE CHART ON A CHARTSHEET USING VBA

CREATE A CLUSTERED COLUMN CYLINDER CHART ON A CHARTSHEET The chartsheet is just like a worksheet. A standalone sheet which will hold one chart.We’ll use the CHART OBJECT for creating this chart.Let us try to create cylinder chart on a chartsheet.

CODE

'GYANKOSH.NET
'CREATING A NEW CHART ON CHARTSHEET

Sub CreateChartSheet()

    Dim MyChart As Chart

    Dim DataRange As Range

    Set DataRange = ActiveSheet.Range("D4:E8") 'Setting the data range.

    Set MyChart = Charts.Add2                  'Adding the chart

    MyChart.SetSourceData Source:=DataRange    'Attaching the data to the chart.

    ActiveChart.ChartType = 92                 'Refer to the code list for cylindrical

                                             'we can also use xlCylinderColClustered
End Sub 

We start by defining the CHART VARIABLE and RANGE DATARANGE.

We set the datarange as D4:E8.

We can use the range in the same way as previous example too.

It is just another way of passing the range.

The chart is added by CHARTS.ADD2

The source is passed to the chart by setSourceData

The type is set by Charttype=92(92 is the code of type of chart needed).

The following picture shows the creation and running of chart. 

We run the code by using the macro option list by going to developer tab and

clicking on macro.

Select the macro and click run.

We can see that a new sheet has been created and the desired chart is present there.