Macro Application.OnTime TimeValue doesnt run all the time

[marcusz]

Hi,

I am new here. This is my first post.I am a Linux sysadmin originally. I am trying to help someone with a system to manage some inventory. I build some Excel workbooks that gather input from different sources in 1 master excel workbook. So far all good.
This master workbook needs to run a macro every night at 01.00h am, so the values are copied to the next sheet ( day ). The workbook has 31 sheets, 1 for every day.
Also I have another macro in this workbook that runs every 5 minutes to grab several cells, open another file, paste them there, and close that file.

The 5 minute macro works perfect, the macro that has to run at 01.00h am, runs perfect for several days, and then sometimes it just does nothing, or doesnt run. I have no idea why it fails to run sometime.
The Excel file is open 24/7

No error message or whatever, it jut appears the next morning it didnt run.
Is there any way to debug this ?

Thanks in advance and also thanks for letting me drop in here and start asking for help immediatelty.

 

[offthelip]

I have just realised why this is not triggering every time, it is because the 5 minute timer starts at a random time, if it happens to start at say 5:25:00 then it runs exactly every 5 minute ON the 5 minutes and the 1am call will trigger because the test 5 minute trigger will run at 12:55 and 1:00.
However if the whole program is started at 5:27:00, then the 5 minute trigger will run at 32, 37, 42, 47, 52, 57, 02, 07 minutes etc. So the 5 minute trigger will be at 12:57 and 1:02, so my software won't trigger the 1am call.
so the solution is to change the "window" to at least 2minute 30 sec. this is absolutlely on the edge as to whether it calls in zero time or twice, so I would favour chaning the interval to the 3 minutes I suggested before and putting a trap in to check that it hasn't already been called in the last 10 minutes.

 

[marcusz]

Hi @offthelip

I think I understand what you are saying here.

So change the 5 min interval to 3 min. modify the old sheduler macro.
But I dont understand how I would code the ' check' if it has run for the last 10 minutes.


It would be the Scheduler macro that runs the 01.00 am macro, but only if the macro didnt run for the last 10 minutes ?
Any hint on how to do that ?

Thanks

 

[offthelip]

So change the 5 min interval to 3 min. modify the old sheduler macro.

Not quite you need to chagne the 30 second window to 3 minute window
try this code , I have added a check to prevent it running if it was last called less than 10 minutes ago:
UNTESTED!!!

Public lasttime
Sub scheduler()
tim = Time()
tim1 = TimeValue("01:00:00")
limt = TimeValue("00:03:00")  ' this is where you need to change the 30 seconds to 3 minutes
deltatim = Abs(tim - tim1)
If deltatim < limt Then
 lastlimt = TimeValue("00:10:00") ' 10 minute limit
 deltalast = Abs(tim - lastime) ' calcualate time since last call
  If deltalast > lastlimt Then ' check time since last call is greater than 10 minutes
    Call Master_IV_copy
    lastime = tim
  End If
End If
Call CollectSave
End Sub
  Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "Scheduler"
End Sub

 

[marcusz]

Thanks @offthelip

Gonna do some testing today and will report results

Thanks !

 

[marcusz]

One more question, shouldnt this part of code be in ThisWorkbook instead of in the Scheduler macro ( module ) ?

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "Scheduler"
End Sub

thanks

 

[offthelip]

yes definitely, sorry I just copied and pasted from this website to do the eidts, I didn't try it in excel!!

 

[marcusz]

Hi @offthelip

I implemented your code but the 01.00am macro is not starting at all. It fails 100% to run.
The 5 min interval works perfect.

I have no clue what I am missing here.

Thanks

 

[offthelip]

That implies that one of the if statement is failing but I can't see why.
you could try initialising "lastime" in the workbook open routine, since it is a public variable it should be available
Set a breakpoint on the if statements and check what values you are getting for all of the variables,
if your code has any difference from the code I posted can you post it here

 

[marcusz]

thanks for the feedback, gonna try to debug this with the info you gave me

 

[marcusz]

That implies that one of the if statement is failing but I can't see why.
you could try initialising "lastime" in the workbook open routine, since it is a public variable it should be available
Set a breakpoint on the if statements and check what values you are getting for all of the variables,
if your code has any difference from the code I posted can you post it here

I did put this in WorkBook routine >

Public lasttime
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "Scheduler"
End Sub

In the module code I did change 'lastime' in 'lasttime'.
I assume this was a typo ?

Testing now, seems to work perfectly