Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Programmatically generating charts in Excel 2010 VBA

Posted on 2015-02-24
6
Medium Priority
?
114 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 35

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 35

Accepted Solution

by:
Norie earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 35

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

597 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