Charts are another very important elements available in Excel. They play key role in data visualization. Charts have been important in comparing data and analytics. We often use them in reports and dashboards. Because they help to get data-driven insights for the users.Today I am going to show you how to create a chart automatically using VBA. Let’s consider this sample data.
This is a monthly profit of a shop. Now let’s create a chart to compare profit vs months. So months will be in x axis and profit will be in y axis. We can create the chart automatically using below code.
|
Sub CreateChart_Ex1() Dim WS As Worksheet Set WS = Worksheets("Sheet1") WS.Activate WS.Range("A1:B13").Select WS.Shapes.AddChart2(227, xlLine).Select End Sub |
Note that you need to activate the sheet before select the range if you are in different sheet. WS.Activate doesn’t throw error even if you are in that same sheet. You will get this chart if you run above code.
However using this code, you can create a chart only in the worksheet where your data is in. Suppose you have data in one sheet and you need to create chart in another sheet. We have a solution for that too. Assume we have data in Sheet1 and we want to create chart in Sheet3. We can do it like this.
|
Sub CreateChart_Ex2() Dim WS As Worksheet Dim WS_New As Worksheet Set WS = Worksheets("Sheet1") Set WS_New = Worksheets("Sheet3") WS_New.Activate WS_New.Shapes.AddChart2(227, xlLine).Select ActiveChart.SetSourceData Source:=Range(WS.Name & "!$A$1:$B$13") End Sub |
In above examples we created graphs for fixed range. How about if we require to create a chart for dynamic range. Then we have to find the last row of the data, assign it to a variable and then use that variable like this.
| WS.Range("A1:B" & LastRow).Select |
Also it is a good practice to define the variable at the beginning. You can define the LastRow as integer or long depending on the amount of rows you will have.
This post explains how to find the row number of the last non empty cell of a worksheet.
Best way to get the last row of any column of the excel sheet
So from this post we learnt how to create a chart with one series automatically. If you want to know how to create a chart with multiple series then check out this post.
