shape range help

[semiopen]

Greetings,
I just upgraded to Excel 2003 a few weeks ago - and the IT
department left out some VBA help files! It is going to be a couple of
days before I have time to wander over there, and in the mean time I'm
discovering that few books on Excel Programming say much about the
drawing layer. So...

I have a fairly large group of shapes grouped into a shape named
"network" , which consists of nodes, edges and costs on the edges. I
want to be able to change the cost displayed on certain edges (the
costs themselves are in text boxes). I gather from google that you have
to ungroup - change - regroup. I can ungroup and change no problem - it
is the regroup that is throwing me. Based on some code snippets I saw
on this group, I would think that the following should work, but it
throws an error:

Sub changeCost(costName As String, newCost As Long)

Dim shpRng As ShapeRange, network As Shape
Dim A As Variant
Dim i As Long, n As Long
Set network = Shapes("network")
n = network.GroupItems.Count
ReDim A(0 To n - 1)
For i = 1 To n
A(i - 1) = network.GroupItems(i).Name
Next i
network.Ungroup
Shapes(costName).TextFrame.Characters.Text = newCost

Set shpRng = Shapes.Range(A)
shpRng.Group
shpRng.Name = "network"

End Sub
+++++++++++++++++++

The line which causes problems is: Set shpRng = Shapes.Range(A)

What bozo-like programming error am I making? Any help would be
appreciated. Is there some sort of way to directly transfer the
groupitems to a shaperange?

Thanks for reading this

-semiopen

 

[Jim Cone]

Try changing...
Set network = Shapes("network")
Set shpRng = Shapes.Range(A)
shpRng.Group

-to-
Set network = ActiveSheet.Shapes("network")
Set shpRng = ActiveSheet.Shapes.Range(A)
shpRng.ReGroup
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


<[email protected]>
wrote in message
Greetings,
I just upgraded to Excel 2003 a few weeks ago - and the IT
department left out some VBA help files! It is going to be a couple of
days before I have time to wander over there, and in the mean time I'm
discovering that few books on Excel Programming say much about the
drawing layer. So...
I have a fairly large group of shapes grouped into a shape named
"network" , which consists of nodes, edges and costs on the edges. I
want to be able to change the cost displayed on certain edges (the
costs themselves are in text boxes). I gather from google that you have
to ungroup - change - regroup. I can ungroup and change no problem - it
is the regroup that is throwing me. Based on some code snippets I saw
on this group, I would think that the following should work, but it
throws an error:

Sub changeCost(costName As String, newCost As Long)
Dim shpRng As ShapeRange, network As Shape
Dim A As Variant
Dim i As Long, n As Long
Set network = Shapes("network")
n = network.GroupItems.Count
ReDim A(0 To n - 1)
For i = 1 To n
A(i - 1) = network.GroupItems(i).Name
Next i
network.Ungroup
Shapes(costName).TextFrame.Characters.Text = newCost

Set shpRng = Shapes.Range(A)
shpRng.Group
shpRng.Name = "network"

End Sub
+++++++++++++++++++

The line which causes problems is: Set shpRng = Shapes.Range(A)
What bozo-like programming error am I making? Any help would be
appreciated. Is there some sort of way to directly transfer the
groupitems to a shaperange?
Thanks for reading this
-semiopen

 

[semiopen]

Thank you for your suggestion. Unfortunately, now

Set shpRng = ActiveSheet.Shapes.Range(A)

throws the dreaded run time error 1004

-semiopen

 

[Jim Cone]

Are you sure that network is still a group?
Did you add "ActiveSheet." in front of ...
Shapes(costName).TextFrame.Characters.Text = newCost

Jim Cone
http://www.officeletter.com/blink/specialsort.html


<[email protected]>
wrote in message
Thank you for your suggestion. Unfortunately, now
Set shpRng = ActiveSheet.Shapes.Range(A)

throws the dreaded run time error 1004
-semiopen

 

[semiopen]

Are you sure that network is still a group?
Did you add "ActiveSheet." in front of ...
Shapes(costName).TextFrame.Characters.Text = newCost

I didn't add the activesheet to *that* line - but that doesn't matter
since it is an implied parent object. After my sub dies - when I look
at Sheet1 (where this resides) - I see a whole bunch of ungrouped
shapes *with the cost changed to reflect the new cost*. When I step
through a test run with F8 - it is definitely the line I indicated that
Excel is balking at. Wierd.

 

[Jim Cone]

I have never heard of an "implied parent object", but if Excel is
accepting "Shapes" without specifying the sheet that it belongs
to, I certainly want to learn more.

To fix your problem, make the variant "A" a real array...
Dim A() as Variant.

Jim Cone
San Francisco, USA

Are you sure that network is still a group?
Did you add "ActiveSheet." in front of ...
Shapes(costName).TextFrame.Characters.Text = newCost

I didn't add the activesheet to *that* line - but that doesn't matter
since it is an implied parent object. After my sub dies - when I look
at Sheet1 (where this resides) - I see a whole bunch of ungrouped
shapes *with the cost changed to reflect the new cost*. When I step
through a test run with F8 - it is definitely the line I indicated that
Excel is balking at. Wierd.

 

