Results 1 to 4 of 4

Thread: set timer for a excel spread sheet using a variable for the time value

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2008
    Posts
    77

    set timer for a excel spread sheet using a variable for the time value

    trying to start a sheet update routine with the timer using a variable. see code below, when I use the hard code that works but not working when using a variable, it doesn't start the timer, perhaps a different way to start the timer?

    Public Sub startTimer()

    '... the following is the procedure to run on the timer'
    saveRTDinfo 'Sub saveRTDinfo()

    alertTime = alertTime 'for debugging to view value
    alertTime = now + TimeValue(alertTime) 'doesn't work see next line

    'debug.print "alertTime = " & alertTime, this prints 00:02:00, literally, without parens
    alertTime = now + TimeValue("00:02:00") 'this works

    Application.OnTime EarliestTime:=alertTime, _
    Procedure:="startTimer", schedule:=True
    End Sub

    here's the variable definitions
    Public alertTime As Variant
    Public alertTimeHH As String
    Public alertTimeMM As String
    Public alertTimeSS As String

    here's how I configured:
    alertTimeHH = InputBox("update time interval hours, default = 00", "00")
    alertTimeMM = InputBox("update time interval minutes, default = 00", "00")
    alertTimeSS = InputBox("update time interval seconds, default = 00", "00")
    testStr = """" & alertTimeHH & ":" & alertTimeMM & ":" & alertTimeSS & """"
    alertTime = alertTimeHH & ":" & alertTimeMM & ":" & alertTimeSS
    Debug.Print "testStr = " & testStr 'just for testing
    Debug.Print "alertTime = " & alertTime

    this is the results from the immediate window, from above I entered 02 for MM
    testStr = "00:02:00"
    alertTime = 00:02:00

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,458

    Re: set timer for a excel spread sheet using a variable for the time value

    This is not VB.NET code. Thread moved to Office Development forum, which is where VBA questions belong. Even VB.NET code that is Office-specific can be posted in Office Development.

  3. #3
    Addicted Member
    Join Date
    Jul 2022
    Posts
    242

    Re: set timer for a excel spread sheet using a variable for the time value

    This worked for me:

    Code:
    Private Sub CommandButton1_Click()
    
        Dim alertTime As Variant
        Dim alertTimeHH As String
        Dim alertTimeMM As String
        Dim alertTimeSS As String
        
        alertTimeHH = InputBox("update time interval hours, default = 00", "00")
        alertTimeMM = InputBox("update time interval minutes, default = 00", "00")
        alertTimeSS = InputBox("update time interval seconds, default = 00", "00")
        testStr = """" & alertTimeHH & ":" & alertTimeMM & ":" & alertTimeSS & """"
        alertTime = alertTimeHH & ":" & alertTimeMM & ":" & alertTimeSS
        Debug.Print "testStr = " & testStr 'just for testing
        Debug.Print "alertTime = " & alertTime
    
        Debug.Print DateTime.Now + CDate(alertTime)
        
    End Sub
    Attached Images Attached Images  

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

    Re: set timer for a excel spread sheet using a variable for the time value

    TimeValue expects a (quoted?) Time-String
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE 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.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width