On Mon, 11 Jan 2010 09:43:01 -0800, Under Pressure
wrote:

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 A1:D100

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("A1:D100"))
End Sub