Excel、VBA 和日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5408225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel, VBA and Dates
提问by Rarge
All I am trying to do is take a date, add one to the date, stick it in a worksheet and let the worksheet run a formulae on the entered date. The cells are all formatted as English dates but when I assign the cell's value with the below code it thinks the date it's being given is in American format even though it's already in English format and so tries to convert it to English format. How can I stop it from thinking in American?
我想要做的就是取一个日期，在日期上添加一个，将其粘贴在工作表中，然后让工作表在输入的日期上运行公式。这些单元格都被格式化为英文日期，但是当我用下面的代码分配单元格的值时，它认为它给出的日期是美国格式，即使它已经是英文格式，因此尝试将其转换为英文格式。我怎样才能阻止它在美国思考？
I abandoned all VBA's functions and made a function to add one to the date because VBA was just giving me a headache.
我放弃了所有 VBA 的功能，并制作了一个将日期添加到日期的功能，因为 VBA 只是让我头疼。
Dim D, M, Y As String
Dim S1, S2, NewDay, NewMonth, NewYear As Integer
S1 = InStr(1, StartDate.Value, "/")
S2 = InStr(S1 + 1, StartDate.Value, "/")
Debug.Print (CStr(S1) + " " + CStr(S2))
D = Mid(StartDate.Value, 1, S1 - 1)
M = Mid(StartDate.Value, S1 + 1, S2 - S1 - 1)
Y = Mid(StartDate.Value, S2 + 1)
Debug.Print (D + " " + M + " " + Y)
NewDay = CInt(D) + 1
NewMonth = CInt(M)
NewYear = CInt(Y)
If NewDay > DaysInMonth(M, Y) Then
NewDay = 1
NewMonth = NewMonth + 1
If NewMonth > 12 Then
NewMonth = 1
NewYear = NewYear + 1
End If
End If
StartDate.Value = CStr(NewDay) + "/" + CStr(NewMonth) + "/" + CStr(NewYear)
then in another function I just set the cell's value like so
然后在另一个函数中，我只是像这样设置单元格的值
With sheet.Range("A2")
'.. Stuff
.Offset(i, 1) = StartDate.Value
回答by Patrick Honorez
VBA expects all dates to be in American way: mm/dd/yy.
I simply use constant for the proper format: kUsFmt = "#mm\/dd\/yyyy#"
Then wherever I need a date I use the format function with the kUsFmt:
VBA 期望所有日期都采用美国方式：mm/dd/yy。
我只是将常量用于正确的格式：kUsFmt = "#mm\/dd\/yyyy#"
然后，无论我在哪里需要日期，我都会使用带有 kUsFmt 的格式函数：
strSql = "...WHERE myDate = " & Format (Date,kUsFmt)
edit:
编辑：
And why are you splitting things in M d y in your code ? Adding 1 to a date is as simple asmyDate = myDate + 1!
为什么要在代码中拆分 M dy 中的内容？给日期加 1 就像myDate = myDate + 1!
回答by Chris
I think that the problem lies in manipulating as a String. Why not try something like this:
我认为问题在于作为字符串进行操作。为什么不尝试这样的事情：
StartDate.Value = Format(DateSerial(NewYear,NewMonth,NewDay), "dd/mm/yyyy")
StartDate.Value = Format(DateSerial(NewYear,NewMonth,NewDay), "dd/mm/yyyy")
回答by stema
Function AddDay(StartDate As Date) As Date
AddDay = StartDate + 1
End Function
Maybe I don't understand your problem, but if you treat your date as Date type, Excel will do this for you. You don't have to think about End of month or end of year.
也许我不明白您的问题，但是如果您将日期视为Date 类型，Excel 将为您执行此操作。您不必考虑月末或年末。
How this will look in your Excel cell is another part. this is pure formatting. Either you format the cell directly (right click -> format cell -> select "Date" and choose your format) or in VBA you have to say the Cell Objectwhat format to display.
这在 Excel 单元格中的外观是另一部分。这是纯格式。要么直接格式化单元格（右键单击 -> 格式化单元格 -> 选择“日期”并选择你的格式），要么在 VBA 中你必须说单元格对象显示什么格式。
Please don't do this by your own manipulating strings, this is like reinventing the wheel.
请不要通过自己的操作字符串来做到这一点，这就像重新发明轮子。
You can learn here a lot about dates in Excel cpearson.com/Excel/DateFunctions, and on Excel at all.
您可以在 Excel cpearson.com/Excel/DateFunctions和 Excel 中了解很多有关日期的信息。
Update: Thisis the better link, my first one is on date formulas within cells.
更新：这是更好的链接，我的第一个是单元格内的日期公式。
回答by Toby Allen
I know that using dates can be hugely frutstrating, but please dont write yet another date conversion / adding / formatting routine, you're future maintainers will not thank you.
我知道使用日期可能会非常令人沮丧，但请不要再写另一个日期转换/添加/格式化例程，你未来的维护者不会感谢你。
About the only format that is universal is yyyy-mm-dd which will be read correctly by virtually every date function around. Try writing in this format and reading in standard British Date format.
关于唯一通用的格式是 yyyy-mm-dd，它几乎可以被周围的每个日期函数正确读取。尝试以这种格式书写并以标准英国日期格式阅读。
