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

A

Alan

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.
 
M

michdenis

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" <[email protected]> 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.
 
D

Dave Peterson

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.
 
A

Alan

michdenis said:
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!
Click to expand...

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.
 
A

Alan

Dave Peterson said:
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.
Click to expand...

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.
 
A

Alan

Alan said:
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.
Click to expand...

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.
 
A

Alan

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.
 
D

Dave Peterson

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
 
Top