Recording real time interval after event trigger

[pkoelkebeck]

Please help! I'm not an expert using Excel or with VBA, but I would really like to know how I can perform this operation in Excel. If there is an addin or program I could get, then please point me in the right direction. If you know how I could do it, I would be very grateful for the help.

Here's what I need to do:

I have already set up a real time monitor (in cell m3) that updates from the system time whenever a change is made anywhere on the sheet (or by running a macro to automate the updates). The clock is in hour, minute, and second format. I want to be able to track the exact minutes and seconds from that clock.

Basically, I need to find out how I can start a timer to track the time elapsed after an event occurs. Here's the path I would like the operation to follow:

1. Cell (h12) value reads "GO"

2. That "GO" event triggers a record or log of the exact time on the real-time clock (in cell m3) to be made and displayed in a cell (p2).

3. Another cell (p3) will keep a continuous update of the elapsed time since the log of the exact time the event occured (p2).

4. Final cell (p14) will indicate whether the elapsed time (p3) is greater than or equal to a specified time length (b9) by reading "YES".

I know this may be somewhat complicated, but I need to implement this operation for my research. Any help in the right direction will be greatly appreciated. Thanks so much. -Paul

 

[Legacy 98055]

Hi Paul.

The following is an example based upon a few assumptions. You did not mention what the specified time length in (b9) was? A number of minutes and/or seconds?

What happens when (h12) changes from "GO" to some other value?

The example, as is, assumes a number of elapsed seconds in (h9) as the "YES" threshold. If (h12) changes from "GO" to some other value, logging is halted without changing any values.

<a href="http://home.fuse.net/tstom/0710080652.329162.zip"><img src="http://home.fuse.net/tstom/zip.gif"width="48"height="48"border="0"></a> <a href="http://home.fuse.net/tstom/0710080652.329162.zip">0710080652.329162.zip</a>

Code in the workbook class:

Private Sub Workbook_Open()
    Sheet1.StartClock
End Sub

Code in worksheet:

Option Explicit

Private RunNext As Date
Private PreviousValue As String
Private IsTracking As Boolean

Friend Sub StartClock()
    IsTracking = False
    Tick
End Sub

Friend Sub Tick()
    Range("M3").Value = Now
    RunNext = Now + TimeSerial(0, 0, 1)
    Application.OnTime RunNext, Me.CodeName & ".Tick"

    If IsTracking Then
        Range("P3").Value = Range("M3").Value - Range("P2").Value
        If DateDiff("s", Range("P2").Value, Range("M3").Value) >= Range("B9").Value Then
            Range("P14").Value = "YES"
        Else
            Range("P14").Value = "NO"
        End If
    End If
    
    If Range("H12").Value <> PreviousValue And Range("H12").Value = "GO" Then
        Range("P2").Value = Now
        IsTracking = True
    ElseIf Range("H12").Value <> PreviousValue And PreviousValue = "GO" Then
        IsTracking = False
    End If
    
    PreviousValue = Range("H12").Value
    
End Sub

Friend Sub StopClock()
    On Error Resume Next
    Application.OnTime RunNext, Me.CodeName & ".Tick", , False
End Sub

 

[Wayne01]

Compile error on example occurs at "Friend Sub StartClock()". Well it does excel 97.