用VBA读写后，Excel日期更改（交换了日期和月份） [英] Excel date changed (month and day swapped) after reading and writing with VBA

问题描述

我写了两行简单的VBA代码来读取和写入单元格的日期，但是出乎意料的是，运行后将月份和日期互换了。步骤为：

I wrote two lines of simple VBA codes to read from and write a date to a cell, but unexpectedly the month and day swapped after the run. The steps are:

1. 在单元格A1中输入日期 1/11/2017。日期现在显示为 01/11/2017。

1. Enter a date "1/11/2017" in the cell A1. The date is now displayed as "01/11/2017".

运行以下代码行：

Dim s As String
s = Cells(1, 1).value
Cells(2, 1).value = s

单元格B1现在显示 11/01/2017，其中月份和日期已互换。

The cell B1 now displays "11/01/2017" with the month and day swapped.

我在Windows的区域中的短日期格式设置为 dd / MM / yyyy，因此字符串s存储值 01/11 / 2017。但是，在写入单元格时，Excel隐式地将字符串s转换为假定为 mm / dd / yyyy的日期，而无需遵循Region中的日期格式设置。我尝试使用不同的短日期格式设置，但这不会更改Excel转换字符串的方式。

My short date format setting in Region in Windows is "dd/MM/yyyy" so the string s stores the value "01/11/2017". However, when writing to the cell, Excel implicitly converts the string s to a date assuming "mm/dd/yyyy" without following the date format setting in Region. I tried to use different short date format settings but that does not change the way Excel converts the string.

所以我的问题是：什么可以解释日期和月份的交换？是什么控制着Excel如何读取日期字符串并写入单元格？

So my question is: what could explain the swapping of the day and month? What controls how Excel reads the date string and write to the cell?

推荐答案

Dim s As String
s = Cells(1, 1).Value

s = Cells（1，1）.value 会将单元格的 value 存储在 String 。不是它在单元格中显示的内容。

s = Cells(1, 1).value will store the value of the cell in a String. Not what it shows in the cell.

例如

如果您的单元格具有 11/1/2017 ，但其格式设置为显示 01/11/17 ，然后 s 将存储 2017年11月1日。这不是约会。它是 String 。如果您不相信我，请尝试

If your cell has 11/1/2017 but is formatted to show 01/11/17 then s will store 11/1/2017. This is not a date. It is a String. If you do not believe me then try this

Dim s As String
s = Cells(1, 1).Value
Debug.Print s + 1 '<~~ You will get a Type Mismatch error

现在试试这个

Dim s As Date
s = Cells(1, 1).Value
Debug.Print s + 1 '<~~ You will get a proper date

== > 当您尝试存储 String 时，该字符串包含具有 General 格式的单元格的日期然后，Excel会将字符串转换为日期（以区域设置为准）为最佳格式。这就是您的情况。

==> When you are trying to store a String which contains a date to a cell which has General format then Excel will convert the string to date in a format what it feels is best (based on regional settings). And this is what is happening in your case.

解决方案：

1 声明一个 Date 变量，然后将值存储在其中。例如： Dim s As Date

1 Declare a Date variable and then store the value in that. Ex: Dim s As Date

OR

2 将字符串转换为日期，然后将其存储。 Excel不会更改它。这就是 DateValue（s）的作用。

2 Convert the string to a date and then store it. Excel will not change it. And that is what DateValue(s) does.

一些测试

让我们考虑这三种情况

Sub SampleA()
    Dim s As Date
    s = Cells(1, 1).Value
    Cells(2, 1).Value = s
End Sub

Sub SampleB()
    Dim s As String
    s = Cells(1, 1).Value
    Cells(2, 1).Value = DateValue(s)
End Sub

Sub SampleC() '<~~ As Suggested in the other answer
    Dim s As String

    s = Cells(1, 1).Value

    Cells(2, 1).NumberFormat = "dd/MM/yyyy"
    Cells(2, 1).Value = s
End Sub

截屏

这篇关于用VBA读写后，Excel日期更改（交换了日期和月份）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！