Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I am measuring the performance of certain operations. I stumbled onto
an anomaly that surprises me. I wonder if anyone has a reasonable explanation. The structure of the macro is (pseudo-code): for cnt = 1 to maxCnt startTime = getTickCount() for n = 1 to maxN perform operation next n deltaTime = getTickCount() - startTime debug.print cnt & ": n=" & maxN & format(deltaTime, "0 msec") next cnt The first time after I modify the macro, the deltaTime for each iteration (not just the first deltaTime) is about 430 msec. Oddly, for subsequent executions (nothing changed), the deltaTime for each iteration is about 730 msec. Why would the n-loop consistently take longer after the first macro execution? (Note: The deltaTime for cnt=1 is always longer. I presume that is due to the incremental compilation feature of VBA. In any case, I ignore the first deltaTime.) In case the details of the operations might make a difference, the following code is more complete (without declarations): Application.Calculation = xlCalculationManual Application.ScreenUpdating = False for cnt = 1 to maxCnt startTime = GetTickCount() for n = 1 to maxN Range("b1:b262").Calculate ' formulas with RAND() Range("d1:d9").Calculate ' depend on B1:B262 Range("f1:f9").Calculate ' depend on D1:D9 y = Range("f1:f9") x = Range("d1:d9") next n deltaTime = GetTickCount() - startTime debug.print cnt & ": n=" & maxN & format(deltaTime, " 0 msec") next cnt Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel Macro, slow performance on terminal server... | Excel Programming | |||
Macro performance | Excel Discussion (Misc queries) | |||
poor macro performance | Excel Programming | |||
Performance problem with VB Macro in Excell XP | Excel Programming | |||
problem with performance of excel macro, pls help | Excel Programming |