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) 

让我们在本地"窗口中查看:

Let's look in the Locals window:

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

在进行任何粘贴之前，我已经使用Ctrl-A-A，Alt-E-A-A手动清除了工作表，并使用...UsedRange.Clear清除了代码.

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)

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

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

但是，如果我这样做(带有Transpose的列数组):

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).因此，十月和十一月变成了一月和一月.这是错误的.让我们也把它扔进去(一个行数组，没有Transpose):

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中，日期类型变为字符串类型.并且所有字符串都是正确的(十月和十一月"为"1/10/2018"，"1/11/2018").

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.

根据我的测试，当我设置日期值，将单元格格式指定为UK或US，然后将值放回变量中时，它似乎可以自动识别.

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屋！