VBA 将字符串转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11589099/
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
VBA Convert String to Date
提问by vulcan raven
I have data in an excel sheet in the following format:
我在 Excel 工作表中有以下格式的数据：
ItemCode DeliveryDate 5456987 24.01.2009 5456988 5456989 12.24.2009 5456990 12/24/2009
I have stored the values of DeliveryDate in an array. I need to make decision on basics of date and then print the result in a new sheet. So I have to convert the values into array:
我已经将 DeliveryDate 的值存储在一个数组中。我需要决定日期的基础知识，然后将结果打印在新的工作表中。所以我必须将值转换为数组：
Dim current as Date, highest as Date, result() as Date
For Each itemDate in DeliveryDateArray
current = CDate(itemDate)
if current > highest then
highest = current
end if
' some more operations an put dates into result array
Next itemDate
'After activating final sheet...
Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result)
Unfortunately, CDate() function throws the error:
不幸的是，CDate() 函数抛出错误：
Run-time error '13':
Type mismatch
运行时错误“13”：
类型不匹配
Is there a function in VBA which can:
VBA中是否有一个函数可以：
- parse string with anydate format and return a date object to work with.
- return an empty date object, if the string is empty or malformed (for comparison in the loop).
- 解析具有任何日期格式的字符串并返回要使用的日期对象。
- 如果字符串为空或格式错误（用于循环中的比较），则返回一个空的日期对象。
Edit:
编辑：
To reproduce the error, simply run
myDate = CDate("24.01.2009")
要重现错误，只需运行
myDate = CDate("24.01.2009")
回答by Mark Hall
Try using Replaceto see if it will work for you. The problem as I see it which has been mentioned a few times above is the CDate function is choking on the periods. You can use replace to change them to slashes. To answer your question about a Function in vba that can parse any date format, there is not any you have very limited options.
尝试使用Replace，看看它是否适合你。正如我所看到的，上面已经提到过几次的问题是 CDate 函数在周期上阻塞。您可以使用替换将它们更改为斜线。要回答有关 vba 中可以解析任何日期格式的函数的问题，您的选择非常有限。
Dim current as Date, highest as Date, result() as Date
For Each itemDate in DeliveryDateArray
Dim tempDate As String
itemDate = IIf(Trim(itemDate) = "", "0", itemDate) 'Added per OP's request.
tempDate = Replace(itemDate, ".", "/")
current = Format(CDate(tempDate),"dd/mm/yyyy")
if current > highest then
highest = current
end if
' some more operations an put dates into result array
Next itemDate
'After activating final sheet...
Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result)
回答by DROP TABLE users
Looks like it could be throwing the error on the empty data row, have you tried to just make sure itemDate isn't empty before you run the CDate() function? I think this might be your problem.
看起来它可能会在空数据行上抛出错误，您是否尝试过在运行 CDate() 函数之前确保 itemDate 不为空？我想这可能是你的问题。
回答by littlecodefarmer758
I used this code:
我使用了这个代码：
ws.Range("A:A").FormulaR1C1 = "=DATEVALUE(RC[1])"
ws.Range("A:A").FormulaR1C1 = "=DATEVALUE(RC[1])"
column A will be mm/dd/yyyy
A 列将是 mm/dd/yyyy
RC[1] is column B, the TEXT string, eg, 01/30/12, THIS IS NOT DATE TYPE
RC[1] 是 B 列，文本字符串，例如 01/30/12，这不是日期类型
