Solved

Programmatically generating charts in Excel 2010 VBA

Posted on 2015-02-24
6
103 Views
Last Modified: 2016-02-10
Hi,

I'm struggling to come up with the code to programmatically add a series of charts to a test spreadsheet in Excel 2010 VBA.  Haven't used VBA a whole lot and less with charts.  

I've looked through a number of examples online and haven't found a combination that seems to work.  Can someone peek at this and suggest a good example to use or sample code?

The first tab is called Distributions.  A sample chart is to the right of the table, and the VBA code I've already created is in the workbook.

Much appreciated!

--Ben
salesexample.xlsm
0
Comment
Question by:Ben Conner
  • 3
  • 3
6 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 40629732
Do you want to create similar charts for each of the tables?
0
 

Author Comment

by:Ben Conner
ID: 40629820
Yes; the only thing that would change is the height of the bars.  The code currently generates empty charts going down the right side.

--Ben
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 40629834
This is what I came up with, it's a little rough but it seems to get the job done.
Option Explicit

Sub CreateCharts()
Dim ws As Worksheet
Dim cht As Chart
Dim chtObj As ChartObject
Dim ser As Series
Dim rng As Range
Dim rngData As Range
Dim I As Long

    Set ws = Sheets("Distributions")

    Set rng = ws.Range("B4:B8")
    
    Do
        Do
            If rngData Is Nothing Then
                Set rngData = rng
            Else
                Set rngData = Union(rngData, rng)

            End If
            Set rng = rng.Offset(, 2)
        Loop Until rng.Cells(1, 1).Value = ""

        Set chtObj = ws.ChartObjects.Add(ws.Cells(rng.Row, "I").Left, rng.Offset(-2).Top, rngData.Width * 2 * 3, rng.Resize(rng.Rows.Count + 2).Height)

        chtObj.Chart.SetSourceData Source:=rngData

        Set cht = chtObj.Chart

        cht.ChartType = XlChartType.xlColumnClustered
        I = 1
        For Each ser In cht.SeriesCollection
            ser.Name = "=" & rngData.Areas(I).Cells(1, 1).Offset(-2).Address(external:=True)
            I = I + 1
        Next ser
        
        Set rngData = Nothing
        
        Set rng = ws.Cells(rng.Row, 2).Resize(5)
        Set rng = rng.Offset(9)
        
    Loop Until rng.Cells(1, 1).Value = ""

End Sub

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Ben Conner
ID: 40629866
Good grief!  I've been messing with this for a day and a half. :)  Wow.  That's impressive.

Were I to want to add a title to each chart reflecting the table title (Wa Sales, etc.), how would that be added?

There's a LOT here for me to look up and see how you did that.  I can't thank you enough for the help!

--Ben
0
 
LVL 33

Expert Comment

by:Norie
ID: 40629892
Ben

This will add the chart title, put it after the code that sets the ChartType
        cht.SetElement msoElementChartTitleAboveChart
        cht.ChartTitle.Text = ws.Cells(rng.Cells(1, 1).Row - 2, "A").Value

Open in new window

0
 

Author Comment

by:Ben Conner
ID: 40629915
Thanks much!

Where would one go to read up on the techniques you used in this?  I've read the Excel 2013 Power Programming with VBA by John Walkenbach but I don't recall seeing coding this sophisticated in it.

--Ben
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now