Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro speed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro speed
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 "NTL" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro speed
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA macro speed
You have another response at your other post.
NTL wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Speed | Excel Programming | |||
Speed-up a macro! | Excel Programming | |||
Speed up macro | Excel Discussion (Misc queries) | |||
Speed-up macro | Excel Programming | |||
MACRO Speed? | Excel Programming |