[semiopen]

The following actually worked:
replace:
Set shpRng = ActiveSheet.Shapes.Range(A)
by:
Set shpRng = ActiveSheet.Shapes.Range((A))

I haven't the foggiest idea why the extra parenthesis makes a
difference - but it does

I don't know if it would make much of a difference if I made A an array
of variants. The code snippets that I saw on the web were of the form
Shapes.Range(Array("rectangle1","triangle 2")) and I thought that Array
returned a variant array rather than an array of variants (I seem to
recall that there was some subtle difference)

As far as "implied parent object" goes - that might not be the right
word for it, but it is the sort of thing you do whenever you type
sheets(1) rather than workbooks(1).sheets(1). Maybe its not good
programming practice (perhaps relying on undocumented behavior) when
dealing with shapes - but all of my other subs worked fine without the
activesheet or sheets(1) qualification.

Thanks again for your help.

 

[Peter T]

If you declare -

Dim A() As Variant iso Dim A As Variant

I think you'll find you don't need to evaluate the array by enclosing in
brackets. see the difference in Locals

Most objects other than Range require an explicit reference to the sheet
(which in turn will be implied to the active workbook unless otherwise
explicitly referenced).

Regards,
Peter T

 

[Peter T]

I didn't fully relate to your code in the original post, try

Dim A() as variant
ReDim A(0 To n - 1)
or
ReDim A(0 To n - 1) as variant

in locals under Type it should appear as

Variant(0 to n-1)
and not
Variant/Variant(0 to n-1)

Regards,
Peter T

If you declare -

Dim A() As Variant iso Dim A As Variant

I think you'll find you don't need to evaluate the array by enclosing in
brackets. see the difference in Locals

Most objects other than Range require an explicit reference to the sheet
(which in turn will be implied to the active workbook unless otherwise
explicitly referenced).

Regards,
Peter T

 

[semiopen]

If you declare -

Dim A() As Variant iso Dim A As Variant

I think you'll find you don't need to evaluate the array by enclosing in
brackets. see the difference in Locals

You (and Jim) are right in that context. To play around with things, I
created a new spreadsheet with three rectangles named Larry, Curly and
Moe (I like giving my shapes interesting names - how would *you* like
to be refered to as Human 12 ?) and wrote the following 3 subs:

Sub Test1()
Dim Stooges As ShapeRange
Dim A() As Variant 'an array of variants
A = Array("Larry", "Curly", "Moe")
Set Stooges = Sheets(1).Shapes.Range(A) 'works fine
Stooges.Group
End Sub

Sub Test2()
Dim Stooges As ShapeRange
Dim A As Variant 'just a variant
A = Array("Larry", "Curly", "Moe")
Set Stooges = Sheets(1).Shapes.Range(A) 'throws an error!
Stooges.Group
End Sub

Sub Test3()
Dim Stooges As ShapeRange
Dim A As Variant 'just a variant
A = Array("Larry", "Curly", "Moe")
Set Stooges = Sheets(1).Shapes.Range((A)) 'the error is gone!
Stooges.Group
End Sub

It seems to be an outright bug, no? Why should A <> (A) ? If you
compare Test1 with Test3, maybe there is some sort of wierd Microsoft
conservation of parenthesis law that I was unaware of.

-semiopen

Most objects other than Range require an explicit reference to the sheet
(which in turn will be implied to the active workbook unless otherwise
explicitly referenced).

Regards,
Peter T

 

[Peter T]

You (and Jim) are right in that context. To play around with things, I
created a new spreadsheet with three rectangles named Larry, Curly and
Moe (I like giving my shapes interesting names - how would *you* like
to be refered to as Human 12 ?) and wrote the following 3 subs:

Sub Test1()
Dim Stooges As ShapeRange
Dim A() As Variant 'an array of variants
A = Array("Larry", "Curly", "Moe")
Set Stooges = Sheets(1).Shapes.Range(A) 'works fine
Stooges.Group
End Sub

Sub Test2()
Dim Stooges As ShapeRange
Dim A As Variant 'just a variant
A = Array("Larry", "Curly", "Moe")
Set Stooges = Sheets(1).Shapes.Range(A) 'throws an error!
Stooges.Group
End Sub

Sub Test3()
Dim Stooges As ShapeRange
Dim A As Variant 'just a variant
A = Array("Larry", "Curly", "Moe")
Set Stooges = Sheets(1).Shapes.Range((A)) 'the error is gone!
Stooges.Group
End Sub

It seems to be an outright bug, no? Why should A <> (A) ? If you
compare Test1 with Test3, maybe there is some sort of wierd Microsoft
conservation of parenthesis law that I was unaware of.

-semiopen

Our recent posts sent at about the same time obviously crossed, but note the
difference between

Variant() vs Variant/Variant()

Regards,
Peter T

<snip>

 

[semiopen]

The wierd thing is that, if I dispense with A altogether in my test
subs and write:

Sub Test4()
Dim Stooges As ShapeRange
Set Stooges = Sheets(1).Shapes.Range(Array("Larry", "Curly", "Moe"))
Stooges.Group
End Sub

