Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
Thank you for your suggestion. Unfortunately, now
Set shpRng = ActiveSheet.Shapes.Range(A) throws the dreaded run time error 1004 -semiopen Jim Cone wrote: 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 wrote in message Thank you for your suggestion. Unfortunately, now Set shpRng = ActiveSheet.Shapes.Range(A) throws the dreaded run time error 1004 -semiopen Jim Cone wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
Jim Cone wrote: 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 http://www.officeletter.com/blink/specialsort.html wrote in message Thank you for your suggestion. Unfortunately, now Set shpRng = ActiveSheet.Shapes.Range(A) throws the dreaded run time error 1004 -semiopen Jim Cone wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 wrote in message oups.com... Jim Cone wrote: 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 http://www.officeletter.com/blink/specialsort.html wrote in message Thank you for your suggestion. Unfortunately, now Set shpRng = ActiveSheet.Shapes.Range(A) throws the dreaded run time error 1004 -semiopen Jim Cone wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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. Jim Cone wrote: 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 wrote in message oups.com... Jim Cone wrote: 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 http://www.officeletter.com/blink/specialsort.html wrote in message Thank you for your suggestion. Unfortunately, now Set shpRng = ActiveSheet.Shapes.Range(A) throws the dreaded run time error 1004 -semiopen Jim Cone wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 wrote in message ps.com... 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. Jim Cone wrote: 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 wrote in message oups.com... Jim Cone wrote: 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 http://www.officeletter.com/blink/specialsort.html wrote in message Thank you for your suggestion. Unfortunately, now Set shpRng = ActiveSheet.Shapes.Range(A) throws the dreaded run time error 1004 -semiopen Jim Cone wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 "Peter T" <peter_t@discussions wrote in message ... 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 wrote in message ps.com... 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. Jim Cone wrote: 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 wrote in message oups.com... Jim Cone wrote: 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 http://www.officeletter.com/blink/specialsort.html wrote in message Thank you for your suggestion. Unfortunately, now Set shpRng = ActiveSheet.Shapes.Range(A) throws the dreaded run time error 1004 -semiopen Jim Cone wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
Peter T wrote: 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 wrote in message ps.com... 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. Jim Cone wrote: 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 wrote in message oups.com... Jim Cone wrote: 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 http://www.officeletter.com/blink/specialsort.html wrote in message Thank you for your suggestion. Unfortunately, now Set shpRng = ActiveSheet.Shapes.Range(A) throws the dreaded run time error 1004 -semiopen Jim Cone wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
wrote in message
ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 Peter T wrote: wrote in message ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 egroups.com... 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 Peter T wrote: wrote in message ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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" wrote in message ... 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 egroups.com... 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 Peter T wrote: wrote in message ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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" <peter_t@discussions bl... 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" wrote in message ... 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 egroups.com... 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 Peter T wrote: wrote in message ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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" wrote in message ... 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" <peter_t@discussions bl... 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" wrote in message ... 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 egroups.com... 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 Peter T wrote: wrote in message ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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" <peter_t@discussions bl... 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" wrote in message ... 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" <peter_t@discussions bl... 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" wrote in message ... 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 egroups.com... 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 Peter T wrote: wrote in message ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 "NickHK" wrote in message ... 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" <peter_t@discussions bl... 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" wrote in message ... 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" <peter_t@discussions bl... 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" wrote in message ... 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 egroups.com... 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 Peter T wrote: wrote in message ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
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 wrote in message oups.com... 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 wrote: 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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
shape range help
Peter,
I'm not familiar enough with the Shape.Range/ShapeRange collections etc (as from our previous thread on these various collection) to be sure what is expected as input. Hence, it's difficult to know the effects of these various combinations we've discussed. Next situation I come across involving something similar I will pay more attention to these aspects and see if I can fathom anything further. NickHK "Peter T" <peter_t@discussions wrote in message ... 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 "NickHK" wrote in message ... 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" <peter_t@discussions bl... 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" wrote in message ... 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" <peter_t@discussions bl... 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" wrote in message ... 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 egroups.com... 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 Peter T wrote: wrote in message ups.com... Peter T wrote: 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 Our recent posts sent at about the same time obviously crossed, but note the difference between Variant() vs Variant/Variant() Regards, Peter T <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shape Range | Charts and Charting in Excel | |||
my curser changed from arrow shape to a cross shape???? | New Users to Excel | |||
Possible to select a range of cells from a Shape Object | Excel Programming | |||
Detecting if a range contains a shape | Excel Programming | |||
align a picture/shape in a Range! | Excel Programming |