Re : chiffres et lettres dans excel
Macro trouvée sur un autre forum, non testée par moi-même
--------------------------------------------------
Public Function sommelettre(ByVal argument As Currency) As String
Dim entier As Double
Dim cts As Double
Dim resultat1 As String
Dim resultat2 As String
entier = Int(argument) ' Valeur entière
cts = (argument - entier) * 100 ' Valeur Centimes
resultat1 = somlet2(entier)
resultat2 = somlet2(cts)
If Trim(resultat1) <> "" Then
resultat1 = resultat1 & " Dinars "
End If
If Trim(resultat2) <> "" Then
resultat2 = resultat2 & " Cts"
End If
sommelettre = resultat1 & " et " & resultat2
End Function
'*************************************************************
' Fonction de conversion chiffres en lettres
'*************************************************************
Public Function somlet2(ByVal argument As Double) As String
Dim lettres As Variant
Dim unites As Variant
Dim dizaines As Variant
Dim centaines As Variant
Dim unite As Integer
Dim dix As Integer
Dim cent As Integer
Dim groupes As Variant
Dim chaine As String
Dim ng, nc As Integer
Dim i, j, k As Integer
Dim xx As String
chaine = Trim(Str(argument))
nc = Len(chaine) ' Nbre de chiffres
lettres = Array("", "mille", "million", "milliard", "billion")
unites = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", "huit", "neuf", "dix", "onze", "douze", "treize", "quatorze", "quinze", "seize", "dix sept", "dix huit", "dix neuf")
dizaines = Array("", "dix", "vingt", "trente", "quarante", "cinquante", "soixante", "soixante dix", "quatre vingt", "quatre vingt dix")
centaines = Array("", "cent", "deux cents", "trois cents", "quatre cents", "cinq cents", "six cents", "sept cents", "huit cents", "neuf cents")
If argument = 0 Then
somlet2 = ""
Else
If nc Mod 3 > 0 Then
ng = Int(nc / 3) + 1 ' Nbre de groupes
Else
ng = nc / 3
End If
ReDim groupes(ng - 1)
chaine = String(ng * 3 - nc, " ") & chaine
nc = Len(chaine)
j = 0
For i = nc To 1 Step -3
xx = Mid(chaine, i - 2, i)
groupes(j) = xx
j = j + 1
Next
chaine = ""
For j = 0 To UBound(groupes)
unite = Val(Mid(groupes(j), 3, 1))
dix = Val(Mid(groupes(j), 2, 1))
If dix = 1 Or dix = 7 Or dix = 9 Then
dix = dix - 1
unite = unite + 10
End If
cent = Val(Mid(groupes(j), 1, 1))
chaine = centaines(cent) & " " & dizaines(dix) & " " & unites(unite) & " " & lettres(j) & " " & chaine
Next
somlet2 = chaine
End If
End Function
