Reading data into a 2 dimensional array

[Under Pressure]

I want to analyse data in a 2 dimensional array but don't know how to get the
data from the spreadsheet into the aray. In the old days of GWBASIC I would
use the following lines:

Dim Table(100,4)
For I = 1 to 100 : For J = 1 to 4
Read Table(I,J)
Next J : Next I

I know macros can handle FOR/NEXT loops but how do you 'READ' the data that
is, say, in the range A1100 ?

Thanks in anticipation.

 

[OssieMac]

See if the following 2 examples explains what you need. Note that you simply
declare a variant and assign the range to the variant. Have included the
Lbound and Ubound functions because sometimes you may not know the actual
number of elements.

Also note that when only assigning a single column in this way to a variant
you must include the first dimension when reading the elements in the array.
(With normal single dimension arrays, the dimension is understood.)

Sub RngToArray()

Dim myArray As Variant
Dim Low1 As Long
Dim Upp1 As Long
Dim Low2 As Long
Dim Upp2 As Long
Dim i As Long
Dim j As Long

myArray = Sheets("Sheet1").Range("A14")

'Lowest element in first dimension
Low1 = LBound(myArray, 1)

'Highest element in first dimension
Upp1 = UBound(myArray, 1)

'Lowest element in second dimension
Low2 = LBound(myArray, 2)

'Highest element in second dimension
Upp2 = UBound(myArray, 2)

For i = Low1 To Upp1
For j = Low2 To Upp2
MsgBox myArray(i, j)
Next j
Next i

End Sub


Sub RngToArray2()

Dim myArray As Variant
Dim Low1 As Long
Dim Upp1 As Long
Dim i As Long

myArray = Sheets("Sheet1").Range("A1:A4")

'Lowest element in first dimension
Low1 = LBound(myArray, 1)

'Highest element in first dimension
Upp1 = UBound(myArray, 1)

'Note because assigned to variant you must
'use the first dimension in the formula
For i = Low1 To Upp1
MsgBox myArray(i, 1)
Next i

'Alternative using 1 as the first element
For i = 1 To Upp1
MsgBox myArray(i, 1)
Next i

End Sub

 

[ker_01]

Although I prefer the solution suggested by OssieMac, an alternative is to
reference the range directly;

Dim myRng as range
Set myRng = Sheet1.Range("A14")

Note that for either solution, Excel defaults to a 0-based array, e.g.
myRng(0 to 3, 0 to 3). If you prefer to work with 1-based ranges, then at the
top of your module add the line
Option Base 1
(note that this is only effective at the module level, and does not affect
the array declarations in other modules)

HTH,
Keith

 

[OssieMac]

Hi Keith,

I think that unlike normal arrays, if you test you will find that both
methods are 1 based by default. Try out the following examples.

Sub RngToArray()

Dim myArray As Variant
Dim Low1 As Long
Dim Upp1 As Long
Dim Low2 As Long
Dim Upp2 As Long
Dim i As Long
Dim j As Long

myArray = Sheets("Sheet1").Range("A14")

'Lowest element in first dimension
Low1 = LBound(myArray, 1)

'Highest element in first dimension
Upp1 = UBound(myArray, 1)

'Lowest element in second dimension
Low2 = LBound(myArray, 2)

'Highest element in second dimension
Upp2 = UBound(myArray, 2)

MsgBox "Low1 = " & Low1 & vbCrLf & _
"Low2 = " & Low2 & vbCrLf & _
"Upp1 = " & Upp1 & vbCrLf & _
"Upp2 = " & Upp2

For i = Low1 To Upp1
For j = Low2 To Upp2
MsgBox myArray(i, j)
Next j
Next i

End Sub


Sub RngToArray2()

Dim myArray As Variant
Dim Low1 As Long
Dim Upp1 As Long
Dim i As Long

myArray = Sheets("Sheet1").Range("A1:A4")

'Lowest element in first dimension
Low1 = LBound(myArray, 1)

'Highest element in first dimension
Upp1 = UBound(myArray, 1)

MsgBox "Low1 = " & Low1 & vbCrLf & _
"Upp1 = " & Upp1

'Note because assigned to variant you must
'use the first dimension in the formula
For i = Low1 To Upp1
MsgBox myArray(i, 1)
Next i

'Alternative using 1 as the first element
'For i = 1 To Upp1
' MsgBox myArray(i, 1)
'Next i

End Sub

Sub RngTest()

Dim myRng As Range
Dim i As Long
Dim j As Long

Set myRng = Sheets("Sheet1").Range("A14")
For i = 1 To 4
For j = 1 To 4
MsgBox myRng(i, j)
Next j
Next i


End Sub