Excel has many options when it comes to creating charts and graphs. But it’s not so easy to automate the VBA process of a choice of chart type due to the large number of options.
This article explains how to create your own procedure to provide users with options when choosing.
Types of listing cards with VBA
When using VBA, you use standard Excel constants to select. The code for creating a graph and selecting a category looks like this:
Charts.add
With ActiveChart
.SetSourceData Source: = Tables (“examples”). Range ("A1: B8")
. Location: = xlLocationAsObject, Name: = "examples"
End with
activeChart.ChartType = xl3DArea
The above code creates a graph from data in a specific range and selects a three-dimensional graph of the area. The constant value “xl3DArea” is a long integer of -4098, indicating the code to use the chart.
The problem is that there is no easy way to use VBA to define constant names for different types of diagrams. The list below shows the permanent names of the five most popular.
- xlBarClustered
- xl3DLine
- xlArea
- xl3DColumn
- xl3DPie
One option is to download the full list from the official Microsoft developer site and create a list in your code library. Click here for a documentary list.
A balanced approach may be to determine the most popular types and use the appropriate constants.
- xlBarClustered = 60
- xl3DLine = -4101
- xlArea = -4098
- xl3DColumn = -4100
- xl3DPie = -4102
Displaying general diagrams with VBA
Now the idea is to display the graphs as examples for the user so that you can make a choice.
First, we can put values into an array.
myArray = Array (60, -4101, -4098, -4100, -4102)
With the values of the listed chart types, the code goes through the array and displays the corresponding graph, pausing to allow the user to make a choice.
The message field is set to “yes” or “no”, and the value “7” means that the user pressed the yes button, exiting the loop and leaving the current chart type selected.
For x = 0 To UBound (myArray)
ActiveChart.ChartType = myArray (x)
useChart = MsgBox (“Select Graph”, vbYesNo)
If useChart = 7, then Exit For
following
With the selected chart, the user can then change the selection manually using standard Excel tools.
Summary
Choosing a chart is a good example of Excel overflow. With so many choices to choose from, many users are disappointed in choosing the right choice, which makes the future style sequence a problem. By implementing some standard VBA procedures, you can make life easier for yourself and your Excel users.
