Convertir un nombre en lettre

martyyal · Mars 25, 2017, 5:04

Bjours les experts
je voudrais savoir comment convertir un nombre en lettre dans Excel 2010

mdo100 · Mars 25, 2017, 7:23

Voici un fichier qui pourrait t’intéresser.

Pour l’utiliser il suffit de mettre les formules comme ceci:
=ConvNumberLetter(A1;0) 0 pour avoir virgule.
=ConvNumberLetter(A1;1) 1 pour les euros.
=ConvNumberLetter(A1;2) 2 pour les dollars.

Voici le code VBA de la fonction:

Option Explicit

'------------------------------------------------------------------------------------
' Devise=0   aucune
'       =1   Euro ?
'       =2   Dollar $
'------------------------------------------------------------------------------------
'
'   Conversion limitée à 999 999 999 999 999 ou 9 999 999 999 999,99
'   si le nombre contient plus de 2 décimales, il est arrondit à 2 décimales
'
'------------------------------------------------------------------------------------
 
Public Function ConvNumberLetter(Nombre As Double, Optional Devise As Byte = 0, Optional Langue As Byte = 0) As String
Dim dblEnt As Variant, byDec As Byte
Dim bNegatif As Boolean
Dim strDev As String, strCentimes As String
 
    If Nombre < 0 Then
        bNegatif = True
        Nombre = Abs(Nombre)
    End If
    dblEnt = Int(Nombre)
    byDec = CInt((Nombre - dblEnt) * 100)
    If byDec = 0 Then
        If dblEnt > 999999999999999# Then
            ConvNumberLetter = "#TropGrand"
            Exit Function
        End If
    Else
        If dblEnt > 9999999999999.99 Then
            ConvNumberLetter = "#TropGrand"
            Exit Function
        End If
    End If
    Select Case Devise
        Case 0
            If byDec > 0 Then strDev = " virgule "
        Case 1
            strDev = " Euro"
            If dblEnt >= 1000000 And Right$(dblEnt, 6) = "000000" Then strDev = " d'Euro"
            If byDec > 0 Then strCentimes = strCentimes & " Cent"
            If byDec > 1 Then strCentimes = strCentimes & "s"
        Case 2
            strDev = " Dollar"
            If byDec > 0 Then strCentimes = strCentimes & " Cent"
    End Select
    If dblEnt > 1 And Devise <> 0 Then strDev = strDev & "s"
    strDev = strDev & " "
    If dblEnt = 0 Then
        ConvNumberLetter = "zéro " & strDev
    Else
        ConvNumberLetter = ConvNumEnt(CDbl(dblEnt), Langue) & strDev
    End If
    If byDec = 0 Then
        If Devise <> 0 Then ConvNumberLetter = ConvNumberLetter & "zéro Cent"
    Else
        If Devise = 0 Then
            ConvNumberLetter = ConvNumberLetter & _
                               ConvNumDizaine(byDec, Langue, True) & strCentimes
        Else
            ConvNumberLetter = ConvNumberLetter & _
                               ConvNumDizaine(byDec, Langue, False) & strCentimes
        End If
    End If
    ConvNumberLetter = Replace(ConvNumberLetter, "  ", " ")
    If Left(ConvNumberLetter, 1) = " " Then ConvNumberLetter = _
       Right$(ConvNumberLetter, Len(ConvNumberLetter) - 1)
    If Right$(ConvNumberLetter, 1) = " " Then ConvNumberLetter = _
       Left(ConvNumberLetter, Len(ConvNumberLetter) - 1)
End Function
 
