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:

Charts.incorporate

With ActiveChart
.SetSourceData Resource:=Sheets(“illustrations”).Array(“A1:B8”)
.Place The place:=xlLocationAsObject, Title:=”examples”

Stop With

activeChart.ChartType=xl3DArea

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.

xlBarClustered
xl3DLine
xlArea
xl3DColumn
xl3DPie

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.

xlBarClustered=sixty
xl3DLine=-4101
xlArea=-4098
xl3DColumn=-4100
xl3DPie=-4102

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

Next

With the chart picked, the user then has the possibility of modifying the selection manually with standard Excel resources.

Summary

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

Leave a Reply

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