Array speed

  • Thread starter Thread starter leungkong
  • Start date Start date
L

leungkong

I use the following code to write the data from a (8615 * 24) array to
worksheet
It is very fast before.
But I don't know why it is slow now.
Today, I spend 2.5 hours for this.
I want to know why and how to speed up. Many thanks.

Dim i As Long, j As Byte
For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
Cells(i + 2, j + 1 = RMaster(i, j)
Next
Next
 
I put the code below in a new workbook and it took under 10 seconds. Try
doing hte same. I think it is the workbook and not the macro. You may have
a lot of data or fromating in the workbook which would slow down the macro.
But it also may be your PC. I would try putting the workbook on another PC
to see what happens.

Sometimes deleting rows that are not used in teh worksheet may help. If you
worksheet goes to column Z then highlight all the columns to the right of you
data and delete the columns not used. Repeat for the rows. Try for all
worksheets.


Sub test()

Dim RMaster(8615, 24)
Dim i As Long, j As Byte
For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
Cells(i + 2, j + 1) = RMaster(i, j)
Next
Next


End Sub
 
Dim RMaster(8615, 24)
Dim i As Long, j As Byte
For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
Click to expand...

As a side point for the OP... you didn't have to hard code the limits for the second For statement... you could use LBound and UBound for them to, just use the optional second argument to get to the second dimension's bounds.

Dim RMaster(8615, 24)
.......
For i = LBound(RMaster) To UBound(RMaster)
For j = LBound(RMaster, 2) To UBound(RMaster, 2)
......
 
This example runs in 0.3 seconds on my system: its much faster to transfer
the data in 1 block from the array than doing it cell-by-cell.

Sub test()

Dim RMaster(8615, 24)
Dim i As Long, j As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

For i = LBound(RMaster) To UBound(RMaster)
For j = 0 To 23
RMaster(i, j) = i * j
'Cells(i + 2, j + 1) = RMaster(i, j)
Next
Next
Cells(2, 1).Resize(UBound(RMaster) + 1, UBound(RMaster, 2) + 1) =
RMaster

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com
 
I want to know why and how to speed up.
Dim i As Long, j As Byte
Click to expand...


Hi. Just another approach...

Sub Demo()
Dim RMaster(8615, 24)
Dim d
'// Load RMaster w/ data...etc

d = Dimensions(RMaster)

[B1].Resize(d(0), d(1)) = RMaster
End Sub


Private Function Dimensions(m) As Variant
Dimensions = Array( _
UBound(m, 1) - LBound(m, 1) + 1, _
UBound(m, 2) - LBound(m, 2) + 1)
End Function

Dim i As Long, j As Byte
Click to expand...

I believe the consensus here in the newsgroup is that "Byte" is not
often used for a looping type. There just doesn't seem to be any speed
improvement.

Note that the number of columns is 25 (ie 0 to 24)
Perhaps you could use something like this:

Dim RMaster(1 To 8615, 1 To 24)

= = = = = = =
HTH :>)
Dana DeLouis
 
Back
Top