Excel Range to Array in VBA

G

Geoff

Is there a way though VBA in one command to put a range to an array.

E.g.

strArray()=ExcelRange.Value

This does not work for us but is it possible without looping through the
range.

Thanks in advance.
 
B

Bob Phillips

strArray= ExcelRange

creates a 2 dimensional array

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
A

Alan Beban

Geoff said:
Is there a way though VBA in one command to put a range to an array.

E.g.

strArray()=ExcelRange.Value

This does not work for us but is it possible without looping through the
range.

Thanks in advance.
Click to expand...
If it works for you to assign the range to a Variant variable, ala:

Dim strArray As Variant, rng As Range
Set rng = Range("b2:b4")
strArray = rng

And if the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, you can do
it with a true array, at least in xl2000 and later, ala:

Dim strArray() As Long, rng As Range
Set rng = Range("b2:b4")
Assign rng, strArray()

Although in this case, unlike in the case of the assignment to a Variant
variable, the array is indeed being loaded by looping, which is built in
to the Assign function.

Alan Beban
 
G

Geoff

thanks all

Alan Beban said:
If it works for you to assign the range to a Variant variable, ala:

Dim strArray As Variant, rng As Range
Set rng = Range("b2:b4")
strArray = rng

And if the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, you can do it
with a true array, at least in xl2000 and later, ala:

Dim strArray() As Long, rng As Range
Set rng = Range("b2:b4")
Assign rng, strArray()

Although in this case, unlike in the case of the assignment to a Variant
variable, the array is indeed being loaded by looping, which is built in
to the Assign function.

Alan Beban
Click to expand...
 
Top