I'm going nuts (again).

At the end of a routine I'm trying to select a group of embedded
charts.
According to the online help in excel 2007, the following ought to
work:

ReDim chtnames(1 to 2)
set the two chtnames
ActiveSheet.Shapes.Range(chtnames).Select

In fact, the above did work for me when I first put it in. But now...
The above generates an "the index into the specified collection is out
of bounds" run time error.

However, the following will work in the immediate window where the
above fails.
activesheet.shapes.range(array(chtnames(1),chtname s(2))).select

What am I missing?

Thanks,

Brian

This is untested...

You ReDim'med chtnames with a lower bound of 1... do me a favor and execute
this statement in the Immediate Window...

? LBound(Array(chtnames(1),chtnames(2)))

Does this print 0 or 1 on your system? If it prints 0, then try changing
your ReDim statement to this...

ReDim chtname(0 To 1)

and then set chtname(0) and chtname(1) instead of what you set originally
and then follow that up with your last line...

ActiveSheet.Shapes.Range(chtnames).Select

--
Rick (MVP - Excel)


"Brian Murphy" wrote in message
...
I'm going nuts (again).

At the end of a routine I'm trying to select a group of embedded
charts.
According to the online help in excel 2007, the following ought to
work:

ReDim chtnames(1 to 2)
set the two chtnames
ActiveSheet.Shapes.Range(chtnames).Select

In fact, the above did work for me when I first put it in. But now...
The above generates an "the index into the specified collection is out
of bounds" run time error.

However, the following will work in the immediate window where the
above fails.
activesheet.shapes.range(array(chtnames(1),chtname s(2))).select

What am I missing?

Thanks,

Brian

Rick, well spotted. LBound 0/1 would appear to be the only difference
between the two arrays. However both LBound 0 and 1 arrays should work just
fine in this context, at least always has done for me AFAIK.

Brian, any difference if you change
ReDim chtnames(1 to 2)
to
ReDim chtnames(0 to 2 - 1)
' etc

It shouldn't make any difference who knows!

Regards,
Peter T

"Rick Rothstein" wrote in message
...
This is untested...

You ReDim'med chtnames with a lower bound of 1... do me a favor and
execute this statement in the Immediate Window...

? LBound(Array(chtnames(1),chtnames(2)))

Does this print 0 or 1 on your system? If it prints 0, then try changing
your ReDim statement to this...

ReDim chtname(0 To 1)

and then set chtname(0) and chtname(1) instead of what you set originally
and then follow that up with your last line...

ActiveSheet.Shapes.Range(chtnames).Select

--
Rick (MVP - Excel)


"Brian Murphy" wrote in message
...
I'm going nuts (again).

At the end of a routine I'm trying to select a group of embedded
charts.
According to the online help in excel 2007, the following ought to
work:

ReDim chtnames(1 to 2)
set the two chtnames
ActiveSheet.Shapes.Range(chtnames).Select

In fact, the above did work for me when I first put it in. But now...
The above generates an "the index into the specified collection is out
of bounds" run time error.

However, the following will work in the immediate window where the
above fails.
activesheet.shapes.range(array(chtnames(1),chtname s(2))).select

What am I missing?

Thanks,

Brian

To clarify, I am selecting a subset of charts on a worksheet.

This is very old code I'm trying to get to work with excel 2007.
Originally it used a base of 0.
When that wouldn't work I tried 1.
The example in the help file specifically uses 1.

I think I'm getting bit by the .Name problems you mentioned.
I've certainly seen this problem in other forms.
Later tonight I will try the loop to get the zOrder values.
How would using Activesheet.Chartobjects compare to using .Shapes?

If you want to have some fun with .Names, try this:
Get a worksheet with a bunch of embedded charts.
Make sure they have unique .Names otherwise you'll have trouble
selecting a subset of charts.
Select some of them and run this code:

for each obj in Selection
debug.print obj.name
next
for i=1 to Selection.count
set obj = Selection(i)
debug.print obj.name
next i

What happens to me is the first loop works as expected.
The second loop starts at the front of the zOrder, I think.

Thanks,

Brian

To clarify, I am selecting a subset of charts on a worksheet.

Already understood, though it wouldn't make any difference if embedded on
chart-sheets

This is very old code I'm trying to get to work with excel 2007.
Originally it used a base of 0.
When that wouldn't work I tried 1.
The example in the help file specifically uses 1.

As I mentioned both 0 and 1 base should work, yet 0/1 base appears to be the
only difference in what worked/failed for you.

I think I'm getting bit by the .Name problems you mentioned.
I've certainly seen this problem in other forms.
Later tonight I will try the loop to get the zOrder values.
How would using Activesheet.Chartobjects compare to using .Shapes?

