View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default Odd macro performance. Explanation?

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