Keeping a running time total

[Jim]

I would like to use Excel for a television program director's rundown
sheet. Is there a simple formula that would show running time by
segment from the start of the program to the end? In other words, the
program would start at 00:00:00 and end at 30:00:00. I would like one
column to keep a running time total for each element of the program.
Thanks for your help!

 

[mms]

Perhaps something like this:

Module code:

Code:
--------------------

Option Explicit


Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long) As Long

Public Declare Function GetTickCount Lib "kernel32" () As Long


Public TimerID As Long
Public TimeStarted As Long

Sub StartTimer()
TimerID = SetTimer(0&, 0&, 1, AddressOf TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
Sheet1.Cells(1, 1).Value = CInt((GetTickCount - TimeStarted) / 10)
End Sub

--------------------


Worksheet code:

Code:
--------------------

Option Explicit


Private Sub CommandButton1_Click()
TimeStarted = GetTickCount
Call StartTimer
End Sub

Private Sub CommandButton2_Click()
Call EndTimer
Sheet1.Cells(1, 1).Value = ""
End Sub

 

[Chip Pearson]

Using Windows Timers in VBA is dangerous. Any number of actions
can cause Excel to crash. Under some circumstances even selecting
another cell will cause a crash.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com