CODEWITHSUNDEEP
vbaexcel
timbram
timbram

Reputation: 1865

VBA - Displaying how long a sub took to run

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!

Upvotes: 5

Views: 5601

Answers (3)

Newd
Newd

Reputation: 2185

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.

Upvotes: 2

Cohan
Cohan

Reputation: 4564

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

Upvotes: 1

Jean Robert
Jean Robert

Reputation: 296

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).

Upvotes: 1

Related Questions