Fast export large 2-dim array to worksheet

[aafraga]

Hello Excel Community,

For many years, I have been programming in EXCEL VBA and often use 2
dimensional arrays, usually of type Double of Variant. Normally, I
would declare the array such as :
Dim aMatrix(10000,50) as Double

Some math code would populate all the cells and I would then print the
aMatrix to a worksheet by nesting in a double loop:
for i...
for j...
Sheet1.Cells(i,j).Value = aMatrix(i,j)

The problem here is that Excel really slows down after a couple of
thousand.
Is there a fast way to bulk copy an array to a worksheet ?

I am looking for a function along the lines of
FastCopytoWorkSheet (aMatrix() as double, aRange as Range)
....

Is it best to just turn off screen updating or is there a more elegant
way ?
Many thanks and best regards,
Alberto

 

[Tom Ogilvy]

Dim aMatrix(10000,50) as Double

' code the populates the aMatrix

lb1 = lbound(aMatrix,1)
lb2 = lbound(aMatrix,2)
ub1 = ubound(aMatrix,1)
ub2 = ubound(aMatrix,2)
rws = ub1 - lb1 + 1
cols = ub2- lb2 + 1
Sheet1.Cells(1,1).Resize(rws,cols).Value = aMatrix

Obviously, if you know the number of rows and columns, you don't need to
calculate them as I have. However, without knowing option base, and you
didn't specify the lower bound, I provided a generalized approach. The key
bit is you can put it all down in one step.

 

[Peter T]

Sub test()
Dim are(1 To 10000, 1 To 50) As Double
Dim r As Long, c As Long

For c = 1 To 50
For r = 1 To 10000
ar(r, c) = r + (c / 100)
Next
Next
Range(Cells(1, 1), Cells(10000, 50)).Value = ar

End Sub


Around this size it might be faster to start breaking down into smaller
arrays before dumping.

Regards,
Peter T

 

[Peter T]

Typo

Dim are(1 To 10000, 1 To 50) As Double

Dim ar(1 To 10000, 1 To 50) As Double

Spell checker must have changed "ar" to "are" without me noticing and
pressing wrong button.

Tom - I didn't see yours, didn't mean to duplicate.

Regards,
Peter T

Sub test()
Dim are(1 To 10000, 1 To 50) As Double
Dim r As Long, c As Long

For c = 1 To 50
For r = 1 To 10000
ar(r, c) = r + (c / 100)
Next
Next
Range(Cells(1, 1), Cells(10000, 50)).Value = ar

End Sub


Around this size it might be faster to start breaking down into smaller
arrays before dumping.

Regards,
Peter T