VBA Convert String to Date

Question

I have data in an excel sheet in the following format:

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:

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:

Run-time error '13':

Type mismatch

Is there a function in VBA which can:

parse string with any date 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")

VBA Convert String to Date

3 Answers