+ Reply to Thread
Results 1 to 9 of 9

Time Calculation - How long a macro takes to run

  1. #1
    cdb
    Guest

    Time Calculation - How long a macro takes to run

    I am trying to add into my macro a bit of code that times how long it takes
    to execute, but I can't seem to get it right.

    As a test I have been using the following code:

    Sub test2()

    i = 1

    starttime = TimeValue(Time)

    While i < 100000000
    i = i + 1
    Wend

    endtime = TimeValue(Time)
    timecalc = TimeValue(starttime) - TimeValue(endtime)

    MsgBox (starttime & endtime & timecalc)

    End Sub

    But when I output the result (timecalc) it gives a really wierd number
    (-1.85185185185233E-04). I have tried it without the TimeValue statement too
    and get the same sort of result.

    Is there an easy way of substracting the start time from the end time??

  2. #2
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    Use:

    Sub test2()

    starttime = Now()

    ' your code here

    endTime = Now()
    totTimeSec = Round(((endTime - startTime) * (24 * CLng(3600))), 1)
    MsgBox (totTimeSec & " seconds")

    End Sub


    - Mangesh

  3. #3
    Tom Ogilvy
    Guest

    Re: Time Calculation - How long a macro takes to run

    Sub test2()

    i = 1

    starttime = Timer

    While i < 100000000
    i = i + 1
    Wend

    endtime = Timer
    timecalc = EndTime - StartTime

    MsgBox (starttime & endtime & timecalc)

    End Sub

    Measures the number of seconds.

    (subtracting the endtime from starttime would give a negative number).

    --
    Regards,
    Tom Ogilvy

    "cdb" <[email protected]> wrote in message
    news:[email protected]...
    > I am trying to add into my macro a bit of code that times how long it

    takes
    > to execute, but I can't seem to get it right.
    >
    > As a test I have been using the following code:
    >
    > Sub test2()
    >
    > i = 1
    >
    > starttime = TimeValue(Time)
    >
    > While i < 100000000
    > i = i + 1
    > Wend
    >
    > endtime = TimeValue(Time)
    > timecalc = TimeValue(starttime) - TimeValue(endtime)
    >
    > MsgBox (starttime & endtime & timecalc)
    >
    > End Sub
    >
    > But when I output the result (timecalc) it gives a really wierd number
    > (-1.85185185185233E-04). I have tried it without the TimeValue statement

    too
    > and get the same sort of result.
    >
    > Is there an easy way of substracting the start time from the end time??




  4. #4
    cdb
    Guest

    Re: Time Calculation - How long a macro takes to run

    Cheers Tom, works a treat. Is there any way in displaying this in mm:ss
    format (as the real code I will use will take several minutes to run)

    "Tom Ogilvy" wrote:

    > Sub test2()
    >
    > i = 1
    >
    > starttime = Timer
    >
    > While i < 100000000
    > i = i + 1
    > Wend
    >
    > endtime = Timer
    > timecalc = EndTime - StartTime
    >
    > MsgBox (starttime & endtime & timecalc)
    >
    > End Sub
    >
    > Measures the number of seconds.
    >
    > (subtracting the endtime from starttime would give a negative number).
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "cdb" <[email protected]> wrote in message
    > news:[email protected]...
    > > I am trying to add into my macro a bit of code that times how long it

    > takes
    > > to execute, but I can't seem to get it right.
    > >
    > > As a test I have been using the following code:
    > >
    > > Sub test2()
    > >
    > > i = 1
    > >
    > > starttime = TimeValue(Time)
    > >
    > > While i < 100000000
    > > i = i + 1
    > > Wend
    > >
    > > endtime = TimeValue(Time)
    > > timecalc = TimeValue(starttime) - TimeValue(endtime)
    > >
    > > MsgBox (starttime & endtime & timecalc)
    > >
    > > End Sub
    > >
    > > But when I output the result (timecalc) it gives a really wierd number
    > > (-1.85185185185233E-04). I have tried it without the TimeValue statement

    > too
    > > and get the same sort of result.
    > >
    > > Is there an easy way of substracting the start time from the end time??

    >
    >
    >


  5. #5
    Tom Ogilvy
    Guest

    Re: Time Calculation - How long a macro takes to run

    from the immediate window:

    numSeconds = 1021
    ? format(numSeconds/86400,"hh:mm:ss")
    00:17:01

    so dividing the number of seconds by 86400 and then using the format command
    should work

    MsgBox format( timecalc/86400,"hh:mm:ss")

    --
    Regards,
    Tom Ogilvy


    "cdb" <[email protected]> wrote in message
    news:[email protected]...
    > Cheers Tom, works a treat. Is there any way in displaying this in mm:ss
    > format (as the real code I will use will take several minutes to run)
    >
    > "Tom Ogilvy" wrote:
    >
    > > Sub test2()
    > >
    > > i = 1
    > >
    > > starttime = Timer
    > >
    > > While i < 100000000
    > > i = i + 1
    > > Wend
    > >
    > > endtime = Timer
    > > timecalc = EndTime - StartTime
    > >
    > > MsgBox (starttime & endtime & timecalc)
    > >
    > > End Sub
    > >
    > > Measures the number of seconds.
    > >
    > > (subtracting the endtime from starttime would give a negative number).
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > > "cdb" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > I am trying to add into my macro a bit of code that times how long it

    > > takes
    > > > to execute, but I can't seem to get it right.
    > > >
    > > > As a test I have been using the following code:
    > > >
    > > > Sub test2()
    > > >
    > > > i = 1
    > > >
    > > > starttime = TimeValue(Time)
    > > >
    > > > While i < 100000000
    > > > i = i + 1
    > > > Wend
    > > >
    > > > endtime = TimeValue(Time)
    > > > timecalc = TimeValue(starttime) - TimeValue(endtime)
    > > >
    > > > MsgBox (starttime & endtime & timecalc)
    > > >
    > > > End Sub
    > > >
    > > > But when I output the result (timecalc) it gives a really wierd number
    > > > (-1.85185185185233E-04). I have tried it without the TimeValue

    statement
    > > too
    > > > and get the same sort of result.
    > > >
    > > > Is there an easy way of substracting the start time from the end

    time??
    > >
    > >
    > >




  6. #6
    cdb
    Guest

    Re: Time Calculation - How long a macro takes to run

    Works a treat - many thanks again for the help.

    One thing puzzles me though. Why do you have to divide by 84000? I know this
    is the number of seconds in a day, but why do you have to divide the number
    of seconds in the timer by the number of seconds in the day to convert to
    hh:mm:ss format??

    Any ideas?? I'm not in that much of a need to know, was just wondering the
    logic behind it.

    "Tom Ogilvy" wrote:

    > from the immediate window:
    >
    > numSeconds = 1021
    > ? format(numSeconds/86400,"hh:mm:ss")
    > 00:17:01
    >
    > so dividing the number of seconds by 86400 and then using the format command
    > should work
    >
    > MsgBox format( timecalc/86400,"hh:mm:ss")
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "cdb" <[email protected]> wrote in message
    > news:[email protected]...
    > > Cheers Tom, works a treat. Is there any way in displaying this in mm:ss
    > > format (as the real code I will use will take several minutes to run)
    > >
    > > "Tom Ogilvy" wrote:
    > >
    > > > Sub test2()
    > > >
    > > > i = 1
    > > >
    > > > starttime = Timer
    > > >
    > > > While i < 100000000
    > > > i = i + 1
    > > > Wend
    > > >
    > > > endtime = Timer
    > > > timecalc = EndTime - StartTime
    > > >
    > > > MsgBox (starttime & endtime & timecalc)
    > > >
    > > > End Sub
    > > >
    > > > Measures the number of seconds.
    > > >
    > > > (subtracting the endtime from starttime would give a negative number).
    > > >
    > > > --
    > > > Regards,
    > > > Tom Ogilvy
    > > >
    > > > "cdb" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > I am trying to add into my macro a bit of code that times how long it
    > > > takes
    > > > > to execute, but I can't seem to get it right.
    > > > >
    > > > > As a test I have been using the following code:
    > > > >
    > > > > Sub test2()
    > > > >
    > > > > i = 1
    > > > >
    > > > > starttime = TimeValue(Time)
    > > > >
    > > > > While i < 100000000
    > > > > i = i + 1
    > > > > Wend
    > > > >
    > > > > endtime = TimeValue(Time)
    > > > > timecalc = TimeValue(starttime) - TimeValue(endtime)
    > > > >
    > > > > MsgBox (starttime & endtime & timecalc)
    > > > >
    > > > > End Sub
    > > > >
    > > > > But when I output the result (timecalc) it gives a really wierd number
    > > > > (-1.85185185185233E-04). I have tried it without the TimeValue

    > statement
    > > > too
    > > > > and get the same sort of result.
    > > > >
    > > > > Is there an easy way of substracting the start time from the end

    > time??
    > > >
    > > >
    > > >

    >
    >
    >


  7. #7
    Tom Ogilvy
    Guest

    Re: Time Calculation - How long a macro takes to run

    Time and dates are one format. Excel records dates (and time) as the
    elapsed number of days since a base date. so 6 hours would be represented
    as 0.25 and 30 hours as 1.25 (one day and 6 hours).

    so you have to convert you time to the number of days (thus divide by
    24*60*60)

    Intrigued? See Chip Pearson's page on this topic:

    http://www.cpearson.com/excel/datetime.htm

    --
    Regards,
    Tom Ogilvy



    "cdb" <[email protected]> wrote in message
    news:[email protected]...
    > Works a treat - many thanks again for the help.
    >
    > One thing puzzles me though. Why do you have to divide by 84000? I know

    this
    > is the number of seconds in a day, but why do you have to divide the

    number
    > of seconds in the timer by the number of seconds in the day to convert to
    > hh:mm:ss format??
    >
    > Any ideas?? I'm not in that much of a need to know, was just wondering the
    > logic behind it.
    >
    > "Tom Ogilvy" wrote:
    >
    > > from the immediate window:
    > >
    > > numSeconds = 1021
    > > ? format(numSeconds/86400,"hh:mm:ss")
    > > 00:17:01
    > >
    > > so dividing the number of seconds by 86400 and then using the format

    command
    > > should work
    > >
    > > MsgBox format( timecalc/86400,"hh:mm:ss")
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "cdb" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Cheers Tom, works a treat. Is there any way in displaying this in

    mm:ss
    > > > format (as the real code I will use will take several minutes to run)
    > > >
    > > > "Tom Ogilvy" wrote:
    > > >
    > > > > Sub test2()
    > > > >
    > > > > i = 1
    > > > >
    > > > > starttime = Timer
    > > > >
    > > > > While i < 100000000
    > > > > i = i + 1
    > > > > Wend
    > > > >
    > > > > endtime = Timer
    > > > > timecalc = EndTime - StartTime
    > > > >
    > > > > MsgBox (starttime & endtime & timecalc)
    > > > >
    > > > > End Sub
    > > > >
    > > > > Measures the number of seconds.
    > > > >
    > > > > (subtracting the endtime from starttime would give a negative

    number).
    > > > >
    > > > > --
    > > > > Regards,
    > > > > Tom Ogilvy
    > > > >
    > > > > "cdb" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > I am trying to add into my macro a bit of code that times how long

    it
    > > > > takes
    > > > > > to execute, but I can't seem to get it right.
    > > > > >
    > > > > > As a test I have been using the following code:
    > > > > >
    > > > > > Sub test2()
    > > > > >
    > > > > > i = 1
    > > > > >
    > > > > > starttime = TimeValue(Time)
    > > > > >
    > > > > > While i < 100000000
    > > > > > i = i + 1
    > > > > > Wend
    > > > > >
    > > > > > endtime = TimeValue(Time)
    > > > > > timecalc = TimeValue(starttime) - TimeValue(endtime)
    > > > > >
    > > > > > MsgBox (starttime & endtime & timecalc)
    > > > > >
    > > > > > End Sub
    > > > > >
    > > > > > But when I output the result (timecalc) it gives a really wierd

    number
    > > > > > (-1.85185185185233E-04). I have tried it without the TimeValue

    > > statement
    > > > > too
    > > > > > and get the same sort of result.
    > > > > >
    > > > > > Is there an easy way of substracting the start time from the end

    > > time??
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  8. #8
    Papa Jonah
    Guest

    Re: Time Calculation - How long a macro takes to run

    Tom,
    This is an interesting thread. I do not understand what causes the timer to
    stop except if i reaches 100000000.

    What will tell it to stop?

    "Tom Ogilvy" wrote:

    > Sub test2()
    >
    > i = 1
    >
    > starttime = Timer
    >
    > While i < 100000000
    > i = i + 1
    > Wend
    >
    > endtime = Timer
    > timecalc = EndTime - StartTime
    >
    > MsgBox (starttime & endtime & timecalc)
    >
    > End Sub
    >
    > Measures the number of seconds.
    >
    > (subtracting the endtime from starttime would give a negative number).
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > "cdb" <[email protected]> wrote in message
    > news:[email protected]...
    > > I am trying to add into my macro a bit of code that times how long it

    > takes
    > > to execute, but I can't seem to get it right.
    > >
    > > As a test I have been using the following code:
    > >
    > > Sub test2()
    > >
    > > i = 1
    > >
    > > starttime = TimeValue(Time)
    > >
    > > While i < 100000000
    > > i = i + 1
    > > Wend
    > >
    > > endtime = TimeValue(Time)
    > > timecalc = TimeValue(starttime) - TimeValue(endtime)
    > >
    > > MsgBox (starttime & endtime & timecalc)
    > >
    > > End Sub
    > >
    > > But when I output the result (timecalc) it gives a really wierd number
    > > (-1.85185185185233E-04). I have tried it without the TimeValue statement

    > too
    > > and get the same sort of result.
    > >
    > > Is there an easy way of substracting the start time from the end time??

    >
    >
    >


  9. #9
    cdb
    Guest

    Re: Time Calculation - How long a macro takes to run

    The timer will stop when it gets to 100000000 only - that is the only
    criteria in the below code.

    "Papa Jonah" wrote:

    > Tom,
    > This is an interesting thread. I do not understand what causes the timer to
    > stop except if i reaches 100000000.
    >
    > What will tell it to stop?
    >
    > "Tom Ogilvy" wrote:
    >
    > > Sub test2()
    > >
    > > i = 1
    > >
    > > starttime = Timer
    > >
    > > While i < 100000000
    > > i = i + 1
    > > Wend
    > >
    > > endtime = Timer
    > > timecalc = EndTime - StartTime
    > >
    > > MsgBox (starttime & endtime & timecalc)
    > >
    > > End Sub
    > >
    > > Measures the number of seconds.
    > >
    > > (subtracting the endtime from starttime would give a negative number).
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > > "cdb" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > I am trying to add into my macro a bit of code that times how long it

    > > takes
    > > > to execute, but I can't seem to get it right.
    > > >
    > > > As a test I have been using the following code:
    > > >
    > > > Sub test2()
    > > >
    > > > i = 1
    > > >
    > > > starttime = TimeValue(Time)
    > > >
    > > > While i < 100000000
    > > > i = i + 1
    > > > Wend
    > > >
    > > > endtime = TimeValue(Time)
    > > > timecalc = TimeValue(starttime) - TimeValue(endtime)
    > > >
    > > > MsgBox (starttime & endtime & timecalc)
    > > >
    > > > End Sub
    > > >
    > > > But when I output the result (timecalc) it gives a really wierd number
    > > > (-1.85185185185233E-04). I have tried it without the TimeValue statement

    > > too
    > > > and get the same sort of result.
    > > >
    > > > Is there an easy way of substracting the start time from the end time??

    > >
    > >
    > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1