need help automating graph creation

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
I do a great deal of graphing of data and am looking for code that will cycle through a grid of data and create graphs pulling not only date and data but x and y axis name, chart title and name of graphed data shown in legend. Example of data set is shown below though it normally ends to far more columns and rows. Would like to have the graphs placed on a different sheet in the workbook with 25 graphs per sheet before a new sheet is begun.

x-axis title in A1
y-axis title in A29
Chart titles in B1:G1
Series names in B29:G29
Book12
ABCDEFG
1DateLocation1Location2Location3Location4Location5Location6
23/16/2006152.79265.8862.39884.8413201830
33/23/2006236.9285.47875.19883.213301760
43/30/2006227.27283.3932.66963.512601750
54/6/2006236.93287.02993.83101013101780
64/13/2006236.87303.89951.59978.2512901700
74/20/2006225.67286.761030104012401770
84/27/2006223.51280.371160118013001740
95/4/2006215.23292.69998.67106011801760
105/11/2006189.49300.251140123013101800
115/18/2006157.98280.31400150014101590
125/25/2006156.31289.871410153014001600
136/1/2006144.69297.691410150014601550
146/8/2006152.25303.641480164014901610
156/15/2006156.24315.011450157015701610
166/22/2006138.26295.621420151015701490
176/29/2006134.14291.981420152016001520
187/6/2006131.23286.761020111015301760
197/13/2006136.01294.581020110016901740
207/20/2006132.95305.581280136017301390
217/27/2006124.58290.61320141017501310
228/3/2006112.72294.321270137014501300
238/10/2006112.81297.231320141014701310
248/17/2006134.42313.271370147015001340
258/24/2006184.72327.861330143015401380
268/31/2006212.62331.871300140015501390
279/7/2006230.98341.61290144015301400
289/14/2006246.65354.281410150015601480
29Usage(MB)5000500250035004102BB103BB
Sheet1
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Here's someting to get you started.

Note, it doesn't size or move the charts. You'll certainly want to add that in.

I haven't exhaustively tested it, but it should be enough to get you headed in the right direction.

-Tim

<hr>
<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> Graph() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>
<SPAN style="color:#007F00">'========================================================================</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> shtData <SPAN style="color:#00007F">As</SPAN> Worksheet, shtGraph <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">Dim</SPAN> rngStart <SPAN style="color:#00007F">As</SPAN> Range, rngXAxis <SPAN style="color:#00007F">As</SPAN> Range, rngData <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> nNumberOfGraphsPerSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, nGraphLoop <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> rngInterimGraph <SPAN style="color:#00007F">As</SPAN> Range, rngInterimYData <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> nNumberOfGraphs <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> chtChart <SPAN style="color:#00007F">As</SPAN> Chart

<SPAN style="color:#00007F">Dim</SPAN> strGraphTitle <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strXAxis <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strYAxis <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strDataStartCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, rngDataStartCell <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Dim</SPAN> nGraphNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#007F00">'========================================================================</SPAN>

<SPAN style="color:#007F00">'========================================================================</SPAN>
<SPAN style="color:#007F00">' Change this section to suit your needs</SPAN>
nNumberOfGraphsPerSheet = 25
<SPAN style="color:#00007F">Set</SPAN> shtData = Sheets(1)      <SPAN style="color:#007F00">'This is the sheet with all your data</SPAN>
<SPAN style="color:#00007F">Set</SPAN> shtGraph = Sheets(2)     <SPAN style="color:#007F00">'This is the FIRST sheet to put your graphs</SPAN>
strDataStartCell = "A1"      <SPAN style="color:#007F00">'This is the cell with "Data" in it</SPAN>
<SPAN style="color:#007F00">'========================================================================</SPAN>

Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">Set</SPAN> rngDataStartCell = shtData.Range(strDataStartCell)
<SPAN style="color:#00007F">Set</SPAN> rngXAxis = shtData.Cells.Range(strDataStartCell, shtData.Cells.Range(strDataStartCell).End(xlDown).Offset(-1, 0))
<SPAN style="color:#00007F">Set</SPAN> rngXAxis = shtData.Range(rngDataStartCell, rngDataStartCell.End(xlDown).Offset(-1))
nNumberOfGraphs = shtData.Range(rngXAxis.Offset(0, 1), rngXAxis.Offset(0, 1).End(xlToRight)).Columns.Count

