如何将 VBA Now() 转换为秒以确定程序总运行时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27231357/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert VBA Now() into seconds to determine total program runtime
提问by arooney88
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 的值转换为秒？还有其他功能比 FORMAT 更好吗？基本上我对 Excel 的 Date Serial 值及其代表的内容感到困惑。
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
采纳答案by Gareth
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 NumberFormatproperty 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:
另一种方法是您可以Now()使用该Format()函数进行格式化：
Worksheets("Table").Range("C2").Value = Format(Now(), "ss")
See the documentation from Microsoft to implement different formats:
请参阅 Microsoft 的文档以实现不同的格式：
NumberFormat: http://msdn.microsoft.com/en-us/library/office/ff196401%28v=office.15%29.aspxFormat: http://msdn.microsoft.com/en-us/library/office/gg251755%28v=office.15%29.aspx
NumberFormat：httpFormat: //msdn.microsoft.com/en-us/library/office/ff196401%28v=office.15%29.aspx：http: //msdn.microsoft.com/en-us/library/office/gg251755 %28v=办公室.15%29.aspx
回答by cheezsteak
Do not use a Datedata member or the Nowmethod to analyze run time of your program. Instead, the Timerfunction is the most appropriate solution as it returns a Singlerepresenting seconds. It will require no type conversion and yields a more accurate result than an integer amount of seconds.
不要使用Date数据成员或Now方法来分析程序的运行时间。相反，该Timer函数是最合适的解决方案，因为它返回一个Single代表秒。它将不需要类型转换并产生比整数秒更准确的结果。
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
回答by Chrismas007
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-dateto 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())
回答by LimaNightHawk
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)
回答by ramses1592
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
