I am trying to program Excel 2007 with VB.NET in order to plot 100 graphs in
one worksheet. I would like to know if there is any way to declare the graphs
as an array, like:

Dim MyPlots(99) as Chart

If so, how can I focus in each individual graph and add it to the worksheet ?

Thanks.

MD

You could declare an array of charts but if it's only for the aid of
creating charts I can't see what purpose it serves, unless you need to go
back over them for some reason. It's not clear what you are doing but
whatever it is maybe the following VBA macro will give you some ideas. (Most
of the code is from another post, adapted to include the chart arrray).

Sub test()
Dim j As Long, k As Long, idx as long
Dim chtObj As ChartObject
Dim cht As Chart
Dim sr As Series
Dim arrCharts() As Chart

For k = 2 To 2 + (5 * 3) Step 5
For j = 2 To 2 + (10 * 4) Step 10
idx = idx + 1
Next
Next
ReDim arrCharts(1 To idx) As Chart

idx = 0

For k = 2 To 2 + (5 * 3) Step 5
For j = 2 To 2 + (10 * 4) Step 10
Set rng = Range(Cells(j, k), Cells(j + 7, k + 3))

With rng
Set chtObj = ActiveSheet.ChartObjects.Add(.Left, .Top,
..Width, .Height)
End With

Set cht = chtObj.Chart

' your own code to set source data here in the loop

Set sr = cht.SeriesCollection.NewSeries
sr.Values = "{1,2,3}"

idx = idx + 1
Set arrCharts(idx) = cht

Next
Next

For i = 1 To idx
Debug.Print arrCharts(i).Name
Next

End Sub

Regards,
Peter T

"MD" wrote in message
...
I am trying to program Excel 2007 with VB.NET in order to plot 100 graphs
in
one worksheet. I would like to know if there is any way to declare the
graphs
as an array, like:

Dim MyPlots(99) as Chart

If so, how can I focus in each individual graph and add it to the
worksheet ?

Thanks.

MD

Peter:

I'd like to elaborate a little more on my application; I have a data file
that is generated in another application (linked to an electronics
acquisition system). I read the file with VB.NET and organize the data in a
format that Excel recognizes. I am trying to use Excel mainly as a plotting
tool. I copy my formatted data in 2 or 3 worksheets of a workbook. Then I add
one worksheet only to plot on it the 100 independent graphs. I do not want to
have separate worksheets per graph, because I need them side by side for
analysis and comparison. I need to be able to:

- Initialize each graph with its own title, size and relative position in
the worksheet. The position and size are important because my output will be
a 25-page report with 4 graphs per page.

- Link different sections of my data to different graphs.

- I have to run this process every couple of days with new data coming from
my acquisition system.

So, what I am looking for is a way to be able to talk independently to each
graph any time, like, changing the scale of graph #53. That is why I thought
having a chart array was an easy way to address each graph and also an easy
way to initialize all the graphs using a For...Next loop.

Thanks.

MD

"Peter T" wrote:

You could declare an array of charts but if it's only for the aid of
creating charts I can't see what purpose it serves, unless you need to go
back over them for some reason. It's not clear what you are doing but
whatever it is maybe the following VBA macro will give you some ideas. (Most
of the code is from another post, adapted to include the chart arrray).

Sub test()
Dim j As Long, k As Long, idx as long
Dim chtObj As ChartObject
Dim cht As Chart
Dim sr As Series
Dim arrCharts() As Chart

For k = 2 To 2 + (5 * 3) Step 5
For j = 2 To 2 + (10 * 4) Step 10
idx = idx + 1
Next
Next
ReDim arrCharts(1 To idx) As Chart

idx = 0

For k = 2 To 2 + (5 * 3) Step 5
For j = 2 To 2 + (10 * 4) Step 10
Set rng = Range(Cells(j, k), Cells(j + 7, k + 3))

With rng
Set chtObj = ActiveSheet.ChartObjects.Add(.Left, .Top,
..Width, .Height)
End With