strXAxis = shtData.Cells(1, 1)
nGraphNumber = 0

<SPAN style="color:#00007F">For</SPAN> nGraphLoop = 1 <SPAN style="color:#00007F">To</SPAN> nNumberOfGraphs
    <SPAN style="color:#007F00">'Make sure we can add another graph to the current sheet</SPAN>
    <SPAN style="color:#00007F">If</SPAN> (nGraphNumber Mod nNumberOfGraphsPerSheet = 0 And nGraphNumber <> 0) <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> (Sheets.Count < shtGraph.Index + 1) <SPAN style="color:#00007F">Then</SPAN>
            ActiveWorkbook.Worksheets.Add
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        
        <SPAN style="color:#00007F">Set</SPAN> shtGraph = Sheets(shtGraph.Index + 1)
        
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

    <SPAN style="color:#007F00">'Figure out the range of data we need to graph</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rngYStart = shtData.Range(rngDataStartCell.Offset(0, nGraphLoop).Address)
    <SPAN style="color:#00007F">Set</SPAN> rngInterimYData = shtData.Cells.Range(rngYStart, rngYStart.End(xlDown).Offset(-1, 0))
    
    strYAxis = rngInterimYData.Cells(1, 1)
    strGraphTitle = rngInterimYData.Cells(rngInterimYData.Rows.Count, 1).Offset(1)
                                            
    <SPAN style="color:#007F00">'Insert the graph</SPAN>
    <SPAN style="color:#007F00">'Charts.Add</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> chtChart = Charts.Add
    chtChart.ChartType = xlXYScatterLines
    chtChart.SetSourceData Source:=shtData.Range(rngXAxis.Address & "," & rngInterimYData.Address), _
        PlotBy:=xlColumns
    <SPAN style="color:#00007F">With</SPAN> chtChart
        .HasTitle = <SPAN style="color:#00007F">True</SPAN>
        .ChartTitle.Characters.Text = strGraphTitle
        .Axes(xlCategory, xlPrimary).HasTitle = <SPAN style="color:#00007F">True</SPAN>
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = strXAxis
        .Axes(xlValue, xlPrimary).HasTitle = <SPAN style="color:#00007F">True</SPAN>
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = strYAxis
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    chtChart.Location Where:=xlLocationAsObject, Name:=shtGraph.Name
    
    nGraphNumber = nGraphNumber + 1
<SPAN style="color:#00007F">Next</SPAN> nGraphLoop

Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>

Graph = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
Being very remedial in my VB skills I've run macros but not a clue how to execute a Function(). I've copied the code to a module but don't see the item in the macro menu.
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
One way is the following:

  • Open up the VBA editor
    Open up the module where you put the function
    Put the cursor anywhere in the function
    Press F5 to run the function

The reason it doesn't show up under "Tools->Macros" is that it's a function and returns a value. That's easy to work around ( especially since you don't really need a return value ).

You can change the declaration to the following:<hr>
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Graph()</FONT><hr>

You should then remove the following line:
<hr><font face=Courier New>Graph = <SPAN style="color:#00007F">True</SPAN></FONT><hr>

That will allow you to select it from the Macros list. This is probably a better implementation for you anyway.

-Tim
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550

ADVERTISEMENT

Thanks for the tip.

There are a few irregularities I need to sort out if you don't mind providing a bit more insight for me:

1) The first graph produced by this method puts the dates on the Y-axis rather than on the X as well as adding an additional series to itself.

2) The code appears to flip flop the intended series name and the chart title, the chart title coming from the headers on the first row and series from the headers on the last row.

3) The title of the Y axis should consistently use one static cell, A29, but appears instead to be using the series name

Once I've gotten those worked out there are a couple mods I was wondering about.

