Re: shape range help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
news:u2QbdoJ2GHA.1304@xxxxxxxxxxxxxxxxxxxxxxx
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" <k24@xxxxxxxxxxx> wrote in message
news:eBioE6A2GHA.1304@xxxxxxxxxxxxxxxxxxxxxxx
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>
¼¶¼g©ó¶l¥ó·s»D:uhA3atA2GHA.1288@xxxxxxxxxxxxxxxxxxxxxxx
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" <k24@xxxxxxxxxxx> wrote in message
news:#85BeYA2GHA.1256@xxxxxxxxxxxxxxxxxxxxxxx
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>
¼¶¼g©ó¶l¥ó·s»D:enLNFKA2GHA.4796@xxxxxxxxxxxxxxxxxxxxxxx
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" <k24@xxxxxxxxxxx> wrote in message
news:uI0WPw$1GHA.868@xxxxxxxxxxxxxxxxxxxxxxx
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

<semiopen@xxxxxxxxxxx>
???????:1158236770.147889.234690@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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:
<semiopen@xxxxxxxxxxx> wrote in message
news:1158233545.320167.51500@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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>















.

Relevant Pages

  • Re: Array Declaration Problem ??
    ... the declaration ReDim awas commented out in Function Zroots. ... Function ZrootsAs Variant() ... Dim j As Integer, its As Integer ... Enter on a worksheet the function "MyRoots() and select a 4 Row*2 Column ...
    (microsoft.public.excel.programming)
  • Re: Array Declaration Problem ??
    ... Function MyRoots (a As Variant, m As Integer, polish As String) ... Dim Roots As Variant ... ReDim ad ...
    (microsoft.public.excel.programming)
  • Re: Re:workdays
    ... Optional Holidays As Variant = Nothing, ... Dim arrayH As Variant ... ' or not an array or cell range with at least one numeric value between ... ReDim arrayH) As Variant ...
    (microsoft.public.excel)
  • Re: How do I Create ragged arrays in Excel VBA?
    ... Sub RaggedArray() ... Dim AAs Variant, i As Long, j As Long ... As Variant ... ReDim A ...
    (microsoft.public.excel.programming)
  • Re: shape range help
    ... Sub Test4() ... Dim Stooges As ShapeRange ... A is *exactly* a variant containing an array - in other words, ...
    (microsoft.public.excel.programming)