Solved

Programmatically generating charts in Excel 2010 VBA

Posted on 2015-02-24
6
109 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 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