As we all know that a Chart is a graphical representation of data. Charts help us to visualize the data in graphical form and can display a lot more information in an easy to understand visual format. Most of the time it is difficult to draw a conclusion just by looking at the data we have, and this is where charts become handy and can be used to convey information.
Charts are easy to insert based on the source data manually but sometimes we need to automate the chart insertion and most people find it difficult to control the chart creation and its properties via VBA. In this article, I will show how you can use VBA to insert a Chart, as well as manage its appearance.
We will start by creating a simple Clustered Column Chart based on the simple Monthly Sales Data as shown below. You can download the file by clicking the link at the bottom of this article.
This is a simple month wise sales data…
Inserting Chart Manually:
First, we will go through the process to manually insert a Clustered Column Chart. To do so, follow the steps given below…
The above method will insert a Clustered Column Chart on the Sheet. You can resize and move the chart by simply selecting and dragging it to the desired location on the Sheet.
To follow the above steps, you can take the help of the following image…
Tip: While resizing the chart, hold down the Alt key and when you resize the chart, it will automatically fit along with the cell boundaries.
The final Clustered Column Chart inserted with the help of the above method will look like the image shown below...
Brief introduction of Chart Elements:
Now it’s time to learn how can we insert the same Clustered Column Chart programmatically with the help of a VBA code.
But before actually going for that, we must be familiar with the chart elements we will manipulate through the VBA code and these are shown below…
Inserting a chart with VBA:
After a short description of important chart elements, let us continue to write a code which will insert a clustered column chart based on the sample sales data.
To do so, either download the Workbook from the link at the bottom of this article or open a new blank Workbook and enter the sales data on Sheet1 manually.
Assuming you have placed the sales data on Sheet1, press Alt + F11 to open Visual Basic Editor. On VB Editor (Visual Basic Editor), click on Insert and choose Module. This will insert a New Module in the VB Project as shown below…
Now place the code given below into the newly inserted module called Module1.
Sub InsertChart() 'Declare all the required variables here Dim wsData As Worksheet 'Sheet variable to hold the reference of Sheet with Source Data Dim chObj As ChartObject 'Chart Object variable Dim SourceData As Range Dim cLeft, cTop, cWidth, cHeight Application.ScreenUpdating = False Set wsData = Sheets("Sheet1") Set SourceData = wsData.Range("A1").CurrentRegion 'setting the dimension and position of the chart 'cLeft and cTop will help to align the chart to the top left of the cell D1 cLeft = wsData.Range("D1").Left cTop = wsData.Range("D1").Top 'cWidth and cHeight will help to set the width and height of the chart cWidth = 500 cHeight = 300 'Deleting any existing chart on wsData Sheet before inserting a New Chart On Error Resume Next wsData.ChartObjects.Delete On Error GoTo 0 'Inserting the default Clustered Column Chart and setting the source data for the chart Set chObj = wsData.ChartObjects.Add(cLeft, cTop, cWidth, cHeight) chObj.Chart.SetSourceData SourceData Application.ScreenUpdating = True End Sub
The above code is all if you need to insert the default Clustered Column Chart Type with default Chart Elements such as Chart Title and Legends added onto it. But most of the times we need to format the Chart or add/delete some Chart Elements on it.
Let’s now talk about how we can add code to format the Chart and add/delete some of the Chart Elements in our main code.
Controlling the Chart Type:
When you insert a basic chart with the help of the code given above, a default Clustered Column Chart is inserted. But you can control which type of Chart you want to insert and some of the examples are given below…
'Clustered Column Chart chObj.Chart.ChartType = xlColumnClustered 'Pie Chart chObj.Chart.ChartType = xlPie 'Bar Chart chObj.Chart.ChartType = xlBarClustered 'Line Chart chObj.Chart.ChartType = xlLine 'Line Chart with Markers chObj.Chart.ChartType = xlLineMarkers In the same way, you can insert other types of charts as well.
Controlling Data Labels:
When you programmatically insert a Chart, the DataLabels are not shown on the Chart but you can show or hide the Data Labels and also set the position of the DataLabels as you're your requirement using the following lines of code…
'Displaying the data labels chObj.Chart.SetElement msoElementDataLabelTop 'You can set the position of the data labels as per your requrement e.g. chObj.Chart.SetElement msoElementDataLabelBottom 'If you want to hide the data lebels chObj.Chart.SetElement msoElementDataLabelNone
Setting the Chart Title:
Though the Chart Title will be automatically be added when you programmatically insert a Chart, you can also explicitly add the Chart Title and set it’s Text as per your requirement using the following lines of code…
'Enabling the Chart Title chObj.Chart.HasTitle = True chObj.Chart.ChartTitle.Text = "Quantity Sold By Month"
Setting the Legend:
You can also hide the Chart Legends and make them visible again if you want using the following lines of code…
'If you want to hide the Legends chObj.Chart.SetElement msoElementLegendNone 'Show Legends on right side chObj.Chart.SetElement msoElementLegendRight
Hide or Show the Chart Gridlines:
You can also hide the Chart Gridlines and make them visible again if you want using the following lines of the code…
'Delete the Gridlines chObj.Chart.Axes(xlValue).MajorGridlines.Delete 'Show the Gridlines again chObj.Chart.SetElement msoElementPrimaryValueGridLinesMajor
So, this was all about how you can programmatically insert a Chart in Excel and control its elements.
In the next part, we will discuss how we can format some other elements e.g. changing the color of the Chart Series, moving Chart from one Sheet to another Sheet etc.
There are two modules called Module1 and Module2 where Module1 contains the code to insert a basic Clustered Column Chart and Module2 contains the complete code which you can customize as per your need by deleting the unwanted lines of code.