Excal, Variant and Range

[witek]

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.

 

[Chip Pearson]

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]

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.

 

[Peter T]

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

 

[witek]

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

 

[witek]

No, it doesn't; it gives you a 2-D array.

Assuming rng refers to a range, unless rng refers to a single cell, v =
rng.Value will give you a 2-D 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.

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.

 

[Alan Beban]

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