It works fine. This is wierd since the documentation on VBA's Array
function explicitly says that it returns a variant containing an array
(as opposed to an array of variants)

When I have

Dim A as Varaint
A = Array("Larry", "Curly", "Moe")

A is *exactly* a variant containing an array - in other words, it
agrees with the return type of Array() - no implicit type coercion or
anything (at least none documented).
Thus, it really does seem to be a bug in Shapes.Range() when it accepts
a variant containing an array if fed directly in from the Array
function but balks at it if the variant containing an array is in a
declared variant variable (unless, mysteriously, enclosed in
parenthesis). But - with your (and Jim's) help - the work-around is
clear, so I won't worry about it. Excel is full of wierd quirks.

Thank you for your time

-semiopen

 

[NickHK]

Brackets do affect the the outcome of some statements.
I forget the exact details, but it something about forcing an evaluation of
the code in the brackets and effecting the ByRef/ByVal passing method.

Private Sub CommandButton1_Click()
Dim InputSample As String

InputSample = "Input"
Call ByRefEG(InputSample)
MsgBox InputSample

InputSample = "Input"
ByRefEG InputSample
MsgBox InputSample

InputSample = "Input"
ByRefEG (InputSample)
MsgBox InputSample

End Sub

Function ByRefEG(ByRef Sample As String) As String
Sample = "Output"
ByRefEG = Sample
End Function

I'm not sure if this has anything to do with your situation; looks like
Peter may be on to something with the nature of the Variant.

NickHK

 

[Peter T]

Hi Nick,

Brackets do affect the the outcome of some statements.
I forget the exact details, but it something about forcing an evaluation of
the code in the brackets and effecting the ByRef/ByVal passing method.

Indeed, passing a variable enclosed in brackets passes the evaluated value
of the variable and not a pointer, quite useful to avoid a variable
returning changed and yet allow use of ByRef.

AFAIK enclosing any expression in brackets evaluates a result, hence use of
brackets converted the Variant/Variant() to a useable Variant().

What I don't understand though is why sometimes I find if I first do -

Dim varr()
Redim varr(0 to 3)

it works as expected but strangely later in the same session I no longer
need to do the initial Dim varr(). IOW, if I don't do that first time I get
Variant/Variant()

but if I do that first time, then later omit, I still get the useable
Variant()

Regards,
Peter T

 

[NickHK]

Peter,
In your your example the "Dim varr()" is not required at all, as the ReDim
effective includes the initial Dim and the resize.
If you check the Locals for
Dim VarArray() As Variant
Dim Var As Variant
You see
Variant()
Variant/Empty

However, if comment out the Dims and rely on the Redims only, you see
Variant()
Variant()
Presumably because if you starting from a ReDim, then an array of variant
must be created.

Not sure where this is leading, but it seems applicable in a fashion...

NickHK

 

[Peter T]

In your your example the "Dim varr()" is not

required at all, as the ReDim
effective includes the initial Dim and the resize.

I am inconsistently getting different results. As I mentioned previously if
never in a session I don't do the initial Dim varr() then redim does NOT
include the initial Dim the way you say, I get a Variant/Variant(). But if I
later comment out the initial Dim varr() next time the redim works as you
say.

I'm testing in xl2000 at the moment, I recall there are some differences
between xl97 & later versions with variant arrays & ReDim requiring / not
requiring the initial dim () .

Not sure where this is leading...

Me neither <g>

Regards,
Peter

 

[NickHK]

Peter,
I can't repeat you experience. Using excel 2K also.
Everytime with ReDim only, I get a Variant()
I noticed some seeming unexpected behaviour with Variant/Variant() etc that
at the time I put down to my coding, but now you have metioned this, I will
pay more attention to these situations in the future.

NickHK

 

[Peter T]

Actually I'm getting a bit confused myself, especially with my inconsistent
results. I'm sure I've got lots of code with only the ReDim varr() that
works just fine.

However I had replicated the both the OP's error and his fix with use of
brackets. However initial use of Dim() avoided use of brackets and he
reported same worked for him. The difference being Variant/Variant() and
Variant().

Regards,
Peter T

 

[semiopen]

Well,

My original post was bone-headed in two ways:

1) My help files were there after all - just compressed or something
and I didn't need the CD to intall them This allowed me to realize
that ...

2) I should have been focusing on the Regroup method:

Sub changeCost(costName As String, newCost As Long)

Dim network As Shape
Dim i As Long, n As Long
Set network = ActiveSheet.Shapes("network")
network.Ungroup
ActiveSheet.Shapes(costName).TextFrame.Characters.Text = newCost
Set network = ActiveSheet.Shapes.Range(costName).Regroup
network.Name = "network"

End Sub

which works fine

The Regroup method saves me from the hassle of keeping track of the
original shapes.
Still, I'm sure that I'll sooner or later use the ( ) device to force
an evaluation that Nick and Peter discussed, so I'm glad I did it the
hard way first.

-semiopen

 

[NickHK]

Well, yes, there that easy way of doing it....
Not sure how applicable all that variant and brackets stuff was, but at
least you are aware that things may not be as they seem sometimes.

NickHK