使用VBA Now与Now（）时的时差？ [英] Time differences when using VBA Now vs. Now()?
问题描述
需要了解为什么我得到时间差异
这是我的VBA代码：
公开OHLCArray（1到481，0到28，0到3）单个
'调试stmt低于
Dim DebugTime As Double
OHLCArray（1，0，3）= Now（）
'接下来的3行是调试行
DebugTime = OHLCArray（1，0，3）
打印＃1，Now（）=& DebugTime
打印＃1，Now& 调试打印行 - （之前推
以下是日志文件中的调试输出行： / p>
现在（）= 42527.4609375
06/06/2016 11:01:00 AMDebug打印行 -
只是这样你知道，时间0.4609375 = 11:03:45 AM;我期待现在在第二个打印声明中给我看，或者不要超过2秒左右。
我的问题：在上面的例子中，为什么我得到不同的时间？现在和现在（）之间有区别吗？这可能会影响时间的捕获，如上图所示？
感谢您的帮助Karl
PS以下是上面列出的行之前的2个调试输出行：
code> 06/06/2016 10:59:34 AM - 跳过的ws_calculate例程 - CurrentTimeID已处理 - CurrentTimeID = 45
06/06/2016 10:59:54 AM - 跳过的ws_calculate例程 - Cu rrentTimeID已处理 - CurrentTimeID = 45
这是一个有关相关主题的链接 - 不确定是否适用于我的问题： Postgres now（）vs'now'in function
PSS谢谢Vegard。在查看具有相似输出数据的其他日志文件时，可以确定我的混淆是由于两个错误引起的：a）在将新的单个数据移动到之前，我没有将DebugTime重置为零（哪里有较小的小数位数从单一被覆盖到双精度？）和b）您的观察结果，将单个数据类型传输到双重数据类型是无用的，因为截断完整日期数据时，最初被移动到我的数组定义为单个。我认为我会保持我的阵列是单一的，但是，为了避免倍增它的大小，当我的数组的99.8％工作正好被定义为单一。我可以在我的工作表的头部（当数组转移到它）时，接受时间关闭最多约2分钟。我了解到，在大多数应用程序中，将日期/时间数据移动到数据类型Single是不够的。数据类型应为Double或DateTime。
P.S.S。
今天（星期三，接近上午11点）与调试语句的逻辑显示如下：
OHLCArray（1，0，3）= Now（）
OHLCArray元素被单独捕获date.time为42529.46。 Now（）然而是42529.45905067（相当于11:01:02 am），这出现在我的print.debug和打印日志文件（CORRECTLY）中。当现在被抛出时，它向上舍入为.46。
DebugTime = OHLCArray（1，0，3）
当数组元素被转换为DebugTime时，其他数字被拾取（错误地）。 DebugTime = 42529.46093750。这转换为11:03:45 am。这里也是有趣的地方。当我将数组元素（42529.46）稍后移动到我的excel表（一个反映完整数组的工作表） - 一个格式化为仅显示数字的单元格，我在表中提到了11:03:45。所以这意味着工作表中的单元格正是以与DebugTime = OHLCArray（1，0，3）语句完全相同的方式接收数字。因为记住，数组元素是42529.46。 0.46转换为11:02:24 am。
感谢VeGard所有额外的努力。我希望这个跟进有助于您更深入地了解EXCEL和VBA的一些不太明显的方式。我总是告诉我非计算机文字的朋友：电脑从来没有错！
在日期的上下文中单个给你正确的答案是一个极端的巧合。为了进一步说明为什么，基于Gary的答案，我已经颠倒了你的代码，使得数组声明是 Double - 当我们想要比较实际正确的 Double 与 Single -cast-to Double 这不正确。
Sub testTime（）
Dim OHLCArray（1到481，0到28，0到3）As Double
'debug stmt below
Dim DblTime As Double，SngTime As Single
OHLCArray（1， 0，3）= Now（）
'next 3行是调试行
DblTime = OHLCArray（1，0，3）
SngTime = OHLCArray（1，0，3）
Debug.PrintDoubletime：& DblTime
Debug.PrintSingletime：& SngTime
Debug.PrintDate Double Single Double（single2）
Debug.PrintSingletime =& CDate（SngTime）& - & CDbl（SngTime）& - & CSng（SngTime）& - & CDbl（SngTime2）
Debug.PrintDoubletime =& CDate（DblTime）& - & CDbl（DblTime）& - & CSng（DblTime）
Debug.PrintNow（）=&现在（）& - & CDbl（Now（））& - & CSng（Now（））
Debug.PrintNow =&现在& - & CDbl（Now）& - & CSng（现在）
End Sub
现在，我们来看看输出。我已经跑了好几次，这清楚地说明了这个问题。观察在Double列的第一行中发生了什么（或者更确切地说，不会发生），这是我们将一个加倍的比例，并将其与正确的 Double 在下一行：
双倍：42528,3991666667
Singletime： 42528,4
日期双人单人
Singletime = 07.06.2016 09:33:45 - 42528,3984375 - 42528,4
双倍= 07.06.2016 09:34:48 - 42528,3991666667 - 42528,4
现在（）= 07.06.2016 09:34:48 - 42528,3991666667 - 42528,4
现在= 07.06.2016 09:34:48 - 42528,3991666667 - 42528,4
双倍：42528,3995138889
Singletime：42528,4
日期双单
Singletime = 07.06.2016 09:33:45 - 42528,3984375 - 42528， 4
双倍= 07.06.2016 09:35:18 - 42528,3995138889 - 42528,4
现在（）= 07.06.2016 09:35:18 - 42528,3995138889 - 42528,4
现在= 07.06.2016 09:35:18 - 42528,3995138889 - 42528,4
双倍：42528,3996180556
Singletime：42528,4
日期双单
Singletime = 07.06.2016 09:33:45 - 42528,3984375 - 42528,4
双倍= 07.06.2016 09:35:27 - 42528,3996180556 - 42528,4
现在（）= 07.06.2016 09:35:27 - 42528,3996180556 - 42528,4
现在= 07.06.2016 09:35:27 - 42528,3996180556 - 42528,4
双倍：42528,3998726852
Singletime：42528,4
日期双人单人
Singletime = 07.06.2016 09:33:45 - 42528,3984375 - 42528,4
双倍= 07.06.2016 09:35:49 - 42528,3998726852 - 42528,4
现在（）= 07.06.2016 09:35:49 - 42528,3998726852 - 42528,4
现在= 07.06.2016 09:35:49 - 42528,3998726852 - 42528,4
双倍：42528,4045486111
Singletime：42528,41
日期双人单人
Singletime = 07.06.2016 09:45:00 - 42 528,40625 - 42528,41
双倍= 07.06.2016 09:42:33 - 42528,4045486111 - 42528,41
现在（）= 07.06.2016 09:42:33 - 42528,4045486111 - 42528,41
现在= 07.06.2016 09:42:33 - 42528,4045486111 - 42528,41
请注意，对于使用 Single 的所有日期转换，时间戳实际上是静态的，因为数据类型不能支持足够的数字。
将其转换为 Double 的事实产生更多的数字不一定会正确 - 它变得稍微更准确，但仍然不正确。
所以问题似乎不在现在或现在（）！如我的示例所示，只要使用正确的数据类型，它们都会产生正确的结果。
您的第一个问题，不正确的时间戳是由数组引起的作为单个而不是 Double 。
您的第二个问题完全是其他问题，也许在您的代码中。或者您正在重新加载现在或可能更有可能，打印正在运行时做一些有趣的事情。尝试用打印语句与 Debug.Print现在：&现在然后将结果与日志文件进行比较，或者更好的是根据文档，更改：
打印＃1，现在& 调试打印行 - （在推
之前：
打印＃1，CDate（Now）&调试打印行 - （之前推
使用系统识别的标准短日期格式
将日期数据写入文件，当日期或时间组件
丢失或零时，只有提供的部分被写入
编辑：
至于神秘的额外数字当单个到 Double 时，这不是编译器完成的近似值，因为我以前错误地猜到了。这是一个已知的数字代表的副作用，显然是计算机科学中的常见主题
对于数字精度，请使用十进制数据类型。
MSDN详细讨论了Visual Basic上下文中的浮点精度。 >
这里： https://msdn.microsoft.com/en-us/library/system.double.aspx?cs-save-lang=1&cs-lang=vb#Conversions
类型转换。 Double结构提供了明确的界面
实现IConvertible接口，支持任何两种标准.NET Framework数据类型之间的
转换。
语言编译器还支持将
的所有其他标准数值类型的值隐式转换为Double值。将任何标准数字类型的
值转换为Double是一个扩展的
转换，不需要转换运算符或
转换方法的用户。
但是，Int64和Single值的转换可能会导致精确度的损失
精确度问题最常影响转换为Double值的单个值。
此处： https://msdn.microsoft.com/en-us/library/system.single.aspx
例如，2/10，以.2作为十进制
分数表示，由.0011111001001100表示为二进制分数，
，模式1100重复到无穷大。在这种情况下，
浮点值提供了它所代表的
数字的不精确表示。对原始浮点值执行额外的数学
操作通常会增加其
缺乏精度。例如，如果将
的结果与.3乘以10乘以.3加上9.3的结果进行比较，那么您将看到
加法产生的结果不太精确，因为它涉及8
比运算更多的操作。请注意，只有通过使用R
标准数字格式字符串显示两个Single值时，这个差异才是
，如果需要，它将显示所有支持的9
数位精度单一类型。
Need to understand why I am getting time differences. Here is my VBA code:
Public OHLCArray(1 To 481, 0 To 28, 0 To 3) As Single
'debug stmt below
Dim DebugTime As Double
OHLCArray(1, 0, 3) = Now()
'next 3 lines are debugging lines
DebugTime = OHLCArray(1, 0, 3)
Print #1, "Now()=" & DebugTime
Print #1, Now & "Debug print line - (just before pushing"
Here are the debug output lines in the logfile:
Now()=42527.4609375
06/06/2016 11:01:00 AMDebug print line - (just before pushing
Just so you know, the time of 0.4609375 = 11:03:45 AM; I was expecting Now in the second Print statement to show me this, or to be off by no more than 2 seconds or so.
My questions: In the above example, why am I getting different times? Is there a difference between now and now()? Can this affect the capturing of time, as it seems to show above?
Thanks for your help. Karl.
P.S. Here are the 2 debug output lines just before the lines identified above:
06/06/2016 10:59:34 AM - Skipped ws_calculate routine - CurrentTimeID already processed - CurrentTimeID = 45
06/06/2016 10:59:54 AM - Skipped ws_calculate routine - CurrentTimeID already processed - CurrentTimeID = 45
P.S.S. Here is a link to a somewhat related topic - not sure if it applies to my issue: Postgres now() vs 'now' in function
P.S.S. Thanks Vegard. Upon reviewing other log files with similar output data, it can be determined that my confusion arose as a result of two errors: a) I did not reset DebugTime to zero before moving the new single data to it (Where do the smaller fractional digits come from when the single is overlaid into the double?), and b) your observation, that the transferring of the single data type to the double data type wasn't useful since truncation of full date data occurred when it was initially moved into my array defined as Single. I think I will maintain my array as single, however, to avoid doubling it in size, when 99.8% of my array works just fine defined as single. I can accept the time being off by up to about 2 minutes in the headers of my sheet (when array is transferred to it). I have learned that moving date/time data to a data type of Single in most applications is inadequate. Data type should be Double or DateTime.
P.S.S. Final:
The logic that ran today (Wednesday, near 11am) with debug statements showed me the following:
OHLCArray(1, 0, 3) = Now()
OHLCArray element being Single captured the date.time as 42529.46. Now() however was 42529.45905067 (equivalent to 11:01:02am), and this appeared in my print.debug and print log file (CORRECTLY). When now was casted, it rounded up to .46.
DebugTime = OHLCArray(1, 0, 3)
When the array element was casted to DebugTime, additional digits were picked up (erroneously). DebugTime = 42529.46093750. This translates to 11:03:45am. Here is where it also gets interesting. When I move that array element (42529.46) later to my excel sheet (a sheet that reflects the complete array) - a cell which is formatted to display the number as time only, I get 11:03:45am in the sheet. So this means that the cell in the sheet is receiving the number in exactly the same way as the statement DebugTime = OHLCArray(1, 0, 3). Because remember, The array element was 42529.46. 0.46 converts to 11:02:24am.
Thanks to VeGard for all his extra effort. I hope this follow up helps give you more insight into some of the not so obvious ways EXCEL and VBA behave. As I always tell my non-computer literate friends: "The computer is never wrong!"
Any instance where the use of Single in the context of dates gives you the correct answer is an extreme coincidence.
To further illustrate why, building on Gary's answer, I've reversed your code so that the array declaration is Double - this makes it easier when we want to compare the actually correct Double versus the Single-cast-to-Double which will not be correct.
Sub testTime()
Dim OHLCArray(1 To 481, 0 To 28, 0 To 3) As Double
'debug stmt below
Dim DblTime As Double, SngTime As Single
OHLCArray(1, 0, 3) = Now()
'next 3 lines are debugging lines
DblTime = OHLCArray(1, 0, 3)
SngTime = OHLCArray(1, 0, 3)
Debug.Print "Doubletime: " & DblTime
Debug.Print "Singletime: " & SngTime
Debug.Print " Date Double Single Double(single2)"
Debug.Print "Singletime = " & CDate(SngTime) & " - " & CDbl(SngTime) & " - " & CSng(SngTime) & " - " & CDbl(SngTime2)
Debug.Print "Doubletime = " & CDate(DblTime) & " - " & CDbl(DblTime) & " - " & CSng(DblTime)
Debug.Print "Now() = " & Now() & " - " & CDbl(Now()) & " - " & CSng(Now())
Debug.Print "Now = " & Now & " - " & CDbl(Now) & " - " & CSng(Now)
End Sub
Now, let's review the output. I've run it several times, and this clearly illuminates the issue. Observe what happens (or rather, what doesn't happen) in the first row of column "Double", which is where we cast a single to double, and compare this to the correct Double on the next row:
Doubletime: 42528,3991666667
Singletime: 42528,4
Date Double Single
Singletime = 07.06.2016 09:33:45 - 42528,3984375 - 42528,4
Doubletime = 07.06.2016 09:34:48 - 42528,3991666667 - 42528,4
Now() = 07.06.2016 09:34:48 - 42528,3991666667 - 42528,4
Now = 07.06.2016 09:34:48 - 42528,3991666667 - 42528,4
Doubletime: 42528,3995138889
Singletime: 42528,4
Date Double Single
Singletime = 07.06.2016 09:33:45 - 42528,3984375 - 42528,4
Doubletime = 07.06.2016 09:35:18 - 42528,3995138889 - 42528,4
Now() = 07.06.2016 09:35:18 - 42528,3995138889 - 42528,4
Now = 07.06.2016 09:35:18 - 42528,3995138889 - 42528,4
Doubletime: 42528,3996180556
Singletime: 42528,4
Date Double Single
Singletime = 07.06.2016 09:33:45 - 42528,3984375 - 42528,4
Doubletime = 07.06.2016 09:35:27 - 42528,3996180556 - 42528,4
Now() = 07.06.2016 09:35:27 - 42528,3996180556 - 42528,4
Now = 07.06.2016 09:35:27 - 42528,3996180556 - 42528,4
Doubletime: 42528,3998726852
Singletime: 42528,4
Date Double Single
Singletime = 07.06.2016 09:33:45 - 42528,3984375 - 42528,4
Doubletime = 07.06.2016 09:35:49 - 42528,3998726852 - 42528,4
Now() = 07.06.2016 09:35:49 - 42528,3998726852 - 42528,4
Now = 07.06.2016 09:35:49 - 42528,3998726852 - 42528,4
Doubletime: 42528,4045486111
Singletime: 42528,41
Date Double Single
Singletime = 07.06.2016 09:45:00 - 42528,40625 - 42528,41
Doubletime = 07.06.2016 09:42:33 - 42528,4045486111 - 42528,41
Now() = 07.06.2016 09:42:33 - 42528,4045486111 - 42528,41
Now = 07.06.2016 09:42:33 - 42528,4045486111 - 42528,41
Note that for all date conversions using the Single, the timestamp is actually static for extended periods of time because the datatype can't support sufficient digits.
The fact that casting it to Double produces more digits doesn't necessarily make it right -- it becomes slightly more accurate, but still not correct.
So the problem does not seem to be with Now or Now()! As shown in my examples, as long as the correct datatype is used, they both yield the correct result.
Your first problem, the incorrect timestamps, is caused by the array being a Single instead of a Double.
Your second problem is something else in entirely, perhaps in your code. Either you are overloading Now or perhaps more probable, Print is doing something funky at runtime. Try prefacing the Print statement with Debug.Print "Now: " & Now and then compare the result to the logfile, or better yet, pursuant to the documentation, change this:
Print #1, Now & "Debug print line - (just before pushing"
to this:
Print #1, CDate(Now) & "Debug print line - (just before pushing"
Date data is written to the file using the standard short date format recognized by your system. When either the date or the time component is missing or zero, only the part provided gets written to the file.
EDIT:
As for the mysterious extra digits that appear when casting Single to Double, this is not an approximation done by the compiler, as I mistakenly guessed earlier. It is a known side-effect of how numbers are represented, an apparently common topic in computer science.
For numeric precision, use the Decimal datatype.
MSDN talks about floating-point precision specifically in a Visual Basic context in some detail.
Here: https://msdn.microsoft.com/en-us/library/system.double.aspx?cs-save-lang=1&cs-lang=vb#Conversions
Type conversion. The Double structure provides an explicit interface implementation for the IConvertible interface, which supports conversion between any two standard .NET Framework data types. Language compilers also support the implicit conversion of values of all other standard numeric types to Double values. Conversion of a value of any standard numeric type to a Double is a widening conversion and does not require the user of a casting operator or conversion method.
However, conversion of Int64 and Single values can involve a loss of precision.
The problem of precision most frequently affects Single values that are converted to Double values.
And here: https://msdn.microsoft.com/en-us/library/system.single.aspx
For example, 2/10, which is represented precisely by .2 as a decimal fraction, is represented by .0011111001001100 as a binary fraction, with the pattern "1100" repeating to infinity. In this case, the floating-point value provides an imprecise representation of the number that it represents. Performing additional mathematical operations on the original floating-point value often increases its lack of precision. For example, if you compare the results of multiplying .3 by 10 and adding .3 to .3 nine times, you will see that addition produces the less precise result, because it involves eight more operations than multiplication. Note that this disparity is apparent only if you display the two Single values by using the "R" standard numeric format string, which, if necessary, displays all 9 digits of precision supported by the Single type.
这篇关于使用VBA Now与Now（）时的时差？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
