Arrays to and from ranges

[Excel dweeb]

Per Tom Ogilvy's earlier example in "Dumping the contents of a VBA
array to a sheet," I understand that I can write an array to a range.

Is there a way to do the reverse of this, i.e., take a range directly
into an array?

My try of:

Option Base 1
Sub abc()
Dim myarr(1 to 3, 1 to 2) as Integer

myarr = Worksheets("Sheet1").Range("A1:B3").Value

tells me I can't do that with an array.

This doesn't work either:

Option Base 1
Sub def()
Dim myvar as Variant

myvar = Worksheets("Sheet1").Range("A1:B3").Value

although the VBA Help seems to imply that it should.

What am I missing?

 

[Tom Ogilvy]

This works fine for me

Sub testarr_read()
Dim singlevar As Variant

singlevar = Worksheets("Sheet2").Range("a1:b3").Value ' Works fine

Worksheets("Sheet2").Range("D12:E14").Value = singlevar ' Works fine

MsgBox singlevar(1, 1) ' works fine

MsgBox LBound(singlevar, 1) & " by " & UBound(singlevar, 1) & _
vbNewLine & LBound(singlevar, 2) & " by " & UBound(singlevar, 2)

End Sub

if you pick up a multicell range from a spreadsheet, it will always have two
dimensions.
The lower bound will always be 1, regardless of your option base setting.

 

[Dana DeLouis]

.....What kind of beast

is this "singlevar" variable of type variant, anyway? Is it like a...

As a technique while stepping thru your code, select the "Locals Window."
You can see the dimensions & Type for this variable.
You can see variables like "singlevar(1,1), etc.

Having said that, you will also see a "singlevar(1)" as an expanding
heading. Using "singlevar(1)" was giving you an error. One would initially
hope that this would be a way to set a variable to the entire Row 1 of the
2-dim array. Something like...
MyVariant = singlevar(1)
which would return Row 1 of the Array. Unfortunately, this is not the
case. Excel's vba can't do that.

 

[Alan Beban]

[snip] One would initially
hope that this would be a way to set a variable to the entire Row 1 of the
2-dim array. Something like...
MyVariant = singlevar(1)
which would return Row 1 of the Array. Unfortunately, this is not the
case. Excel's vba can't do that.

But it can come close.

Sub TestVlook()
Dim singlevar, singlevarRow(), i As Long
singlevar = Range("A1:C4")
ReDim singlevarRow(1 To UBound(singlevar))
For i = 1 To UBound(singlevar)
singlevarRow(i) = Application.Index(singlevar, i, 0)
Next
End Sub

I.e., singlevarRow(n) is the nth row of singlevar.

At least through xl2000, this doesn't work on arrays of more than 5461
elements.

Alan Beban