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

[bbxrider]

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

[jmcilhinney]

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.

[jdelano]

This worked for me:

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

[Zvoni]

TimeValue expects a (quoted?) Time-String