Good VBA Dates and Bad Excel Dates

Posted on by Dick Kusleika

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

Instead of 2016, the user entered 1206. VBA doesn’t care.

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

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

but if I override the cast by casting it as a String, it works

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

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.

This is great example of a function that needs some comments.

10 thoughts on “Good VBA Dates and Bad Excel Dates”

  1. If you use .Value2 it does not crash.

    Visual Basic
    rStart.Resize(UBound(vaWrite, 1), UBound(vaWrite, 2)).Value2 = vaWrite
    1

  2. If you’ve followed me down this rabbit hole and are still reading,
    then may god have mercy on your soul.

    Can’t believe you didn’t call this post “I’m late, I’m late, for a very important date” given the rabbit hole aspect.

    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).

    Can’t say I understand what you are saying here. Excel complained the first opportunity it got. i.e. as soon as it got wind that it was being fed something illegal, it choked, and told VBA. That seems an entirely separate issue than the ‘avoided overhead’ properties of transferring a 2D array in one operation vs transferring one line at a time. I guess you’re saying that while you can instruct VBA to dump a 2D variant into Excel in one line, Excel itself still has to process the data one cell at a time – as evidenced by the fact that you got some results returned from that one line of VBA. But the ‘avoided overhead’ of a variant is about getting data across the VBA/Excel boundary, and not about Excel having to loop through data one cell at a time, I thought.

    In other words, it’s like me ordering 20,000,000 shipments of one thing from Amazon vs one shipment of 20,000,000 things from Amazon…the bottleneck is likely to be processing delays at my local post office, and I can reduce the delay at my local post office by bundling the things into one shipment. But there’s still a possibility as I unwrap that one shipment and check the inventory that I will find one imperfect unit in it, and then complain to Amazon about it. Jeez, now I am late for a very important date…work. Luckily the boss is away down a rabbit-hole of his own today, and he doesn’t read this stuff.

  3. To me, writing an array to a Range is a single operation. It should fail or succeed. I think the interpreter should fail based on what I think is happening, not what’s really happening. I mean, that’s the point of a compiler/interpreter – to shield me from the messiness.

    What if Join didn’t accept dates and you tried Join(Array(1, "a", #12/31/2016#))? Would you expect this

    or an error. Join isn’t a single operation to VBA, but it is to me.

  4. Ok, gotcha. So the problem with the status quo I guess is that because it has done a partial write, you potentially now have to undo that partial write, so that the operator can fix the dodgy date and then reprocess the data.

    I think the interpreter should fail based on what I think is happening

    God forbid they would build Excel to reflect the vagrancies of your mind :-)

  5. Dick I’m curious about this part:

    Why, if there’s something in the passed field are you replacing it with an empty string when the default is zero. I would expect the default to only come into play if the passed field is null, and for the second part of your main If/Else to be just Nz = fldTest.Value as in your 2007 version.

    Hey, that’s some funky code formatting!

  7. When I said

    This is great example of a function that needs some comments.

    that’s exactly the line I was talking about. I couldn’t figure out what the heck I was doing there. But after some reflection, it came back to me.

    Ninety-five percent of what I do with ADO is reading text files exported from Open Systems Accounting Software. That Replace line fixes a particular problem with OSAS that would apply to literally nobody else. When a number doesn’t fit in the column of an OSAS report, the formatting is removed and an asterisk is added and this note at the bottom of the report.

    Turning print formatted reports into data sucks.

  8. One more thing. I had to relearn that wildcards aren’t allowed in Replace. Much of my confusion was about why I was replacing the entire field with an empty string. But I was replacing literal asterisks.

  9. Thanks for the explanation. I was reading the “*” as a wildcard too, and of course would never have guessed your special situation.

    Although I have been there. I recently created a system that takes two text files from an enterprise system and used VBA and formulas to merge them into a normalized worksheet, which I then query from another workbook. In the text files blanks could mean either “same as above” or “you shouldn’t expect data for this field in this record.” And then sometimes there was a string of asterisks, which meant “missing data.” Sussing it all out was a nightmare, but kinda fun.

  10. Pingback: #Excel Super Links #60 – shared by David Hager | Excel For You

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.