0

The following Excel VBA code compares the effect of accumulating hours (1/24 of a day) in a Variant array with Date members, and in a Date array. I have columns C, E, G formatted as Date and D, F, H as Number with a lot of d.p.s. In columns A and B I replicated the calculation using worksheet functions. You probably won't believe this until you try it, but the last output from the Variant array of Date is 1 day early. The numeric value, which is reported as 39814.9999999999 by VBA, is converted to 39814 in Excel.

Edit: when I say columns C, E, G in Date format, I mean Custom "dd/mm/yyyy hh:mm:ss.00".

Has anybody seen this before? But more importantly, how do I know what I can trust when using Variants? I have tens of thousands of lines to maintain and Variant arrays are used everywhere that we need to do intensive processing on the Excel data.

Sub FillDatesBug()
Dim dtHours() As Date
Dim vHours As Variant
Dim vHours2 As Variant
Dim dtHour As Date
Dim dHour As Double
Dim i As Long

ReDim dtHours(1 To 25, 1 To 1)
ReDim vHours(1 To 25, 1 To 1)
ReDim vHours2(1 To 25, 1 To 1)

dtHour = CDate(1 / 24)
dHour = 1 / 24
dtHours(1, 1) = CDate(39814)
vHours(1, 1) = CDate(39814)
vHours2(1, 1) = 39814#

For i = 2 To 25
    dtHours(i, 1) = dtHours(i - 1, 1) + dtHour
    vHours(i, 1) = vHours(i - 1, 1) + dtHour
    vHours2(i, 1) = vHours2(i - 1, 1) + dHour
Next i

Range("C2:C26").Value = dtHours
Range("D2:D26").Value = dtHours
Range("E2:E26").Value = vHours
Range("F2:F26").Value = vHours
Range("G2:G26").Value = vHours2
Range("H2:H26").Value = vHours2

Range("C28").Value = "dtHours(25,1) = " & dtHours(25, 1) & " or " & CDbl(dtHours(25, 1))
Range("E28").Value = "vHours(25,1) = " & vHours(25, 1) & " or " & CDbl(vHours(25, 1))
Range("G28").Value = "vHours2(25,1) = " & Format(vHours2(25, 1), "dd/mm/yyyy hh:mm:ss") & " or " & vHours2(25, 1)
End Sub

And here's a screenshot - I don't have the rep to display it, apparently.

As you can see, a Date array and a Variant array with Double members get the right answers (including the perfectly normal and understood numerical error). Certain values in a Variant array with Date members get translated into Excel wrongly.

First person to actually see what I'm saying here wins my eternal gratitude.

Boddle
  • 13
  • 4

2 Answers2

0

EDIT (better explanation). I have seen something similar in the past, but do not recall the details. In this particular case, it seems to be related, at least in part, to some of the implicit data conversions going on when using Variant data types, and writing results to the worksheet.

One fix, if you must use Variant arrays, is to round to your desired level of precision. So you would make the following changes, to round, for example to milliseconds:

For i = 2 To 25
    vHours(i, 1) = Round((vHours(i - 1, 1) + dtHour) * 86400000, 0) / 86400000
    dtHours(i, 1) = Round((dtHours(i - 1, 1) + dtHour) * 86400000, 0) / 86400000
Next I

EDIT Additional observation

If the variant array is written back to the sheet using the Range.Value2 property, instead of the Range.Value property, the date displayed in the worksheet cell will be correct. However, the date displayed in the formula bar will be incorrect (one day earlier than expected). This seems to be an Excel problem as a value, such as 39814.99999999999 entered directly into a cell will also demonstrate different dates in the formula bar vs the worksheet cell.

It would seem the safest, and fastest method of dealing with this (and other issues) would be to use the Value2 property when reading/writing between variant arrays and the worksheet. Interesting discussion here, and also at the link referenced by Williams in his response.

If you want the dates in the formula bar and the worksheet cell to agree, I see no other option than rounding. But that may not be necessary.

