Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excal, Variant and Range
Hi guys.
Here it is dim v as variant v = Range("a1").Value gives me ordinal type of variant v (double, string ... etc) v = Range("a1:a5").Value gives me 1-dimensional array v = Range ("a1:c1").Value gives me 2-dimensional array sub a (rng as range) v = rng.value gives me .....????? end sub questions 1. does anybody know how to always have 2-dimensional array in v= rng.value ? 2. Does anybody know how to find dimension of variant if I even don't know if it is an array 2. Does anybody know how to make 2-dim array from a number or 1-dim array? Thanks for help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excal, Variant and Range
The following function will return the number of dimensions in an array. It
will return 0 if the variable is not an array or is an unallocated array. Function NumBounds(V As Variant) As Long Dim N As Long Dim LB As Long On Error Resume Next Do Until Err.Number < 0 N = N + 1 LB = LBound(V, N) Loop NumBounds = N - 1 End Function -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "witek" wrote in message ... Hi guys. Here it is dim v as variant v = Range("a1").Value gives me ordinal type of variant v (double, string ... etc) v = Range("a1:a5").Value gives me 1-dimensional array v = Range ("a1:c1").Value gives me 2-dimensional array sub a (rng as range) v = rng.value gives me .....????? end sub questions 1. does anybody know how to always have 2-dimensional array in v= rng.value ? 2. Does anybody know how to find dimension of variant if I even don't know if it is an array 2. Does anybody know how to make 2-dim array from a number or 1-dim array? Thanks for help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excal, Variant and Range
Chip Pearson wrote:
The following function will return the number of dimensions in an array. It will return 0 if the variable is not an array or is an unallocated array. Function NumBounds(V As Variant) As Long Dim N As Long Dim LB As Long On Error Resume Next Do Until Err.Number < 0 N = N + 1 LB = LBound(V, N) Loop NumBounds = N - 1 End Function Yes I know. Thanks. I would prefer solution where rng.value is always 2-dim array or something like Make2D (rng.value) It can't be for x = 1 ... for y = 1 .... it is too slow. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excal, Variant and Range
I would prefer solution where rng.value is always 2-dim array
Providing rng.areas(1).count is at least 2 cells, rng.value is always a 2D array 1 to rng.areas(1).Rows.count, 1 to rng.areas(1).columns.count Regards, Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excal, Variant and Range
Peter T wrote:
I would prefer solution where rng.value is always 2-dim array Providing rng.areas(1).count is at least 2 cells, rng.value is always a 2D array 1 to rng.areas(1).Rows.count, 1 to rng.areas(1).columns.count Regards, Peter T Great! Thanks. That is what I need. I was sure that range was a single area so I did not check .Areas |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excal, Variant and Range
Alan Beban wrote:
witek wrote: Hi guys. Here it is dim v as variant v = Range("a1").Value gives me ordinal type of variant v (double, string ... etc) v = Range("a1:a5").Value gives me 1-dimensional array No, it doesn't; it gives you a 2-D array. v = Range ("a1:c1").Value gives me 2-dimensional array sub a (rng as range) v = rng.value gives me .....????? end sub questions 1. does anybody know how to always have 2-dimensional array in v= rng.value ? Assuming rng refers to a range, unless rng refers to a single cell, v = rng.Value will give you a 2-D array. 2. Does anybody know how to find dimension of variant if I even don't know if it is an array After v = rng.Value, assuming rng refers to a range, IsArray(v) will return True if rng refers to a multi-cell range, False if rng refers to a single-cell range. 2. Does anybody know how to make 2-dim array from a number Dim v() ReDim v(1 To 1, 1 To 1) v(1, 1) = Range("a1").Value or 1-dim array? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook v = TwoD(v) Alan Beban Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excal, Variant and Range
witek wrote:
Hi guys. Here it is dim v as variant v = Range("a1").Value gives me ordinal type of variant v (double, string ... etc) v = Range("a1:a5").Value gives me 1-dimensional array No, it doesn't; it gives you a 2-D array. v = Range ("a1:c1").Value gives me 2-dimensional array sub a (rng as range) v = rng.value gives me .....????? end sub questions 1. does anybody know how to always have 2-dimensional array in v= rng.value ? Assuming rng refers to a range, unless rng refers to a single cell, v = rng.Value will give you a 2-D array. 2. Does anybody know how to find dimension of variant if I even don't know if it is an array After v = rng.Value, assuming rng refers to a range, IsArray(v) will return True if rng refers to a multi-cell range, False if rng refers to a single-cell range. 2. Does anybody know how to make 2-dim array from a number Dim v() ReDim v(1 To 1, 1 To 1) v(1, 1) = Range("a1").Value or 1-dim array? If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook v = TwoD(v) Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
microsoft.excal 2009 | New Users to Excel | |||
range and variant | Excel Programming | |||
Disconnected Range to Variant | Excel Programming | |||
How do I assign range to variant and use | Excel Discussion (Misc queries) | |||
Excal Concatination of cells that are not empty | Excel Worksheet Functions |