Arrays to and from ranges

  • Thread starter Thread starter Excel dweeb
  • Start date Start date
E

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?
 
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.
 
.....What kind of beast
is this "singlevar" variable of type variant, anyway? Is it like a...
Click to expand...

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.
 
Dana said:
[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.
Click to expand...

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
 
Back
Top