VBA macro speed

[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

 

[NickHK]

Simon,
Whilst I have seen memory issues with objects or cut/pastes in a tight loop,
your code runs consistently fast on my machine, using Debug.Print.
Using a Msgbox to display the result is ~100 ticks slower, but still
consistent and hardly noticeably longer.

Not much of an answer, just my results...

NickHK

 

[kemal]

Has anyone seen this before, got an explanation, fix or a workround?

I have a similar strange performance change.

If I keep pressing a commandbutton longer than just clicking it
punishes me next time when I click it
by taking longer time to execute the code behind it.
What is even stranger :
only some of the commanbuttons do this not all of them that have the
same sort of code.

Quiting application returns the same normal speed till a forgetful
pressing occurs.

 

[Dave Peterson]

You have another response at your other post.

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