Convertion chiffre en lettre

Le
chp
peut on convertir un chiffre ou un nombre en lettre ?
ex 12 => douze
1235 => mille deux cent trente cinq
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
jero
Le #20847951
BONJOUR,
Oui, sans doute... (tu as un moteur de recherche ? internet ?)
MERCI D'AVANCE

"chp"
peut on convertir un chiffre ou un nombre en lettre ?
ex 12 => douze
1235 => mille deux cent trente cinq


Philippe.R
Le #20848291
Bonsoir,

Colle la fonction perso qui suit dans un module ordinaire du projet VBA du
classeur :

Function chiffrelettre(s)
Dim a As Variant, gros As Variant
a = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
"huit", "neuf", "dix", "onze", "douze", "treize", "quatorze", "quinze",
"seize", "dix sept", _
"dix huit", "dix neuf", "vingt", "vingt et un", "vingt deux", "vingt trois",
"vingt quatre", _
"vingt cinq", "vingt six", "vingt sept", "vingt huit", "vingt neuf",
"trente", "trente et un", _
"trente deux", "trente trois", "trente quatre", "trente cinq", "trente six",
"trente sept", _
"trente huit", "trente neuf", "quarante", "quarante et un", "quarante deux",
"quarante trois", _
"quarante quatre", "quarante cinq", "quarante six", "quarante sept",
"quarante huit", _
"quarante neuf", "cinquante", "cinquante et un", "cinquante deux",
"cinquante trois", _
"cinquante quatre", "cinquante cinq", "cinquante six", "cinquante sept",
"cinquante huit", _
"cinquante neuf", "soixante", "soixante et un", "soixante deux", "soixante
trois", _
"soixante quatre", "soixante cinq", "soixante six", "soixante sept",
"soixante huit", _
"soixante neuf", "soixante dix", "soixante et onze", "soixante douze",
"soixante treize", _
"soixante quatorze", "soixante quinze", "soixante seize", "soixante dix
sept", _
"soixante dix huit", "soixante dix neuf", "quatre-vingts", "quatre-vingt
un", _
"quatre-vingt deux", "quatre-vingt trois", "quatre-vingt quatre",
"quatre-vingt cinq", _
"quatre-vingt six", "quatre-vingt sept", "quatre-vingt huit", "quatre-vingt
neuf", _
"quatre-vingt dix", "quatre-vingt onze", "quatre-vingt douze", "quatre-vingt
treize", _
"quatre-vingt quatorze", "quatre-vingt quinze", "quatre-vingt seize",
"quatre-vingt dix sept", _
"quatre-vingt dix huit", "quatre-vingt dix neuf")
gros = Array("", "billions", "milliards", "millions", "mille", "Euros",
"billion", _
"milliard", "million", "mille", "Euro")
sp = Space(1)
chaine = "00000000000000"
centime = s * 100 - (Int(s) * 100)
s = Str(Int(s)): lg = Len(s) - 1: s = Right(s, lg): lg = Len(s)
If lg < 15 Then chaine = Mid(chaine, 1, (15 - lg)) Else chaine = ""
s = chaine + s
'billions au centaines
gp = 1
For k = 1 To 5
x = Mid(s, gp, 1): c = a(Val(x))
x = Mid(s, gp + 1, 2): d = a(Val(x))
If k = 5 Then
If t2 <> "" And c & d = "" Then mydz = "Euros" & sp: GoTo fin
If t <> "" And c = "" And d = "un" Then mydz = "un Euros" & sp: GoTo fin
If t <> "" And t2 = "" And c & d = "" Then mydz = "d'Euros" & sp: GoTo fin
If t & c & d = "" Then myct = "": mydz = "": GoTo fin
End If
If c & d = "" Then GoTo fin
If d = "" And c <> "" And c <> "un" Then mydz = c & sp & "cents " & gros(k)
& sp: GoTo fin
If d = "" And c = "un" Then mydz = "cent " & gros(k) & sp: GoTo fin
If d = "un" And c = "" Then myct = IIf(k = 4, gros(k) & sp, "un " & gros(k +
5) & sp): GoTo fin
If d <> "" And c = "un" Then mydz = "cent" & sp
If d <> "" And c <> "" And c <> "un" Then mydz = c & sp & "cent" + sp
myct = d & sp & gros(k) & sp
fin:
t2 = mydz & myct
t = t & mydz & myct
mydz = "": myct = ""
gp = gp + 3
Next
d = a(centime)
If t <> "" Then myct = IIf(centime = 1, " centime", " centimes")
If t = "" Then myct = IIf(centime = 1, " centime d'Euro", " centimes
d'Euro")
If centime = 0 Then d = "": myct = ""
chiffrelettre = t & d & myct
End Function

Il te suffira alors d'utiliser =chiffrelettre(12) pour obtenir douze

--
Avec plaisir
http://dj.joss.free.fr/trombine.htm
http://jacxl.free.fr/mpfe/trombino.html
Philippe.R
Pour se connecter au forum :
http://www.excelabo.net/web/mpfe
News://news.microsoft.com/microsoft.public.fr.excel
"chp" news:
peut on convertir un chiffre ou un nombre en lettre ?
ex 12 => douze
1235 => mille deux cent trente cinq


Mgr.Abile
Le #20848281
Bonsoir mon fils,
Par pitié, mon fils, ne le condamnez pas !
Ce paroissien est déjà bien en peine avec CDO
--
News://news.microsoft.com/microsoft.public.fr.excel
Allez en paix
T.Abile
"jero" news:
BONJOUR,
Oui, sans doute... (tu as un moteur de recherche ? internet ?)
MERCI D'AVANCE

"chp"
peut on convertir un chiffre ou un nombre en lettre ?
ex 12 => douze
1235 => mille deux cent trente cinq






chp
Le #20851301
Bonjour,
merci pour cette fonction, un problème lorsque je veut l'utiliser, le
message suivant "erreur de complilation erreur de syntaxe"

"Philippe.R" a écrit :

Bonsoir,

Colle la fonction perso qui suit dans un module ordinaire du projet VBA du
classeur :

Function chiffrelettre(s)
Dim a As Variant, gros As Variant
a = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
"huit", "neuf", "dix", "onze", "douze", "treize", "quatorze", "quinze",
"seize", "dix sept", _
"dix huit", "dix neuf", "vingt", "vingt et un", "vingt deux", "vingt trois",
"vingt quatre", _
"vingt cinq", "vingt six", "vingt sept", "vingt huit", "vingt neuf",
"trente", "trente et un", _
"trente deux", "trente trois", "trente quatre", "trente cinq", "trente six",
"trente sept", _
"trente huit", "trente neuf", "quarante", "quarante et un", "quarante deux",
"quarante trois", _
"quarante quatre", "quarante cinq", "quarante six", "quarante sept",
"quarante huit", _
"quarante neuf", "cinquante", "cinquante et un", "cinquante deux",
"cinquante trois", _
"cinquante quatre", "cinquante cinq", "cinquante six", "cinquante sept",
"cinquante huit", _
"cinquante neuf", "soixante", "soixante et un", "soixante deux", "soixante
trois", _
"soixante quatre", "soixante cinq", "soixante six", "soixante sept",
"soixante huit", _
"soixante neuf", "soixante dix", "soixante et onze", "soixante douze",
"soixante treize", _
"soixante quatorze", "soixante quinze", "soixante seize", "soixante dix
sept", _
"soixante dix huit", "soixante dix neuf", "quatre-vingts", "quatre-vingt
un", _
"quatre-vingt deux", "quatre-vingt trois", "quatre-vingt quatre",
"quatre-vingt cinq", _
"quatre-vingt six", "quatre-vingt sept", "quatre-vingt huit", "quatre-vingt
neuf", _
"quatre-vingt dix", "quatre-vingt onze", "quatre-vingt douze", "quatre-vingt
treize", _
"quatre-vingt quatorze", "quatre-vingt quinze", "quatre-vingt seize",
"quatre-vingt dix sept", _
"quatre-vingt dix huit", "quatre-vingt dix neuf")
gros = Array("", "billions", "milliards", "millions", "mille", "Euros",
"billion", _
"milliard", "million", "mille", "Euro")
sp = Space(1)
chaine = "00000000000000"
centime = s * 100 - (Int(s) * 100)
s = Str(Int(s)): lg = Len(s) - 1: s = Right(s, lg): lg = Len(s)
If lg < 15 Then chaine = Mid(chaine, 1, (15 - lg)) Else chaine = ""
s = chaine + s
'billions au centaines
gp = 1
For k = 1 To 5
x = Mid(s, gp, 1): c = a(Val(x))
x = Mid(s, gp + 1, 2): d = a(Val(x))
If k = 5 Then
If t2 <> "" And c & d = "" Then mydz = "Euros" & sp: GoTo fin
If t <> "" And c = "" And d = "un" Then mydz = "un Euros" & sp: GoTo fin
If t <> "" And t2 = "" And c & d = "" Then mydz = "d'Euros" & sp: GoTo fin
If t & c & d = "" Then myct = "": mydz = "": GoTo fin
End If
If c & d = "" Then GoTo fin
If d = "" And c <> "" And c <> "un" Then mydz = c & sp & "cents " & gros(k)
& sp: GoTo fin
If d = "" And c = "un" Then mydz = "cent " & gros(k) & sp: GoTo fin
If d = "un" And c = "" Then myct = IIf(k = 4, gros(k) & sp, "un " & gros(k +
5) & sp): GoTo fin
If d <> "" And c = "un" Then mydz = "cent" & sp
If d <> "" And c <> "" And c <> "un" Then mydz = c & sp & "cent" + sp
myct = d & sp & gros(k) & sp
fin:
t2 = mydz & myct
t = t & mydz & myct
mydz = "": myct = ""
gp = gp + 3
Next
d = a(centime)
If t <> "" Then myct = IIf(centime = 1, " centime", " centimes")
If t = "" Then myct = IIf(centime = 1, " centime d'Euro", " centimes
d'Euro")
If centime = 0 Then d = "": myct = ""
chiffrelettre = t & d & myct
End Function

Il te suffira alors d'utiliser =chiffrelettre(12) pour obtenir douze

--
Avec plaisir
http://dj.joss.free.fr/trombine.htm
http://jacxl.free.fr/mpfe/trombino.html
Philippe.R
Pour se connecter au forum :
http://www.excelabo.net/web/mpfe
News://news.microsoft.com/microsoft.public.fr.excel
"chp" news:
> peut on convertir un chiffre ou un nombre en lettre ?
> ex 12 => douze
> 1235 => mille deux cent trente cinq

.



SLED
Le #20852041
Bonjour,

Voici le code largement inspiré de cathy.
Tiens compte de nos particularités régionnales.

= NumLet("123";1;1;1;1)

A+
Led

---------------------------------------------------------------

'***********
' Devise=0 aucune
' =1 Euro €
' =2 Dollar $
' =3 €uro €
' Langue=0 Français
' =1 Belgique
' =2 Suisse
' =3 Hollandais
' =4 Néerlandais
' =5 Anglais
' Casse =0 Minuscule
' =1 Majuscule en début de phrase
' =2 Majuscule
' =3 Majuscule en début de chaque mot
' ZeroCent=0 Ne mentionne pas les cents s'ils sont égal à 0
' =1 Mentionne toujours les cents
'***********
' 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 NumLet(Nombre As Double, Optional Devise As Byte = 0, _
Optional Langue As Byte = 0, _
Optional Casse As Byte = 0, _
Optional ZeroCent 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
NumLet = "#TropGrand"
Exit Function
End If
Else
If dblEnt > 9999999999999.99 Then
NumLet = "#TropGrand"
Exit Function
End If
End If
Select Case Devise
Case 0
Select Case Langue
Case 0 To 2
If byDec > 0 Then strDev = " virgule "
Case 3 To 4
If byDec > 0 Then strDev = " komma "
Case 5
If byDec > 0 Then strDev = " comma "
Case Else
If byDec > 0 Then strDev = " virgule "
End Select
Case 1
strDev = " Euro"
Select Case Langue
Case 1 To 2
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 3 To 4
If dblEnt >= 1000000 And Right(dblEnt, 6) = "000000" Then
strDev = " Euro"
If byDec > 0 Then strCentimes = strCentimes & " Cent"
If byDec > 1 Then strCentimes = strCentimes & ""
Case 5
If dblEnt >= 1000000 And Right(dblEnt, 6) = "000000" Then
strDev = " Euro"
If byDec > 0 Then strCentimes = strCentimes & " Cent"
If byDec > 1 Then strCentimes = strCentimes & ""
End Select
Case 2
strDev = " Dollar"
If byDec > 0 Then strCentimes = strCentimes & " Cent"
Case 3
strDev = " €uro"
Select Case Langue
Case 1 To 2
If dblEnt >= 1000000 And Right(dblEnt, 6) = "000000" Then strDev
= " d'€uro"
If byDec > 0 Then strCentimes = strCentimes & " Cent"
If byDec > 1 Then strCentimes = strCentimes & "s"
Case 3 To 4
If dblEnt >= 1000000 And Right(dblEnt, 6) = "000000" Then strDev
= " €uro"
If byDec > 0 Then strCentimes = strCentimes & " Cent"
If byDec > 1 Then strCentimes = strCentimes & ""
Case 5
If dblEnt >= 1000000 And Right(dblEnt, 6) = "000000" Then strDev
= " €uro"
If byDec > 0 Then strCentimes = strCentimes & " Cent"
If byDec > 1 Then strCentimes = strCentimes & ""
End Select
End Select
Select Case Langue
Case 1 To 2
If dblEnt > 1 And Devise <> 0 Then strDev = strDev & "s"
Case 3 To 4
If dblEnt > 1 And Devise <> 0 Then strDev = strDev & ""
Case 5
If dblEnt > 1 And Devise <> 0 Then strDev = strDev & ""
End Select
strDev = strDev & " "
If dblEnt = 0 Then
Select Case Langue
Case 1 To 2
NumLet = "zéro " & strDev
Case 3 To 4
NumLet = "nul " & strDev
Case 5
NumLet = "zero " & strDev
End Select
Else
NumLet = ConvNumEnt(CDbl(dblEnt), Langue) & strDev
End If
If byDec = 0 Then
If Devise <> 0 Then
Select Case Langue
Case 1 To 2
If ZeroCent = 1 Then NumLet = NumLet & "zéro Cent"
Case 3 To 4
If ZeroCent = 1 Then NumLet = NumLet & "nul Cent"
Case 5
If ZeroCent = 1 Then NumLet = NumLet & "zero Cent"
End Select
End If
Else
If Devise = 0 Then
NumLet = NumLet & _
ConvNumDizaine(byDec, Langue, True) & strCentimes
Else
NumLet = NumLet & _
ConvNumDizaine(byDec, Langue, False) & strCentimes
End If
End If
NumLet = Replace(NumLet, " ", " ")
If Left(NumLet, 1) = " " Then NumLet = _
Right(NumLet, Len(NumLet) - 1)
If Right(NumLet, 1) = " " Then NumLet = _
Left(NumLet, Len(NumLet) - 1)
Select Case Casse
Case 0
NumLet = LCase(NumLet)
Case 1
NumLet = UCase(Left(NumLet, 1)) & _
LCase(Right(NumLet, Len(NumLet) - 1))
Case 2
NumLet = UCase(NumLet)
Case 3
NumLet = Application.WorksheetFunction.Proper(NumLet)
If Devise = 3 Then _
NumLet = Replace(NumLet, "€Uros", "€uros", , , vbTextCompare)
End Select
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
Select Case Langue
Case 1 To 2
strTmp = " mille "
Case 3 To 4
strTmp = "duizend " 'pas d'espace entre unité et mille
Case 5
strTmp = " thousand "
End Select
Case Else
Select Case Langue
Case 1 To 2
strTmp = strTmp & " mille "
Case 3 To 4
strTmp = strTmp & "duizend " 'pas d'espace entre unité et mille
Case 5
strTmp = strTmp & " thousand "
End Select
End Select

Select Case Langue
Case 1 To 2
If iMille = 0 And iCent > 0 Then ConvNumEnt = "et " & ConvNumEnt
Case 3 To 4
If iMille = 0 And iCent > 0 Then ConvNumEnt = "en " & ConvNumEnt
Case 5
If iMille = 0 And iCent > 0 Then ConvNumEnt = "and " & ConvNumEnt
End Select

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
Select Case Langue
Case 1 To 2
strTmp = strTmp & " million "
Case 3 To 4
strTmp = strTmp & " miljoen "
Case 5
strTmp = strTmp & " million "
End Select
Case Else
Select Case Langue
Case 1 To 2
strTmp = strTmp & " millions "
Case 3 To 4
strTmp = strTmp & " miljoen "
Case 5
strTmp = strTmp & " million "
End Select
End Select
Select Case Langue
Case 1 To 2
If iMille = 1 Then ConvNumEnt = "et " & ConvNumEnt
Case 3 To 4
If iMille = 1 Then ConvNumEnt = "en " & ConvNumEnt
Case 5
If iMille = 1 Then ConvNumEnt = "and " & ConvNumEnt
End Select
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
Select Case Langue
Case 1 To 2
strTmp = strTmp & " milliard "
Case 3 To 4
strTmp = strTmp & " miljard "
Case 5
strTmp = strTmp & " billion "
End Select
Case Else
Select Case Langue
Case 1 To 2
strTmp = strTmp & " milliards "
Case 3 To 4
strTmp = strTmp & " miljard "
Case 5
strTmp = strTmp & " billion "
End Select
End Select
Select Case Langue
Case 1 To 2
If iMillion = 1 Then ConvNumEnt = "et " & ConvNumEnt
Case 3 To 4
If iMillion = 1 Then ConvNumEnt = "en " & ConvNumEnt
Case 5
If iMillion = 1 Then ConvNumEnt = "and " & ConvNumEnt
End Select
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
Select Case Langue
Case 1 To 2
strTmp = strTmp & " billion "
Case 3 To 4
strTmp = strTmp & " biljoen "
Case 5
strTmp = strTmp & " billion "
End Select
Case Else
Select Case Langue
Case 1 To 2
strTmp = strTmp & " billions "
Case 3 To 4
strTmp = strTmp & " biljoen "
Case 5
strTmp = strTmp & " billion "
End Select
End Select
Select Case Langue
Case 1 To 2
If iMilliard = 1 Then ConvNumEnt = "et " & ConvNumEnt
Case 3 To 4
If iMilliard = 1 Then ConvNumEnt = "en " & ConvNumEnt
Case 5
If iMilliard = 1 Then ConvNumEnt = "and " & ConvNumEnt
End Select
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
Select Case Langue
Case 1 To 2
TabDiz = Array("zéro", "", "vingt", "trente", "quarante",
"cinquante", _
"soixante", "soixante", "quatre-vingt", "quatre-vingt")
Case 3 To 4
TabDiz = Array("nul", "", "twintig", "dertig", "veertig", "vijftig", _
"zestig", "zeventig", "tachtig", "negentig")
Case 5
TabDiz = Array("zero", "", "twenty", "thirty", "forty", "fifty", _
"sixty", "seventy", "eighty", "ninety")
End Select
Else
Select Case Langue
Case 1 To 2
TabDiz = Array("", "", "vingt", "trente", "quarante", "cinquante", _
"soixante", "soixante", "quatre-vingt", "quatre-vingt")
Case 3 To 4
TabDiz = Array("", "", "twintig", "dertig", "veertig", "vijftig", _
"zestig", "zeventig", "tachtig", "negentig")
Case 5
TabDiz = Array("", "", "twenty", "thirty", "forty", "fifty", _
"sixty", "seventy", "eighty", "ninety")
End Select
End If

If Nombre = 0 Then
Select Case Langue
Case 0 To 2
TabUnit = Array("zéro")
Case 3 To 4
TabUnit = Array("")
Case 5
TabUnit = Array("zero")
End Select
Else
Select Case Langue
Case 0 To 2
TabUnit = Array("", "un", "deux", "trois", "quatre", "cinq",
"six", "sept", _
"huit", "neuf", "dix", "onze", "douze", "treize", "quatorze",
"quinze", _
"seize", "dix-sept", "dix-huit", "dix-neuf")
Case 3 To 4
TabUnit = Array("", "één", "twee", "drie", "vier", "vijf",
"zes", "zeven", _
"acht", "negen", "tien", "elf", "twaalf", "dertien", "veertien",
"vijftien", _
"zestien", "zeventien", "achttien", "negentien")
Case 5
TabUnit = Array("", "one", "two", "three", "four", "five",
"six", "seven", _
"eight", "nine", "ten", "eleven", "twelve", "threeteen",
"fourteen", "fiveteen", _
"sixteen", "seventeen", "eightteen", "nineteen")
End Select
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 = "-"
Select Case Langue
Case 1 To 2
If byUnit = 1 Then strLiaison = " et "
Case 3 To 4
If byUnit = 1 Then strLiaison = "en"
If byUnit = 2 Then strLiaison = "ën"
If byUnit = 3 Then strLiaison = "ën"
If byUnit = 4 Then strLiaison = "en"
If byUnit = 5 Then strLiaison = "en"
If byUnit = 6 Then strLiaison = "en"
If byUnit = 7 Then strLiaison = "en"
If byUnit = 8 Then strLiaison = "en"
If byUnit = 9 Then strLiaison = "en"
Case 5
If byUnit = 1 Then strLiaison = " and "
End Select

Select Case Langue
Case 3 To 4
Select Case byDiz
Case 0
strLiaison = "" 'espace entre unité et centaine
Case 1
byUnit = byUnit + 10
strLiaison = " " 'espace entre unité et devise
Case 7
If Langue = 0 Then byUnit = byUnit + 10
Case 9
If Langue = 0 Then
byUnit = byUnit + 10
strLiaison = "-"
End If
End Select
Case Else
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
End Select
ConvNumDizaine = TabDiz(byDiz)
If byDiz = 8 And Langue < 2 And byUnit = 0 Then ConvNumDizaine =
ConvNumDizaine & "s"
If TabUnit(byUnit) <> "" Then
Select Case Langue
Case 1 To 2
ConvNumDizaine = ConvNumDizaine & strLiaison & TabUnit(byUnit)
'GESTION DE L'INVERSION
Case 3 To 4
ConvNumDizaine = TabUnit(byUnit) & strLiaison & ConvNumDizaine
Case 5
ConvNumDizaine = ConvNumDizaine & strLiaison & TabUnit(byUnit)
End Select
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
Select Case Langue
Case 0 To 2
TabUnit = Array("", "un", "deux", "trois", "quatre", "cinq",
"six", "sept", _
"huit", "neuf", "dix")
Case 3 To 4
TabUnit = Array("", "een", "twee", "drie", "vier", "vijf",
"zes", "zeven", _
"acht", "negen", "tien")
Case 5
TabUnit = Array("", "one", "two", "three", "four", "five",
"six", "seven", _
"eight", "nine", "ten")
End Select

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
Select Case Langue
Case 1 To 2
ConvNumCent = "cent"
Case 3 To 4
ConvNumCent = "honderd"
Case 5
ConvNumCent = "hunderd"
End Select
Else
Select Case Langue
Case 1 To 2
ConvNumCent = "cent " & strReste
Case 3 'hollandais
ConvNumCent = "honderd " & strReste
Case 4 ' flammand
Select Case byReste
Case 1 To 13 'en flandre "en" entre centaine et 1 à 12
ConvNumCent = "honderd " & " en " & strReste
Case Else
ConvNumCent = "honderd " & strReste
End Select
Case 5
ConvNumCent = "hunderd " & strReste
End Select
End If
Case Else
If byReste = 0 Then
Select Case Langue
Case 1 To 2
ConvNumCent = TabUnit(byCent) & " cents"
Case 3 To 4
ConvNumCent = TabUnit(byCent) & "honderd"
Case 5
ConvNumCent = TabUnit(byCent) & " hunderd"
End Select
Else
Select Case Langue
Case 1 To 2
ConvNumCent = TabUnit(byCent) & " cent " & strReste
Case 3 'hollandais
ConvNumCent = TabUnit(byCent) & "honderd " & strReste
Case 4 'flammand
Select Case byReste
Case 1 To 13 'en flandre "en" entre centaine et 1 à 13
ConvNumCent = TabUnit(byCent) & "honderd " & " en " &
strReste
Case Else
ConvNumCent = TabUnit(byCent) & "honderd " & strReste
End Select
Case 5
ConvNumCent = TabUnit(byCent) & " hunderd " & strReste
End Select
End If
End Select
End Function

Private Function Nz(strNb As String) As String
Dim var1 As String
If strNb = " zéro" Then var1 = " zéro"
If strNb = "nul" Then var1 = " zéro"
If strNb = " zero" Then var1 = " zéro"

If var1 = " zéro" Then
Nz = " "
Else: Nz = strNb
End If


End Function
Mgr T. Banni
Le #20852971
oui, oui, bien sûr ; dans la mienne de région, "régional", même s'il est de
l'étape, ne s'écrit qu'avec un "n"
T.B.

"SLED"

Tiens compte de nos particularités régionnales.


Philippe.R
Le #20853541
Bonjour,
Quelle est la ligne de code surlignée par l'éditeur ?
Attention à supprimer les retour de ligne intempestifs lors de la recopie
(les lignes de code sont alors en police rouge)
--
Avec plaisir
http://dj.joss.free.fr/trombine.htm
http://jacxl.free.fr/mpfe/trombino.html
Philippe.R
Pour se connecter au forum :
http://www.excelabo.net/web/mpfe
News://news.microsoft.com/microsoft.public.fr.excel
"chp" news:
Bonjour,
merci pour cette fonction, un problème lorsque je veut l'utiliser, le
message suivant "erreur de complilation erreur de syntaxe"

"Philippe.R" a écrit :

Bonsoir,

Colle la fonction perso qui suit dans un module ordinaire du projet VBA
du
classeur :

Function chiffrelettre(s)
Dim a As Variant, gros As Variant
a = Array("", "un", "deux", "trois", "quatre", "cinq", "six", "sept", _
"huit", "neuf", "dix", "onze", "douze", "treize", "quatorze", "quinze",
"seize", "dix sept", _
"dix huit", "dix neuf", "vingt", "vingt et un", "vingt deux", "vingt
trois",
"vingt quatre", _
"vingt cinq", "vingt six", "vingt sept", "vingt huit", "vingt neuf",
"trente", "trente et un", _
"trente deux", "trente trois", "trente quatre", "trente cinq", "trente
six",
"trente sept", _
"trente huit", "trente neuf", "quarante", "quarante et un", "quarante
deux",
"quarante trois", _
"quarante quatre", "quarante cinq", "quarante six", "quarante sept",
"quarante huit", _
"quarante neuf", "cinquante", "cinquante et un", "cinquante deux",
"cinquante trois", _
"cinquante quatre", "cinquante cinq", "cinquante six", "cinquante sept",
"cinquante huit", _
"cinquante neuf", "soixante", "soixante et un", "soixante deux",
"soixante
trois", _
"soixante quatre", "soixante cinq", "soixante six", "soixante sept",
"soixante huit", _
"soixante neuf", "soixante dix", "soixante et onze", "soixante douze",
"soixante treize", _
"soixante quatorze", "soixante quinze", "soixante seize", "soixante dix
sept", _
"soixante dix huit", "soixante dix neuf", "quatre-vingts", "quatre-vingt
un", _
"quatre-vingt deux", "quatre-vingt trois", "quatre-vingt quatre",
"quatre-vingt cinq", _
"quatre-vingt six", "quatre-vingt sept", "quatre-vingt huit",
"quatre-vingt
neuf", _
"quatre-vingt dix", "quatre-vingt onze", "quatre-vingt douze",
"quatre-vingt
treize", _
"quatre-vingt quatorze", "quatre-vingt quinze", "quatre-vingt seize",
"quatre-vingt dix sept", _
"quatre-vingt dix huit", "quatre-vingt dix neuf")
gros = Array("", "billions", "milliards", "millions", "mille", "Euros",
"billion", _
"milliard", "million", "mille", "Euro")
sp = Space(1)
chaine = "00000000000000"
centime = s * 100 - (Int(s) * 100)
s = Str(Int(s)): lg = Len(s) - 1: s = Right(s, lg): lg = Len(s)
If lg < 15 Then chaine = Mid(chaine, 1, (15 - lg)) Else chaine = ""
s = chaine + s
'billions au centaines
gp = 1
For k = 1 To 5
x = Mid(s, gp, 1): c = a(Val(x))
x = Mid(s, gp + 1, 2): d = a(Val(x))
If k = 5 Then
If t2 <> "" And c & d = "" Then mydz = "Euros" & sp: GoTo fin
If t <> "" And c = "" And d = "un" Then mydz = "un Euros" & sp: GoTo fin
If t <> "" And t2 = "" And c & d = "" Then mydz = "d'Euros" & sp: GoTo
fin
If t & c & d = "" Then myct = "": mydz = "": GoTo fin
End If
If c & d = "" Then GoTo fin
If d = "" And c <> "" And c <> "un" Then mydz = c & sp & "cents " &
gros(k)
& sp: GoTo fin
If d = "" And c = "un" Then mydz = "cent " & gros(k) & sp: GoTo fin
If d = "un" And c = "" Then myct = IIf(k = 4, gros(k) & sp, "un " &
gros(k +
5) & sp): GoTo fin
If d <> "" And c = "un" Then mydz = "cent" & sp
If d <> "" And c <> "" And c <> "un" Then mydz = c & sp & "cent" + sp
myct = d & sp & gros(k) & sp
fin:
t2 = mydz & myct
t = t & mydz & myct
mydz = "": myct = ""
gp = gp + 3
Next
d = a(centime)
If t <> "" Then myct = IIf(centime = 1, " centime", " centimes")
If t = "" Then myct = IIf(centime = 1, " centime d'Euro", " centimes
d'Euro")
If centime = 0 Then d = "": myct = ""
chiffrelettre = t & d & myct
End Function

Il te suffira alors d'utiliser =chiffrelettre(12) pour obtenir douze

--
Avec plaisir
http://dj.joss.free.fr/trombine.htm
http://jacxl.free.fr/mpfe/trombino.html
Philippe.R
Pour se connecter au forum :
http://www.excelabo.net/web/mpfe
News://news.microsoft.com/microsoft.public.fr.excel
"chp" news:
> peut on convertir un chiffre ou un nombre en lettre ?
> ex 12 => douze
> 1235 => mille deux cent trente cinq

.





Publicité
Poster une réponse
Anonyme