Feeding a Variable to Application.Wait?

[Kentetsu]

Hi all,

I'm attempting to reuse strTime as the variable for Application.Wait but I'm getting a "Type Mismatch" when it gets to that point. Any idea on how to make this work?

Dim strMsg      As String
Dim strTime     As String

'-------------------------------------------------------------------------------------------------------------------------------
If Sheets("Scrap").Range("E13").Value = "End" Then
Call EndSession

strMsg = _
"Your 1st Ingredient(s) = @2ING@1@1And will mix for: @time Minutes.  @1@1 Add the ingredient(s), THEN Click OK to Begin Timing"

With Sheets("Scrap")
        strTime = .Cells(14, 5).Text
        strMsg = Replace(strMsg, "@2ING", .Cells(13, 5).Value)
        strMsg = Replace(strMsg,  @time", strTime)
        MsgBox Replace(strMsg, "@1", vbCrLf), vbOKOnly, "First Ingredient(s)"
    End With
    
Sheets("Stop").Visible = True
Sheets("Go").Visible = xlVeryHidden
    
Application.Wait (Now + TimeValue(strTime))

Sheets("Go").Visible = True
Sheets("Stop").Visible = xlVeryHidden
End If

 

[joeu2004]

I'm getting a "Type Mismatch" when it gets to that point. Any idea on how to make this work?

No, because you neglect to show us the value of strTime.

strTime should be an amount of time in the form "h:m:s". Try the following demonstration.

Sub doit()
Dim n As Date, strTime As String
strTime = "0:0:3"
n = Now
Application.Wait Now + TimeValue(strTime)
MsgBox n & vbNewLine & Now
End Sub

PS.... Application.Wait (Now + TimeValue(strTime)) works just as well. But the extra parentheses are unnecessary.

 

[Norie]

Have you checked what the value of strTime is when you get the error?

 

[Kentetsu]

Thanks for the reply. Got it working with this just as I got your reply.

Application.Wait (Now + TimeSerial(0, Worksheets("Scrap").Range("$E$14").Value, 0))

Not ideal, but workable. Thanks again...

 

[Kentetsu]

strTime = 2:00

 

[joeu2004]

strTime = 2:00

Application.Wait (Now + TimeSerial(0, Worksheets("Scrap").Range("$E$14").Value, 0))

If strTime was "2:00", then E14 displayed 2:00. So I suspect you should write:

Application.Wait Now + TimeValue(Worksheets("Scrap").Range("$E$14").Text/60)

Based on your Wait/TimeSerial statement, I assume that 2:00 should be interpreted as 2 min 0 sec.

-----

Your Wait/TimeSerial statement is probably not waiting at all.

If your TimeSerial expression works and E14 displays 2:00, then the value in E14 probably is numeric time.

But 2:00 is represented internally as a fraction of a day, namely 2/24 = 0.0833333333333333 if 2:00 is 2 hr 0 min, or 2/60/24 = .0.00138888888888889 if 2:00 is 2 min 0 sec.

In that case, your TimeSerial expression returns zero because either fraction is rounded to 0 minutes.

Your Wait/TimeSerial statement would work as intended (i.e. wait until Now plus 2 min) only if the value in E14 is the string "2" or the numeric value 2 (when rounded).