N
NTL
Hi, I was wondering if anyone could help with a little problem I have.
I was trying to make my macros perform better & testing the relative
peroformance of various aspects of VBA & Excel. During the course of my
investigations I found that something strange was happening. To try to
track down the problem I distilled my macro down to a very simple form:
Option Explicit
Public Declare Function GetTickCount Lib "kernel32" () As Long
Public Sub test()
Dim startt As Long
Dim endt As Long
Dim x As Long
Dim y As Long
startt = GetTickCount()
For x = 1 To 10
For y = 1 To 100000000
Next y
Next x
endt = GetTickCount()
MsgBox endt - startt
End Sub
When running the macro several times in succession I sometimes get fairly
consistent run times in the region of 8.3 seconds, but occasionally the run
time just increases & increases. For example, in 10 runs I got the
following figures:
8328, 8406, 8890, 9500, 10218, 11031, 12015, 12765, 13937, 15250
All timings are in ticks (milliseconds).
After leaving the system alone for a few minutes the runtime comes back down
to its original level & then starts to rise again. The only explanation
that I can think of is that there is some sort of memory management problem
in VBA / EXCEL & that after a while memory management kicks in & fixes
things again. However, I have checked Excel's memory usage in Task Manager
& nothing seems to change. I have also chaged Excel priority to High to
ensure (as far as possible anyway) that nothing else affects it, at least
CPU wise.
Has anyone seen this before, got an explanation, fix or a workround?
Thanks
Simon
I was trying to make my macros perform better & testing the relative
peroformance of various aspects of VBA & Excel. During the course of my
investigations I found that something strange was happening. To try to
track down the problem I distilled my macro down to a very simple form:
Option Explicit
Public Declare Function GetTickCount Lib "kernel32" () As Long
Public Sub test()
Dim startt As Long
Dim endt As Long
Dim x As Long
Dim y As Long
startt = GetTickCount()
For x = 1 To 10
For y = 1 To 100000000
Next y
Next x
endt = GetTickCount()
MsgBox endt - startt
End Sub
When running the macro several times in succession I sometimes get fairly
consistent run times in the region of 8.3 seconds, but occasionally the run
time just increases & increases. For example, in 10 runs I got the
following figures:
8328, 8406, 8890, 9500, 10218, 11031, 12015, 12765, 13937, 15250
All timings are in ticks (milliseconds).
After leaving the system alone for a few minutes the runtime comes back down
to its original level & then starts to rise again. The only explanation
that I can think of is that there is some sort of memory management problem
in VBA / EXCEL & that after a while memory management kicks in & fixes
things again. However, I have checked Excel's memory usage in Task Manager
& nothing seems to change. I have also chaged Excel priority to High to
ensure (as far as possible anyway) that nothing else affects it, at least
CPU wise.
Has anyone seen this before, got an explanation, fix or a workround?
Thanks
Simon