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.

 

[mole999]

you might try and trap the actual time the event runs and put it on a sheet. if its exactly 1 am and something is busy then i wouldn't think it will run on time

 

[marcusz]

Thanks for the reply.
So if the 5 min macro is running exactly at 01.00h am , then the 01.00h am macro would skip ?

In my ' workbook code ' I have this now

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

And then I have the several 'modules' with the macros in it. Does this look like an approach that isn;t failsafe ?


Thanks

 

[offthelip]

I think you might be getting interference between the two triggers, so I would get rid of the 1am trigger and put a comparison in the 5 minute trigger to see if the time is abot 1am and if it is run the Master_IV_copy from with "collect_save" ( or create another macro called "scheduler" as below:

Sub scheduler()
tim = Time()
tim1 = TimeValue("01:00:00")
limt = TimeValue("00:00:30")
deltatim = Abs(tim - tim1)
If deltatim < limt Then
Call Master_IV_copy
End If
Call CollectSave
End Sub
  Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:05:00"), "Scheduler"
End Sub

 

[marcusz]

Thanks,
Will dive into your solution.
Much appreciated. Will report back when I get it working.

 

[marcusz]

Hi @offthelip ,

I have been testing and modifying your solution a lot, but still it happens that the 01.00h o clock macro doesnt run sometimes. I do a quick test , then it runs, and then the next day it skips the macro for example. So still it happens sometimes it cant run.
The 5 minutes interval macro runs perfect.

Just to be sure I am making no mistakes, the code I have:

ThisWorkbook code

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

Code in Module Scheduler

Sub Scheduler()
tim = Time()
tim1 = TimeValue("01:00:00")
limt = TimeValue("00:00:30")
deltatim = Abs(tim - tim1)
If deltatim < limt Then
Call Master_IV_copy
End If
Call Collect_Save
End Sub

Code in Module Master_IV_copy
> some easy macro code that only has to run 1 time a day at 01.00h but fails to execute sometimes, and sometimes works

Code in Module Collect_Save

Sub Collect_Save()
Call Collect
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.OnTime Now + TimeValue("00:05:00"), "Collect_Save"
End Sub

Thanks in advance

 

[mole999]

Just reading this to myself assuming 1 a.m
tim = Time()
tim1 = TimeValue("01:00:00")
limt = TimeValue("00:00:30")
deltatim = Abs(tim - tim1)
If deltatim < limt Then
tim = 1am
tim1 testing for 1am
limt 30 seconds
delta is 1am - 1am
if delta is less than limit ( is 0 less than 30 seconds )
so literally if it doesn't run in a 30 second window, then it won't run
I think that's what i am saying

 

[marcusz]

Hi @mole999
indeed it should run at 01.00 am

The solution provided by @offthelip should prevent the 2 macros to interfere. So when the 5 minute interval macro is running, the 01.00am macro skips now, and that is not what should happen. The solution above should check if it is 30 seconds before 01.00am and then run the 01.00 am macro, if not, run the 5 minutes interval macro.
This is how I understood it, but somehow it is not 100% fault proof.

Basically I am looking for a solution to
1 > ALWAYS run the 01.00am macro every day at 01.00 am
2 > run the 5 minute macro at 5 minute interval ( it would be allowed to have the 5 minute interval macro run at a specific time frame around the 01.00am, for example starting at 02.00 am and till 00.00 every day ) so the 2 wont interfere, but I have no clue how to do this


Thanks

 

[mole999]

maybe run the five minute macro and if it is 1am then trigger the 1am portion. I'm not clear in my head how I would make that happen, but only one macro loop running then

 

[offthelip]

maybe run the five minute macro and if it is 1am then trigger the 1am portion. I'm not clear in my head how I would make that happen, but only one macro loop running then

My code is supposed to do exactly that,
Note:
deltatim = Abs(tim - tim1)
the deltatime is the Absolute value of the difference in time between 1am and current time , this checks to see if it is between 12:59:30 and 01:00:30. Not 30 seconds before 1am, I was deliberately checking fo a window.
I did test this code ( NOT with 1am but another time) and it did work.
So I am wondering whether your machine hangs up for a period of more that 1 minute occasionally, because this is the only way I can see that this macro wouldn't work.
Try increasing the window to 3 minutes instead of 30 seconds. your only risk is the code could be called twice, but you could put a check in to prevent that.