none
Code to detect the hour change? RRS feed

  • Question

  • I am using Excel and have quite a bit of code in the developer.

    My next task is to determine when the hour changes and then to reset a variable. This can happen any time when the minutes = 00 but maybe the code is just as easy for minutes = 00 and seconds = 00.

    I do not see an option to set a timer to test for the minutes.

    Thoughts appreciated.

    Wednesday, November 16, 2016 4:28 PM

Answers

  • This was answered already and I set the answered flag.  I am not sure how this got duplicated.

    Meanwhile, to answer the question here, the Application.OnTime method worked for me.  I extracted the present hour, incremented it by 1 and ran the method as (NewTime, 00, 00) to activate a sub routine.

    The need was to solve a timing problem.  Since it is impossible to start the program exactly on the hour, I had to correct for the discrepancy in order to trigger a function hourly, on the hour, as the program continues to run.

    In other words, the function will run for the remainder of the hour when the program is started  then start fresh on the hour.

    As far as the URL that was supplied is concerned, it was worthless for me because I was not interested in reading through every possible conjugation of the method to see what fits.  I did find another discussion group that explained it in plain English.


    Thursday, November 17, 2016 5:50 AM

All replies

  • Hi GarySutcliff,

    Could you share us why you want to detect the hour change? Do you want to run something for minutes=00 and seconds=00? I think you could try Application.OnTime to schedule a procedure to be run at a specified time in the future.

    You could refer the link below for more information.

    # Application.OnTime Method (Excel)

    https://msdn.microsoft.com/en-us/library/office/ff196165.aspx

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 17, 2016 5:38 AM
  • This was answered already and I set the answered flag.  I am not sure how this got duplicated.

    Meanwhile, to answer the question here, the Application.OnTime method worked for me.  I extracted the present hour, incremented it by 1 and ran the method as (NewTime, 00, 00) to activate a sub routine.

    The need was to solve a timing problem.  Since it is impossible to start the program exactly on the hour, I had to correct for the discrepancy in order to trigger a function hourly, on the hour, as the program continues to run.

    In other words, the function will run for the remainder of the hour when the program is started  then start fresh on the hour.

    As far as the URL that was supplied is concerned, it was worthless for me because I was not interested in reading through every possible conjugation of the method to see what fits.  I did find another discussion group that explained it in plain English.


    Thursday, November 17, 2016 5:50 AM
  • Hi GarySutcliff,

    Thanks for sharing, I suggest you mark your reply as answer to indicate this issue is answered.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 17, 2016 9:39 AM