Formatting Excel Line Charts Using VBA

We often use Excel charts in our worksheets. They are really important no matter for which field your spreadsheet is related to. Because charts can graphically represent data which helps to gain accurate understanding of the data. From our earlier lessons we learnt how to add charts with one series or multiple series automatically using vba. Check out these posts if you want to learn how.

Create a line chart (with one series)

Create line chart with multiple series

So today we are going to look at how we can format a line chart automatically using VBA. Here is some sample data I have in my excel sheet.

So if we insert a line chart in Excel 2013, then the default chart will look like this.

However there are lots of styles you can choose from for your chart. This is how you can change the style of your chart using VBA.

Sub ChartStyle33()      Dim Cht As Chart      Set Cht = ActiveSheet.ChartObjects("Chart 1").Chart      Cht.ChartStyle = 33 End Sub

If we run the above code, the chart will change to something like this.

Note that, in the above code we refer to the chart using it’s name. So you will need to change the “Chart 1” with the name of your chart. Don’t know how to find the name of a chart? This post explain how to find the name of a chart manually.

Find the name of a chart in Excel

Read this post if you want to find the name of the active chart using VBA.

How to find the name of an active chart using VBA

In the above macro, we changed the style of the chart using it’s name. However instead of the name we can refer to the chart using ActiveChart property, if we want to change the style of the active chart. Then we can rewrite the above code as follows.

Sub ChartStyle43()      Dim Cht As Chart      Set Cht = ActiveChart      Cht.ChartStyle = 43 End Sub

Note that I used a different number for the Cht.ChartStyle in this example. So the result will look like this.

Now you may have thought what are the numbers you can use for Cht.ChartStyle. You can use numbers from 1 to 48 for the Cht.ChartStyle. In addition to those styles, the following are also available in Excel 2013.

    342
    332
    239
    237
    236
    235
    234
    233
    232
    231
    230
    228
    227