How Do I create a Timing Tool

T

timharding

All,
I wonder if I can call on your expert advice.

I am looking to create a Timing Tool within Excel which will be able t
capture timings of specific user defined areas.

For Example looking to time specific sections of say for a telephon
call and the interaction the agent has with the Computer System..ho
long specific actions take...and then from that be able to submit th
data into a sheet..and move on to another one.

This is so that summary data can be captured and analysed.

All help appreciated.

Regards

Tim Hardin
 
B

Bob Phillips

Tim,

It depends upon the precision required.

Is it not sufficient to capture the time (NOW) at the start and the end, and
subtract one from the other? I am assuming you are okay using VBA with it?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

timharding

Not Really Bob..

for example....wanna capture from start to end with action /
transactions in the middle..

want to be able to log a series of different events in the tool....

Time a process and break it down into different transactions....and
record the time of each transaction and thw whole process.

Tim

happy with VBA.
 
B

Bob Phillips

Tim,

Here is an example in VBA. See what I mean about precision.

Dim nTime As Date
Dim i As Long

nTime = Now
For i = 1 To 10000000
Next i
Debug.Print "Transaction 1 - " & Format(Now - nTime, "hh:mm:ss.00")
nTime = Now
For i = 1 To 105900000
Next i
Debug.Print "Transaction 2 - " & Format(Now - nTime, "hh:mm:ss.00")
nTime = Now
For i = 1 To 302100000
Next i
Debug.Print "Transaction 3 - " & Format(Now - nTime, "hh:mm:ss.00")
nTime = Now
For i = 1 To 789100000
Next i
Debug.Print "Transaction 4 - " & Format(Now - nTime, "hh:mm:ss.00")

You could output the timings to an Excel sheet, to a text file, or whatever.
ARe you happy with this, or do you want details? If the latter, tell me
which you prefer.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

timharding

Many Thanks Bob...

Sorry the latter..dont suppose you have like a working example..not
really ofey with VBA...something that I can play with..yes submitting
to another sheet within the same Excel file would be great.

Many Thanks again Bob

My work email is

[email protected]
 
B

Bob Phillips

Tim,

Here is a sample that outputs to the active Excel sheet. I have included 2
transactions as examples, you should replace this with calls to your
transactions that you wish to log

Sub TimsMacro()
Dim nTime As Date
Dim i As Long

Cells(1, "A").Value = "Application Step"
Cells(1, "B").Value = "Time"
Cells(1, "C").Value = "Elapsed"
Range("A1:C1").Font.Bold = True

i = 2
nTime = Now
LogTime nTime, "Application started", i, False

transaction1
i = i + 1
LogTime Now, "Transaction1", i
nTime = Now

transaction2
i = i + 1
LogTime Now, "Transaction2", i
nTime = Now

i = i + 1
nTime = Now
LogTime nTime, "Application ended", i, False

Columns("A:C").AutoFit

End Sub


This is the bit that logs it

Private Sub LogTime(nTime As Date, msg As String, nRow As Long, Optional
Elapsed As Boolean = True)
Cells(nRow, "A").Value = msg
With Cells(nRow, "B")
.Value = nTime
.NumberFormat = "hh:mm:ss.00"
End With

If Elapsed Then
With Cells(nRow, "C")
.FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
.NumberFormat = "hh:mm:ss.00"
End With
End If

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top