Get time it takes to run program!!

[cjsasl]

Hello all, I have a macro set-up to check a through a range of cells looking for members code 1 & changing it to a sequential number & dependent code 2 & setting it to null.

What I'd like to do is when it pops up the msgbox at the end informing the user how many members records were changed & dependent records changed I can also include something like "Task completed in ## seconds".

How do I set up a timer to count of the seconds from the start of the code until the last record has been changed?

Do you need me to post the code I have written so far?

Thanks in advance,

eric

[Ken Macksey]

Hi

While not extremely accurate, you could do something like this which will
give you a rough idea of the time.

Dim totaltime As Long
Dim Starttime As Long
Dim Endtime As Long
Dim Min as Integer
Dim Sec as Integer



starttime = Now()


'Your code here



Endtime = Now()

totaltime = DateDiff("s", starttime, endtime)

'convert seconds into minutes and seconds to display
Min = Int(totaltime \ 60)
sec = totaltime Mod 60

Totaltimetaken = Min & " Minute(s) and " & sec & " Seconds."

MsgBox(Totaltimetaken)



HTH

Ken

[Jim Rech]

Sub a()
Dim StartTime As Double
Dim Counter As Long
StartTime = Timer
For Counter = 1 To 100000
DoEvents
Next
MsgBox Format(Timer - StartTime, "0") & " seconds"
End Sub


--
Jim
"cjsasl" <[email protected]> wrote in
message news:[email protected]...
|
| Hello all, I have a macro set-up to check a through a range of cells
| looking for members code 1 & changing it to a sequential number &
| dependent code 2 & setting it to null.
|
| What I'd like to do is when it pops up the msgbox at the end informing
| the user how many members records were changed & dependent records
| changed I can also include something like "Task completed in ##
| seconds".
|
| How do I set up a timer to count of the seconds from the start of the
| code until the last record has been changed?
|
| Do you need me to post the code I have written so far?
|
| Thanks in advance,
|
| eric
|
|
| --
| cjsasl
| ------------------------------------------------------------------------
| cjsasl's Profile:
http://www.excelforum.com/member.php...o&userid=28712
| View this thread: http://www.excelforum.com/showthread...hreadid=484006
|

[cjsasl]

Ken I tried your code but it keeps displaying 0:0 for the time

Jim yours won't work where I'm trying to display the time in a seperate function message box.

Below is the code I have written.

Down in the Function CheckCell() section under the If (Vol = "") then statement is were I am trying to get the elapse time to be stored to use in the msgbox below it.

Hope this helps



------------Excel Code----------
Option Explicit
Dim MemRecords As Integer ' Creates MemRecords memory variable for numeric values.
Dim TotalRecords As Integer ' Creates TotalRecords memory variable for numeric values.
Dim Vol As String ' Creates Vol memory variable for string values.
Dim StartPoint As String ' Creates StartingPoint memory variable for string values.
Dim TotalTime As Long
Dim StartTime As Double

' Start section looks for the first cell that contains the Members code
' or the code for Depentent, then resets the main count variables to 0.
Sub Start()

On Error Resume Next ' if any errors encountered continue without crashing macro.

StartPoint = "A1" ' Stores the cell number A1 in the StartPoint variable.
Range(StartPoint).Select 'Moves the pointer to the StartPoint value.
Vol = ActiveCell.Value ' Stores the contents of the active cell in the Vol variable.

' Checks each cell from A1 down until it finds a cell containing a 1 or 2.
Do Until (Vol = 1) Or (Vol = 2) ' Checks the contentes of current cell for a 1 or 2 value.
ActiveCell.Offset(1, 0).Select ' Moves the cursor down 1 cell & selects it.
Vol = ActiveCell.Value ' Stores the contents of the current cell in the Vol variable.
Loop ' Loops back the check the value in Vol variable for a 1 or 2.

' The Do loop has completed the search for the start of the Members code and now the
' commands below are run.
MemRecords = 0 ' Sets MemRecords to 0.
TotalRecords = 0 ' Sets TotalRecords to 0.
CheckCell ' Tells the macro to jump to the CheckCell Sun routine.
End Sub

' This CheckCell Sub routine checks to see if the contents of the cells are a 1 & if so
' changes it to the next incremented number. If the cell contains a 2 then change it to
' empty.


Function CheckCell()

' Stores the contents of the current cell to the Vol variable before it checks to verify
' if the cell contents is a 1 or 2 to make the correct changes.
Vol = ActiveCell.Value

' When Vol = "" then the current cell being checked means the cell is empty & last record was found.
If (Vol = "") Then ' Checking to see if an empty cell is found which denotes the end of the records.
ActiveCell.Offset(-1, 0).Select ' When the 1st empty cell is found on this moves the cursor back up to last cell containing a 1 or 2.
' MsgBox command below displays the message that the search has been completed & shows how many Member & Dependent records were changed & the total records searched.

