Excel 2007 VBA CDate 方法接受的格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15813246/
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 2007 VBA CDate method accepted formats
提问by Maciej Ogonowski
I am developing a change event in VBA for specific cells in Excel 2007. I want to convert dates (with time) with two different formats entered into these cells into one format (American).
我正在为 Excel 2007 中的特定单元格在 VBA 中开发更改事件。我想将输入这些单元格的两种不同格式的日期（带时间）转换为一种格式（美式）。
Here is my code which checks if entered dates are in one of the two desired formats. crmdate is the String value of the ActiveCell:
这是我的代码，用于检查输入的日期是否为两种所需格式之一。crmdate 是 ActiveCell 的字符串值：
If RegexServiceManager.test(crmdate) Then
outputDate = Format(CDate(crmdate), "MM/dd/yyyy hh:mm")
Application.EnableEvents = False
ActiveCell.Value = outputDate
ActiveCell.NumberFormat = "MM/dd/yyyy hh:mm"
Application.EnableEvents = True
ElseIf RegexSiebel.test(crmdate) Then
outputDate = CDate(crmdate)
Application.EnableEvents = False
ActiveCell.Value = outputDate
ActiveCell.NumberFormat = "MM/dd/yyyy hh:mm"
Application.EnableEvents = True
Else
MsgBox "Inapropriate date and time format"
End If
RegexServiceManager checks if date is in YYYY/MM/DD HH:MM:SS format and this works fine. RegexSiebel checks if date is int DD-MMM-YYYY HH:MM format and this is where trouble begins.
RegexServiceManager 检查日期是否为 YYYY/MM/DD HH:MM:SS 格式，这工作正常。RegexSiebel 检查日期是否为 int DD-MMM-YYYY HH:MM 格式，这就是问题开始的地方。
I get a "Type mismatch" error on outputDate = CDate(crmdate)line. I have removed Format method like the one in the upper "If" to verfify that the error comes from CDate.
我在线收到“类型不匹配”错误outputDate = CDate(crmdate)。我已经删除了上面“If”中的 Format 方法，以验证错误来自 CDate。
Could anyone advise on this? Maybe CDate does not recognize DD-MMM-YYYY (example: 01-Jan-2013) format? If so could anyone propose a workaround?
有人可以就此提出建议吗？也许 CDate 无法识别 DD-MMM-YYYY（例如：01-Jan-2013）格式？如果是这样，有人可以提出解决方法吗？
Target format is MM/DD/YYYY HH:MM.
目标格式为 MM/DD/YYYY HH:MM。
Thank you & Best Regards,
谢谢您最好的问候，
Maciej
马切伊
EDIT:
编辑：
outputDate is of Date format!
outputDate 是日期格式！
回答by Maciej Ogonowski
I think I found the answer. It is a bit silly but the above code does not work with Polish regional settings. It works fine with American (and probably British too) regional settings.
我想我找到了答案。这有点愚蠢，但上面的代码不适用于波兰语区域设置。它适用于美国（也可能是英国）区域设置。
I also changed the outputDate to Variant type.
我还将 outputDate 更改为 Variant 类型。
I ended up with this:
我结束了这个：
If RegexServiceManager.test(crmdate) Then
MsgBox "otputDate: " & TypeName(outputDate) & vbCrLf & "crmdate: " & TypeName(crmdate)
outputDate = CDate(crmdate)
MsgBox "otputDate: " & TypeName(outputDate) & vbCrLf & "crmdate: " & TypeName(crmdate)
Application.EnableEvents = False
ActiveCell.Value = outputDate
ActiveCell.NumberFormat = "MM/dd/yyyy hh:mm"
Application.EnableEvents = True
ElseIf RegexSiebel.test(crmdate) Then
MsgBox "otputDate: " & TypeName(outputDate) & vbCrLf & "crmdate: " & TypeName(crmdate)
outputDate = CDate(crmdate)
MsgBox "otputDate: " & TypeName(outputDate) & vbCrLf & "crmdate: " & TypeName(crmdate)
Application.EnableEvents = False
ActiveCell.Value = outputDate
ActiveCell.NumberFormat = "MM/dd/yyyy hh:mm"
Application.EnableEvents = True
Else
MsgBox "Inapropriate date and time format"
End If
The Message Boxes are just for debugging purposes.
消息框仅用于调试目的。
It is probably wise to detect regional settings at the beginning of the program, or write it in a better way to avoid this. :)
在程序开始时检测区域设置可能是明智的，或者以更好的方式编写它来避免这种情况。:)
Hope this helps someone.
希望这可以帮助某人。
Thank you & Best Regards,
谢谢您最好的问候，
