VBA - Displaying how long a sub took to run

Question

I am trying to use the following code to display how long a series of commands took to run. In this example case, I expected it to come back with "10" or something similar.

Instead it comes back with:

enter image description here

What is going on and how can I format this correctly?

Sub timeyWimey()

    Dim t1 As Date
    Dim t2 As Date
    Dim timeTaken As Date

        t1 = Now()
        Application.Wait (Now + TimeValue("0:00:10"))
        t2 = Now()

        timeTaken = t2 - t1

        MsgBox timeTaken

End Sub

Edit:

Final Code after some great Answers:

Sub timeyWimey()

    'Dim t1 As Double
    'Dim t2 As Double


        t1 = Now()
        Application.Wait (Now + TimeValue("0:00:10"))
        t2 = Now()

        timeTaken = t2 - t1

        MsgBox Format(timeTaken, "nn:ss.000")

End Sub

Results in:

enter image description here

BAM! Problem Solved! Thanks everyone for your help!

Newd · Accepted Answer · 2015-07-10T16:26:54.630

2

Date's are stored as a numeric value within the MS Access and MS Excel. So if in your immediate window (Ctrl+G) you type ?Cdbl(now()) you will get a number like this: 42195.5204050926.

The whole numbers depicts how many days have passed since 1899 December 30th, and the decimal shows how much of the current day has passed.

So in your code you are basically saying something like this:

timeTaken = 42195.5222337963  - 42195.5204050926

In this example I just checked Now() once and then again a few minutes later. So I ended up with 0.0018287037.

Now if I go to display that using a Date variable such as in your example, am basically saying what time was it at 0.0018287037 which is December 30th 1899, 12:02:38 AM.

You can visually see this by going back to your immediate window and typing ?cdate(0.0018287037) and you will get a result like: 12:02:38 AM. To take it one step further you can then type ?cdate(1.0018287037) and you will get a result saying: 1899-12-31 12:02:38 AM

So in your situation you can simply just change:

MsgBox timeTaken

To:

MsgBox Format(timeTaken, "nn:ss")

Note: I hadn't noticed in the screenshot it says "Excel" though this answer should still be valid.

edited Jul 10 '15 at 16:26

answered Jul 10 '15 at 15:38

Newd

2,174
2
17
31

Thank you. So that definitely explains the problem. Let me think about this for a bit. – timbram Jul 10 '15 at 16:15
So, how does format of the numbers after the decimal (the amount of the current day that has passed) work? If re-run my script, keeping everything as doubles, I get that the time that has elapsed (10 sec) is 0.00005787036934635. Is that a percentage of the day or something? – timbram Jul 10 '15 at 16:41
I appended how you could format the double onto the end of my answer. And `0.00005787036934635` is basically a percentage for the day. For example if it was 0.5 it would be 12:00noon because half of the day has gone by. – Newd Jul 10 '15 at 17:34
2

Solid explanation. If you want milliseconds as well, you can append `.000` to get `ss.000`. See http://stackoverflow.com/questions/3095407/display-milliseconds-in-excel. – Byron Wall Jul 10 '15 at 17:52
1

@ByronWall Neat! I was actually looking for something like that but my go-to [reference](http://www.techonthenet.com/access/functions/date/format.php) website was lacking on examples. Personally I just stole the CTimer code from [here](http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code) to use in my database Haha. – Newd Jul 10 '15 at 17:59
Very cool! Just so I know, in case I didn't want to use format() or if it didn't exist...mathematically...how do you convert a percentage of a day to a time? I am drawing a blank in my head on how to do that. – timbram Jul 10 '15 at 19:17
1

@timbram I think that you probably want to post a new question for that. I don't entirely think that will fit into a comment box :-) – Newd Jul 10 '15 at 19:33
@Newd lol Yeah I kind of hate these comment boxes! If it continues to bother me, I may ask another question on it! :) – timbram Jul 10 '15 at 19:36

score 1 · Answer 2 · answered Jul 10 '15 at 15:40

Normally you'd use the DateDiff function to calculate the difference between two dates, but i think in your case you'll want something else, as DateDiff won't give you milliseconds. Instead, just replace your call to the "Now" function by Timer(), which is just a number of seconds (careful though, it only gives you the time elapsed since midnight, so t2 - t1 can be negative, and if your commands last several days you won't get any meaningful result).

score 1 · Answer 3 · answered Jul 10 '15 at 15:54

1

You can try the timer function. As other answers indicate, there may be some caveats.

Dim startTime As Double, endTime As Double
startTime = Timer

Application.Wait (Now + TimeValue("0:00:10"))

endTime = Timer
msgBox endTime - startTime

answered Jul 10 '15 at 15:54

Cohan

4,384
2
22
40

I hate it and I love it! :) I hate that messing with times is this much of a pain in VBA! Still, dealing with time can be a complex beast in ANY language! This method works, and for my current process I am not as much worried about milliseconds, and will not be running it over night or anything. – timbram Jul 10 '15 at 16:46

VBA - Displaying how long a sub took to run

3 Answers3