Excel is a very comprehensive tool when it comes to manual manipulation of its functions, but then create a graph using VBA macros allows you to insert a dynamic content to your spreadsheet.
Although many users are unaware of their usefulness, macros allow any work that is difficult to complete manually.
The main job of this helper is automate tasks that are recorded to insert them quickly when needed.
Learn VBA language is indispensable for such creations, because knowing the objects Chart and ChartObject will facilitate understanding.
Excel has many tools and options to make your work faster and easier. A tool that may desconozcas of this program is the option to design href=”https://tdftips.com/hacer-tabla-dinamica-excel-actualice-automaticamente/”>
previous aspects to create a chart using VBA macros
ChartObjects are a collection that is within Excel spreadsheets , so it will start with the ChartObject object, because there may be more than one; must be distinguished with an index as follows: “ ChartObject (i) ”
This object is the handle which contain graphic or known in this language as Chart, which is governed by the hierarchy of:
- Application & gt; WorkBook & gt; WorkSheet & gt; ChartObject & gt; Chart & gt; ChartTitle (example to define an object that is within the graph)
This is considered the most important parts of the graph, knowledge of which is essential for making the VBA code plasmarás the graph in the worksheet.
It is important to remember that within the macro any of these aspects can become an object so that so you can understand the code you write.
Step by step to make the macro code for a graphic
The example used in this article to create a graph using VBA macros discuss the rainfall for a year in a specific area and shall be embodied in the form of bars.
To start, two sheets of the Microsoft Excel program, where the first will be named “ Graphic ” and the second the name “ Data strong>” will be used.
You may also be interested in:
Press the keys “ Alt + F11 ” to open the Editor visual Basic to choose your workbook in the project Explorer.
In the menu editor, select the option “ Insert ” to click on “ Block ” and there write the code for your chart. In this case, is the following:
- Sub GDLluvias () Dim As ChartObjectSet Rain Rain = Sheets ( “Graphic”) ChartObjetcts.Add (Left:. = 300, Top: = 0,
Width: = 300, Height: = 200) -Here Top and Left are based on the upper left margin of sheet cálculo.With Chart.SetSourceData Rain Source: = Sheets ( “Data”) Range ( “A1: B13”. ) -This is the source of datos..ChartType = xlColumnClustered -. chart type
.Legend.Delete-It has been decided to delete the caption data p>.
Since this example only has a set of data, it is decided delete the legend because it could disrupt the quality and space of the graph in general.
In the event that the type of chart you want to create have more than one set of data; the use of legends is recommended as it will help identify colors and shapes of each of the entered data.
If on the other hand want to change the colors of the bars, you must add to your macro code “ .SeriesCollection (1) .Format.Fill.ForeColor.RGB = RGB (0, 170, 171) “.
This should be placed between the terms “ With ” and “ End With “. In the example it has been decided to place the bars emerald, but if you want to place other single color must place the code number of the language VBA.
One of the great advantages of this method is the ability to record macros that have been encoded for reuse later.
It is not only important to create a chart correctly, you must also create a legend to help you identify and distinguish good sections of your chart.
You may also be interested in:
I'm sorry this content was not useful for you!
Let me improve this content!
Tell me, how can I improve this content?