Array of Values from Worksheet Range - What does it 'look' like?
 

Hi All,

Can anyone explain why these are not equal:

RangeArray = Array(Range("Names").Value)

MyArray = Array("Alan", "Bob", "Charles")


The worksheet range name 'Names' contains three cells (A1, A2, A3)
with "Alan", "Bob", and "Charles" in respectively.


I am thinking that perhaps the first expression is a single element
array, that contains a (sub)array with three elements, whereas the
second is an array of three elements. I am getting this from
examining the locals window, but I admit I don't fully understand what
I am seeing there.


More specifically, how do I change the first expression to evaluate
exactly equal to the second?

Thanks,

Alan.

Array of Values from Worksheet Range - What does it 'look' like?
 

Hi alan,

This way,


dim rangearray as variant
dim MyArray as variant

rangearray = Range("Names")
MyArray = Array("Alan", "Bob", "Charles")

Both ways gave the same result.


Salutations!





"Alan" a écrit dans le message de ...

Hi All,

Can anyone explain why these are not equal:

RangeArray = Array(Range("Names").Value)

MyArray = Array("Alan", "Bob", "Charles")


The worksheet range name 'Names' contains three cells (A1, A2, A3)
with "Alan", "Bob", and "Charles" in respectively.


I am thinking that perhaps the first expression is a single element
array, that contains a (sub)array with three elements, whereas the
second is an array of three elements. I am getting this from
examining the locals window, but I admit I don't fully understand what
I am seeing there.


More specifically, how do I change the first expression to evaluate
exactly equal to the second?

Thanks,

Alan.

Array of Values from Worksheet Range - What does it 'look' like?
 

If you did this:

dim RangeArray as variant
rangearray = range("names").value
You'd end up with a 3 row by 1 column array (2 dimensions).

But this:
dim RangeArray as variant
RangeArray = Array(Range("Names").Value)
is like embedding an array into an array.
Legal, but different.

On the other hand:
dim myArray as variant
MyArray = Array("Alan", "Bob", "Charles")

Will end up as a nice single dimension array.

Alan wrote:

Hi All,

Can anyone explain why these are not equal:

RangeArray = Array(Range("Names").Value)

MyArray = Array("Alan", "Bob", "Charles")

The worksheet range name 'Names' contains three cells (A1, A2, A3)
with "Alan", "Bob", and "Charles" in respectively.

I am thinking that perhaps the first expression is a single element
array, that contains a (sub)array with three elements, whereas the
second is an array of three elements. I am getting this from
examining the locals window, but I admit I don't fully understand what
I am seeing there.

More specifically, how do I change the first expression to evaluate
exactly equal to the second?

Thanks,

Alan.

--

Dave Peterson

Array of Values from Worksheet Range - What does it 'look' like?
 

"michdenis" wrote in message
...

Hi alan,

This way,


dim rangearray as variant
dim MyArray as variant

rangearray = Range("Names")
MyArray = Array("Alan", "Bob", "Charles")

Both ways gave the same result.


Salutations!


Hi Michdenis,

I don't seem to get that result.

In my locals window, it shows that RANGEARRAY is a (1 to 3 , 1 to 1)
two dimensional array

Whereas MYARRAY is a (0 to 2) one dimensional array.

This seems to tie into Dave Peterson's answer just after yours:

...


However, Dave does not (appear?) to tell me how to return a (0 to 2)
one dimensional array using the worksheet range name.

Thanks for your help!

Alan.

Array of Values from Worksheet Range - What does it 'look' like?
 

"Dave Peterson" wrote in message
...

If you did this:

dim RangeArray as variant
rangearray = range("names").value
You'd end up with a 3 row by 1 column array (2 dimensions).

But this:
dim RangeArray as variant
RangeArray = Array(Range("Names").Value)
is like embedding an array into an array.
Legal, but different.

On the other hand:
dim myArray as variant
MyArray = Array("Alan", "Bob", "Charles")

Will end up as a nice single dimension array.


Hi Dave,

Thank you for your reply - I am understanding better now.

However, I still cannot seem to figure out how to return a (0 to 2)
one dimensional array using the worksheet range as opposed to
hard coding it.

