Machine timing

[Brian222]

Hi, i am conducting machine timings, sort of work study, currently using a stop watch and 'laptiming' certain points in the process then inputting the times to excel to process them, was wondering if there was anyway of integrating a stopwatch into excel and making it input the times in serperate cells down a column as i would click a button to define the break points? any help would be greatly appreciated

 

[VoG]

Hi

Here is some code.

Public t As Date

Sub startwatch()
t = Now
End Sub

Sub stopwatch()
ActiveCell.Value = Format(t - Now, "hh:mm:ss")
ActiveCell.Offset(1, 0).Select
End Sub

To use this press ALT + F11 to open the Visual Basic Editor, Insert > Module, paste in the code and close the VBE. Then from the Forms toolbar (View > Toolbars > Forms) add two buttons. Assign one to startwatch and the other to stopwatch.

Position the cursor at the top of a column and click the startwatch button. When the machine does something clock the stopwatch button. And so on... the cursor will move one cell down the column every time you press the stopwatch button.

 

[Brian222]

great thanks,works perfectly was just wondering how to get it so that it puts in the "laptime" and not the cumlative time and is it possible to record the time in centiminutes??

Thanks

 

[VoG]

Assuming that a centiminute is a hundredth of a minute try this

Public t As Date

Sub startwatch()
t = Now
End Sub

Sub stopwatch()
ActiveCell.Value = Format((Now - t) * 144000, "0")
ActiveCell.Offset(1, 0).Select
End Sub

 

[Legacy 98055]

VOG. Are you sure that vba.now has a resolution of .01? I cannot do better than 1 second... Excel's Now() seems to offer .01 res. The timer function, in windows, offers .001 but the midnight reset always made using that a pain.

Brian. Put a button on your worksheet and call TickTock from it's click event.

Option Explicit
 
Declare Function GetTickCount Lib "kernel32" () As Long
 
Private Const Millisecond As Double = 1.15740740740741E-08
Private TickCount As Long
Private CurrentTime As Double
Private r As Range
 
Sub TickTock()
    Dim NewTickCount As Long
 
    NewTickCount = GetTickCount
 
    If TickCount = 0 Then
        Set r = Range("A1:B1")
        r(1) = 0
        r(2) = "Total Time"
        r.Columns.EntireColumn.NumberFormat = "hh:mm:ss.000"
    Else
        r(1) = (NewTickCount - TickCount) * Millisecond
        r(2).Formula = "=Sum(A1:" & r(1).Offset.Address & ")"
    End If
 
    Set r = r.Offset(1)
    TickCount = NewTickCount
End Sub

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

 

[VoG]

Right_Click: much better than my primitive effort