+ Reply to Thread
Results 1 to 7 of 7

Automatically run a macro 10 times a day, half past the hour, between 08:30 and 17:30

  1. #1
    Registered User
    Join Date
    08-23-2006
    Location
    Darlington, England
    MS-Off Ver
    Microsoft 365
    Posts
    29

    Automatically run a macro 10 times a day, half past the hour, between 08:30 and 17:30

    Hi
    I would like to make a macro run 10 times a day, half past the hour, between 08:30 and 17:30

    I thought it was as simple as adding the following for each time of the day I need it to run within the macro and also in 'ThisWorkbook' object.
    Please Login or Register  to view this content.
    etc...

    The result I get isn't as expected. The first time runs OK but then each additional time runs more than once as it progresses through each time. So 09:30 runs twice then 10:30 runs 4 times etc...
    That's not the behaviour I expected.

    I know you can use this code to run every hour from the current time the workbook was opened but that is not the behaviour I want.
    Please Login or Register  to view this content.
    How can I achieve what I need please?

    Thanks

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,073

    Re: Automatically run a macro 10 times a day, half past the hour, between 08:30 and 17:30

    Quote Originally Posted by anthgav View Post
    I need it to run within the macro and also in 'ThisWorkbook' object.
    Please Login or Register  to view this content.
    etc...
    Don't put the Application.OnTime lines within the Stats macro. Run them once at the beginning of the day and that's it. If they are in the Stats code, every hour it runs then another instance of each hour is scheduled.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    422

    Re: Automatically run a macro 10 times a day, half past the hour, between 08:30 and 17:30

    I believe that Alphafrog method should work.

    Alternatively, you can schedule the macro to run every 30 minutes AND HAVE A TIME LIMIT ON THE FUNCTION ARGUMENT AS THE LATEST TIME THE PROGRAM CAN RUN, SO IT DOESN"T RUN AFTER THAT TIME
    Please Login or Register  to view this content.
    If you think someone helped you, click on the "* Add Reputation" as a way to say thank you.

    If your problem is solved, go to Thread Tools and select Mark This Thread Solved

  4. #4
    Registered User
    Join Date
    08-23-2006
    Location
    Darlington, England
    MS-Off Ver
    Microsoft 365
    Posts
    29

    Re: Automatically run a macro 10 times a day, half past the hour, between 08:30 and 17:30

    Thanks guys. That has solved it for me.
    Is it possible to use
    Please Login or Register  to view this content.
    but replace 'Now' with '08:30' so that the macro runs at intervals from from that time? or can that only be done with separate lines?

  5. #5
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    422

    Re: Automatically run a macro 10 times a day, half past the hour, between 08:30 and 17:30

    Please Login or Register  to view this content.
    This will run the STATS at 8 30

    Only thing to note is you need to find a way to run the sub this line is on - either add it to workbook open or something like that.

  6. #6
    Registered User
    Join Date
    08-23-2006
    Location
    Darlington, England
    MS-Off Ver
    Microsoft 365
    Posts
    29
    Quote Originally Posted by saravnepali View Post
    Please Login or Register  to view this content.
    This will run the STATS at 8 30

    Only thing to note is you need to find a way to run the sub this line is on - either add it to workbook open or something like that.
    This won't make it run once an hour though will it? Which is what I require.

  7. #7
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Automatically run a macro 10 times a day, half past the hour, between 08:30 and 17:30

    Hi anthgav,

    I created a general Excel file which you may be able to use to do what you want.

    The file has a 'Macro Scheduler' Sheet which contains the following User Modifiable items:
    a. List a Macros and the Time of Day each Macro is to run.
    b. A List of Days of the Week (does NOT apply to individual Macros) which can be set to Yes or No to indicate which Days of the Week the Macros will run.
    c. A Holiday Yes No Cell which indicates whether Macros are to be run on Holidays (provided the Day of the Week is also a 'Yes').
    d. A List of Holidays

    How the software works:
    a. When the file is opened Macros are Scheduled per the 'Macro Scheduler' Sheet.
    b. Any Macro that is to be scheduled for a time that has passed, is NOT SCHEDULED for the Current Day.
    c. At one second after Midnight Macros are Scheduled for the Current Day.
    d. There are 3 'Yellow Shape' CommandButtons:
    (1) to Stop All Macros from Running.
    (2) to Stop All Macros from Running and to Restart the Macro Schedule.
    (3) List the Macro Schedule and Status for Today (sorted in ascending time of day order).
    e. Items on the Spreadsheet can be changed at any time. They do not take effect until either:
    (1) Scheduling for the next day occurs just after midnight, or
    (2) The Macros are stopped, and restarted by User Action.

    NOTE: For the file to work properly the Excel File MUST be open 24/7 and Windows cannot be sleeping.

    It took several days to debug the code, which is the nature of real time software. If I overlooked something, please let me know.

    Important code excerpts follow from the attached file:
    Please Login or Register  to view this content.
    Lewis
    Last edited by LJMetzger; 12-11-2019 at 01:08 PM. Reason: Uploaded new file - functionally the same - status messages were made clearer

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Graphing Start and End times for multiple data sets (by half hour)
    By terpsgirl08 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-11-2019, 02:06 PM
  2. [SOLVED] Filling In Data Based On Half-Hour Times
    By windme in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-28-2019, 06:23 PM
  3. Replies: 1
    Last Post: 05-09-2016, 11:35 PM
  4. [SOLVED] Overtime and payment at half hour intervals with a lower payment for the first half hour
    By brettamine in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-24-2014, 07:51 AM
  5. Run code each half hour (12:00;00:30;01:00..23:00;23:30)
    By Bishonen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-12-2013, 07:19 AM
  6. [SOLVED] Calculate Fractional minutes between half hour intervals with multiple start and end times
    By CheeksExcelForum in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-20-2013, 02:19 PM
  7. [SOLVED] relative references? Converting half hour incriments into hour incriments
    By Babbabooie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-07-2012, 11:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts