vba 类型与 CDate 不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20806580/
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
Type mismatch with CDate
提问by DarkSun
I have the following code:
我有以下代码：
minDate = CDate(Table.Cell(i, 4).Range.Text)
But I get a "Type mismatch error".
Table.Cell(i, 4)is in a "dd.mm.yy" format.
但我收到“类型不匹配错误”。
Table.Cell(i, 4)是“dd.mm.yy”格式。
回答by laylarenee
I wrote a macro to test this and the date format dd.mm.yyyyis invalid, it seems you must use dd/mm/yyyy(using slashes instead of period) or dd-mm-yyyy(using hyphens instead of periods):
我写了一个宏来测试这个并且日期格式dd.mm.yyyy无效，看来您必须使用dd/mm/yyyy（使用斜杠而不是句点）或dd-mm-yyyy（使用连字符而不是句点）：
Sub Macro1()
Dim minDate As Date
Dim DateStr As String
' with slashes, ok
DateStr = "27/12/2013"
minDate = CDate(DateStr)
' replace periods w/ slashes, ok
DateStr = "27.12.2013"
minDate = CDate(Replace(DateStr, ".", "/"))
' replace periods w/ hyphens, ok
DateStr = "27.12.2013"
minDate = CDate(Replace(DateStr, ".", "-"))
' type mismatch
DateStr = "27.12.2013"
minDate = CDate(DateStr)
End Sub
So, to solve your problem, you just need to replace all periods in your date to either hyphen or slash:
因此，要解决您的问题，您只需要将日期中的所有句点替换为连字符或斜线：
minDate = CDate(Replace(Table.Cell(i, 4).Range.Text, ".", "/"))
回答by Blackhawk
Try
尝试
minDate = CDate(Table.Cell(i, 4))
回答by andrija
The date conversion performance depends on the date format settings in Region and Language.
The following function ensures a successful conversion:
日期转换性能取决于区域和语言中的日期格式设置。
以下函数可确保成功转换：
Function TextToDate(vstrText) As Variant
' Description: Convert Text Date to Date
'--
Dim d As Variant
Dim sDate As String
sDate = Replace(vstrText, ".", "-")
If Right(sDate, 1) = "-" Then
sDate = Left(sDate, Len(sDate) - 1)
End If
If IsDate(sDate) Then
d = CDate(sDate)
Else
d = ""
End If
TextToDate = d
End Function
