Getting elapsed time

[Saucer Man]

I would like to display the elapsed time a macro takes to run in minutes &
seconds. I only need seconds to be two places. How do I do this?

 

[Bernard Liengme]

If you can forego some precision and work only in second, then use Timer
In the demo below I use Select to make an inefficient macro

Sub tryme()
Start = Timer
For j = 1 To 10000
Cells(j, 1).Select
Next j
Cells(1, 1).Select
Cells(1, 1) = (Timer - Start) / (60 * 60)
Selection.NumberFormat = "[hh]:mm:ss"
End Sub

best wishes

 

[Gary''s Student]

See:

http://support.microsoft.com/default.aspx/kb/q172338

 

[Bernard Liengme]

To get more precision than the Timer function gives, see
http://msdn.microsoft.com/en-us/library/aa730921.aspx
best wishes

 

[Saucer Man]

This isn't working for me. I am using your code in an example which
displays the time in a message box(I don't want it in a cell).

Start = Timer

For Count = 1 To 100000
Sheet1.Cells(1, 1) = "test"
Next Count

Start = (Timer - Start) / (60 * 60)
MsgBox Format(Start, "[hh]:mm:ss")

The msgbox shows :12:44. It should show 4 secs. What is wrong?

If you can forego some precision and work only in second, then use Timer
In the demo below I use Select to make an inefficient macro

Sub tryme()
Start = Timer
For j = 1 To 10000
Cells(j, 1).Select
Next j
Cells(1, 1).Select
Cells(1, 1) = (Timer - Start) / (60 * 60)
Selection.NumberFormat = "[hh]:mm:ss"
End Sub

best wishes

 

[Bernard Liengme]

Use MsgBox Format(Start, "h:mm:ss")
The [hh] notation works in Excel but not in VBA
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

This isn't working for me. I am using your code in an example which
displays the time in a message box(I don't want it in a cell).

Start = Timer

For Count = 1 To 100000
Sheet1.Cells(1, 1) = "test"
Next Count

Start = (Timer - Start) / (60 * 60)
MsgBox Format(Start, "[hh]:mm:ss")

The msgbox shows :12:44. It should show 4 secs. What is wrong?

If you can forego some precision and work only in second, then use Timer
In the demo below I use Select to make an inefficient macro

Sub tryme()
Start = Timer
For j = 1 To 10000
Cells(j, 1).Select
Next j
Cells(1, 1).Select
Cells(1, 1) = (Timer - Start) / (60 * 60)
Selection.NumberFormat = "[hh]:mm:ss"
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I would like to display the elapsed time a macro takes to run in minutes
& seconds. I only need seconds to be two places. How do I do this?

 

[Saucer Man]

Hmmmmm...it's still now giving the correct time. Now it shows 0:01:43. It
should show 0:00:04 since it only takes 4 seconds to run.

Use MsgBox Format(Start, "h:mm:ss")
The [hh] notation works in Excel but not in VBA
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

This isn't working for me. I am using your code in an example which
displays the time in a message box(I don't want it in a cell).

Start = Timer

For Count = 1 To 100000
Sheet1.Cells(1, 1) = "test"
Next Count

Start = (Timer - Start) / (60 * 60)
MsgBox Format(Start, "[hh]:mm:ss")

The msgbox shows :12:44. It should show 4 secs. What is wrong?

If you can forego some precision and work only in second, then use Timer
In the demo below I use Select to make an inefficient macro

Sub tryme()
Start = Timer
For j = 1 To 10000
Cells(j, 1).Select
Next j
Cells(1, 1).Select
Cells(1, 1) = (Timer - Start) / (60 * 60)
Selection.NumberFormat = "[hh]:mm:ss"
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I would like to display the elapsed time a macro takes to run in minutes
& seconds. I only need seconds to be two places. How do I do this?

 

[ryguy7272]

'Paste these two lines of code just after the beginning of the code
Dim sngStart As Double
sngStart = Now

'Paste these three lines of code just before the end of the code
MsgBox "Process Complete!! " & Counter & _
" File Updated" & vbNewLine & _
" took " & Format(Now - sngStart, "hh:mm:ss")

Regards,
Ryan---

 

[joeu2004]

Hmmmmm...it's still now giving the correct time.
Now it shows 0:01:43. It should show 0:00:04
since it only takes 4 seconds to run.

Bernard forgot to divide by 24. Also, I'm not sure what you mean by "I only
need seconds to be two places". Usually, the term "two places" refers to
decimal fractions. Finally, originally you said you want just minutes and
seconds, not hours.

Try the following macro. Uncomment the For statement if you want to test
with non-zero minutes. Note: The VBA Format function is not as robust as
Excel custom formats.

Public Declare Sub Sleep Lib "kernel32" (ByVal msec As Long)

Sub doit()
Dim st As Double, et As Double, sec As Double
Dim i As Integer, min As Integer
st = Timer
' press ctrl-G to see Debug.Print output
'For i = 1 To 60: Sleep (1000): Debug.Print i; "sec": Next
Sleep (1234)
et = Timer
sec = et - st
Range("A1") = sec / 86400
Range("A1").NumberFormat = "mm:ss.00"
MsgBox Format(sec / 86400, "h:mm:ss")
' for true mm:ss.00 format in MsgBox
min = Int(sec / 60)
sec = sec - min * 60
MsgBox Format(min, "0") & ":" & Format(sec, "0.00")
End Sub


----- original posting -----

Hmmmmm...it's still now giving the correct time. Now it shows 0:01:43. It
should show 0:00:04 since it only takes 4 seconds to run.

Use MsgBox Format(Start, "h:mm:ss")
The [hh] notation works in Excel but not in VBA
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

This isn't working for me. I am using your code in an example which
displays the time in a message box(I don't want it in a cell).

Start = Timer

For Count = 1 To 100000
Sheet1.Cells(1, 1) = "test"
Next Count

Start = (Timer - Start) / (60 * 60)
MsgBox Format(Start, "[hh]:mm:ss")

The msgbox shows :12:44. It should show 4 secs. What is wrong?


If you can forego some precision and work only in second, then use Timer
In the demo below I use Select to make an inefficient macro

Sub tryme()
Start = Timer
For j = 1 To 10000
Cells(j, 1).Select
Next j
Cells(1, 1).Select
Cells(1, 1) = (Timer - Start) / (60 * 60)
Selection.NumberFormat = "[hh]:mm:ss"
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I would like to display the elapsed time a macro takes to run in minutes
& seconds. I only need seconds to be two places. How do I do this?