How to adjust for differences between 32 and 64 bit Excel VBA date handling?

Question

Problem:

64 bit Excel VBA has a nasty habit of converting array dates to numbers when that array is assigned to a range. 32 bit VBA, on the other hand, preserves the date format.

Example:

Here's a quick bit of example code to demonstrate the different date handling:

Sub test()
    Dim arr(0 to 1) As Variant
    arr(0) = "Text"
    arr(1) = #9/12/2007#
    ActiveSheet.Range("A1:B1") = arr
End Sub

(Note that the dates are not converted in 64 bit Excel if a single date value is used; it is necessary to have the first text value present)

Results:

When run in 32 bit Excel, the output is Text, 9/12/2007

When run in 64 bit Excel, the output is Text, 39337

It is as though 64 bit VBA only uses the Value2 property for all range objects.

Question:

How can I get 64 bit VBA to behave like 32 bit VBA without writing a function to handle all array writes?

Just to head off a possible well-intentioned response: I am aware that the underlying formula remains the same between those cells. 32 bit Excel, however, automatically sets the proper cells to a date format which greatly simplifies my code.

Answer 1

I had a problem like this earlier and resolved it by ensuring that the cell format was always set to a date format.

Something like this should do the trick:

Range("A1").Select
Selection.Format = "long date"