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 want a collection
'***************************
dim colCharts as collection
cim cht as chart
set colCharts = new collection

colCharts.add MyChart, Mychart.name

for each cht in colCharts
msgbox cht.name
next cht
'***************************
--
HTH...

Jim Thomlinson


"MD" wrote:

> 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" <(E-Mail Removed)> wrote in message
news:EF6E3D95-C540-4009-A4F5-(E-Mail Removed)...
> 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

There is already a collection of chartobjects in the worksheet, or a
collection of charts (chart sheets) in the workbook. Defining a collection
of charts seems redundant. An array of charts also seems redundant, but the
order chart objects are enumerated depends on their Z stacking order, not
the order they were created. As Peter said, unless you have to go back and
reference each chart later, it doesn't seem like you'd need an array of
charts, just loop through and create each as needed.

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


"Jim Thomlinson" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:33050389-9F34-4158-9981-(E-Mail Removed)...
> You want a collection
> '***************************
> dim colCharts as collection
> cim cht as chart
> set colCharts = new collection
>
> colCharts.add MyChart, Mychart.name
>
> for each cht in colCharts
> msgbox cht.name
> next cht
> '***************************
> --
> HTH...
>
> Jim Thomlinson
>
>
> "MD" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
> news:EF6E3D95-C540-4009-A4F5-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:30D41250-6E9A-4F19-B5A1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:EF6E3D95-C540-4009-A4F5-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
news:30D41250-6E9A-4F19-B5A1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:EF6E3D95-C540-4009-A4F5-(E-Mail Removed)...
> > > 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>