(I want to get the time it too to run code set to TotalTime variable here to use in msgbox statement below)

MsgBox MemRecords & " Member records were numbered in sequence." & vbCrLf & TotalRecords - MemRecords & " Dependent records were set to Null." & vbCrLf & "-----" & vbCrLf & TotalRecords & " Total records found/changed.", vbOKOnly, "ClaimSecure's Members Eligibility Search & Replace -Kim Blake-" ' When a 1 is found in the current cell being checked the code below is run."
Range(StartPoint).Select ' Moves the cursor back to the A1 starting point after the clicks Ok on the Message box from line above."
End ' Ends the macro since the blank cell at bottom of spreadsheed has been found.

' When a 1 is found in the current cell being checked the code below is run.
ElseIf (Vol = 1) Then ' Checks to verify the cell contains a 1 then runs the code below.
ActiveCell.Formula = MemRecords + 1 ' Changes the value of the current cell from the 1 to the next number in the sequence.
ActiveCell.Offset(1, 0).Select ' Drops down one cell & selects it.
MemRecords = MemRecords + 1 ' Changes the value in the MemRecords variable by adding 1 to it.
TotalRecords = TotalRecords + 1 ' Changes the value in the TotalRecords variable by adding 1 to the total.
CheckCell ' Calls the CheckCell Sub rouutine again to check the value in the cell it just moved to.

' When a 2 is found in the current cell being checked the code below is run.
ElseIf (Vol = 2) Then ' Checks to verify the cell contains a 2 then runs the code below.
ActiveCell.Formula = Null ' Changes the 2 value in the cell and set it to Null/empty.
ActiveCell.Offset(1, 0).Select ' Drops down to the next cell and selects it.
TotalRecords = TotalRecords + 1 ' Changes the value in the TotalRecords variable by adding 1 to the total.
CheckCell ' Calls the CheckCell Sub rouutine again to check the value in the cell it just moved to.

End If
End Function

[cjsasl]

one other thing, if you try to copy this code & run it you need a spread sheet open with a couple of celles with the value 1 or 2 in them starting in the A1 cell and working down, it stops at the 1st empy cell.

eric

[Tom Ogilvy]

Sub Start()

On Error Resume Next ' if any errors encountered continue without
crashing macro.
StartTime = Timer

--------------------------
then just before your message box

TotalTime = timer - StartTime


By dimming TotalTime as Long, you will only get whole seconds, but this
shouldn't be a problem it your routine takes more than a second.

--
Regards,
Tom Ogilvy



