VBA Timer

[VegasNath]

Code: Select all

Sub Timer()

Dim TimeBeg As Variant, TimeEnd As Variant
Dim Msg As String

TimeBeg = Format(Now(), "ddd dd mmm yyyy - hh:mm:ss")

'Do some other stuff

TimeEnd = Format(Now(), "ddd dd mmm yyyy - hh:mm:ss")

Msg = Msg & vbCrLf & vbCrLf & "Process Started - " & TimeBeg
Msg = Msg & vbCrLf & vbCrLf & "Process Completed - " & TimeEnd
Msg = Msg & vbCrLf & vbCrLf & "Completion Time = " & Format(TimeValue(Right(TimeEnd, 8)) - TimeValue(Right(TimeBeg, 8)), "hh:mm:ss")
        
MsgBox Msg, vbInformation

End Sub

Is there a way that I can have the message report in format "08 minutes & 09 seconds"?

[agibsonsw]

I would prefer to set the variables as Date types so I can just subtract to find the time difference. Then use Format() when you want to display the results.

Code: Select all

Sub TimeThis()
    Dim TimeBeg As Date
    Dim TimeEnd As Date
    Dim TimeDiff As Date
    
    TimeBeg = Now
    MsgBox "Wait for this"
    TimeEnd = Now
    TimeDiff = TimeEnd - TimeBeg
    MsgBox Format(TimeDiff, "nn") & " minutes & " & Format(TimeDiff, "ss") & " seconds"
    
End Sub

[VegasNath]

Thankyou, that works great.