a) What is the best location within the code for placing a standard format section for the graph (size, fonts, etc) so that all newly created graphs will follow it

b) How difficult would it be to have the graphs placed at given points rather than on top of one another? Standard graph size I intend to use is 23 rows by 9 columns with five columns of five graphs at the following points on the graph sheet:
A1,24,47,70,93 (graphs 1-5)
J1,24,47,70,93
S1,24,47,70,93
AB1,24,47,70,93
AK1,24,47,70,93

Appreciate your efforts.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
There's probably no need to create so many graphs all at once. Also, charts can be customized w/o VBA. You should be able to customize the various ideas at
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html
and
Dynamic Chart Title
http://www.tushar-mehta.com/excel/newsgroups/dynamic_chart_title/index.html


I do a great deal of graphing of data and am looking for code that will cycle through a grid of data and create graphs pulling not only date and data but x and y axis name, chart title and name of graphed data shown in legend. Example of data set is shown below though it normally ends to far more columns and rows. Would like to have the graphs placed on a different sheet in the workbook with 25 graphs per sheet before a new sheet is begun.

x-axis title in A1
y-axis title in A29
Chart titles in B1:G1
Series names in B29:G29
{snip}
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151

ADVERTISEMENT

1) The first graph produced by this method puts the dates on the Y-axis rather than on the X as well as adding an additional series to itself.
I don't see that behavior. Are you saying that the other graphs are fine?

2) The code appears to flip flop the intended series name and the chart title, the chart title coming from the headers on the first row and series from the headers on the last row.
Ok, I mis-read the problem statement. I can change that.

3) The title of the Y axis should consistently use one static cell, A29, but appears instead to be using the series name
Easy fix.

a) What is the best location within the code for placing a standard format section for the graph (size, fonts, etc) so that all newly created graphs will follow it
Search the help file for "user-defined custom chart". After you create one, change the code to apply that chart type upon creation.

b) How difficult would it be to have the graphs placed at given points rather than on top of one another?
You'll have to move the graph after it's inserted into the worksheet ( the chtChart.Location ). This process escapes me at the moment.
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Odd... I'll have to look into that later. The first graph appears fine on my version of Excel ( 11.8033.8036 ).

For now, try this new version of the subroutine.

For this to work, you have to create a user-defined chart type named "MyNewChart" ( for now ).

-Tim
<hr><font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Graph()
<SPAN style="color:#007F00">'========================================================================</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> shtData <SPAN style="color:#00007F">As</SPAN> Worksheet, shtGraph <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">Dim</SPAN> rngStart <SPAN style="color:#00007F">As</SPAN> Range, rngXAxis <SPAN style="color:#00007F">As</SPAN> Range, rngData <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> nNumberOfGraphsPerSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, nGraphLoop <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> rngInterimGraph <SPAN style="color:#00007F">As</SPAN> Range, rngInterimYData <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> nNumberOfGraphs <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> chtChart <SPAN style="color:#00007F">As</SPAN> Chart

<SPAN style="color:#00007F">Dim</SPAN> strGraphTitle <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strXAxis <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strYAxis <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strDataStartCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, rngDataStartCell <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Dim</SPAN> nChartSizeRows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, nChartSizeColumns <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> rngChartTopLeft <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">Dim</SPAN> nGraphNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#007F00">'========================================================================</SPAN>

<SPAN style="color:#007F00">'========================================================================</SPAN>
<SPAN style="color:#007F00">' Change this section to suit your needs</SPAN>
nNumberOfGraphsPerSheet = 25
<SPAN style="color:#00007F">Set</SPAN> shtData = Sheets(1) <SPAN style="color:#007F00">'This is the sheet with all your data</SPAN>
<SPAN style="color:#00007F">Set</SPAN> shtGraph = Sheets(2) <SPAN style="color:#007F00">'This is the FIRST sheet to put your graphs</SPAN>
strDataStartCell = "A1" <SPAN style="color:#007F00">'This is the cell with "Data" in it</SPAN>
strXAxisTitleCell = "A1" <SPAN style="color:#007F00">'All graphs will use this x axis title</SPAN>
stryaxistitlecell = "A29" <SPAN style="color:#007F00">'All graphs will use this y axis title</SPAN>

