INPLAY TIMER

[BERTRAND]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If updating Then Exit Sub
updating = True
If Cells(2, 5) = "In Play" Then
If Cells(1, 27) = "" Then Cells(1, 27) = Cells(2, 3)
If Cells(1, 27) <> "" Then Cells(1, 28) = DateDiff("s", Cells(1, 27), Cells(2, 3))
End If
If Cells(2, 5) <> "In Play" And Cells(2, 6) <> "Suspended" Then
Cells(1, 27) = ""
Cells(1, 28) = ""
End If
updating = False
End Sub

I am using Gary's timer as above and would like to change it so that it is accurate to 0.2sec(200ms) increments.
Thanks

[BERTRAND]

I don't know how the smilies got in their

[GaryRussell]

I don't know how the smilies got in their

When typing your message click the code button underneath the subject box, paste the code then press the code button again. It will then appear without smilies. I have edited your post.

[BERTRAND]

Well, I found this:- but I'm not sure how to integrate it.


if you really want to get exact...

Create a new class module called StopWatch
Put the following code in the StopWatch class module:

'*********** Start Code ****************

Code: Select all

Private mlngStart As Long
Private Declare Function GetTickCount Lib "kernel32" () As Long

Public Sub StartTimer()
    mlngStart = GetTickCount
End Sub

Public Function EndTimer() As Long
    EndTimer = (GetTickCount - mlngStart)
End Function

' *********** End Code ****************

You use the code as follows:

' *********** Start Code **************

Code: Select all

Dim sw as StopWatch
Set sw = New StopWatch
sw.StartTimer

' Do whatever you want to time here

Debug.Print "That took: " & sw.EndTimer & "milliseconds"

' ********* End Code *************

Remember that this returns milliseconds that have elapsed...so to get seconds, you must divide the result by 1000, minutes by 60000, etc.

Hope this helps you out.....

[BERTRAND]

On second thoughts, maybe it's not such a good idea to have a millisecond timer running in my triggersheet.
For 3 weeks I was triggering at 1 second(>=)Inplay, but the last 4 days every single matched bet the stake is 10-20% more than it should be. My stake is calculated on SP and "Kelly" criteria. It seems strange that all of a sudden 1second is too short to reconcile SP.

[osknows]

Hi Bert,

A simpler way is utilize the full datetime stamp in cell B2 instead of C2 which only contains the truncated time.

Follow these steps

1. Use this code instead (replacing Sheet1 with the codename of your sheet)

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
If updating Then Exit Sub
Application.EnableEvents = False
updating = True
With Sheet1
    If .Cells(2, 5) = "In Play" Then
    If .Cells(1, 27) = "" Then .Cells(1, 27) = .Cells(2, 2)
    If .Cells(1, 27) <> "" Then .Cells(1, 28) = .Cells(2, 2) - .Cells(1, 27)
    End If
    If .Cells(2, 5) <> "In Play" And .Cells(2, 6) <> "Suspended" Then
    .Cells(1, 27) = ""
    .Cells(1, 28) = ""
    End If
End With
updating = False
Application.EnableEvents = True
End Sub

2. In cell [AB1] right click and Format Cells/Custom/ and type in hh:mm:ss.000

[BERTRAND]

Thanks Os
I will modify

[Tarmac]

Folks. I'm new to VBA so would appreciate some assistance. I'm using the code below (found on the forum) to record min/max in running prices. I would also like to record the in running time. How would I add the Timer code to my VBA macro below so both would work?? Thanks for any help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iRow As Integer
Dim iCol As Integer
Dim cell As Object

For Each cell In Target
iRow = cell.Row
iCol = cell.Column


' Update Max and Min Back Values
If Cells(2, 5) = "Not In Play" And iCol = 15 And iRow > 4 And iRow < 46 Then

Application.EnableEvents = False
If IsEmpty(Range("AJ" & iRow).Value) Then Range("AJ" & iRow).Value = 0
If IsEmpty(Range("AI" & iRow).Value) Then Range("AI" & iRow).Value = 1001
If IsEmpty(Range("AH" & iRow).Value) Then Range("AH" & iRow).Value = Range("O" & iRow).Value

If Not IsEmpty(cell.Value) Then
Range("AG" & iRow).Value = Range("O" & iRow).Value
If cell.Value < Range("AI" & iRow).Value And cell.Value > 1 Then Range("AI" & iRow).Value = cell.Value
If cell.Value > Range("AJ" & iRow).Value And cell.Value < 1001 Then Range("AJ" & iRow).Value = cell.Value
End If

Application.EnableEvents = True
End If



Next cell

End Sub

[BERTRAND]

I have just changed my elapsed time format to (hh:mm:ss.000) and I want to include this in an "IF" function -ie:- =IF(A1>"00:00:01.200",
But I am doing something wrong because it doesn't work!
Can someone put me right?
Thanks in advance
Bert

[Captain Sensible]

Excel handles time as a decimal number but can format it into a regular time format that we can undestand. Your formula is treating the time as text A1>"00:00:01.200" so they won't match.

There is a function in excel for time TIMEVALUE, but I don't think that recognises milliseconds. Easiest work around is just to stick you time value 00:00:01.200 into say cell B1 and use =IF(A1>B1,

[BERTRAND]

Cheers Cap'n
I spend hours trawling through the Excel help sites, but the salient information I just don't take onboard!