need help automating graph creation

jmckeone · Sep 27, 2006

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

A

B

C

D

E

F

G

1

Date

Location1

Location2

Location3

Location4

Location5

Location6

2

3/16/2006

152.79

265.8

862.39

884.84

1320

1830

3

3/23/2006

236.9

285.47

875.19

883.2

1330

1760

4

3/30/2006

227.27

283.3

932.66

963.5

1260

1750

5

4/6/2006

236.93

287.02

993.83

1010

1310

1780

6

4/13/2006

236.87

303.89

951.59

978.25

1290

1700

7

4/20/2006

225.67

286.76

1030

1040

1240

1770

8

4/27/2006

223.51

280.37

1160

1180

1300

1740

9

5/4/2006

215.23

292.69

998.67

1060

1180

1760

10

5/11/2006

189.49

300.25

1140

1230

1310

1800

11

5/18/2006

157.98

280.3

1400

1500

1410

1590

12

5/25/2006

156.31

289.87

1410

1530

1400

1600

13

6/1/2006

144.69

297.69

1410

1500

1460

1550

14

6/8/2006

152.25

303.64

1480

1640

1490

1610

15

6/15/2006

156.24

315.01

1450

1570

1610

16

6/22/2006

138.26

295.62

1420

1510

1570

1490

17

6/29/2006

134.14

291.98

1420

1520

1600

1520

18

7/6/2006

131.23

286.76

1020

1110

1530

1760

19

7/13/2006

136.01

294.58

1020

1100

1690

1740

20

7/20/2006

132.95

305.58

1280

1360

1730

1390

21

7/27/2006

124.58

290.6

1320

1410

1750

1310

22

8/3/2006

112.72

294.32

1270

1370

1450

1300

23

8/10/2006

112.81

297.23

1320

1410

1470

1310

24

8/17/2006

134.42

313.27

1370

1470

1500

1340

25

8/24/2006

184.72

327.86

1330

1430

1540

1380

26

8/31/2006

212.62

331.87

1300

1400

1550

1390

27

9/7/2006

230.98

341.6

1290

1440

1530

1400

28

9/14/2006

246.65

354.28

1410

1500

1560

1480

29

Usage(MB)

5000

5002

5003

5004

102BB

103BB

Sheet1

tmcfadden · Sep 27, 2006

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>
Function Graph() As Boolean
'========================================================================
Dim shtData As Worksheet, shtGraph As Worksheet
Dim rngStart As Range, rngXAxis As Range, rngData As Range
Dim nNumberOfGraphsPerSheet As Integer, nGraphLoop As Integer
Dim rngInterimGraph As Range, rngInterimYData As Range
Dim nNumberOfGraphs As Integer
Dim chtChart As Chart

Dim strGraphTitle As String, strXAxis As String, strYAxis As String
Dim strDataStartCell As String, rngDataStartCell As Range

Dim nGraphNumber As Integer
'========================================================================

'========================================================================
' Change this section to suit your needs
nNumberOfGraphsPerSheet = 25
Set shtData = Sheets(1) 'This is the sheet with all your data
Set shtGraph = Sheets(2) 'This is the FIRST sheet to put your graphs
strDataStartCell = "A1" 'This is the cell with "Data" in it
'========================================================================

Application.ScreenUpdating = False

Set rngDataStartCell = shtData.Range(strDataStartCell)
Set rngXAxis = shtData.Cells.Range(strDataStartCell, shtData.Cells.Range(strDataStartCell).End(xlDown).Offset(-1, 0))
Set 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

For nGraphLoop = 1 To nNumberOfGraphs
 'Make sure we can add another graph to the current sheet
 If (nGraphNumber Mod nNumberOfGraphsPerSheet = 0 And nGraphNumber <> 0) Then
 If (Sheets.Count < shtGraph.Index + 1) Then
 ActiveWorkbook.Worksheets.Add
 End If

 Set shtGraph = Sheets(shtGraph.Index + 1)

 End If

 'Figure out the range of data we need to graph
 Set rngYStart = shtData.Range(rngDataStartCell.Offset(0, nGraphLoop).Address)
 Set 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)

 'Insert the graph
 'Charts.Add
 Set chtChart = Charts.Add
 chtChart.ChartType = xlXYScatterLines
 chtChart.SetSourceData Source:=shtData.Range(rngXAxis.Address & "," & rngInterimYData.Address), _
 PlotBy:=xlColumns
 With chtChart
 .HasTitle = True
 .ChartTitle.Characters.Text = strGraphTitle
 .Axes(xlCategory, xlPrimary).HasTitle = True
 .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = strXAxis
 .Axes(xlValue, xlPrimary).HasTitle = True
 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = strYAxis
 End With
 chtChart.Location Where:=xlLocationAsObject, Name:=shtGraph.Name

 nGraphNumber = nGraphNumber + 1
