Solved

Programmatically generating charts in Excel 2010 VBA

Posted on 2015-02-24
6
112 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 34

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 34

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 34

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

707 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