Reading data into a 2 dimensional array

[Under Pressure]

My original post on this appears to have disappeared, so here goes again.

I want to do some complex analysis on some data in a spreadsheet. I would
like to use a macro but don't know how to 'read' the data from the
spreadsheet into a 2 dimensional array. Back in the old days of programming
in GWBASIC, I would have used lines as follows :

DIM TABLE(100,40)
FOR I = 1 TO 100 : FOR J= 1 TO 4
READ TABLE(I,J)
NEXT J: NEXT I

I know that macros can handle FOR/NEXT loops but what about getting the data
from a range say A1100

Thanks again in advance

Under Pressure

 

[Lars-Åke Aspelin]

My original post on this appears to have disappeared, so here goes again.

I want to do some complex analysis on some data in a spreadsheet. I would
like to use a macro but don't know how to 'read' the data from the
spreadsheet into a 2 dimensional array. Back in the old days of programming
in GWBASIC, I would have used lines as follows :

DIM TABLE(100,40)
FOR I = 1 TO 100 : FOR J= 1 TO 4
READ TABLE(I,J)
NEXT J: NEXT I

I know that macros can handle FOR/NEXT loops but what about getting the data
from a range say A1100

Thanks again in advance

Under Pressure

Here is an example that you can modify according to your needs:

Function complex_analysis(r As Range)
Dim a() As Double
ReDim a(1 To r.Rows.Count, 1 To r.Columns.Count)
s = 0
' transfer the data from Range r to Array a
For i = 1 To r.Rows.Count
For j = 1 To r.Columns.Count
a(i, j) = r(i, j)
Next j
Next i

' do complex analyis, summing is just an example
For i = 1 To UBound(a, 1)
For j = 1 To UBound(a, 2)
s = s + a(i, j)
Next j
Next i

complex_analysis = s

End Function

Sub test()
MsgBox complex_analysis(Range("A1100"))
End Sub

 

[Rick Rothstein]

If you are reading values in from a worksheet, you don't need a loop at all.
This code will load up the Table array as you have indicated...

Dim TABLE() As Variant
TABLE = Range("A120").Value

Just change my example range of A1100 to whatever range of cells you want
to put into your array variable.