Excel/VBA 在从 VBA 转置/粘贴时将日期转换为美国格式 [英] Excel/VBA converts dates to American format on transpose/paste from VBA

问题描述

我现在在英国环境中工作.我想我已经在区域设置中设置了(企业环境中的 Windows 7，Excel 2016，ver 1803(我认为这也可能是 Office 365-?不管那是什么.)).我正在尝试使用 VBA 中的日期并将结果粘贴到 Excel 中.在开始和结束时，日期以英国格式处理.但在中间，Excel 坚持将它们转换为美国.粘贴:英国-> 美国-> 英国.

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) 

让我们看看 Locals 窗口:

Let's look in the Locals window:

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

在任何粘贴之前，我已经清除了工作表，无论是手动使用 Ctrl-AA、Alt-EAA 还是在代码中使用 ...UsedRange.清除.

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.

如果我这样粘贴:

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

我得到2018 年 1 月 10 日"和2018 年 1 月 11 日"的 Excel 值，相差 31 天.哪个是对的.

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)

前两个值是10/01/2018"和11/01/2018"(减去 1).所以十月和十一月变成了一月和一月.这是错误的.让我们把这个也扔进去(一个行数组，没有转置):

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

啊！现在他们进来了！:43374、43405(又是 31 分)！

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

在 v2 到 v5 中，Date 类型变成了 String 类型.并且所有字符串都是正确的(1/10/2018"、1/11/2018"，用于 10 月和 11 月).

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

因此，出于某种原因，Excel/VBA 对这些字符串使用了错误的区域设置.如果我在两个单元格中键入1/10/2018"和1/11/2018"，然后将它们相减，我得到 31.但是当我的转置数组将这两个相同的字符串放入两个单元格时，它们显示为10/01/2018"和11/01/2018"(并减去 1).粘贴的字符串被读取为美国日期，然后转换为英国.

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

(在上面的所有示例中，工作表中的日期都是通用格式.我尝试在粘贴前后的单元格上使用日期格式，但没有任何改进.在不同的时间和地点进行类似的工作，我的做法是避免在 Excel 单元格中使用日期，并隐藏我的字符串应该是日期的事实(制作 MMM.YY 形式的字符串)，以避免让 Excel 更改它们.这次我认为只是在该死的 Excel 中使用该死的日期.)

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

推荐答案

如果您不指定单元格格式，它似乎会替换为该国家/地区格式的日期格式.您可能希望适当地格式化单元格.

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.

我们地区的默认日期格式为 yyyy-mm-dd.

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

测试代码

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

图纸图片

本地窗口

我发现当我转置一个日期格式的数组时，它会变为字符格式.防止这种情况的一种方法是将矩阵转换为代码.

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 在从 VBA 转置/粘贴时将日期转换为美国格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！