Visual Basic Timer for Excel slows down when a game is running

Question

Basically I am using the Timer function to act as a stopwatch, which triggers different tasks when a designated time is reached. The program works great, except when I attempt to run a game at the same time. The time seems to be delayed. By this I mean 30 seconds in real life gets dragged out to 45 seconds in the VBA Excel program. The delay continues to get worse as the program continues to run. Any suggestions would be great

Answer 1

Josh,

For what it is worth: another timing method uses the Windows API.

'At the top of the module ...

Public Declare Function timeGetTime Lib "winmm.dll" () As Long

'Returns the number of milliseconds since midnight

'Then in your sub...

Dim lngStart as Long

Dim lngTotal as Long

lngStart = timeGetTime

'code to do stuff

lngTotal = timeGetTime - lngStart

MsgBox Format$(lngTotal / 1000, "#00.000 Seconds")

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

Hey James,

I figured it out actually. So initially I was comparing the reaction time of the VBA excel program to the clock in the game I was running. Using a stopwatch I discovered the in game clock was incrementing at 1.4x the speed of real life. So actually the VBA program was running correctly it just appeared to be lagging due to the game clock running at an accelerated pace.

Answer 3

Hey James,

I figured it out actually. So initially I was comparing the reaction time of the VBA excel program to the clock in the game I was running. Using a stopwatch I discovered the in game clock was incrementing at 1.4x the speed of real life. So actually the VBA program was running correctly it just appeared to be lagging due to the game clock running at an accelerated pace.

Answer 4

Hi James, I am pretty sure the timer is slowing down since the tasks that it is triggering are really simple. Right now its just manipulating the format of text in an excel sheet. Basically it is increasing the text size and making it bold. Now I am pretty much brand new to VBA for applications so efficiency may not be a strong point in my code. With that being said the loop that runs is only 25 Lines long. I attached a photo of the loop that runs.  To clarify a few things inital=Timer and is set outside the loop which essentially makes it a reference point of time. Cnt has to do with something I read about adding a "Do Events" statement every so often to help keep the program for locking up during a long loop. The Allsec array contains times in seconds corresponding to a range in the excel document.

I'll look into displaying Elapsed into a Cell and seeing if it keeps up with a Google timer or something of the sort.

Thanks for the Reply btw

Answer 5

Are you sure that the Timer slowing down or are the "tasks" just taking longer to execute?

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)