Getting elapsed time
 

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?

--
Thanks!

Getting elapsed time
 

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

"Saucer Man" wrote in message
...
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?

--
Thanks!

Getting elapsed time
 

See:

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


--
Gary''s Student - gsnu200837


"Saucer Man" wrote:

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?

--
Thanks!

Getting elapsed time
 

To get more precision than the Timer function gives, see
http://msdn.microsoft.com/en-us/library/aa730921.aspx
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Saucer Man" wrote in message
...
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?

--
Thanks!

Getting elapsed time
 

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?


"Bernard Liengme" wrote in message
...
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

"Saucer Man" wrote in message
...
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?

--
Thanks!

Getting elapsed time
 

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

"Saucer Man" wrote in message
...
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?


"Bernard Liengme" wrote in message
...
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

"Saucer Man" wrote in message
...
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?

--
Thanks!

Getting elapsed time
 

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 Liengme" wrote in message
...
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

"Saucer Man" wrote in message
...
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?


"Bernard Liengme" wrote in message
...
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

"Saucer Man" wrote in message
...
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?

--
Thanks!

Getting elapsed time
 

'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---


--
RyGuy


"Gary''s Student" wrote:

See:

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


--
Gary''s Student - gsnu200837


"Saucer Man" wrote:

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?

--
Thanks!

Getting elapsed time
 

"Saucer Man" wrote:
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 -----

"Saucer Man" wrote:
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 Liengme" wrote in message
...
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

"Saucer Man" wrote in message
...
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?


"Bernard Liengme" wrote in message
...
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

"Saucer Man" wrote in message
...
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?

--
Thanks!