Not sure what you mean by the difference between the 3 sec and 24 sec, using
that code I posted.
The printed time will when everything has finished, assuming you do not have
anything else running.
Note that there is no calculation of dependent cells on the XL sheet
involved in this, which may not be the your case.

Yes, the time consuming part is dumping the large array to the WS. If you
can avoid using such a large array, by splitting it up and only dumping what
is need/changed, you will speed up the routine.

NickHK

"ecanet" wrote in message
...
Thanks for replying so fast !
I tested your code (on excel2003).
I'm afraid I don't understand, the printed time is very good (<3s), but
the
real time (after refresh of the grid) is much different (24s).
I noticed that you also used the Value property, as I do.

The calculation time isn't the problem, but the populating of each cell
is.






"NickHK" wrote:

If you were talking about the difference between XL2007 and earlier
versions, then you would probably see marked reduction in speed as
numerous
posts here have related.
Without knowing exactly what you are doing, the test code below does run
faster in XL2K:
You can use Application.ScreenUpdating/Calculation etc to reduce Excel's
overhead, but with the code below, this reduced all results only by
about 1
second.

XL2K XL2002
38 sec 53sec

Private Sub CommandButton1_Click()
Dim arr(1 To 5001, 1 To 256) As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim OutRange As Range
Dim StartTime As Date

Const RUNS As Long = 10

StartTime = Now()

For i = 1 To RUNS
For j = LBound(arr, 1) To UBound(arr, 1)
For k = LBound(arr, 2) To UBound(arr, 2)
arr(j, k) = Rnd()
Next
Next

Set OutRange = Range("A1").Resize(UBound(arr, 1) - LBound(arr, 1),
UBound(arr, 2) - LBound(arr, 2))
With OutRange
.ClearContents
.Value = arr()
End With

Next

Debug.Print "**** With Excel " & Application.Version
Debug.Print , "Execution of " & RUNS & " runs of " &
OutRange.Cells.Count &
" cells"
Debug.Print , "Time taken: " & Format(Now() - StartTime, "s") & "
seconds"
Debug.Print "****"
End Sub

NickHK

"ecanet" wrote in message
...
Hi all,

I know everyone is looking for latest version, but I work as a
developer
and
my customer use Excel 2000. He asked me to evaluate the cost of an
upgrade
to
Excel 2003, so here I am.

My program must fill 5000*255 cells.
I use the VBA method "Resize" to select all the range, then "Value"
and
set
them with an array containing all the datas.
AFAIK, this is the most effective way to do this.


The problem I have is about the performances :
this code works both on Excel 2000 and on Excel 2003 but on the same
computer respectively need 3 sec and 9 sec.
This difference is not acceptable by my customer.


Could anyone tell me if this lack of perf is a known issue when
upgrading
and if there is a workaround ?
I see on the net that this is a computer problem, but I'm afraid that
they
wouldn't change their hardware for more competitive ones.....


Thanks for giving me any pointers and have a nice day !


Manu, hope I made myself clear enough, I'm not a good english speaker
!