How To Use VBA To Select The Chart Type In Excel

Excel has a large number of choices in terms of creating charts and graphs. But it surely’s not really easy to automate the VBA course of of choosing the kind of chart due to the big variety of choices.

This text explains learn how to create your individual process to offer customers choices when making a range.

Itemizing Chart Sorts With VBA

When utilizing VBA to make the choice, you employ commonplace Excel constants. The code to create a graph and choose the class goes one thing like this:


With ActiveChart
.SetSourceData Supply:=Sheets("examples").Vary("A1:B8")
.Location The place:=xlLocationAsObject, Title:="examples"

Finish With


The above code creates a graph from the info within the outlined vary and selects a Three-D space graph. The fixed worth “xl3DArea” is the lengthy integer -4098 telling the code which chart to make use of.

The issue is there isn’t a easy approach utilizing VBA to search out out the fixed names for the several types of charts. The listing beneath covers the fixed names for 5 of the most well-liked ones.

  • xlBarClustered
  • xl3DLine
  • xlArea
  • xl3DColumn
  • xl3DPie

One choice is to obtain the complete listing from the official Microsoft developer’s web site and construct the listing into your code library. Click on right here for the documented listing.

A balanced method is perhaps to establish the most well-liked sorts and use the related constants.

  • xlBarClustered=60
  • xl3DLine=-4101
  • xlArea=-4098
  • xl3DColumn=-4100
  • xl3DPie=-4102

Displaying Widespread Charts With VBA

The thought now could be to show the graphs as examples to the person so a range could be made.

First, we will put the values into an array.

myArray = Array(60, -4101, -4098, -4100, -4102)

With the values for the chart sorts listed, the code loops by way of the array and shows the related chart, pausing to offer the person the choice of creating a range.

The message field is ready to sure or no and the worth “7” signifies the person has clicked the “sure” button, exiting the loop and leaving the present chart kind as chosen.

For x = zero To UBound(myArray)

ActiveChart.ChartType = myArray(x)

useChart = MsgBox("Choose chart", vbYesNo)

If useChart = 7 Then Exit For


With the chart chosen, the person then has the choice of modifying the choice manually with commonplace Excel instruments.


Chart choice is an efficient instance of Excel over-delivering. With so many choices to select from, many customers grow to be pissed off with making the correct choice, making future consistency of favor a problem. By implementing some commonplace VBA procedures, you can also make life simpler for your self and customers of your Excel software.

Leave a Reply

Your email address will not be published. Required fields are marked *

Everledger’s Kemp and Omise’s Hasegawa join TC Blockchain

SQL Reporting Services Tutorials: Quick Guide – Basic to Advanced