nChartSizeRows = 20
nChartSizeColumns = 10
<SPAN style="color:#007F00">'========================================================================</SPAN>

Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>

<SPAN style="color:#00007F">Set</SPAN> rngDataStartCell = shtData.Range(strDataStartCell)
<SPAN style="color:#00007F">Set</SPAN> rngXAxis = shtData.Range(rngDataStartCell, rngDataStartCell.End(xlDown).Offset(-1))
nNumberOfGraphs = shtData.Range(rngXAxis.Offset(0, 1), rngXAxis.Offset(0, 1).End(xlToRight)).Columns.Count

strXAxisTitle = shtData.Range(strXAxisTitleCell)
strYAxisTitle = shtData.Cells.Range(stryaxistitlecell).Value
nGraphNumber = 0

<SPAN style="color:#00007F">Set</SPAN> rngChartTopLeft = shtGraph.Range("A1")

<SPAN style="color:#00007F">For</SPAN> nGraphLoop = 1 <SPAN style="color:#00007F">To</SPAN> nNumberOfGraphs
<SPAN style="color:#007F00">'Make sure we can add another graph to the current sheet</SPAN>
<SPAN style="color:#00007F">If</SPAN> (nGraphNumber Mod nNumberOfGraphsPerSheet = 0 And nGraphNumber <> 0) <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">If</SPAN> (Sheets.Count < shtGraph.Index + 1) <SPAN style="color:#00007F">Then</SPAN>
ActiveWorkbook.Worksheets.Add
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">Set</SPAN> shtGraph = Sheets(shtGraph.Index + 1)
<SPAN style="color:#00007F">Set</SPAN> rngChartTopLeft = shtGraph.Range("A1")

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#007F00">'Figure out the range of data we need to graph</SPAN>
<SPAN style="color:#00007F">Set</SPAN> rngYStart = shtData.Range(rngDataStartCell.Offset(0, nGraphLoop).Address)
<SPAN style="color:#00007F">Set</SPAN> rngInterimYData = shtData.Cells.Range(rngYStart, rngYStart.End(xlDown).Offset(-1, 0))

strGraphTitle = rngInterimYData.Cells(1, 1)

<SPAN style="color:#007F00">'Insert the graph</SPAN>
<SPAN style="color:#00007F">Set</SPAN> chtChart = Charts.Add
chtChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="MyNewChart"

chtChart.SetSourceData Source:=shtData.Range(rngXAxis.Address & "," & rngInterimYData.Address), _
PlotBy:=xlColumns
<SPAN style="color:#00007F">With</SPAN> chtChart
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = strXAxisTitle
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = strYAxisTitle
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
chtChart.Location Where:=xlLocationAsObject, Name:=shtGraph.Name

<SPAN style="color:#007F00">'Ugly hack, sorry</SPAN>
<SPAN style="color:#00007F">Set</SPAN> shpChart = ActiveSheet.Shapes(Right(ActiveChart.Name, Len(ActiveChart.Name) - InStr(ActiveChart.Name, " ")))
shpChart.Top = rngChartTopLeft.Top
shpChart.Left = rngChartTopLeft.Left
shpChart.Width = shtGraph.Cells(rngChartTopLeft.Row, rngChartTopLeft.Column + nChartSizeColumns).Left - rngChartTopLeft.Left
shpChart.Height = shtGraph.Cells(rngChartTopLeft.Row + nChartSizeRows, rngChartTopLeft.Column).Top - rngChartTopLeft.Top

nGraphNumber = nGraphNumber + 1

<SPAN style="color:#00007F">Set</SPAN> rngChartTopLeft = rngChartTopLeft.Offset(nChartSizeRows)
<SPAN style="color:#00007F">Next</SPAN> nGraphLoop

Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT><hr>
 

Forum statistics

Threads
1,137,330
Messages
5,680,861
Members
419,937
Latest member
Talic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top