Function to return a date from a string (1 Viewer)

[Isskint]

Several times i have asked myself this question and seen it asked by others. If you know the format the date will take, it is easy to find it.
EG if the format is dd-mm-yy then Mid(SuppliedString,Instr(SuppliedString,"-")-2,8) would return you the date.

But what if the format is unknown? Using Mid(SuppliedString,Instr(SuppliedString,"-")-2,8) when the format supplied is, say dd mm yy, would return no date. What if the month was spelt out!

Inspired by this post http://www.access-programmers.co.uk/forums/showthread.php?t=236737 i decided to look at creating a generic function that could 'find' a date in ANY format in a supplied string. The result is a function that will attempt to find a date in any numeric format (dd mm yy, mm/dd/yy, m-dd-yyyy etc) and also if the month is supplied as a name (10 october 2012, october 10 2012 etc). I had hoped to include the use of st, nd, rd and th (12th, 21st etc) but with my approach using IsDate(), these are not recognised. Perhaps someone will be able to suggest an approach to do this

Public Function GetDates(varString As String) As String
'Function designed by Mark Godwin aka Isskint [EMAIL="akhnatunsolutions@gmail.com"]akhnatunsolutions@gmail.com[/EMAIL]
'Please use this as you wish. Recognition is appreciated but not required
'This function will find all possible date combinations in a supplied string
'It will return the last date substring found. During testing the 'valid' date was always the last substring
'This Function can be adjusted to return a Variant containing all the possible dates for user interrogation
'To do this change the Function to Public Function GetDates(varString As String) As Variant
'And change this line GetDates = varTemp(UBound(varTemp)) to GetDates = varTemp
Dim iLen, iMin, iMax, iLoopOuter, iLoopInner, iLenSubStr, iCnt As Integer
Dim varSubString As String
Dim varTemp As Variant
iLen = Len(varString)
If iLen > 26 Then
    iMax = 27
Else
    iMax = iLen
End If
If iLen < 6 Then
    iMin = iLen
Else
    iMin = 6
End If
iLenSubStr = iMin
iCnt = 0
For iLoopOuter = 1 To ((iLen - iMin + 1) - (iLen - iMax + 1))
 
    For iLoopInner = 1 To iLen - iLenSubStr + 1
        If IsDate(Mid(varString, iLoopInner, iLenSubStr)) Then
            varSubString = varSubString & Mid(varString, iLoopInner, iLenSubStr) & "~"
            iCnt = iCnt + 1
            If iCnt > 100 Then GoTo GetDatesExit
        End If
    Next iLoopInner
    iLenSubStr = iLenSubStr + 1
Next iLoopOuter
GetDatesExit:
If iCnt = 0 Then
    GetDates = "No date found"
Else
    varSubString = Left(varSubString, Len(varSubString) - 1)
    varTemp = Split(varSubString, "~")
 
    GetDates = varTemp(UBound(varTemp))
End If
End Function