Community
  • 1
  • 1
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • I'm not sure you read the post carefully enough. I'm familiar with the effects of finite precision arithmetic. What I don't expect is VBA says the value is 39814.9999999999, Excel says the value is 39814. Not 39815. Also note that a Date array gets the right result while Date in Variant array doesn't. – Boddle Jan 23 '17 at 23:15
  • 1
    A variant array is storing the value as a double. 1/24 = 0.041666666666... 39814+ 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 + 1/24 ► 39814.99999999999... When that value is formatted as a date, Excel does not round to the nearest date; it only looks at the integer part. – Ron Rosenfeld Jan 24 '17 at 00:42
  • Wrong. (Right if you only show the date part, but I am not showing only the date part.) If you actually ran the code, you would see that the first Custom Date formatted entry appears as "01/01/2009 00:00:00.00", the next as "01/01/2009 01:00:00.00" ... the 24th as "01/01/2009 23:00:00.00", and the last as "01/01/2009 00:00:00.00". Formatted as Number, the same entries show as 39814.000..., 39814.0416..., ..., 39814.9583..., 39814.000... VBA reports all values the same, except the last one, which it reports as 39814.9999999999. Trust me, I'm not a noob. Run the code. This is a bug. – Boddle Jan 24 '17 at 09:43
  • I had run your code, but until this AM, in part because of the formatting on my worksheet, did not appreciate the significance of starting the series with a `Date` vs a `Double`. I suspect there is an issue in the implicit type conversions going on. I tend to use specific data typing unless I have a need for a Variant array, but it still appears that, if you want to use Variant arrays, you may need to round to your desired level of precision. – Ron Rosenfeld Jan 24 '17 at 12:42
  • @Boddle I should also note that the edits you made after I initially read your post have enabled me to understand your point more clearly. – Ron Rosenfeld Jan 24 '17 at 13:18
  • OK - good. The thing is, you need to use a Variant array when you get data out of the worksheet with array = range.Value (because the user might have put the wrong data type in a cell, or might have a valid choice of data type e.g. number or keyword). And a date value (or text Excel thinks is a date if the format is General) will put a Date in the array. And now I've lost confidence in what Excel will do when I put it back. I could round all dates, yes, but actually I still don't know what level of rounding will always work. It might be less kludgy to force conversion to Double with CDbl? – Boddle Jan 24 '17 at 14:28
0

My conclusion is - it isn't safe to copy a Variant array containing Date elements into Excel using range.Value = array. There is a bug which occurs only in this case, and apparently only when the value is very close to, and presumably less than, an integer.

The bug exists in Excel 2003, 2007, 2010, 2013 ... I haven't installed 2016 yet.

A Date array is OK, and a Variant array containing Doubles is OK. A Variant array containing Dates can be converted to Double one element at a time with

array(i,j) = CDbl(array(i,j))

and then it is safe again. The arithmetic in VBA is identical in all three cases - Dates are Doubles in VBA internally and you can use them interchangeably in arithmetic statements. Apart from formatting, Dates are Doubles for Excel as well. It's just handing from one to the other in this specific way triggers a bad bit of internal conversion code that rounds the fraction part up and the integer part down.

EDIT: Ron's suggestion of using .Value2 instead of .Value works and is much tidier (and faster) than converting to Double. Thanks, Ron. Global search and replace, here we go ...

Boddle
  • 13
  • 4
  • I can confirm that this behavior is also present in Excel 2016 – Ron Rosenfeld Jan 26 '17 at 01:45
  • See my last edit on my answer for an additional observation regarding the use of the `Value2` property. – Ron Rosenfeld Jan 26 '17 at 02:40
  • Oh, on my computer, the variant array converted to doubles will display the correct date in the worksheet cell; BUT the incorrect date is still displayed in the formula bar. – Ron Rosenfeld Jan 26 '17 at 02:50
  • I believe that may be an error in Excel, because if you enter a value such as 38715.9999999999 into some cell directly, you will see the same issue -- different display in cell and formula bar – Ron Rosenfeld Jan 26 '17 at 02:57
  • That is extremely weird. But I don't think it's going to cause a problem in the code. – Boddle Jan 26 '17 at 12:55