Next nGraphLoop

Application.ScreenUpdating = True

Graph = True
End Function

jmckeone · Sep 27, 2006

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 · Sep 28, 2006

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>
Sub Graph()<hr>

You should then remove the following line:
<hr>Graph = True<hr>

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

-Tim

jmckeone · Sep 28, 2006

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 · Sep 28, 2006

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 · Sep 28, 2006

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.

jmckeone · Sep 28, 2006

reply

McFadden,

The other graphs appear normal. First graph can be seen at the following link:

http://www.onlinephotostorage.com/redweb/nomad/graph1.jpg

Tusharm,

There's probably no need to create so many graphs all at once.

Try telling my boss that

The project I'm current working on has me creating graphs for over 400 columns of data.

tmcfadden · Sep 28, 2006

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>Sub Graph()
'========================================================================
Dim shtData As Worksheet, shtGraph As Worksheet
Dim rngStart As Range, rngXAxis As Range, rngData As Range
Dim nNumberOfGraphsPerSheet As Integer, nGraphLoop As Integer
Dim rngInterimGraph As Range, rngInterimYData As Range
Dim nNumberOfGraphs As Integer
Dim chtChart As Chart

Dim strGraphTitle As String, strXAxis As String, strYAxis As String
Dim strDataStartCell As String, rngDataStartCell As Range

Dim nChartSizeRows As Integer, nChartSizeColumns As Integer
Dim rngChartTopLeft As Range

Dim nGraphNumber As Integer
'========================================================================

'========================================================================
' Change this section to suit your needs
nNumberOfGraphsPerSheet = 25
Set shtData = Sheets(1) 'This is the sheet with all your data
Set shtGraph = Sheets(2) 'This is the FIRST sheet to put your graphs
strDataStartCell = "A1" 'This is the cell with "Data" in it
strXAxisTitleCell = "A1" 'All graphs will use this x axis title
stryaxistitlecell = "A29" 'All graphs will use this y axis title

nChartSizeRows = 20
nChartSizeColumns = 10
'========================================================================

Application.ScreenUpdating = False

Set rngDataStartCell = shtData.Range(strDataStartCell)
Set 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

Set rngChartTopLeft = shtGraph.Range("A1")

For nGraphLoop = 1 To nNumberOfGraphs
'Make sure we can add another graph to the current sheet
If (nGraphNumber Mod nNumberOfGraphsPerSheet = 0 And nGraphNumber <> 0) Then
If (Sheets.Count < shtGraph.Index + 1) Then
ActiveWorkbook.Worksheets.Add
End If

Set shtGraph = Sheets(shtGraph.Index + 1)
Set rngChartTopLeft = shtGraph.Range("A1")

End If

'Figure out the range of data we need to graph
Set rngYStart = shtData.Range(rngDataStartCell.Offset(0, nGraphLoop).Address)
Set rngInterimYData = shtData.Cells.Range(rngYStart, rngYStart.End(xlDown).Offset(-1, 0))

strGraphTitle = rngInterimYData.Cells(1, 1)

'Insert the graph
Set chtChart = Charts.Add
chtChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:="MyNewChart"

chtChart.SetSourceData Source:=shtData.Range(rngXAxis.Address & "," & rngInterimYData.Address), _
PlotBy:=xlColumns
With chtChart
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = strXAxisTitle
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = strYAxisTitle
End With
chtChart.Location Where:=xlLocationAsObject, Name:=shtGraph.Name

'Ugly hack, sorry
Set 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

Set rngChartTopLeft = rngChartTopLeft.Offset(nChartSizeRows)
Next nGraphLoop

Application.ScreenUpdating = True

End Sub<hr>

need help automating graph creation

jmckeone

Well-known Member

Excel Facts

tmcfadden

Board Regular

jmckeone

Well-known Member

tmcfadden

Board Regular

jmckeone

Well-known Member

tusharm

MrExcel MVP

tmcfadden

Board Regular

jmckeone

Well-known Member

tmcfadden

Board Regular

Similar threads

Forum statistics

Share this page

need help automating graph creation

Well-known Member

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

MrExcel MVP

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock