No, I’m not launching a match making service for Excel nerds. I’m talking about calendar dates. I was importing some data from a system that doesn’t seem to care what dates you might enter. Here’s the offender
|
1 |
TrsfrIn 03-2017 12/13/1206 IN OMAHA 00243406 GL 440.0000 3.9400 1733.60 440.0000 GL |
Instead of 2016, the user entered 1206. VBA doesn’t care.
|
1 2 |
?isdate("12/13/1206") True |
But Excel cares. As you know, Excel stores dates as the number of days since December 31, 1899. Anything before 1900-01-01 isn’t considered a date. The way this manifested was strange to me. I got the error (Application-defined or object-defined error) on this line
|
1 |
rStart.Resize(UBound(vaWrite, 1), UBound(vaWrite, 2)).Value = vaWrite |
When I filled the class, and specifically the TranDate property, no problem as VBA recognizes it as a date. When I fill the array vaWrite, no problem – the array contains text, numbers, and dates so it’s typed as a Variant. It’s only when I try to write it to a range that it complains. But why? I can type 12/13/1206 into a cell with no problem. It won’t recognize it as a date, but it doesn’t throw an error either.
A user (who is not me) got the error and clicked End. They’ve learned that clicking Debug only makes things worse – not that there are ever bugs in my code. When he clicked End, the code filled up the range all the way up to the bad date, line 1103.
That’s unexpected behavior. I would think the whole write operation would fail, but apparently not. The good news is that this partial writing of data led me to the root of the problem very quickly. The bad news is that the user was perfectly happy that clicking End produced data and he didn’t realize that the data wasn’t complete. He went on about his day until things just weren’t tying out properly. As much as I like the debugging help of a partial write, I think I would prefer if it didn’t write anything at all.
In any case, it’s an interesting insight into writing an array to a range all at once. It’s long known that filling an array and plopping it into a range is way faster than writing out cell-by-cell. But whatever you do in VBA to avoid looping, you’re not really avoiding looping. All you’re doing is moving the loop to a faster place. For instance, when you Join an array, something in VBA is looping, just not you. In this case, VBA is looping through vaWrite and filling up cells and it’s doing it faster than you or I could. The interesting thing to me is that it’s really a screen refresh that’s hiding the loop (maybe). As opposed to, say, VBA building a bunch of XML in the background and replacing part of the sheet.
But back to the error. Why an error anyway? As I said, I can type that non-date into Excel without error. I can even type that non-date in a cell and format the cell as a date without error. If I enter the formula =DATE(1206,12,13) in a cell, it returns 12/13/3106. Not a good result, but not an error either.
I think it all comes down to the fact that VBA has cast this data as a date and Excel won’t accept a date outside of its range. At least it won’t accept it from VBA. If I look at the locals window, I can see that my Variant Array has cast that value as a Variant/Date.
And this command in the Immediate Window fails with the same error as above
|
1 |
activecell.Value = dateserial(1206,12,13) |
but if I override the cast by casting it as a String, it works
|
1 |
activecell.Value = cstr(dateserial(1206,12,13)) |
If you’ve followed me down this rabbit hole and are still reading, then may god have mercy on your soul. Here’s my fix
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Public Sub FillFromRecordset(ByRef adRs As ADODB.Recordset, ByVal sItem As String, ByVal sDesc As String, ByVal sLoc As String) Dim dtTrans As Date With Me .ItemID = sItem .Description = Trim$(sDesc) .Location = Trim$(sLoc) .TranType = Trim$(Nz(adRs.Fields("TranType"))) .Period = Split(adRs.Fields("PerYear"), "-")(0) .Year = Split(adRs.Fields("PerYear"), "-")(1) '.TranDate = Nz(adRs.Fields("TranDate"), 0) dtTrans = Nz(adRs.Fields("TranDate"), 0) If VBA.Year(dtTrans) < 1900 Then .TranDate = DateSerial(1900, Month(dtTrans), Day(dtTrans)) Else .TranDate = dtTrans End If .Source = Trim$(Nz(adRs.Fields("Source"))) .SourceID = Trim$(Nz(adRs.Fields("SourceID"))) .RefNo = Trim$(Nz(adRs.Fields("RefNo"))) .DefUnits = Trim$(Nz(adRs.Fields("DefUnits"))) .DefQuantity = Nz(adRs.Fields("DefQuantity"), 0) .DefUnitCost = Nz(adRs.Fields("DefUnitCost"), 0) .Quantity = Nz(adRs.Fields("Quantity"), 0) .Units = Trim$(Nz(adRs.Fields("Units"))) End With End Sub |
Instead of assigning the recordset date field to the property (I commented out that line), I assign it to a date variable and test the year. If the year is less than 1900, then I change it to 1900. I considered changing it to the current year, but I think having a different incorrect year that doesn’t cause an error is better than trying to guess what it should have been.
You might have noticed that I prefixed the Year function with VBA. I have a property in this class called Year, so when I try to use it, VBA thinks I’m referring the class property and not the VBA function. Prefixing the function call with the library name ensures that it uses the right one.
I wrote that Nz function back in 2007 but it’s been updated since. So here’s the new and improved version.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Function Nz(fldTest As ADODB.Field, _ Optional vDefault As Variant) As Variant If IsNull(fldTest.Value) Then If IsMissing(vDefault) Then Select Case fldTest.Type Case adBSTR, adGUID, adChar, adWChar, adVarChar, adVarWChar Nz = vbNullString Case Else Nz = 0 End Select Else Nz = vDefault End If Else If Not IsMissing(vDefault) Then If vDefault = 0 Then Nz = Replace(fldTest.Value, "*", "") Else Nz = fldTest.Value End If Else Nz = fldTest.Value End If End If End Function |
This is great example of a function that needs some comments.