VBA Displays how long it took a sub

Question

I am trying to use the following code to show how long a series of commands has been running. In this example, I expected it to return with a “10” or something similar.

Instead, it returns with:

What is happening and how can I format it 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 excellent 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:

Bam! The problem is solved! Thank you all for your help!

Answer 1

Date is stored as a numeric value in MS Access and MS Excel. Therefore, if you enter ?Cdbl(now()) in your next window ( Ctrl + G ?Cdbl(now()) , you will get the following number: 42195.5204050926 .

Integers indicate how many days have passed since 1899 on December 30, and a decimal number shows how many of the current day have passed.

So, in your code you basically say something like this:

 timeTaken = 42195.5222337963 - 42195.5204050926 

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

Now, if I show that using the Date variable, for example, in your example, I basically say what time it was at 0.0018287037 , which was December 30, 1899, 12:02:38 AM.

You can visually see this by returning to the nearest window and typing ?cdate(0.0018287037) , and you will get the result: 12:02:38 AM . To take this one step further, you can enter ?cdate(1.0018287037) and you will get the result: 1899-12-31 12:02:38 AM

So, in your situation, you can simply change:

 MsgBox timeTaken 

To:

 MsgBox Format(timeTaken, "nn:ss") 

Note: I did not notice that the screenshot says "Excel", although this answer will still be valid.

Answer 2

Usually you should use the DateDiff function to calculate the difference between the two dates, but I think you need something else in your case, since DateDiff will not give you milliseconds. Instead, simply replace your call with the Now function Timer () , which is only a few seconds (careful, but it only gives you the time elapsed since midnight, so t2 - t1 can be negative, and if your commands last several days , you will not get any meaningful result).

Answer 3

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