How To Create An Excel Chart Using Vba Macros -Step By Step

 logo pc excel in

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.

How to Create a Graph Using Excel VBA Macros -Step by Step

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=””>

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.

 icon vba

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 ” will be used.

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 .

    End With

    End Sub

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.

 computer excel

In the same way, for add multiple columns as information only should cover the entire range these occupy to the data set in the code.

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:

The graphics you think you can design the way you want Excel is the tool to make your circular charts. Once completed your chart in Excel this can turn it into a TIFF file.

How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

I'm sorry this content was not useful for you!

Let me improve this content!

Tell me, how can I improve this content?

Deja un comentario