dcsimg
Results 1 to 12 of 12

Thread: Error 1004 with Application.OnTime

  1. #1

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,117

    Error 1004 with Application.OnTime

    I have the following line of code at the bottom of a workbook's Workbook_Open. When I open the workbook or when I manually execute that procedure in Excel 2010 everything is okay, but a user of mine who I think runs Excel 2016 64-bit gets "Run-time error 1004, Method OnTime of object-'Application' failed..." error. How can I fix that?

    '
    Code:
     Run at 9 AM
    Application.OnTime TimeValue("09:00:00"), "MyMacro"

  2. #2
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,501

    Re: Error 1004 with Application.OnTime

    Possibly useful links:

    https://www.mrexcel.com/board/thread...ailed.1037247/

    http://www.cpearson.com/excel/OnTime.aspx

    Don't know if the 64-bit versions would be an issue with this particular function. It can affect some things.

  3. #3

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,002

    Re: Error 1004 with Application.OnTime

    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,002

    Re: Error 1004 with Application.OnTime

    Or maybe your User suffers from this "Break"-Mode-bug i've run into myself.
    (Code stops executing as if a Breakpoint is active)


    https://www.mrexcel.com/board/thread...edure.1011293/
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6

    Thread Starter
    Former Admin/Moderator MartinLiss's Avatar
    Join Date
    Sep 1999
    Location
    San Jose, CA
    Posts
    33,117

    Re: Error 1004 with Application.OnTime

    I don't think that applies since the error happens in Workbook_Open and it happens every tome that the workbook is opened. I've at least temporarily corrected the problem by moving the Application.OnTime line to its own macro and adding a button on the workbook's main sheet that calls that macro. I'd like not to need the button but it works.

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,002

    Re: Error 1004 with Application.OnTime

    Hmmm, sounds like a race-condition.
    Workbook_Open executes/wants to execute code while other modules are not ready.
    Have you thought about making a Public Sub "StartUp" in a regular module with your time-code?
    and from Workbook_open you only call this Sub

    Code:
    Private Sub Workbook_Open()
      StartUp
    End Sub
    ........
    'Somewhere in modMain
    Public Sub StartUp()
    InitializeMe
    DoSomething
    CookCoffee
    Application.OnTime TimeValue("09:00:00"), "MyMacro"
    End Sub
    
    Public Sub MyMacro()
    'Being executed at 9AM
    End Sub
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,622

    Re: Error 1004 with Application.OnTime

    can you use an api timer, either in place of application.ontime, or to initially call application.ontime after a short interval?

    i have posted code for api timer in this forum a few times
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,622

    Re: Error 1004 with Application.OnTime

    NO, but if you use a fairly course interval (maybe 1 minute, i think 65535 is max interval) and you can test for the time now and whether already run for that day

    in your timer event something like
    Code:
    static lastrun as date
    if lasatrun = 0 then lastrun = date
    if lastrun < date and time > 9:00 then
        ' dostuff
        lastrun = date
    end if
    if application.ontime works in 64bit, just not in the open event you could just use a 5 second value then call application.ontime once and end the timer
    as i don not have 64 bit office, i only just stopped using office 2000, i can not test

    looks like you might have to invest in new office for testing
    Last edited by westconn1; Dec 5th, 2019 at 03:16 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width