如何将VBA Now()转换为秒来确定整个程序运行时

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.

我不知道如何使用VBA中的FORMAT()函数将C2的值转换为秒?还有比格式更好的功能吗?基本上，我对Excel的日期序列值和它们代表什么感到困惑。

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

5 个解决方案

#1

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

有几种方法可以使用VBA来格式化单元格/变量。

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

在没有特定顺序的情况下，首先可以使用NumberFormat属性对范围进行格式化，如下所示:

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:

另一种方法是使用format()函数设置Now():

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

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

#2

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

DateDiff()是您正在寻找的。“s”定义了你正在寻找不同的秒。

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.

编辑:http://www.likeoffice.com/28057/excel-date学习更多关于在Excel VBA中处理日期和时间的信息。重要的是要理解，日期在VBA上下文中的工作方式是不同的，并且有自己独特的一组用于操作的语法函数。

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

第二版编辑:更简洁的版本是:

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

#3

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.

不要使用日期数据成员或Now方法来分析程序的运行时间。相反，计时器函数是最合适的解决方案，因为它返回一个表示秒的值。它将不需要类型转换，并且产生比整数秒更准确的结果。

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

使用LimaNightHawk的答案作为模板，因为您应该将它们存储在本地变量中，而不是直接写入工作表。

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

定时器函数的文档

#4

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

然后使用DateDiff

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

#5

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

#1