Excel Range to Array in VBA

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.

strArray= ExcelRange

creates a 2 dimensional array

--

HTH

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


"Geoff" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
>

Geoff wrote:
> 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.
>
>
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

thanks all

"Alan Beban" <[email protected]> wrote in message
news:%[email protected]...
> Geoff wrote:
>> 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.
> 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
>