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

NameRequired/OptionalData typeDescription
StyleOptionalVariantA number is used. Use -1 for default. Type of Chart, the one which we choose after we have chosen the major type.
XlChartTypeOptionalVariantThe type of chart.( Line, Pie, Column etc.)
LeftOptionalVariantThe position, in points, of the left edge of the chart, relative to the anchor. (Distance from left screen)
TopOptionalVariantThe position, in points, of the top edge of the chart, relative to the anchor. (Distance from the top of worksheet)
WidthOptionalVariantThe width, in points, of the chart.
HeightOptionalVariantThe height, in points, of the chart.
NewLayoutOptionalVariantIf 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.

NameValueDescription
xl3DArea-40983D Area.
xl3DAreaStacked783D Stacked Area.
xl3DAreaStacked10079100% Stacked Area.
xl3DBarClustered603D Clustered Bar.
xl3DBarStacked613D Stacked Bar.
xl3DBarStacked100623D 100% Stacked Bar.
xl3DColumn-41003D Column.
xl3DColumnClustered543D Clustered Column.
xl3DColumnStacked553D Stacked Column.
xl3DColumnStacked100563D 100% Stacked Column.
xl3DLine-41013D Line.
xl3DPie-41023D Pie.
xl3DPieExploded70Exploded 3D Pie.
xlArea1Area
xlAreaStacked76Stacked Area.
xlAreaStacked10077100% Stacked Area.
xlBarClustered57Clustered Bar.
xlBarOfPie71Bar of Pie.
xlBarStacked58Stacked Bar.
xlBarStacked10059100% Stacked Bar.
xlBubble15Bubble.
xlBubble3DEffect87Bubble with 3D effects.
xlColumnClustered51Clustered Column.
xlColumnStacked52Stacked Column.
xlColumnStacked10053100% Stacked Column.
xlConeBarClustered102Clustered Cone Bar.
xlConeBarStacked103Stacked Cone Bar.
xlConeBarStacked100104100% Stacked Cone Bar.
xlConeCol1053D Cone Column.
xlConeColClustered99Clustered Cone Column.
xlConeColStacked100Stacked Cone Column.
xlConeColStacked100101100% Stacked Cone Column.
xlCylinderBarClustered95Clustered Cylinder Bar.
xlCylinderBarStacked96Stacked Cylinder Bar.
xlCylinderBarStacked10097100% Stacked Cylinder Bar.
xlCylinderCol983D Cylinder Column.
xlCylinderColClustered92Clustered Cone Column.
xlCylinderColStacked93Stacked Cone Column.
xlCylinderColStacked10094100% Stacked Cylinder Column.
xlDoughnut-4120Doughnut.
xlDoughnutExploded80Exploded Doughnut.
xlLine4Line.
xlLineMarkers65Line with Markers.
xlLineMarkersStacked66Stacked Line with Markers.
xlLineMarkersStacked10067100% Stacked Line with Markers.
xlLineStacked63Stacked Line.
xlLineStacked10064100% Stacked Line.
xlPie5Pie.
xlPieExploded69Exploded Pie.
xlPieOfPie68Pie of Pie.
xlPyramidBarClustered109Clustered Pyramid Bar.
xlPyramidBarStacked110Stacked Pyramid Bar.
xlPyramidBarStacked100111100% Stacked Pyramid Bar.
xlPyramidCol1123D Pyramid Column.
xlPyramidColClustered106Clustered Pyramid Column.
xlPyramidColStacked107Stacked Pyramid Column.
xlPyramidColStacked100108100% Stacked Pyramid Column.
xlRadar-4151Radar.
xlRadarFilled82Filled Radar.
xlRadarMarkers81Radar with Data Markers.
xlRegionMap140Map chart.
xlStockHLC88High-Low-Close.
xlStockOHLC89Open-High-Low-Close.
xlStockVHLC90Volume-High-Low-Close.
xlStockVOHLC91Volume-Open-High-Low-Close.
xlSurface833D Surface.
xlSurfaceTopView85Surface (Top View).
xlSurfaceTopViewWireframe86Surface (Top View wireframe).
xlSurfaceWireframe843D Surface (wireframe).
xlXYScatter-4169Scatter.
xlXYScatterLines74Scatter with Lines.
xlXYScatterLinesNoMarkers75Scatter with Lines and No Data Markers.
xlXYScatterSmooth72Scatter with Smoothed Lines.
xlXYScatterSmoothNoMarkers73Scatter 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

DAYPEOPLE
15
26
37
48

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

CREATING SIMPLE CHART USING VBA

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. 

CREATING CHART USING CHARTSHEET

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.