"cjsasl" <[email protected]> wrote in
message news:[email protected]...
>
> Ken I tried your code but it keeps displaying 0:0 for the time
>
> Jim yours won't work where I'm trying to display the time in a seperate
> function message box.
>
> Below is the code I have written.
>
> Down in the Function CheckCell() section under the If (Vol = "") then
> statement is were I am trying to get the elapse time to be stored to
> use in the msgbox below it.
>
> Hope this helps
>
>
>
> ------------Excel Code----------
> Option Explicit
> Dim MemRecords As Integer ' Creates MemRecords memory variable for
> numeric values.
> Dim TotalRecords As Integer ' Creates TotalRecords memory variable for
> numeric values.
> Dim Vol As String ' Creates Vol memory variable for string values.
> Dim StartPoint As String ' Creates StartingPoint memory variable for
> string values.
> Dim TotalTime As Long
> Dim StartTime As Double
>
> ' Start section looks for the first cell that contains the Members
> code
> ' or the code for Depentent, then resets the main count variables to
> 0.
> Sub Start()
>
> On Error Resume Next ' if any errors encountered continue without
> crashing macro.
>
> StartPoint = "A1" ' Stores the cell number A1 in the StartPoint
> variable.
> Range(StartPoint).Select 'Moves the pointer to the StartPoint value.
> Vol = ActiveCell.Value ' Stores the contents of the active cell in the
> Vol variable.
>
> ' Checks each cell from A1 down until it finds a cell containing a 1 or
> 2.
> Do Until (Vol = 1) Or (Vol = 2) ' Checks the contentes of current cell
> for a 1 or 2 value.
> ActiveCell.Offset(1, 0).Select ' Moves the cursor down 1 cell &
> selects it.
> Vol = ActiveCell.Value ' Stores the contents of the current cell in
> the Vol variable.
> Loop ' Loops back the check the value in Vol variable for a 1 or 2.
>
> ' The Do loop has completed the search for the start of the Members
> code and now the
> ' commands below are run.
> MemRecords = 0 ' Sets MemRecords to 0.
> TotalRecords = 0 ' Sets TotalRecords to 0.
> CheckCell ' Tells the macro to jump to the CheckCell Sun routine.
> End Sub
>
> ' This CheckCell Sub routine checks to see if the contents of the cells
> are a 1 & if so
> ' changes it to the next incremented number. If the cell contains a 2
> then change it to
> ' empty.
>
>
> Function CheckCell()
>
> ' Stores the contents of the current cell to the Vol variable before it
> checks to verify
> ' if the cell contents is a 1 or 2 to make the correct changes.
> Vol = ActiveCell.Value
>
> ' When Vol = "" then the current cell being checked means the cell
> is empty & last record was found.
> If (Vol = "") Then ' Checking to see if an empty cell is found
> which denotes the end of the records.
> ActiveCell.Offset(-1, 0).Select ' When the 1st empty cell is
> found on this moves the cursor back up to last cell containing a 1 or
> 2.
> ' MsgBox command below displays the message that the search has
> been completed & shows how many Member & Dependent records were changed
> & the total records searched.
>
> (I want to get the time it too to run code set to TotalTime variable
> here to use in msgbox statement below)
>
> MsgBox MemRecords & " Member records were numbered in sequence." &
> vbCrLf & TotalRecords - MemRecords & " Dependent records were set to
> Null." & vbCrLf & "-----" & vbCrLf & TotalRecords & " Total records
> found/changed.", vbOKOnly, "ClaimSecure's Members Eligibility Search &
> Replace -Kim Blake-" ' When a 1 is found in the current cell being
> checked the code below is run."
> Range(StartPoint).Select ' Moves the cursor back to the A1
> starting point after the clicks Ok on the Message box from line
> above."
> End ' Ends the macro since the blank cell at bottom of
> spreadsheed has been found.
>
> ' When a 1 is found in the current cell being checked the code
> below is run.
> ElseIf (Vol = 1) Then ' Checks to verify the cell contains a 1 then
> runs the code below.
> ActiveCell.Formula = MemRecords + 1 ' Changes the value of the
> current cell from the 1 to the next number in the sequence.
> ActiveCell.Offset(1, 0).Select ' Drops down one cell & selects
> it.
> MemRecords = MemRecords + 1 ' Changes the value in the
> MemRecords variable by adding 1 to it.
> TotalRecords = TotalRecords + 1 ' Changes the value in the
> TotalRecords variable by adding 1 to the total.
> CheckCell ' Calls the CheckCell Sub rouutine again to check the
> value in the cell it just moved to.
>
> ' When a 2 is found in the current cell being checked the code
> below is run.
> ElseIf (Vol = 2) Then ' Checks to verify the cell contains a 2 then
> runs the code below.
> ActiveCell.Formula = Null ' Changes the 2 value in the cell and
> set it to Null/empty.
> ActiveCell.Offset(1, 0).Select ' Drops down to the next cell
> and selects it.
> TotalRecords = TotalRecords + 1 ' Changes the value in the
> TotalRecords variable by adding 1 to the total.
> CheckCell ' Calls the CheckCell Sub rouutine again to check the
> value in the cell it just moved to.
>
> End If
> End Function
>
>
> --
> cjsasl
> ------------------------------------------------------------------------
> cjsasl's Profile:
http://www.excelforum.com/member.php...o&userid=28712
> View this thread: http://www.excelforum.com/showthread...hreadid=484006
>

[cjsasl]

Thank you very much Tom, the only thing I had to change was the code:

TotalTime = (Timer - StartTime) ' This gave me a - time (ie -3 Second(s))
to
TotalTime = (StartTime - Timer)

And now it works perfect.

Thanks Tom you are the best...

[Tom Ogilvy]

If you set StartTime to the value of Timer at the start of your code, then
when you are ready to show the time, you should subtract StartTime from
Timer.

Timer is the elapsed number of seconds since midnight.

If you were getting a negative time with

Timer - StartTime

then something isn't working correctly.

You should only set TotalTime in the same code block where the message box
string is built, so it is set just before showing the message.

--
Regards,
Tom Ogilvy


"cjsasl" <[email protected]> wrote in
message news:[email protected]...
>
> Thank you very much Tom, the only thing I had to change was the code:
>
> TotalTime = (Timer - StartTime) ' This gave me a - time (ie -3
> Second(s))
> to
> TotalTime = (StartTime - Timer)
>
> And now it works perfect.
>
> Thanks Tom you are the best...
>
>
> --
> cjsasl
> ------------------------------------------------------------------------
> cjsasl's Profile:
http://www.excelforum.com/member.php...o&userid=28712
> View this thread: http://www.excelforum.com/showthread...hreadid=484006
>

[Ken Macksey]

Hi

Sorry about that. It works fine if you remove some of the dims like this.

Dim totaltime As Long



starttime = Now()


'Your code here



Endtime = Now()

totaltime = DateDiff("s", starttime, endtime)

'convert seconds into minutes and seconds to display
Mins = Int(totaltime \ 60)
Secs = totaltime Mod 60

Totaltimetaken = Mins & " Minute(s) and " & secs & " Seconds."

MsgBox(Totaltimetaken)



HTH

Ken