How To Use VBA To Pick out The Chart Kind In Excel
Posted in WordPress on Jun 29, 2014
Excel has a multitude of options when it comes to producing charts and graphs. But it is really not so quick to automate the VBA system of picking out the sort of chart simply because of the large range of choices.
This report explains how to create your own process to give users selections when making a assortment.
Listing Chart Forms With VBA
When working with VBA to make the collection, you use typical Excel constants. The code to make a graph and find the group goes anything like this:
.Place The place:=xlLocationAsObject, Title:=”examples”
The above code produces a graph from the knowledge in the described selection and selects a 3-D area graph. The frequent worth “xl3DArea” is the extended integer -4098 telling the code which chart to use.
The challenge is there is no very simple way making use of VBA to locate out the consistent names for the various types of charts. The checklist down below covers the regular names for five of the most common kinds.
One choice is to down load the whole record from the official Microsoft developer’s site and establish the checklist into your code library. Click on below for the documented list.
A well balanced tactic could be to detect the most well-liked sorts and use the applicable constants.
Displaying Widespread Charts With VBA
The strategy now is to display the graphs as illustrations to the person so a choice can be built.
Initial, we can set the values into an array.
myArray = Array(sixty, -4101, -4098, -4100, -4102)
With the values for the chart varieties listed, the code loops by way of the array and displays the applicable chart, pausing to give the person the choice of building a choice.
The information box is established to sure or no and the price “7” indicates the consumer has clicked the “of course” button, exiting the loop and leaving the recent chart sort as selected.
For x = To UBound(myArray)
ActiveChart.ChartType = myArray(x)
useChart = MsgBox(“Pick out chart”, vbYesNo)
If useChart = 7 Then Exit For
With the chart picked, the user then has the possibility of modifying the selection manually with standard Excel resources.
Chart collection is a very good illustration of Excel about-delivering. With so quite a few possibilities to choose from, lots of consumers turn out to be disappointed with making the proper choice, producing foreseeable future regularity of style an issue. By employing some regular VBA strategies, you can make existence less difficult for on your own and end users of your Excel application.
By Andy L Gibson