Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Function to Create Array of Size n with values x Through | Excel Discussion (Misc queries) | |||
Array Pasted from One Worksheet Converts Null Values to Zeros | Excel Discussion (Misc queries) | |||
efficiently copy values from a Range of cells to an array (in VB.N | Excel Programming | |||
efficiently copy values from a Range of cells to an array (in VB.N | Excel Programming | |||
Create Array From Values in range | Excel Programming |