If you did explain that above, and I am not understanding, please
accept my apologies!

Thanks,

Alan.

Array of Values from Worksheet Range - What does it 'look' like?
 

"Alan" wrote in message
...

Hi All,

Can anyone explain why these are not equal:

RangeArray = Array(Range("Names").Value)

MyArray = Array("Alan", "Bob", "Charles")


The worksheet range name 'Names' contains three cells (A1, A2, A3)
with "Alan", "Bob", and "Charles" in respectively.


I am thinking that perhaps the first expression is a single element
array, that contains a (sub)array with three elements, whereas the
second is an array of three elements. I am getting this from
examining the locals window, but I admit I don't fully understand
what I am seeing there.


More specifically, how do I change the first expression to evaluate
exactly equal to the second?

Thanks,

Alan.


I also just tried this:

Sub test()

Dim NameArray(0 To 2) As Variant
Dim Myarray As Variant


NameArray2D = Range("Names")

For Counter = 0 To 2

NameArray(Counter) = NameArray2D(Counter + 1, 1)

Next Counter

Myarray = Array("Alan", "Bob", "Charles")

Check = (NameArray = Myarray)

End Sub


This won't compile, due to a type mismatch in the CHECK line, but if I
comment that out, and look in the locals window, the following
descriptions are shown:

NameArray2D Variant/Variant(1 to 3, 1 to 1)

NameArray: Variant(0 to 2)
MyArray Variant/Variant(0 to 2)

So, in some way, those two (NameArray and MyArray) are still different
beasts (as predicted by the compile error).



I need to create an array, in the code, that is equal to MyArray (as
shown above), but by referencing the worksheet range where those three
names are stored.

Any help is much appreciated!

Thanks,

Alan.

Array of Values from Worksheet Range - What does it 'look' like?
 

Getting closer.

This appears to generate identical arrays, except that the check still
claims a mismatch!

+-+-+-+-+-+-+-+-+

Option Base 1

Sub test()

NamesArray = Application.Transpose(Range("Names").Value)

Myarray = Array("Alan", "Bob", "Charles")

Check = (NamesArray = Myarray)

End Sub

+-+-+-+-+-+-+-+-+

In the locals window I see the following:

NamesArray: Variant/Variant(1 to 3)
MyArray: Variant/Variant(1 to 3)

In other words, they appear to be objects of the same type?

I have checked for typos in the strings and copied / pasted
across from the worksheet to the VBE to be sure it is not
just that, but the 'type mismatch' compile error seems to
imply it is more fundamental than that.

Driving me nuts!

Alan.

Array of Values from Worksheet Range - What does it 'look' like?
 

Application.transpose is the most common method--but in versions before xl2002,
you're limited to 5461 elements.

And you could inspect each element to see if they match:

Option Explicit
Option Base 1
Sub test()

Dim myArray As Variant
Dim NamesArray As Variant
Dim Check As Boolean
Dim iCtr As Long

NamesArray = Application.Transpose(Range("Names").Value)
myArray = Array("Alan", "Bob", "Charles")

Check = True
If UBound(NamesArray) = UBound(myArray) _
And LBound(NamesArray) = LBound(myArray) Then
For iCtr = LBound(NamesArray) To UBound(NamesArray)
If NamesArray(iCtr) = myArray(iCtr) Then
'do nothing
Else
Check = False
Exit For
End If
Next iCtr
End If
MsgBox Check

End Sub



Alan wrote:

Getting closer.

This appears to generate identical arrays, except that the check still
claims a mismatch!

+-+-+-+-+-+-+-+-+

Option Base 1

Sub test()

NamesArray = Application.Transpose(Range("Names").Value)

Myarray = Array("Alan", "Bob", "Charles")

Check = (NamesArray = Myarray)

End Sub

+-+-+-+-+-+-+-+-+

In the locals window I see the following:

NamesArray: Variant/Variant(1 to 3)
MyArray: Variant/Variant(1 to 3)

In other words, they appear to be objects of the same type?

I have checked for typos in the strings and copied / pasted
across from the worksheet to the VBE to be sure it is not
just that, but the 'type mismatch' compile error seems to
imply it is more fundamental than that.

Driving me nuts!

Alan.

--

Dave Peterson