How to adjust the differences between 32 and 64 bit Excel VBA date processing?

Question

Problem:

64-bit Excel VBA has the nasty habit of converting array dates to numbers when this array is assigned to a range. On the other hand, 32-bit VBA retains the date format.

Example:

Here is a small code example to demonstrate 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 dates are not converted to 64-bit Excel, if you use the same date value, you must have the first text value)

Results:

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

When working in 64-bit Excel, the output is Text, 39337

As if 64-bit VBA uses only the Value2 property for all objects in the range.

Question:

How can I make a 64-bit VBA behave like a 32-bit VBA without writing a function to process all array entries?

Just to prevent a possible good reaction: I know that the basic formula remains unchanged between these cells. 32-bit Excel, however, automatically sets the correct cells to the date format, which greatly simplifies my code.

Answer 1

I had this problem before, and I solved it by making sure that the cell format is always set to the date format.

Something like this should do the trick:

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