Learning the VBA language is essential to perform this type of creation, since knowing the objects Chart e ChartObject it will make understanding easier.
Excel has many tools and options to make your work easier and faster. One tool you may not be familiar with in this program is the option to design dynamic tables.
Previews for creating a chart using VBA macros
ChartObjects are one collection found within Excel spreadsheets, then it will start with the ChartObject object which, since there can be more than one; must be distinguished with an index as follows: " ChartObject (i) ".
This object is the one that will have the task of containing the graph or known in this language as Graph, which is governed by the hierarchy of:
- Application> WorkBook> WorkSheet> ChartObject> ChartTitle (example to define an object that is inside the graph).
These are considered to be the most important parts of the chart, knowledge of which is essential to create the VBA code of the chart that you will capture in the spreadsheet.
It is important to remember that within the macro some of these aspects can become an object, so that you can understand the code you write.
Step by step to create the macro code of a chart
The example that will be used in this article to create a chart using VBA macros will talk about the rainfall during a year in a specific area and will be shown in the form of bars.
To start we will use two sheets of the Microsoft Excel program, where the first will be called " Graphic "And the second will be called" Data ".
Press i keys " Alt + F11 ”To open the Visual Basic Editor and choose the working folder within the Project Explorer.
In the editor menu, select the option " Inserisci "To click" Module “, And there you will write the code for your chart. In this case, it's the following:
- Sub GD Rain () Dim Rain As ChartObjectSet Rain = Sheets (“Graph”). ChartObjetcts.Add (Left: = 300, Top: = 0,
Width: = 300, Height: = 200) - Here Top and Left are based on the upper left margin of the spreadsheet. With Rains Chart.SetSourceData Source: = Sheets ("Data") Range ("A1: B13") - This is the data source..ChartType = xlColumnClustered - Type of chart..Legend.Delete - It has been decided to delete the data legend.
Ends with
End subtitle
Since this example has only one data series, we decide to delete the legend because it could compromise the quality overall and spacing of the graph.
advice
In case the type of chart you want to create has more than one data series; The use of legends is recommended as they will help identify the colors and shapes of each of the data entered.
Likewise, to add the multiple columns as information you just need to cover the entire range they occupy when setting up the data in the code.
If, on the other hand, you want to change the colors of the bars, you have to add the " .SeriesCollection (1).Format.Fill.ForeColor.RGB = RGB (0, 170, 171) ".
It should be inserted between the terms " With " is " Finish with ". In the example it was decided to place the bars in emerald color, but if you want to insert another type of color, just enter the numeric code of the VBA language.
One of the great advantages of this method is the ability to record macros that have been coded to be reused later.
Not only is it important to create a chart correctly, you should also create a legend that helps you identify and distinguish sections of your chart well.
The graphics you create can be designed the way you want, Excel has the tool to make your graphics circular. Once your chart is finished in Excel, you can convert it to a TIFF file.
TagsEccellere
