Convertir date au format texte us en numérique

Option Explicit
Dim valeur As String
Dim jour As String
Dim mois As String
Dim annee As String
Private Sub CommandButton1_Click()
valeur = "May 19th 2015"
extractionMots
End Sub
'Extraire les données séparées par un espace dans une chaine de caractères
Sub extractionMots()
    Dim Tableau() As String
    Dim i As Integer
    
    'découpe la chaine en fonction des espaces " "
    'le résultat de la fonction Split est stocké dans un tableau
    Tableau = Split(valeur, " ")
    
    'boucle sur le tableau pour visualiser le résultat
    For i = 0 To UBound(Tableau)
        'Le résultat s'affiche dans la fenêtre d'execution de l'éditeur de macros
        Debug.Print Tableau(i)
    Next i
    mois = Tableau(0)
    convertir
    jour = Tableau(1)
    annee = Tableau(2)
    extraireValeursNumeriques_DansChaine
    valeur = jour & "/" & mois & "/" & annee
    MsgBox valeur
End Sub
Sub extraireValeursNumeriques_DansChaine()
    Dim i As Byte, Nb As Byte
    Dim Cible As String, Resultat As String
    Dim Nombre As Double
    
    Cible = jour
    'Pour que fonction Val puisse reconnaitre les décimales: Remplacement des
    'virgules par des points
    Cible = Replace(Cible, ",", ".")
    'Pour gérer deux nombres qui se suivent: remplacement des espaces
    'par un caractère Alpha
    Cible = Replace(Cible, " ", "x")
    
    For i = 1 To Len(Cible)
        If IsNumeric(Mid(Cible, i, 1)) Then
        Nombre = val(Mid(Cible, i, Len(Cible) - i + 1))
        Nb = Nb + 1
        Resultat = Resultat & Nombre & vbLf
        i = i + Len(Str(Nombre)) - 1
        End If
    Next
    jour = Nombre
    ' MsgBox "Il y a " & Nb & " valeurs numériques dans la cellule " & vbLf & Resultat
End Sub
Sub convertir()
mois = Left(mois, 3)
    Select Case mois
    Case Is = "Jan"
      mois = "01"
    Case Is = "Feb"
       mois = "02"
    Case Is = "Mar"
        mois = "03"
    Case Is = "Apr"
       mois = "04"
    Case Is = "May"
       mois = "05"
    Case Is = "Jun"
       mois = "06"
    Case Is = "Jul"
       mois = "07"
    Case Is = "Aug"
      mois = "08"
    Case Is = "Sep"
       mois = "09"
    Case Is = "Oct"
       mois = "10"
    Case Is = "Nov"
       mois = "11"
    Case Is = "Dec"
       mois = "12"
    End Select
End Sub

Function DateFR(s As String) As Date
Dim t() As String, jour As Integer, mois As Integer, an As Integer, i As Integer
Dim MoisUS() As Variant

MoisUS() = Array("JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DIC")

t() = Split(s)

jour = Val(t(1)) ' jour

For i = 0 To 11
If UCase(Left(t(0), 3)) = MoisUS(i) Then Exit For
Next
mois = i + 1 ' mois

an = Val(t(2)) 'an

DateFR = CDate(jour & "/" & mois & "/" & an)

End Function

Sub test()

Dim Madate As Date, DateUS As String

DateUS = "May 19th 2015"
Madate = DateFR(DateUS)
MsgBox DateUS & vbCrLf & Format(Madate, "D MMMM YYYY")

DateUS = "August 1st 2015"
Madate = DateFR(DateUS)
MsgBox DateUS & vbCrLf & Format(Madate, "D MMMM YYYY")


DateUS = "Dicember 2nd 2015"
Madate = DateFR(DateUS)
MsgBox DateUS & vbCrLf & Format(Madate, "D MMMM YYYY")


End Sub

'déclaration des valeurs
Dim madate As String: Dim tablo1: Dim tablo2: Dim ii As Byte
Dim i As Byte
'valeurs des tableaux dans activation du UF
tablo1 = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
tablo2 = Array("th", "nd", "rd", "st")

'routine intégrée dans ma procédure
madate = "December 3rd 2015"
For ii = 0 To 3 'remplace les th,st,...
If madate Like "*" & tablo2(ii) & "*" Then madate = Replace(madate, tablo2(ii), ""): Exit For
Next ii

For i = 0 To 11 'attribue une valeur numérique au mois
If madate Like "*" & tablo1(i) & "*" Then madate = Replace(madate, tablo1(i), i + 1): Exit For
Next i

MsgBox Format(madate, "dd/mm/yyyy")