ZOrder and shape.ZOrderPosition are not equivalent. Here's the ZOrder at the
drawingobjects level alternative

with activesheet.Chartobjects
arrX(1) = .Item(2).ZOrder
arrX(2) = .Item(4).ZOrder
end with
Activesheet.DRAWINGOBJECTS(arrX).Select

But with this ironically you need to be careful in earlier versions if any
Comments exist on the sheet. Their order positions are included in the mix,
unlike in 2007 where they are always last.

Another thing, in earlier versions you can do this -

ActiveSheet.ChartObjects(Array(1, 3)).Select
where 1 & 3 are the 1st & 3rd items in the chartobjects collection, but not
in 2007.


If you want to have some fun with .Names, try this:
Get a worksheet with a bunch of embedded charts.
Make sure they have unique .Names otherwise you'll have trouble
selecting a subset of charts.
Select some of them and run this code:

for each obj in Selection
debug.print obj.name
next
for i=1 to Selection.count
set obj = Selection(i)
debug.print obj.name
next i

What happens to me is the first loop works as expected.
The second loop starts at the front of the zOrder, I think.

Both work for me in the same way, but not as expected. Namely, 'Selection'
returns *all* the chartobjects even if only some are physically selected.

Part of the problem is the new chartobject is radically different to the old
chartobject.

Regards,
Peter T

Ok, here we go.

I put in the zOrderPosition stuff and it seems to work just right.

I ran into another quirk, though. Sometimes after the selecting the
shape range, not all of the charts will "look" selected, even though
they definitely are all selected (verified by hitting the Delete key
to see what is deleted). I think it might have always been the first
one in the group (don't recall which). Not life threatening, but very
annoying. After a couple of hours of fighting that battle I found
that excel is "stuck" in some goofy mode where selecting multiple
charts is working normally, but if I select the first data series on
the first chart of the group that breaks it out of this mode. So
doing this in code gets the entire selected chart group looking
selected.

Here's what I ended up with (Activechart is the last one in the group,
at this point it is "selected" but not "activated")
ActiveChart.ChartArea.Select ' doesn't work without this
line, strange but true
ActiveSheet.Shapes.Range(GetZorderArray(chtnames)
(1)).Select
ActiveChart.SeriesCollection(1).Select
ActiveSheet.Shapes.Range(GetZorderArray(chtnames)) .Select

Function GetZorderArray(objnames) As Variant 'created 10/15/2009
ReDim arrx(1 To UBound(objnames) - LBound(objnames) + 1) As Long
Dim i%, shp As Shape
For i = LBound(objnames) To UBound(objnames)
For Each shp In ActiveSheet.Shapes
If shp.Type = msoChart And shp.Name = objnames(i) Then
arrx(i) = shp.ZOrderPosition
Exit For
End If
Next shp
Next i
GetZorderArray = arrx
End Function

Excel 2007 is really something else!

Brian

What am I missing?

Merely the fact that there's much wrong shapes and charts in 2007 as far VBA
is concerned.

That said I don't see why array(chtnames(1),chtnames(2)) works yet chtnames
fails. Yet, I think I've also had that error description before but can't
recreate at the moment.

I assume your array is chartObject string names. In all versions "name" can
be unreliable if duplicate named objects exist (shouldn't be possible but it
is), but in 2007 there seem to be even more problems using names.

ReDim arrS(1 To ActiveSheet.ChartObjects.Count) As String
ReDim arrX(1 To ActiveSheet.ChartObjects.Count) As Long

For Each shp In ActiveSheet.Shapes
If shp.Type = msoChart Then
i = i + 1
arrS(i) = shp.Name
arrX(i) = shp.ZOrderPosition
End If
Next
ActiveSheet.Shapes.Range(arrX).Select

arrS should normally work but arrX is more reliable

For your purposes adapt not to select all but as required, not sure though
it'll fix your odd problem.

Of course in earlier versions to select all charts, simply
Activesheet.Chartobjects.Select
but that doesn't work in 2007 (though other drawingobjects do work, eg
..Rectables.Select).

Regards,
Peter T


"Brian Murphy" wrote in message
...
I'm going nuts (again).

At the end of a routine I'm trying to select a group of embedded
charts.
According to the online help in excel 2007, the following ought to
work:

ReDim chtnames(1 to 2)
set the two chtnames
ActiveSheet.Shapes.Range(chtnames).Select

In fact, the above did work for me when I first put it in. But now...
The above generates an "the index into the specified collection is out
of bounds" run time error.

However, the following will work in the immediate window where the
above fails.
activesheet.shapes.range(array(chtnames(1),chtname s(2))).select

What am I missing?

Thanks,

Brian