CODEWITHSUNDEEP
vbaexceldatetimeruntime
arooney88
arooney88

Reputation: 305

How to convert VBA Now() into seconds to determine total program runtime

I'm working on a problem where I need to determine the total time my program takes to execute. First line of code needs to write the current "Start time" and last lines of code need to write the current "End time". Then I'm subtracting "Start time" - "End Time" = Total Time.

I'm confused how I would use the FORMAT() function within VBA on the value of C2 to convert into seconds? Is there an other function that would work better than FORMAT? Basically I'm confused about Excel's Date Serial values and what they represent.

Code is below

EDIT: Thanks for the responses everyone. Both answers below work for what I'm trying to do. 

sub ExecutionTime()

Worksheets("Table").Range("A2").Value = Now()

'All my executable code goes here. It's a relatively small program compiling a table so it
runs very quick. 

Worksheets("Table").Range("B2").Value = Now()
Worksheets("Table").Range("C2").Value = Worksheets("Table").Range("A2").Value - Worksheets("Table").Range("B2").Value

end Sub

Upvotes: 9

Views: 33962

Answers (5)

ramses1592
ramses1592

Reputation: 550

How i generally go about bragging my process time to user 

Sub Process()
Dim startTime as Date
Dim endTime as Date

startTime = Now

'Logic for what your process must do

endTime = Now

MsgBox "Process completed in : " & Format(endTime - startTime, "hh:mm:ss")

End Sub

Upvotes: 0

cheezsteak
cheezsteak

Reputation: 2923

Do not use a Date data member or the Now method to analyze run time of your program. Instead, the Timer function is the most appropriate solution as it returns a Single representing seconds. It will require no type conversion and yields a more accurate result than an integer amount of seconds.

Using LimaNightHawk's answer as a template as you should be storing these in local variables instead of writing directly to the worksheet.

Dim startTime as Single
startTime = Timer()

'  Do stuff

Dim endTime as Single
endTime = Timer()

Dim runTime as Single
runTime = endTime - startTime

Results should be written at the end of the routine.

With Worksheets("Table")
    .Range("A2").Value = startTime
    .Range("B2").Value = endTime 
    .Range("C2").Value = runTime
End With

Documentation on the timer function

Upvotes: 8

Chrismas007
Chrismas007

Reputation: 6105

DateDiff() is what you are looking for. The "s" defines that you are looking for the difference in seconds.

Worksheets("Table").Range("C2").Value = DateDiff("s", Worksheets("Table").Range("A2").Value, Worksheets("Table").Range("B2").Value)

EDIT: http://www.likeoffice.com/28057/excel-date to learn more about working with dates and times in Excel VBA. It is important to understand that dates work differently within the context of VBA, and have their own unique set of syntax functions for manipulating.

2nd EDIT: A cleaner version of this would be:

StartDateTime = Now()
'Run Code
Worksheets("Table").Range("C2").Value = DateDiff("s", StartDateTime, Now())

Upvotes: 4

LimaNightHawk
LimaNightHawk

Reputation: 7093

In the first line of your program get the date (no need to format):

Dim startTime as Date
startTime = Now()

At the end of your program, get the date again:

Dim endTime as Date
endTime = Now()

Then use the DateDiff

Dim timeInSeconds as long
timeInSeconds = DateDiff("s", startTime, endTime)

Upvotes: 4

Gareth
Gareth

Reputation: 5243

There's a few ways you can use VBA to format cells / variables.

In no particular order, firstly you can format ranges with the NumberFormat property which can be applied like so:

Worksheets("Table").Range("C2").Value = Now()
Worksheets("Table").Range("C2").NumberFormat = "ss"

The other way is that you could format Now() using the Format() function:

Worksheets("Table").Range("C2").Value = Format(Now(), "ss")

See the documentation from Microsoft to implement different formats:

NumberFormat: http://msdn.microsoft.com/en-us/library/office/ff196401%28v=office.15%29.aspx Format: http://msdn.microsoft.com/en-us/library/office/gg251755%28v=office.15%29.aspx

Upvotes: 2

Related Questions