Set cht = chtObj.Chart

' your own code to set source data here in the loop

Set sr = cht.SeriesCollection.NewSeries
sr.Values = "{1,2,3}"

idx = idx + 1
Set arrCharts(idx) = cht

Next
Next

For i = 1 To idx
Debug.Print arrCharts(i).Name
Next

End Sub

Regards,
Peter T

"MD" wrote in message
...
I am trying to program Excel 2007 with VB.NET in order to plot 100 graphs
in
one worksheet. I would like to know if there is any way to declare the
graphs
as an array, like:

Dim MyPlots(99) as Chart

If so, how can I focus in each individual graph and add it to the
worksheet ?

Thanks.

MD

Actually, you could use another sheet to store settings for the charts. Each
row has data for a particular chart, You could include properties like name,
size and position of the chart object; chart title and axis titles; axis
scale parameters; source data (column number or column header text). Then
using an array makes sense, and since you would have to put it into a loop
anyway, you may as well loop through this table of chart settings.

Another option: could you create a template file containing your data sheets
and all the charts? All you would have to do is create a new file based on
this template, then change the data to update the new file's charts.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"MD" wrote in message
...
Peter:

I'd like to elaborate a little more on my application; I have a data file
that is generated in another application (linked to an electronics
acquisition system). I read the file with VB.NET and organize the data in
a
format that Excel recognizes. I am trying to use Excel mainly as a
plotting
tool. I copy my formatted data in 2 or 3 worksheets of a workbook. Then I
add
one worksheet only to plot on it the 100 independent graphs. I do not want
to
have separate worksheets per graph, because I need them side by side for
analysis and comparison. I need to be able to:

- Initialize each graph with its own title, size and relative position in
the worksheet. The position and size are important because my output will
be
a 25-page report with 4 graphs per page.

- Link different sections of my data to different graphs.

- I have to run this process every couple of days with new data coming
from
my acquisition system.

So, what I am looking for is a way to be able to talk independently to
each
graph any time, like, changing the scale of graph #53. That is why I
thought
having a chart array was an easy way to address each graph and also an
easy
way to initialize all the graphs using a For...Next loop.

Thanks.

MD

"Peter T" wrote:

You could declare an array of charts but if it's only for the aid of
creating charts I can't see what purpose it serves, unless you need to go
back over them for some reason. It's not clear what you are doing but
whatever it is maybe the following VBA macro will give you some ideas.
(Most
of the code is from another post, adapted to include the chart arrray).

Sub test()
Dim j As Long, k As Long, idx as long
Dim chtObj As ChartObject
Dim cht As Chart
Dim sr As Series
Dim arrCharts() As Chart

For k = 2 To 2 + (5 * 3) Step 5
For j = 2 To 2 + (10 * 4) Step 10
idx = idx + 1
Next
Next
ReDim arrCharts(1 To idx) As Chart

idx = 0

For k = 2 To 2 + (5 * 3) Step 5
For j = 2 To 2 + (10 * 4) Step 10
Set rng = Range(Cells(j, k), Cells(j + 7, k + 3))

With rng
Set chtObj = ActiveSheet.ChartObjects.Add(.Left, .Top,
..Width, .Height)
End With

Set cht = chtObj.Chart

' your own code to set source data here in the loop

Set sr = cht.SeriesCollection.NewSeries
sr.Values = "{1,2,3}"

idx = idx + 1
Set arrCharts(idx) = cht

Next
Next

For i = 1 To idx
Debug.Print arrCharts(i).Name
Next

End Sub

Regards,
Peter T

"MD" wrote in message
...
I am trying to program Excel 2007 with VB.NET in order to plot 100
graphs
in
one worksheet. I would like to know if there is any way to declare the
graphs
as an array, like:

Dim MyPlots(99) as Chart

If so, how can I focus in each individual graph and add it to the
worksheet ?

Thanks.

MD

If you tried the little demo I posted I hope that may already have answered
some of your questions. You could, for example, arrange something like this
(air code)

