Excel/VBA converts dates to American format on transpose/paste from VBA

Question

I'm working in a British context right now. Which I think I have set up in regional settings (Windows 7 in a corporate context, Excel 2016, ver 1803 (I think this may also be Office 365-? Whatever that is.)). I'm trying to work with dates in VBA and paste the results to Excel. At the beginning, and at the end, the dates are treated in UK format. But in the middle Excel insists on converting them to American. Pasting does: UK-> US -> UK.

Dim p_dtTermArray() As Date
ReDim p_dtTermArray(1 To lgNumTerms)
p_dtTermArray(1) = DateSerial(2018, 10, 1) 
p_dtTermArray(2) = DateSerial(2018, 11, 1)

Let's look in the Locals window:

p_dtTermArray(1)        #1/10/2018#       Date
p_dtTermArray(2)        #1/11/2018#       Date

Before any pasting, I've cleared the worksheet, both manually with Ctrl-A-A, Alt-E-A-A, and in code with ...UsedRange.Clear.

Well the situation just evolved as I'm writing this and trying things.

If I paste this way:

rgOutput.Offset(0, 0) = p_dtTermArray(1)
rgOutput.Offset(0, 1) = p_dtTermArray(2)

I get Excel values of "1/10/2018" and "1/11/2018", with a difference of 31 days. Which is right.

But if I do this (a column array, with Transpose):

wb.Range(rgOutput.Offset(3, 6), rgOutput.Offset(2 + lgNumTerms, 6)) = Application.Transpose(p_dtTermArray)

The first two values are "10/01/2018" and "11/01/2018" (which subtract to 1). So Oct and Nov have turned into Jan and Jan. Which is wrong. Let's throw this one in too (a row array, no Transpose):

wb.Range(rgOutput.Offset(3, 7), rgOutput.Offset(3, 6 + lgNumTerms)) = p_dtTermArray

Ah! Now they come in as longs!: 43374, 43405 (which are 31 apart again)!

So it's (partly) due to Transpose! Let's try:

Dim v1, v2, v3, v4, v5
v1 = p_dtTermArray
v2 = Application.Transpose(p_dtTermArray)
v3 = Application.Transpose(v1)
v4 = Application.Transpose(v2)
v5 = Application.Transpose(Application.Transpose(v1))

In v2 to v5, the Date type gets turned into String type. And all the strings are correct ("1/10/2018", "1/11/2018", for Oct and Nov).

So for some reason, Excel/VBA use the wrong regional setting on these strings. If I type "1/10/2018" and "1/11/2018" in two cells, and subtract them, I get 31. But when my transposed array puts those same two strings into two cells, they appear as "10/01/2018" and "11/01/2018" (and subtract to 1). The pasted strings are read as American dates and then converted to UK.

I don't know where else to look for what might govern the misreading of these strings in one very specific context. Is the right thing just to avoid Transpose with dates (and, what?, manually transpose for column vectors (when you need to have the flexibility to do either row or column)?)?

(In all the examples above, the dates in the worksheet have General format. I have tried using date formats on the cells both before and after pasting, with no improvement. In a different time and place when I did similar work, my practice was to avoid using dates in Excel cells, and hide the fact that my strings were meant to be dates (making strings of form MMM.YY), to avoid having Excel alter them. This time I thought it might be nice to just use darned dates in darned Excel.)

Thanks for the comment, Tim Williams. I failed to find that one. Mine might fairly be called a duplicate. I've only been able to skim that thread so far, but my first reaction has been to stick to my current work around of simply avoiding transposing dates. Now I'm making (1xnum, 1x1) arrays. Later I'll just use strings not dates if I run into more problems. Thanks for the link. — RomnieEE

Dy.Lee Dy.Lee · Accepted Answer · 2018-11-17T10:30:34

If you do not specify a cell format, it appears to be replaced with a date format for that country format. It is likely that you want to format the cell appropriately.

According to my tests, it seems to automatically recognize when I set the date value, specify the cell format as UK or US, and then put the value back into the variable.

Our region has a default date format of yyyy-mm-dd.

test code

Sub test()
    Dim p_dtTermArray() As Date
    Dim vR() As Long
    Dim lgnumterms As Integer
    lgnumterms = 2

    ReDim p_dtTermArray(1 To lgnumterms)
    p_dtTermArray(1) = DateSerial(2018, 10, 1)
    p_dtTermArray(2) = DateSerial(2018, 11, 1)
    ReDim vR(1 To lgnumterms)

    vR(1) = DateSerial(2018, 10, 1)
    vR(2) = DateSerial(2018, 11, 1)
    With Range("a1").Resize(1, 2)
        .Value = p_dtTermArray
        .NumberFormatLocal = "dd-mm-yyyy"
    End With

    With Range("a2").Resize(1, 2)
        .Value = vR
        .NumberFormatLocal = "mm-dd-yyyy"
    End With

    Dim vDB, vDB2
        vDB = Range("a1").Resize(1, 2)
        vDB2 = Range("a2").Resize(1, 2)

End Sub

Sheet image

Locals window

I found that when I transpose an array of date format, it changes to a character format. A way to prevent this would be to convert the matrix into code.

Sub test()
    Dim p_dtTermArray() As Date
    Dim vR() As Long
    Dim lgnumterms As Integer
    Dim r As Long, c As Long, i As Long, c As Long
    lgnumterms = 2

    ReDim p_dtTermArray(1 To lgnumterms)
    p_dtTermArray(1) = DateSerial(2018, 10, 1)
    p_dtTermArray(2) = DateSerial(2018, 11, 1)
    ReDim vR(1 To lgnumterms)

    vR(1) = DateSerial(2018, 10, 1)
    vR(2) = DateSerial(2018, 11, 1)
    With Range("a1").Resize(1, 2)
        .Value = p_dtTermArray
        .NumberFormatLocal = "dd-mm-yyyy"
    End With

    With Range("a2").Resize(1, 2)
        .Value = vR
        .NumberFormatLocal = "mm-dd-yyyy"
    End With

    Dim vDB, vDB2, vD() As Long, vD2() As Long

        vDB = Range("a1").Resize(1, 2).Value
        vDB2 = Range("a2").Resize(1, 2).Value


    r = UBound(vDB, 1)
    c = UBound(vDB, 2)

    ReDim vD(1 To c, 1 To r)
    ReDim vD2(1 To c, 1 To r)
    For i = 1 To r
        For j = 1 To c
            vD(j, i) = vDB(i, j)
            vD2(j, i) = vDB2(i, j)
        Next j
    Next i
    Range("d1").Resize(2) = vD
    Range("f1").Resize(2) = vD2


End Sub

Excel/VBA converts dates to American format on transpose/paste from VBA

1 Answers