Solved

Programmatically generating charts in Excel 2010 VBA

Posted on 2015-02-24
6
110 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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.

860 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