Sub Main()
dim arrChrts() as Excel.Chart
dim Qty

' work out some prelim arg's & qty

Call MakeEm(arrChrts() as Excel.Chart, Qty, arg1)

Call SourceEm(arrChrts(), arg1)
Call TweakEm(arrChrts(), arg1)
Call NamesInCells(arrChrts())

etc
End sub
'

Sub MakeEm(arr() as Excel.Chart, qty&, arg1)

redim arr(1 to qty) as excel.chart
' code to make basic charts
End Sub

Sub NamesInCells(arr() as excel.chart, bGet, optional ws as Worksheet)

if bGet then
qty = some-stored-value
redim arr() 1 to qty
else
for i = 1 to ubound(arr)
end if

for i = 1 to qty
if bGet then

set arr(i) = ws.chartobjects(someWs.Cells(i,1)).Chart
' validate the chart, ie check it still exists
else
someWs.Cells(i,1) = arr(i).Parent.Name
end if
next

End Sub


Notice the NamesInCells routine, one way to store details of your charts so
you can repopulate the array in your session two days later.

Regards,
Peter T



"MD" wrote in message
...
Peter:

I'd like to elaborate a little more on my application; I have a data file
that is generated in another application (linked to an electronics
acquisition system). I read the file with VB.NET and organize the data in
a
format that Excel recognizes. I am trying to use Excel mainly as a
plotting
tool. I copy my formatted data in 2 or 3 worksheets of a workbook. Then I
add
one worksheet only to plot on it the 100 independent graphs. I do not want
to
have separate worksheets per graph, because I need them side by side for
analysis and comparison. I need to be able to:

- Initialize each graph with its own title, size and relative position in
the worksheet. The position and size are important because my output will
be
a 25-page report with 4 graphs per page.

- Link different sections of my data to different graphs.

- I have to run this process every couple of days with new data coming
from
my acquisition system.

So, what I am looking for is a way to be able to talk independently to
each
graph any time, like, changing the scale of graph #53. That is why I
thought
having a chart array was an easy way to address each graph and also an
easy
way to initialize all the graphs using a For...Next loop.

Thanks.

MD

"Peter T" wrote:

You could declare an array of charts but if it's only for the aid of
creating charts I can't see what purpose it serves, unless you need to
go
back over them for some reason. It's not clear what you are doing but
whatever it is maybe the following VBA macro will give you some ideas.
(Most
of the code is from another post, adapted to include the chart arrray).

Sub test()
Dim j As Long, k As Long, idx as long
Dim chtObj As ChartObject
Dim cht As Chart
Dim sr As Series
Dim arrCharts() As Chart

For k = 2 To 2 + (5 * 3) Step 5
For j = 2 To 2 + (10 * 4) Step 10
idx = idx + 1
Next
Next
ReDim arrCharts(1 To idx) As Chart

idx = 0

For k = 2 To 2 + (5 * 3) Step 5
For j = 2 To 2 + (10 * 4) Step 10
Set rng = Range(Cells(j, k), Cells(j + 7, k + 3))

With rng
Set chtObj = ActiveSheet.ChartObjects.Add(.Left, .Top,
..Width, .Height)
End With

Set cht = chtObj.Chart

' your own code to set source data here in the loop

Set sr = cht.SeriesCollection.NewSeries
sr.Values = "{1,2,3}"

idx = idx + 1
Set arrCharts(idx) = cht

Next
Next

For i = 1 To idx
Debug.Print arrCharts(i).Name
Next

End Sub

Regards,
Peter T

"MD" wrote in message
...
I am trying to program Excel 2007 with VB.NET in order to plot 100
graphs
in
one worksheet. I would like to know if there is any way to declare the
graphs
as an array, like:

Dim MyPlots(99) as Chart

If so, how can I focus in each individual graph and add it to the
worksheet ?

Thanks.

MD

Jon has explained pretty much what I was getting at, as regards to storing
and retrieving chart details, very much more clearly !

Regards,
Peter T

<snip