Private Function ConvNumEnt(Nombre As Double, Langue As Byte)
Dim iTmp As Variant, dblReste As Double
Dim strTmp As String
Dim iCent As Integer, iMille As Integer, iMillion As Integer
Dim iMilliard As Integer, iBillion As Integer
 
    iTmp = Nombre - (Int(Nombre / 1000) * 1000)
    iCent = CInt(iTmp)
    ConvNumEnt = Nz(ConvNumCent(iCent, Langue))
    dblReste = Int(Nombre / 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iTmp = dblReste - (Int(dblReste / 1000) * 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iMille = CInt(iTmp)
    strTmp = ConvNumCent(iMille, Langue)
    Select Case iTmp
        Case 0
        Case 1
            strTmp = " mille "
        Case Else
            strTmp = strTmp & " mille "
    End Select
    If iMille = 0 And iCent > 0 Then ConvNumEnt = "et " & ConvNumEnt
    ConvNumEnt = Nz(strTmp) & ConvNumEnt
    dblReste = Int(dblReste / 1000)
    iTmp = dblReste - (Int(dblReste / 1000) * 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iMillion = CInt(iTmp)
    strTmp = ConvNumCent(iMillion, Langue)
    Select Case iTmp
        Case 0
        Case 1
            strTmp = strTmp & " million "
        Case Else
            strTmp = strTmp & " millions "
    End Select
    If iMille = 1 Then ConvNumEnt = "et " & ConvNumEnt
    ConvNumEnt = Nz(strTmp) & ConvNumEnt
    dblReste = Int(dblReste / 1000)
    iTmp = dblReste - (Int(dblReste / 1000) * 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iMilliard = CInt(iTmp)
    strTmp = ConvNumCent(iMilliard, Langue)
    Select Case iTmp
        Case 0
        Case 1
            strTmp = strTmp & " milliard "
        Case Else
            strTmp = strTmp & " milliards "
    End Select
    If iMillion = 1 Then ConvNumEnt = "et " & ConvNumEnt
    ConvNumEnt = Nz(strTmp) & ConvNumEnt
    dblReste = Int(dblReste / 1000)
    iTmp = dblReste - (Int(dblReste / 1000) * 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iBillion = CInt(iTmp)
    strTmp = ConvNumCent(iBillion, Langue)
    Select Case iTmp
        Case 0
        Case 1
            strTmp = strTmp & " billion "
        Case Else
            strTmp = strTmp & " billions "
    End Select
    If iMilliard = 1 Then ConvNumEnt = "et " & ConvNumEnt
    ConvNumEnt = Nz(strTmp) & ConvNumEnt
End Function
 
Private Function ConvNumDizaine(Nombre As Byte, Langue As Byte, bDec As Boolean) As String
Dim TabUnit As Variant, TabDiz As Variant
Dim byUnit As Byte, byDiz As Byte
Dim strLiaison As String
 
    If bDec Then
        TabDiz = Array("zéro", "", "vingt", "trente", "quarante", "cinquante", _
                       "soixante", "soixante", "quatre-vingt", "quatre-vingt")
    Else
        TabDiz = Array("", "", "vingt", "trente", "quarante", "cinquante", _
                       "soixante", "soixante", "quatre-vingt", "quatre-vingt")
    End If
    If Nombre = 0 Then
        TabUnit = Array("zéro")
    Else
        TabUnit = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
                        "huit", "neuf", "dix", "onze", "douze", "treize", "quatorze", "quinze", _
                        "seize", "dix-sept", "dix-huit", "dix-neuf")
    End If
    If Langue = 1 Then
        TabDiz(7) = "septante"
        TabDiz(9) = "nonante"
    ElseIf Langue = 2 Then
        TabDiz(7) = "septante"
        TabDiz(8) = "huitante"
        TabDiz(9) = "nonante"
    End If
    byDiz = Int(Nombre / 10)
    byUnit = Nombre - (byDiz * 10)
    strLiaison = "-"
    If byUnit = 1 Then strLiaison = " et "
    Select Case byDiz
        Case 0
            strLiaison = " "
        Case 1
            byUnit = byUnit + 10
            strLiaison = ""
        Case 7
            If Langue = 0 Then byUnit = byUnit + 10
        Case 8
            If Langue <> 2 Then strLiaison = "-"
        Case 9
            If Langue = 0 Then
                byUnit = byUnit + 10
                strLiaison = "-"
            End If
    End Select
    ConvNumDizaine = TabDiz(byDiz)
    If byDiz = 8 And Langue <> 2 And byUnit = 0 Then ConvNumDizaine = ConvNumDizaine & "s"
    If TabUnit(byUnit) <> "" Then
        ConvNumDizaine = ConvNumDizaine & strLiaison & TabUnit(byUnit)
    Else
        ConvNumDizaine = ConvNumDizaine
    End If
End Function
 
Private Function ConvNumCent(Nombre As Integer, Langue As Byte) As String
Dim TabUnit As Variant
Dim byCent As Byte, byReste As Byte
Dim strReste As String
 
    TabUnit = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
                    "huit", "neuf", "dix")
    byCent = Int(Nombre / 100)
    byReste = Nombre - (byCent * 100)
    strReste = ConvNumDizaine(byReste, Langue, False)
    Select Case byCent
        Case 0
            ConvNumCent = strReste
        Case 1
            If byReste = 0 Then
                ConvNumCent = "cent"
            Else
                ConvNumCent = "cent " & strReste
            End If
        Case Else
            If byReste = 0 Then
                ConvNumCent = TabUnit(byCent) & " cents"
            Else
                ConvNumCent = TabUnit(byCent) & " cent " & strReste
            End If
    End Select
End Function
 
Private Function Nz(strNb As String) As String
    If strNb <> " zéro" Then Nz = strNb
End Function

Et voici le fichier ICI==> martyyal V1.xlsm (23,8 Ko)

Bonne utilisation.

Cordialement.

DocteurExcel · Mars 25, 2017, 4:24

Hello, peux tu mettre le code de cette fonction dans le corps de ton message stp?

mdo100 · Mars 25, 2017, 6:12

Salut @DocteurExcel,

Fait.

Cordialement.

martyyal · Mars 30, 2017, 1:35

mdo100:

Option Explicit

'------------------------------------------------------------------------------------
’ Devise=0 aucune
’ =1 Euro ?
’ =2 Dollar $
'------------------------------------------------------------------------------------
’
’ Conversion limitée à 999 999 999 999 999 ou 9 999 999 999 999,99
’ si le nombre contient plus de 2 décimales, il est arrondit à 2 décimales
’
'------------------------------------------------------------------------------------

Public Function ConvNumberLetter(Nombre As Double, Optional Devise As Byte = 0, Optional Langue As Byte = 0) As String
Dim dblEnt As Variant, byDec As Byte
Dim bNegatif As Boolean
Dim strDev As String, strCentimes As String

If Nombre &lt; 0 Then
    bNegatif = True
    Nombre = Abs(Nombre)
End If
dblEnt = Int(Nombre)
byDec = CInt((Nombre - dblEnt) * 100)
If byDec = 0 Then
    If dblEnt &gt; 999999999999999# Then
        ConvNumberLetter = "#TropGrand"
        Exit Function
    End If
Else
    If dblEnt &gt; 9999999999999.99 Then
        ConvNumberLetter = "#TropGrand"
        Exit Function
    End If
End If
Select Case Devise
    Case 0
        If byDec &gt; 0 Then strDev = " virgule "
    Case 1
        strDev = " Euro"
        If dblEnt &gt;= 1000000 And Right$(dblEnt, 6) = "000000" Then strDev = " d'Euro"
        If byDec &gt; 0 Then strCentimes = strCentimes & " Cent"
        If byDec &gt; 1 Then strCentimes = strCentimes & "s"
    Case 2
        strDev = " Dollar"
        If byDec &gt; 0 Then strCentimes = strCentimes & " Cent"
End Select
If dblEnt &gt; 1 And Devise &lt;&gt; 0 Then strDev = strDev & "s"
strDev = strDev & " "
If dblEnt = 0 Then
    ConvNumberLetter = "zéro " & strDev
Else
    ConvNumberLetter = ConvNumEnt(CDbl(dblEnt), Langue) & strDev
End If
If byDec = 0 Then
    If Devise &lt;&gt; 0 Then ConvNumberLetter = ConvNumberLetter & "zéro Cent"
Else
    If Devise = 0 Then
        ConvNumberLetter = ConvNumberLetter & _
                           ConvNumDizaine(byDec, Langue, True) & strCentimes
    Else
        ConvNumberLetter = ConvNumberLetter & _
                           ConvNumDizaine(byDec, Langue, False) & strCentimes
    End If
End If
ConvNumberLetter = Replace(ConvNumberLetter, "  ", " ")
If Left(ConvNumberLetter, 1) = " " Then ConvNumberLetter = _
   Right$(ConvNumberLetter, Len(ConvNumberLetter) - 1)
If Right$(ConvNumberLetter, 1) = " " Then ConvNumberLetter = _
   Left(ConvNumberLetter, Len(ConvNumberLetter) - 1)

End Function

Private Function ConvNumEnt(Nombre As Double, Langue As Byte)
Dim iTmp As Variant, dblReste As Double
Dim strTmp As String
Dim iCent As Integer, iMille As Integer, iMillion As Integer
Dim iMilliard As Integer, iBillion As Integer

iTmp = Nombre - (Int(Nombre / 1000) * 1000)
iCent = CInt(iTmp)
ConvNumEnt = Nz(ConvNumCent(iCent, Langue))
dblReste = Int(Nombre / 1000)
If iTmp = 0 And dblReste = 0 Then Exit Function
iTmp = dblReste - (Int(dblReste / 1000) * 1000)
If iTmp = 0 And dblReste = 0 Then Exit Function
iMille = CInt(iTmp)
strTmp = ConvNumCent(iMille, Langue)
Select Case iTmp
    Case 0
    Case 1
        strTmp = " mille "
    Case Else
        strTmp = strTmp & " mille "
End Select
If iMille = 0 And iCent &gt; 0 Then ConvNumEnt = "et " & ConvNumEnt
ConvNumEnt = Nz(strTmp) & ConvNumEnt
dblReste = Int(dblReste / 1000)
iTmp = dblReste - (Int(dblReste / 1000) * 1000)
If iTmp = 0 And dblReste = 0 Then Exit Function
iMillion = CInt(iTmp)
strTmp = ConvNumCent(iMillion, Langue)
Select Case iTmp
    Case 0
    Case 1
        strTmp = strTmp & " million "
    Case Else
        strTmp = strTmp & " millions "
End Select
If iMille = 1 Then ConvNumEnt = "et " & ConvNumEnt
ConvNumEnt = Nz(strTmp) & ConvNumEnt
dblReste = Int(dblReste / 1000)
iTmp = dblReste - (Int(dblReste / 1000) * 1000)
If iTmp = 0 And dblReste = 0 Then Exit Function
iMilliard = CInt(iTmp)
strTmp = ConvNumCent(iMilliard, Langue)
Select Case iTmp
    Case 0
    Case 1
        strTmp = strTmp & " milliard "
    Case Else
        strTmp = strTmp & " milliards "
End Select
If iMillion = 1 Then ConvNumEnt = "et " & ConvNumEnt
ConvNumEnt = Nz(strTmp) & ConvNumEnt
dblReste = Int(dblReste / 1000)
iTmp = dblReste - (Int(dblReste / 1000) * 1000)
If iTmp = 0 And dblReste = 0 Then Exit Function
iBillion = CInt(iTmp)
strTmp = ConvNumCent(iBillion, Langue)
Select Case iTmp
    Case 0
    Case 1
        strTmp = strTmp & " billion "
    Case Else
        strTmp = strTmp & " billions "
End Select
If iMilliard = 1 Then ConvNumEnt = "et " & ConvNumEnt
ConvNumEnt = Nz(strTmp) & ConvNumEnt

End Function

Private Function ConvNumDizaine(Nombre As Byte, Langue As Byte, bDec As Boolean) As String
Dim TabUnit As Variant, TabDiz As Variant
Dim byUnit As Byte, byDiz As Byte
Dim strLiaison As String

If bDec Then
    TabDiz = Array("zéro", "", "vingt", "trente", "quarante", "cinquante", _
                   "soixante", "soixante", "quatre-vingt", "quatre-vingt")
Else
    TabDiz = Array("", "", "vingt", "trente", "quarante", "cinquante", _
                   "soixante", "soixante", "quatre-vingt", "quatre-vingt")
End If
If Nombre = 0 Then
    TabUnit = Array("zéro")
Else
    TabUnit = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
                    "huit", "neuf", "dix", "onze", "douze", "treize", "quatorze", "quinze", _
                    "seize", "dix-sept", "dix-huit", "dix-neuf")
End If
If Langue = 1 Then
    TabDiz(7) = "septante"
    TabDiz(9) = "nonante"
ElseIf Langue = 2 Then
    TabDiz(7) = "septante"
    TabDiz(8) = "huitante"
    TabDiz(9) = "nonante"
End If
byDiz = Int(Nombre / 10)
byUnit = Nombre - (byDiz * 10)
strLiaison = "-"
If byUnit = 1 Then strLiaison = " et "
Select Case byDiz
    Case 0
        strLiaison = " "
    Case 1
        byUnit = byUnit + 10
        strLiaison = ""
    Case 7
        If Langue = 0 Then byUnit = byUnit + 10
    Case 8
        If Langue &lt;&gt; 2 Then strLiaison = "-"
    Case 9
        If Langue = 0 Then
            byUnit = byUnit + 10
            strLiaison = "-"
        End If
End Select
ConvNumDizaine = TabDiz(byDiz)
If byDiz = 8 And Langue &lt;&gt; 2 And byUnit = 0 Then ConvNumDizaine = ConvNumDizaine & "s"
If TabUnit(byUnit) &lt;&gt; "" Then
    ConvNumDizaine = ConvNumDizaine & strLiaison & TabUnit(byUnit)
Else
    ConvNumDizaine = ConvNumDizaine
End If

End Function

Private Function ConvNumCent(Nombre As Integer, Langue As Byte) As String
Dim TabUnit As Variant
Dim byCent As Byte, byReste As Byte
Dim strReste As String

TabUnit = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
                "huit", "neuf", "dix")
byCent = Int(Nombre / 100)
byReste = Nombre - (byCent * 100)
strReste = ConvNumDizaine(byReste, Langue, False)
Select Case byCent
    Case 0
        ConvNumCent = strReste
    Case 1
        If byReste = 0 Then
            ConvNumCent = "cent"
        Else
            ConvNumCent = "cent " & strReste
        End If
    Case Else
        If byReste = 0 Then
            ConvNumCent = TabUnit(byCent) & " cents"
        Else
            ConvNumCent = TabUnit(byCent) & " cent " & strReste
        End If
End Select

End Function

Private Function Nz(strNb As String) As String
If strNb <> " zéro" Then Nz = strNb
End Function

COMMENT DOIT JE INTRODUIRE CE CODE DANS MON FEUILLE DE CALCUL

mdo100 · Mars 30, 2017, 7:27

Bonjour à toi @martyyal,

Billy · Février 21, 2018, 9:13

Bonjour à tous,

Veuillez m’excuser pour l’intrusion, mais j’ai deux questions sur ce code :

1 - Comment ajouter une devise, et
2 - Comment mettre les textes en majuscules ?

Remarque : la devise ne prend jamais de “s” en pluriel

Merci d’avance

Mimimathy · Février 21, 2018, 9:20

Bonjour,

Regarde sur ce post,
tu as une fonction avec tout se qu’il te faut (devise,langue, etc)

Billy · Février 21, 2018, 9:36

Merci beaucoup, la casse est réglé.

Il me reste de mettre ma devise qui ne prend pas de “s” au pluriel.

Si vous avez la solution idéale, ce sera la bienvenue.

Merci encore.

Mimimathy · Février 21, 2018, 9:39

Re,

Donne un exemple de ce que tu cherches en devise ?

mdo100 · Février 21, 2018, 9:41

Bonjour @Billy,
Salut @Mimimathy,

@Billy Merci pour cette leçon d’orthographe, mais pour ton instruction:

Cordialement.

Billy · Février 21, 2018, 9:44

Re,

Ma devise est le “Ariary”, et dans ce code, si le chiffre est supérieur à 1, les devises sont au pluriels (Euros, Livres, Dollars).
Et le Ariarys ne se dit pas chez nous.

Billy · Février 21, 2018, 9:48

Re

merci à vous @mdo100 pour l’explication, mais je veux parler des devises “Euro, Dollar, … Ariary, Dinar, …”

Mimimathy · Février 21, 2018, 10:41

Re,

Ne voulant me remettre dans les Fonctions qui, de plus, ne sont pas de moi
Je te propose, d’avrés le classeur que j’ai fourni
de mettre ta formule sans mettre les devises et d’ajouter la tienne
ce qui donnerais:

=ConvNumberLetter(A2;0;0;1;1)&" Ariary"

voir même, faire un format personnalisé sur la ou les colonnes, le cas échéant

mdo100 · Février 21, 2018, 10:44

Re @Billy,

Je ne connaissais pas cette monnaie qui est l’unité monétaire de la République de Madagascar.

Voici la macro modifiée pour cette monnaie.

En “C2” si tu veux le texte en majuscule.

=MAJUSCULE(ConvNumberLetter(A2;1))

Ou en “C2” si tu veux le texte en nom propre.

=NOMPROPRE(ConvNumberLetter(A2;1))

La macro:

Option Explicit

'------------------------------------------------------------------------------------
' Devise=0   aucune
' Devise=1   Ariary unité monétaire de la République de Madagascar.

'------------------------------------------------------------------------------------
'
'   Conversion limitée à 999 999 999 999 999 ou 9 999 999 999 999,99
'   si le nombre contient plus de 2 décimales, il est arrondit à 2 décimales
'
'------------------------------------------------------------------------------------
 
Public Function ConvNumberLetter(Nombre As Double, Optional Devise As Byte = 0, Optional Langue As Byte = 0) As String
Dim dblEnt As Variant, byDec As Byte
Dim bNegatif As Boolean
Dim strDev As String, strCentimes As String
 
    If Nombre < 0 Then
        bNegatif = True
        Nombre = Abs(Nombre)
    End If
    dblEnt = Int(Nombre)
    byDec = CInt((Nombre - dblEnt) * 100)
    If byDec = 0 Then
        If dblEnt > 999999999999999# Then
            ConvNumberLetter = "#TropGrand"
            Exit Function
        End If
    Else
        If dblEnt > 9999999999999.99 Then
            ConvNumberLetter = "#TropGrand"
            Exit Function
        End If
    End If
    Select Case Devise
        Case 0
            If byDec > 0 Then strDev = " virgule "
        Case 1
            strDev = " Ariary"
            If dblEnt >= 1000000 And Right$(dblEnt, 6) = "000000" Then strDev = " d'Ariary"
            If byDec > 0 Then strCentimes = strCentimes & " Centime"
            If byDec > 1 Then strCentimes = strCentimes & "s"
        
    End Select
    If dblEnt > 1 And Devise <> 0 Then strDev = strDev & ""
    strDev = strDev & " "
    If dblEnt = 0 Then
        ConvNumberLetter = "zéro " & strDev
    Else
        ConvNumberLetter = ConvNumEnt(CDbl(dblEnt), Langue) & strDev
    End If
    If byDec = 0 Then
        If Devise <> 0 Then ConvNumberLetter = ConvNumberLetter & "zéro Cent"
    Else
        If Devise = 0 Then
            ConvNumberLetter = ConvNumberLetter & _
                               ConvNumDizaine(byDec, Langue, True) & strCentimes
        Else
            ConvNumberLetter = ConvNumberLetter & _
                               ConvNumDizaine(byDec, Langue, False) & strCentimes
        End If
    End If
    ConvNumberLetter = Replace(ConvNumberLetter, "  ", " ")
    If Left(ConvNumberLetter, 1) = " " Then ConvNumberLetter = _
       Right$(ConvNumberLetter, Len(ConvNumberLetter) - 1)
    If Right$(ConvNumberLetter, 1) = " " Then ConvNumberLetter = _
       Left(ConvNumberLetter, Len(ConvNumberLetter) - 1)
End Function
 
Private Function ConvNumEnt(Nombre As Double, Langue As Byte)
Dim iTmp As Variant, dblReste As Double
Dim strTmp As String
Dim iCent As Integer, iMille As Integer, iMillion As Integer
Dim iMilliard As Integer, iBillion As Integer
 
    iTmp = Nombre - (Int(Nombre / 1000) * 1000)
    iCent = CInt(iTmp)
    ConvNumEnt = Nz(ConvNumCent(iCent, Langue))
    dblReste = Int(Nombre / 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iTmp = dblReste - (Int(dblReste / 1000) * 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iMille = CInt(iTmp)
    strTmp = ConvNumCent(iMille, Langue)
    Select Case iTmp
        Case 0
        Case 1
            strTmp = " mille "
        Case Else
            strTmp = strTmp & " mille "
    End Select
    If iMille = 0 And iCent > 0 Then ConvNumEnt = "et " & ConvNumEnt
    ConvNumEnt = Nz(strTmp) & ConvNumEnt
    dblReste = Int(dblReste / 1000)
    iTmp = dblReste - (Int(dblReste / 1000) * 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iMillion = CInt(iTmp)
    strTmp = ConvNumCent(iMillion, Langue)
    Select Case iTmp
        Case 0
        Case 1
            strTmp = strTmp & " million "
        Case Else
            strTmp = strTmp & " millions "
    End Select
    If iMille = 1 Then ConvNumEnt = "et " & ConvNumEnt
    ConvNumEnt = Nz(strTmp) & ConvNumEnt
    dblReste = Int(dblReste / 1000)
    iTmp = dblReste - (Int(dblReste / 1000) * 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iMilliard = CInt(iTmp)
    strTmp = ConvNumCent(iMilliard, Langue)
    Select Case iTmp
        Case 0
        Case 1
            strTmp = strTmp & " milliard "
        Case Else
            strTmp = strTmp & " milliards "
    End Select
    If iMillion = 1 Then ConvNumEnt = "et " & ConvNumEnt
    ConvNumEnt = Nz(strTmp) & ConvNumEnt
    dblReste = Int(dblReste / 1000)
    iTmp = dblReste - (Int(dblReste / 1000) * 1000)
    If iTmp = 0 And dblReste = 0 Then Exit Function
    iBillion = CInt(iTmp)
    strTmp = ConvNumCent(iBillion, Langue)
    Select Case iTmp
        Case 0
        Case 1
            strTmp = strTmp & " billion "
        Case Else
            strTmp = strTmp & " billions "
    End Select
    If iMilliard = 1 Then ConvNumEnt = "et " & ConvNumEnt
    ConvNumEnt = Nz(strTmp) & ConvNumEnt
End Function
 
Private Function ConvNumDizaine(Nombre As Byte, Langue As Byte, bDec As Boolean) As String
Dim TabUnit As Variant, TabDiz As Variant
Dim byUnit As Byte, byDiz As Byte
Dim strLiaison As String
 
    If bDec Then
        TabDiz = Array("zéro", "", "vingt", "trente", "quarante", "cinquante", _
                       "soixante", "soixante", "quatre-vingt", "quatre-vingt")
    Else
        TabDiz = Array("", "", "vingt", "trente", "quarante", "cinquante", _
                       "soixante", "soixante", "quatre-vingt", "quatre-vingt")
    End If
    If Nombre = 0 Then
        TabUnit = Array("zéro")
    Else
        TabUnit = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
                        "huit", "neuf", "dix", "onze", "douze", "treize", "quatorze", "quinze", _
                        "seize", "dix-sept", "dix-huit", "dix-neuf")
    End If
    If Langue = 1 Then
        TabDiz(7) = "septante"
        TabDiz(9) = "nonante"
    ElseIf Langue = 2 Then
        TabDiz(7) = "septante"
        TabDiz(8) = "huitante"
        TabDiz(9) = "nonante"
    End If
    byDiz = Int(Nombre / 10)
    byUnit = Nombre - (byDiz * 10)
    strLiaison = "-"
    If byUnit = 1 Then strLiaison = " et "
    Select Case byDiz
        Case 0
            strLiaison = " "
        Case 1
            byUnit = byUnit + 10
            strLiaison = ""
        Case 7
            If Langue = 0 Then byUnit = byUnit + 10
        Case 8
            If Langue <> 2 Then strLiaison = "-"
        Case 9
            If Langue = 0 Then
                byUnit = byUnit + 10
                strLiaison = "-"
            End If
    End Select
    ConvNumDizaine = TabDiz(byDiz)
    If byDiz = 8 And Langue <> 2 And byUnit = 0 Then ConvNumDizaine = ConvNumDizaine & "s"
    If TabUnit(byUnit) <> "" Then
        ConvNumDizaine = ConvNumDizaine & strLiaison & TabUnit(byUnit)
    Else
        ConvNumDizaine = ConvNumDizaine
    End If
End Function
 
Private Function ConvNumCent(Nombre As Integer, Langue As Byte) As String
Dim TabUnit As Variant
Dim byCent As Byte, byReste As Byte
Dim strReste As String
 
    TabUnit = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
                    "huit", "neuf", "dix")
    byCent = Int(Nombre / 100)
    byReste = Nombre - (byCent * 100)
    strReste = ConvNumDizaine(byReste, Langue, False)
    Select Case byCent
        Case 0
            ConvNumCent = strReste
        Case 1
            If byReste = 0 Then
                ConvNumCent = "cent"
            Else
                ConvNumCent = "cent " & strReste
            End If
        Case Else
            If byReste = 0 Then
                ConvNumCent = TabUnit(byCent) & " cents"
            Else
                ConvNumCent = TabUnit(byCent) & " cent " & strReste
            End If
    End Select
End Function
 
Private Function Nz(strNb As String) As String
    If strNb <> " zéro" Then Nz = strNb
End Function

Et un fichier exemple ICI==> Billy V1.xlsm (27,1 Ko)

Cordialement.

Billy · Février 21, 2018, 11:29

@Mimimathy , @mdo100, Je vais essayer les deux solutions, merci bcp.

Cordiales salutations,

Billy · Février 22, 2018, 8:32

Bonjour Messieurs,

C’est encore moi , pour une dernière question :

Est-ce possible de mettre le Ariary parmi les autres devises, c-a-d choisir un numéro pour l’afficher ET d’isoler le code qui met le “s” pour les autres devises seulement ?

Sincères salutations,

Billy

PS : Le “code” n’est pas mon fort , et je m’excuse pour le dérangement

K4te · Décembre 22, 2019, 9:43

Bonjour, je souhaite convertir des chiffres ( salaires ) en lettre. La formule VBA fonctionne mais le texte est toujours en anglais. Je n’arrive pas à le mettre en français.

Pourriez-vous m’aider ? suivi des contrats et avenants.xlsm (28,2 Ko)

Mimimathy · Décembre 22, 2019, 10:56

Bonjour

A tester
suivi des contrats et avenants.xlsm (39,9 Ko)