[ad_1]
Excel has a multitude of options when it comes to creating charts and graphs. But it’s not so easy to automate the VBA process of selecting the type of chart because of the large number of options.
This article explains how to create your own procedure to give users options when making a selection.
Listing Chart Types With VBA
When using VBA to make the selection, you use standard Excel constants. The code to create a graph and select the category goes something like this:
Charts.add
With ActiveChart
.SetSourceData Source:=Sheets("examples").Range("A1:B8")
.Location Where:=xlLocationAsObject, Name:="examples"End With
activeChart.ChartType=xl3DArea
The above code creates a graph from the data in the defined range and selects a 3-D area graph. The constant value “xl3DArea” is the long integer -4098 telling the code which chart to use.
The problem is there is no simple way using VBA to find out the constant names for the different types of charts. The list below covers the constant names for five of the most popular ones.
- xlBarClustered
- xl3DLine
- xlArea
- xl3DColumn
- xl3DPie
One option is to download the full list from the official Microsoft developer’s site and build the list into your code library. Click here for the documented list.
A balanced approach might be to identify the most popular types and use the relevant constants.
- xlBarClustered=60
- xl3DLine=-4101
- xlArea=-4098
- xl3DColumn=-4100
- xl3DPie=-4102
Displaying Common Charts With VBA
The idea now is to display the graphs as examples to the user so a selection can be made.
First, we can put the values into an array.
myArray = Array(60, -4101, -4098, -4100, -4102)
With the values for the chart types listed, the code loops through the array and displays the relevant chart, pausing to give the user the option of making a selection.
The message box is set to yes or no and the value “7” indicates the user has clicked the “yes” button, exiting the loop and leaving the current chart type as selected.
For x = 0 To UBound(myArray)ActiveChart.ChartType = myArray(x)
useChart = MsgBox("Select chart", vbYesNo)
If useChart = 7 Then Exit For
Next
With the chart selected, the user then has the option of modifying the selection manually with standard Excel tools.
Summary
Chart selection is a good example of Excel over-delivering. With so many options to choose from, many users become frustrated with making the right selection, making future consistency of style an issue. By implementing some standard VBA procedures, you can make life easier for yourself and users of your Excel application.
[ad_2]
