LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Macro, slow performance on terminal server... Lars Gaarde Excel Programming 4 April 23rd 07 01:58 PM
Macro performance John Excel Discussion (Misc queries) 0 February 2nd 07 05:06 PM
poor macro performance [email protected] Excel Programming 1 August 11th 06 07:52 PM
Performance problem with VB Macro in Excell XP RayOTown Excel Programming 1 December 6th 04 05:57 PM
problem with performance of excel macro, pls help lee777[_2_] Excel Programming 1 June 18th 04 11:47 AM


All times are GMT +1. The time now is 07:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"