Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Looking for Accuracy in Macro run time interval result

0

Hi Experts,

Hope you all are doing great.

I am looking for accurate result in Macro run time Interval.

I am not getting correct result for Time interval in macro.

There is no accuracy in result of time interval.

For example: In attached excel sheet, I have set the various time interval in macro, like 10 sec, 5 min and 5:10 min, but the run time interval is not adhering the Set time in macros. It giving some inconsistant result, like below:

8 sec,9 sec against set 10 sec.

3 min against set 5 min.

3.5 min against set 5:10 min.

I am looking for Accurate results in set time interval in macro.

Require your help to solve the problem.

Would really appreciate your response. 

Regards,

excel
MS Office Version: 2016
posted by: SunilA (rep: 58) on Fri Aug 13, 2021 at 7:26 am
Answer
Discuss

Discussion

Sunil

The basic timer seems okay. If I get set the Interval (=EarliestTime parameter if you look at MS guidance Application.OnTime method (Excel)) and print the start time then use it to trigger a sub to print the time that process is run:
Sub Timer1()
 
Interval = Now + TimeValue("00:00:10")
Debug.Print "Started at " & Now
 
Application.OnTime Interval, "GiveDoneTime"
 
End Sub
 
Sub GiveDoneTime()
 
Debug.Print "Done at " & Now
 
End Sub
for a 10 seconds Interval (as above), I get output: 
Started at 13/08/2021 15:05:08
Done at 13/08/2021 15:05:18
i.e 10 seconds (MS guidance shows it's to the nearest second), and for 5 minutes I get:
Started at 13/08/2021 15:08:43
Done at 13/08/2021 15:13:43
i.e. spot on 5 minutes (as expected).

You'll see in the MS note that the Procedure runs when Excel is ready. Suspect your problem is that Excel or Windows is busy on other things (e.g. remote updates) or you have more than one timer running simultaneously.

I assume you realise that a timer sub for 5 minutes may run in less than a second and return control to you but the Procedure will run independently after that 5 minutes (if Excel isn't busy)

I won't be spending any more time on this.
John_Ru (rep: 6142) Aug 13, '21 at 10:16 am
Add to Discussion

Answers

0
Selected Answer

Sunil

The timer seems okay even in your file. If I add the line

Debug.Print Now
to the start of both your macros SetTimer and MyMacro (which its 10 second timer triggers), the output reads:
13/08/2021 15:39:42 
13/08/2021 15:39:52  
13/08/2021 15:39:52
13/08/2021 15:40:02 
13/08/2021 15:40:02 
13/08/2021 15:40:12 
13/08/2021 15:40:12 
13/08/2021 15:40:22 
13/08/2021 15:40:22
where each pair of values come from the start of those macros. There's a ten-second gap between them (as expected) e.g timer starts 15:39:52,  MyMacro starts 15:40:02 (ten seconds later) then timer starts again at 15:40:02. 
posted by: John_Ru (rep: 6142) on Fri Aug 13, 2021 at 10:49 am
Discuss

Discussion

Thank you John for your kind response and solution. Very much Appreciated.
SunilA (rep: 58) Aug 14, '21 at 7:45 am
Thanks for selecting my Answer, Sunil. Not sure it helps you directly but hopefully you can find whatever else is leading to the apparent inaccuracy. 
John_Ru (rep: 6142) Aug 14, '21 at 5:09 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login