Convertir les nombres en textes en VBA Excel
Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :
Convertir les nombres en texte
Cette formation propose de créer une fonction capable de traduire le résultat numérique d'un calcul, en chaîne de textes. Concrètement, elle offre la possibilité d'afficher le montant d'une facture client en toutes lettres, à côté de son résultat en chiffres. C'est une fonction VBA Excel qui permet de réaliser cette prouesse. Nous devons la coder. Une fois que le code sera transformé en complément VBA rattaché à l'application Excel, la fonction de conversion sera disponible comme n'importe quelle autre fonction de la feuille de calcul.
La figure ci-dessus illustre le résultat. Le montant numérique d'une facture client est traduit en toutes lettres, grâce à la fonction que nous devons développer.
Avant tout, je tiens vivement à remercier Abdelaziz Chelbi. Ce fabuleux programme est son oeuvre. Mais ce personnage charismatique empli d'humilité a souhaité que je le transcrive à sa place. Il s'agit d'un travail colossal qui est le fruit de beaucoup d'efforts et de nombreuses heures de réflexion, de tests et d'ajustements, pour enfin toucher au but. Avant même de penser au code, il s'agissait de trier et poser les hypothèses, afin d'amorcer la réflexion et d'envisager les solutions. Cher Abdelaziz, un immense merci donc, sachant que ce code sera utile à de nombreux professionnels.
Les nombres en lettres dans la langue française - Fonctionnement
Dans la prononciation ou l'écriture en lettres des nombres, on fait la distinction entre la partie entière et la partie décimale. Puis on utilise le terme virgule pour les séparer. Ces deux parties sont ensuite traitées de la même manière pour les convertir en toutes lettres. C'est le rôle de la première fonction à créer, NbEnLettres.
La fonction NbEnLettres() doit considérer que la partie entière est formée par des blocs de 3 chiffres. C'est la reconnaissance de ces blocs qui permet d'attribuer les désignations comme Mille, Million, Milliard etc... Pour la conversion en lettres, l'attribut Mille est remarquable. Tout d'abord il est invariable. Il ne s'accorde donc pas au pluriel. Toujours contrairement aux autres, lorsque le bloc des milliers vaut 1, le terme un ne se prononce pas. On dit et on écrit Mille par opposition à deux Mille, un Million ou un Milliard et deux Millions qui s'accordent par exemple. Nous crérons la fonction VBA NbEnMille() pour réaliser ce traitement.
Pour chaque bloc résultant du précédent traitement, on distinguera les chiffres des centaines et ceux des dizaines. Comme pour les milliers, le 1 devant le cent ne se prononce et ne s'écrit pas. Cent s'accorde, quand les dizaines associées sont nulles et ne s'accorde pas dans le cas contraire. Ainsi on écrit Cent dix-huit, deux cents et trois cent vingt et un par exemple. Pour ce traitement spécifique, nous créerons la fonction NbEnCent().
Le traitement des dizaines s'avère plus délicat. On remarque tout d'abord que le chiffre des unités s'écrit et se prononce normalement lorsqu'il est précédé de 0, 2, 3, 4, 5, 6 et 8. On dit bien Quatre pour 04 et Quarante Quatre pour 44. En revanche, s'il est précédé de 1, 7 ou 9, il s'écrit sous la forme onze, douze, treize etc... On dit bien Seize et soixante Seize par exemple. La solution est alors de considérer comme unité, tous les chiffres de 1 à 9, ainsi que toutes les dizaines de 10 à 19. Selon le cas donc, les dizaines associées correspondantes, varient pour former l'expression finale en lettres : Quatre Vingt Un et Quatre Vingt Onze. Nous devons donc créer la fonction VBA NbEnDizaines() pour traiter ces cas.
Il faudra de plus traiter des spécificités pour les Belges et Suisses. Les premiers utilisent les termes Septante et Nonante pour 70 et 90. Les seconds utilisent en plus le terme Huitante ou Octante pour 80.
Le programme respectera les mêmes limites qu'Excel dans le traitement et l'affichage des nombres longs :
- 999 999 999 999 999 (soit 15 chiffres) pour les nombres entiers
- 9 999 999 999 999,99 (soit 13 chiffres) pour les décimaux
Le programme que nous allons construire doit s'articuler autour de fonctions. Chacune doit réaliser un traitement spécifique, selon les problématiques énoncées précédemment. Chacune renverra son traitement sous forme de chaîne de caractères (String), récupéré par la fonction précédente. Le tout doit être consolidé par la fonction principale NbEnLettres(). Une fois le module converti en complément VBA, c'est elle qui, appelée comme n'importe quelle fonction de calcul Excel, permettra de réaliser la conversion des nombres en lettres, dans les cellules d'une feuille Excel. La formation VBA Excel sur la création de fonctions, démontre comment créer et lier un complément VBA à l'application.
- Démarrer Excel et créer un nouveau classeur vierge,
- Basculer dans l'éditeur de code à l'aide du raccourci clavier ALT + F11,
- Cliquer sur le menu Insertion, en haut de l'éditeur et choisir Module dans la liste,
- Saisir l'instruction suivante dans la feuille de code :
Cette instruction oblige le développeur à déclarer toutes les variables. VBA est permissif. S'il rencontre des variables non déclarées, il les type comme des Variant. Or ce type est non défini, il accepte n'importe quelle affectation, y compris les variables objets. L'allocation mémoire est donc plus importante. A force, vous obtenez un programme gourmand en ressources. La formation VBA Excel sur la déclaration de variables explique comment bien typer et dimensionner ces dernières.
- A la suite du code, créer la fonction NbEnLettres() comme suit :
End Function
Il s'agit de la fonction principale pour extraire la partie entière et la partie décimale. Comme c'est elle qui sera appelée dans la feuille de calcul Excel, nous précédons sa déclaration par le mot clé Public. Ainsi elle sera reconnue et disponible. Elle doit réaliser un traitement et retourner un résultat, dans une cellule, sous forme de chaîne de caractères. C'est pourquoi nous la déclarons comme une fonction (Function) et non comme une procédure classique (Sub). Le type qu'elle retourne est défini après la déclaration de ses paramètres (As String).
Lorsque vous ouvrez les parenthèses d'une fonction de calcul Excel, celle-ci demande généralement les paramètres dont elle a besoin (cellules de la feuille), pour réaliser le calcul et retourner le résultat en lieu et place. C'est le même principe ici. Pour convertir un nombre en texte, la fonction a besoin de la valeur numérique à transformer. Le premier paramètre est donc obligatoire (Nombre As Double). L'utilisateur pourra alors désigner n'importe quelle cellule numérique. Le type Double correspond à un nombre décimal double précision.
Les deux arguments suivants sont facultatifs. C'est pourquoi ils sont préfixés du mot clé Optional et initialisés à la valeur 0. La devise (Optional Devise) à indiquer sous forme numérique (As Byte), si elle n'est pas renseignée (=0), ne sera pas ajoutée au résultat converti. La langue est elle aussi à indiquer sous forme numérique (Optional Langue As Byte), pour les subtilités des Belges et Suisses. Si elle n'est pas renseignée (=0), le programme doit considérer le français par défaut.
Maintenant nous devons déclarer les variables nécessaires au stockage et au traitement d'informations, dans les bornes de la fonction.
- Dans les bornes de la fonction (Entre Public Function et End Function), ajouter les déclarations suivantes :
Dim texteDevise As String, texteCentimes As String, position As Byte
Comme son nom l'indique, partieEntiere sera utilisée pour mémoriser la partie entière de la donnée numérique. Nous la déclarons comme un Variant pour accepter la désignation d'un montant sans contrôle, et ainsi éviter les incompatibilités de type. Bien entendu, partieDecimale doit permettre de stocker le nombre après la virgule. texteDevise doit permettre de mémoriser la devise en texte (Euros, Dollars, Dinars), en fonction du choix numérique effectué, en paramètre de la fonction. Puis, les centimes d'un montant ne s'écrivent pas de la même façon, selon la devise. Donc, nous stockerons cette information textuelle (String) dans la variable texteCentimes. Enfin, la variable position permettra de déclarer l'emplacement de la virgule ou du point, pour la partie décimale.
Pour éviter tout souci, nous devons considérer et traiter aussi les nombres négatifs. Nous envisageons la correction en prélevant leur valeur positive grâce à la fonction VBA ABS(), littéralement pour la valeur absolue. Pour ce faire :
- A la suite du code, ajouter le test suivant :
L'instruction If permet de poser le critère pour savoir si la valeur désignée est négative (If (Montant < 0)). Si c'est le cas (Then), nous stockons sa valeur absolue (Montant = Abs(Montant)). Comme une seule action est envisagée si le critère est vérifié, celle-ci peut être écrite sur la même ligne, afin d'éviter de boucler l'instruction If, par un End If. La formation VBA sur la gestion des critères exploite cette instruction conditionnelle.
Il s'agit maintenant de prélever la partie entière de ce nombre. Pour cela :
- Ajouter la ligne de code suivante :
La fonction VBA Int() retourne la partie entière du nombre passé en paramètre de la fonction, et éventuellement retravaillé par l'instruction If, si la valeur était négative. Il s'agit maintenant d'extraire la partie décimale de ce montant numérique, avec trois chiffres si la devise est indéfinie ou le Dinar, et avec deux chiffres dans le cas contraire. Comme VBA est permissif, nous allons traiter le montant comme s'il s'agissait d'une chaîne de caractères, afin de déceler l'emplacement de la virgule. Il ne restera plus qu'à prélever la valeur située après cette virgule, avec la précision souhaitée. Pour cela :
- Ajouter les lignes de code suivantes :
If (position < 1) Then position = InStr(1, Montant, '.')
La fonction VBA InStr renvoie la position d'une occurrence recherchée (','), dans une chaîne de caractères (Montant), à partir d'une position définie (1). Nous cherchons donc la position de la virgule et le cas échéant, la position du point, pour les anglo-saxons. Si la valeur retournée est inférieure à 1, il n'y a pas de décimales et il s'agit donc d'un montant entier. En revanche, si elle est trouvée, il s'agit de stocker cette valeur décimale pour traitement ultérieur. Pour cela :
- Ajouter les lignes de code suivantes :
position = position + 1
If Devise = 0 Or Devise = 3 Then
partieDecimale = Left(Mid(Montant, position), 3)
Else
partieDecimale = Left(Mid(Montant, position), 2)
End If
Else
partieDecimale = 0
End If
Si le caractère de la décimale est trouvé (If (position > 0) Then), nous partons de la position située après ce caractère (position = position + 1). La fonction VBA Mid() permet de prélever une partie de la chaîne de caractères en partant d'une position donnée. Dans la chaîne du montant, nous prélevons tout ce qui est situé après le caractère de la décimale (Mid(Montant, position)). La formation VBA Excel pour modifier la casse enseigne notamment cette fonction Mid() et l'utilité de ses paramètres. Lorsque la devise vaut 0 ou 3 (If Devise = 0 Or Devise = 3 Then), nous gardons une précision de trois décimales en prélevant la chaîne sur une longeur de 3 caractères en partant de la gauche, grâce à la fonction Left(). Dans le cas contraire, nous ne conservons que deux 2 décimales.
Il est temps de tester le code. Pour cela, la fonction doit retourner des valeurs. Et pour la tester, nous avons besoin d'un tableau. Contrairement à d'autres langages, VBA n'utilise pas l'instruction Return, pour renvoyer son résultat. Il suffit simplement de l'affecter au nom de la fonction elle-même, déclarée comme une variable (As String) et donc exploitée en tant que telle.
- A la suite du code, ajouter l'instruction temporaire suivante :
- Réaliser la combinaison ALT + F11 pour basculer sur la feuille Excel,
- Taper quelques valeurs numériques avec des décimales,
- Dans les cellules adjacentes, appeler la fonction en saisissant =NbEnLettres(ref_cellule),
Comme nous l'avons ordonné avec la dernière instruction, la fonction VBA retourne à ce stade, la partie entière d'une part et la partie décimale réduite, d'autre part. Tout fonctionne bien, nous pouvons poursuivre le développement du code.
- Revenir dans l'éditeur de code avec ALT + F11,
- Supprimer la dernière instruction (NbEnLettres = partieEntiere & ' et ' & partieDecimale),
- A la suite du code, ajouter l'instruction If suivante :
If partieEntiere > 999999999999999# Then
NbEnLettres = '# TropGrand #'
Exit Function
End If
Else If partieEntiere > 9999999999999# Then
NbEnLettres = '# TropGrand #'
Exit Function
End If
End If
Lorsque la partie entière dépasse les capacités, nous retournons un message d'erreur (NbEnLettres = '# TropGrand #'). Puis, nous mettons fin à l'exécution du code grâce à l'instruction Exit Function.
Il s'agit maintenant de traduire en texte, la devise passée en paramètre ainsi que l'extension des décimales correspondante. Par exemple, on parle de Cents en Euro et de Millimes en Dinar.
- Pour ce faire, ajouter les lignes de code suivantes :
Case 0
If partieDecimale > 0 Then texteDevise = ' virgule'
Case 1
texteDevise = ' Euro'
If partieDecimale > 0 Then texteCentimes = ' Cents'
Case 2
texteDevise = ' Dollar'
If partieDecimale > 0 Then texteCentimes = ' Cent'
Case 3
texteDevise = ' Dinar'
If partieDecimale = 1 Then texteCentimes = ' Millime'
If partieDecimale > 1 Then texteCentimes = ' Millimes'
End Select
Tout d'abord nous utilisons l'instruction Select Case pour tester toutes les valeurs possibles pour la variable Devise. Elle est plus structurée et efficace qu'une instruction If lorsque le nombre de critères augmente. Elle est d'ailleurs présentée dans le support de formation VBA Excel sur les instructions Select Case.
Pour une devise à zéro (Case 0), soit indéfinie, nous nous contenterons de concaténer les parties entières et décimales converties, avec le texte virgule (texteDevise = ' virgule'). Dans les autres cas, en fonction de la valeur numérique, nous stockons les textes de la devise et du suffixe pour les décimales (texteDevise = 'Euro' et texteCentimes = 'Cents'), lorsque celle-ci n'est pas nulle (If partieDecimale > 0 Then). Il s'agira de concaténer ces bouts de texte dans le bon ordre, lorsque nous aurons réussi à convertir les parties entières et décimales, en textes. Nous pouvons de nouveau réaliser un petit essai :
- Pour ce faire, ajouter l'affectation suivante pour la fonction :
Il s'agit maintenant de raffraîchir les calculs de la feuille. Pour ce faire, il existe une astuce qui est d'ailleurs proposée dans la formation sur les trucs et astuces Excel.
- Basculer sur la feuille Excel (Alt + F11),
- Sélectionner la plage de cellules des calculs précédents,
- Enfoncer la touche F2 du clavier pour activer la saisie de la première cellule de la plage,
- Puis valider le re-calcul en enfonçant ensemble les touches CTRL et Entrée,
Pour pallier le souci d'accord de la devise, nous allons ajouter deux lignes de traitements.
- Revenir dans l'éditeur de code VBA,
- Supprimer la dernière ligne de code,
- A la place, saisir les deux instructions suivantes :
If Devise <> 0 And partieEntiere > 1 Then texteDevise = texteDevise & 's'
Lorsque le montant n'est qu'une valeur décimale et que la devise est renseignée, la devise n'est pas écrite dans la conversion (If Devise <> 0 And partieEntiere = 0 Then texteDevise = ''). Lorsque la devise est demandée et que le montant est supérieur à 1, il s'agit d'accorder le texte de la devise en ajoutant un 's' par concaténation (texteDevise = texteDevise & 's').
La fonction NbEnLettres() a terminé son traitement. Elle doit passer le relai aux autres fonctions pour le traitement des centaines, dizaines et parties entières supérieures à 1000, selon les principes que nous avons énoncés, en début de support.
- Pour cela, déclarer les trois fonctions privées comme suit :
End Function
Private Function NbEnCent(Nombre As Integer, Langue As Byte) As String
End Function
Private Function NbEnDizaines(Nombre As Byte, Langue As Byte) As String
End Function
- Puis ajouter leur appel, pour l'affectation du résultat, à la fin de la fonction NbEnLettres(), juste avant le End Function, comme suit :
Nous déclarons ces trois fonctions préfixées du mot clé Private. En effet, elles sont dédiées à la fonction nbEnLettres() et ne peuvent être utilisées que selon les arguments que cette dernière leur passe. Elles ne doivent donc pas être disponibles comme une fonction normale, depuis la feuille de calcul. La première fonction (NbEnMille) doit traiter la partie entière afin de transcrire explicitement les milliers, centaines et dizaines en texte. Le résultat retourné sera associé (&) au nom de la devise, suivi du traitement en texte de la partie décimale, retournée par la fonction NbEnCent.
Le code complet de la fonction principale NbEnLettres est le suivant :
Dim partieEntiere As Variant, partieDecimale As Integer
Dim texteDevise As String, texteCentimes As String, position As Byte
If (Montant < 0) Then Montant = Abs(Montant)
partieEntiere = Int(Montant)
position = InStr(1, Montant, ',')
If (position < 1) Then position = InStr(1, Montant, '.')
If (position > 0) Then
position = position + 1
If Devise = 0 Or Devise = 3 Then
partieDecimale = Left(Mid(Montant, position), 3)
Else
partieDecimale = Left(Mid(Montant, position), 2)
End If
Else
partieDecimale = 0
End If
If partieDecimale = 0 Then
If partieEntiere > 999999999999999# Then
NbEnLettres = '# TropGrand #'
Exit Function
End If
Else If partieEntiere > 9999999999999# Then
NbEnLettres = '# TropGrand #'
Exit Function
End If
End If
Select Case Devise
Case 0
If partieDecimale > 0 Then texteDevise = ' virgule'
Case 1
texteDevise = ' Euro'
If partieDecimale > 0 Then texteCentimes = ' Cents'
Case 2
texteDevise = ' Dollar'
If partieDecimale > 0 Then texteCentimes = ' Cent'
Case 3 texteDevise = ' Dinar'
If partieDecimale = 1 Then texteCentimes = ' Millime'
If partieDecimale > 1 Then texteCentimes = ' Millimes'
End Select
If Devise <> 0 And partieEntiere = 0 Then texteDevise = ''
If Devise <> 0 And partieEntiere > 1 Then texteDevise = texteDevise & 's'
NbEnLettres = NbEnMille(CDbl(partieEntiere), Langue) & texteDevise & ' ' & NbEnCent(partieDecimale, Langue) & texteCentimes
Traitement des milliers en texte
Ici nous entrons dans une réflexion plus compliquée. Il s'agit de réussir à découper les nombres par blocs de 3 chiffres afin de réussir à transcrire indépendamment les Centaines, Milliers, Millions etc... Une boucle doit permettre, à chaque passage, de diviser le nombre restant par 1000, afin de traiter la partie restante et de la convertir en conséquence, grâce à un Select Case (Selon le cas). C'est la fonction NbEnMille, à qui on a passé la partie entière du montant, qui débute ce traitement. Pour travailler sur les potentiels blocs de 3 chiffres de la partie entière d'origine, nous avons besoin de variables. Pour cela :
- Ajouter les déclarations de variables suivantes, dans la fonction NbEnMille :
Dim nombreBloc As Integer, reste As Double : Dim texteBloc As String
Nous déclarons la variable tabBloc que nous allons exploiter comme un tableau de variables, dans lequel seront stockées, les valeurs en texte des blocs de chiffres : Mille, Millions etc... La variable numBloc, déclarée comme un entier court (Byte), sera utilisée comme repère, pour savoir dans quel bloc nous nous situons, parmi ceux que nous parcourons. La variable nombreBloc sera utilisée pour stocker le bloc de 3 chiffres, en cours d'analyse dans la boucle. La variable reste permettra de stocker le reste de la division par 1000, pour décomposer chacun de ces blocs. La variable texteBloc servira à stocker la conversion de chaque bloc en texte, à concaténer. C'est la raison pour laquelle nous avons déclaré cette variable en tant que String.
Comme toujours, il convient ensuite d'initialiser les variables qui peuvent être affectées. Pour ce faire :
- Ajouter les affectations suivantes :
numBloc = 0
If Nombre = 0 Then Exit Function
Une variable Variant peut-être transformée à tout moment et accepter n'importe quel type. C'est la raison pour laquelle, nous la transformons en un tableau de valeurs, grâce au mot clé Array et l'énumération de chaque valeur séparée d'une virgule. Chaque valeur est alors repérée par sa position dans le tableau. Ces valeurs en l'occurrence correspondent aux conversions textuelles des blocs de 3 chiffres à décomposer. La variable numBloc est initialisée à 0. Elle sera incrémentée à chaque passage dans la boucle de traitement de ces blocs. La dernière ligne coule de source (If Nombre = 0 Then Exit Function). Si la partie entière du nombre à convertir est nulle, le traitement est abandonné, fort logiquement.
La partie suivante, par le biais d'une boucle, consiste à retirer, à chaque itération, un bloc nombreBloc de 3 chiffres. La partie restante est stockée dans la variable reste. Afin de reconstruire et transcrire chacun des blocs, la nouvelle valeur de Nombre est alors le résultat stocké dans la variable reste et la boucle s'arrête lorsque cette dernière est nulle.
- Ajouter les lignes suivantes pour initialiser la boucle :
reste = Int(Nombre / 1000) : nombreBloc = Nombre - (reste * 1000)
texteBloc = NbEnCent(CInt(nombreBloc), Langue)
Loop
Une boucle Do While s'exécute tant que le critère (Nombre > 0) est vérifié. Son instruction se ferme nécessairement par le mot clé Loop. Elles sont notamment exploitées dans la formation pour importer et exporter des données dans Excel. La variable reste stocke la partie entière du montant divisé par 1000. La variable nombreBloc permet ainsi de stocker le bloc de 3 chiffres à droite, pour le traitement qui suit. Par exemple, si le montant de départ est 12 553 615, la variable reste mémorise 12 553. Ainsi nombreBloc récupère la valeur 615 qui est la différence entre le nombre de départ 12 553 615 et le reste multiplié par 1000 soit 12 553 000. Nous avons extrait le bloc de 3 chiffres en partant de la droite. Il s'agit maintenant de le convertir en texte. C'est pourquoi ce bloc est passé à la fonction NbEnCent (NbEnCent(CInt(nombreBloc)) pour traitement, et récupéré dans texteBloc pour ensuite lui affecter le qualificatif des milliers avec le code qui suit :
- Ajouter les instructions suivantes dans la boucle, avant l'instruction Loop :
Case 0
Case 1
If nombreBloc = 1 Then
texteBloc = tabBloc(numBloc) & ' '
Else
texteBloc = texteBloc & ' ' & tabBloc(numBloc) & ''
End If
Case Else
If nombreBloc = 1 Then
texteBloc = texteBloc & ' ' & tabBloc(numBloc) & ' '
Else
texteBloc = texteBloc & ' ' & tabBloc(numBloc) & 's '
End If
End Select
NbEnMille = texteBloc & NbEnMille
Nombre = reste : numBloc = numBloc + 1
Selon la valeur de l'itération (numBloc) dans la boucle, nous saurons quel bloc de Milliers nous traitons. Cette variable numBloc est à ce titre incrémentée à chaque passage, en fin de boucle (numBloc = numBloc + 1).
Si la valeur de numBloc qui part de 0, n'a pas le temps d'être incrémentée, parce que la variable nombre, réaffectée au reste en fin de boucle, ne justifie pas la poursuite de cette dernière, (Do While Nombre > 0), aucun bloc de millier ne lui sera affecté par les instructions du Select Case. Selon ce principe, le traitement suivant est donc effectué :
Pour le premier bloc de 3 chiffres (numBloc=0), aucun attribut n'est affecté,
Pour le deuxième bloc de 3 chiffres (numBloc=1), on attribue mille qui est invariable (et si nombreBloc = 1, alors on omet le un obtenu dans texteBloc,
Pour les blocs suivants de 3 chiffres (numBloc = 2, 3, 4), on attribue million, milliard, billion en ajoutant le 's' du pluriel si la variable nombreBloc dépasse 1.
Le Case 0 dans l'instruction Select est essentiel. Il ne doit pas être englobé dans le Case Else. Dans ce cas à part, aucune dénomination textuelle n'est attribuée, donc aucun accord n'est à réaliser. C'est pourquoi, aucune instruction n'est écrite dans ce cas.
Résumons la situation avec un exemple concret. Le montant de départ est 2693,68. La partie entière passée à la fonction NbEnMille est donc 2693. Le reste se voit affecter la valeur 2, tandis que nombreBloc prend la valeur 693. Ce 693 passe dans le traitement de la fonction NbEnCent qui doit la retourner convertie en texte. Ensuite elle passe dans le Select Case de la fonction en cours. Comme il s'agit du premier passage (Case 0), aucune action n'est entreprise. En fin de boucle le résultat est concaténé au précédent (NbEnMille = texteBloc & NbEnMille), donc NbEnMille vaut Six cent quatre-vingt treize à ce stade. Puis le Nombre de départ est réaffecté au reste soit à 2 (Nombre = reste), avant de boucler. Du coup le reste passe à 0 (reste = Int(Nombre / 1000)) et nombreBloc devient 2. Ce chiffre est de nouveau passé à la fonction NbEnCent pour conversion en texte (Deux). Dans le Select Case, puisque nous sommes dans le deuxième passage de la boucle, l'instruction s'arrête sur Case 1 (Le cas des milliers). Comme nombreBloc est supérieur à 1, le texte Deux est concaténé à la valeur correspondante du tableau, soit Mille. Nous obtenons Deux Mille. En fin de boucle, ce résultat est associé à l'ancien (NbEnMille = texteBloc & NbEnMille). Nous obtenons Deux Mille six cent quatre-vingt treize. Nombre prend de nouveau la valeur du reste, soit 0 et la boucle se termine.
- A la fin de la fonction NbEnMille, ajouter la ligne de code suivante :
Nous passons ainsi la première lettre de la chaîne retournée, en majuscule. La fonction VBA Left permet de retourner le premier caractère de la chaîne (Left(NbEnMille, 1)), que nous passons en majuscule grâce à la fonction UCase. Nous concaténons cette majuscule avec le reste de la chaîne inchangé, grâce à la fonction Mid qui prélève cette dernière à partir du deuxième caractère, soit après la majuscule (Mid(NbEnMille, 2)).
Le code complet de la fonction NbEnMille est le suivant :
Dim tabBloc As Variant: Dim numBloc As Byte
Dim nombreBloc As Integer, reste As Double : Dim texteBloc As String
tabBloc = Array('', 'mille', 'million', 'milliard', 'billion')
numBloc = 0
If Nombre = 0 Then Exit Function
Do While Nombre > 0
reste = Int(Nombre / 1000) : nombreBloc = Nombre - (reste * 1000)
texteBloc = NbEnCent(CInt(nombreBloc), Langue)
Select Case numBloc
Case 0
Case 1
If nombreBloc = 1 Then
texteBloc = tabBloc(numBloc) & ' '
Else
texteBloc = texteBloc & ' ' & tabBloc(numBloc) & ' '
End If
Case Else
If nombreBloc = 1 Then
texteBloc = texteBloc & ' ' & tabBloc(numBloc) & ' '
Else
texteBloc = texteBloc & ' ' & tabBloc(numBloc) & 's '
End If
End Select
NbEnMille = texteBloc & NbEnMille
Nombre = reste : numBloc = numBloc + 1
Loop
NbEnMille = UCase(Left(NbEnMille, 1)) & Mid(NbEnMille, 2)
Convertir les centaines en texte
Le traitement des centaines est plus simple. Seul un bloc de trois chiffres est passé à la fonction qui doit retourner le résultat en toutes lettres. Il s'agit de la fonction NbEnCent que nous avons déclarée précédemment. Elle reçoit deux paramètres : Le reste transmis par la fonction NbEnMille (Nombre) et la Langue. Commençons par déclarer les variables dont nous avons besoin.
- Dans les bornes de la fonction NbEnCent, ajouter les déclarations suivantes :
Dim nbCent As Byte, reste As Byte : Dim texteReste As String
tabUnites sera transformé en tableau de valeurs afin de stocker les unités converties en textes. nbCent doit servir à stocker le chiffre des centaines à extraire. Les chiffres des dizaines seront alors affectés à la variable reste. L'appel de la fonction NbEnDizaines, permettra de retourner la valeur en toutes lettres de ce reste, dans la variable texteReste.
Il s'agit maintenant d'affecter ces variables en commençant par le tableau des unités.
- A la suite, ajouter la déclaration suivante :
Nous devons maintenant extraire le chiffre des centaines ainsi que le nombre des dizaines à transmettre pour conversion en toutes lettres. Pour ce faire :
- Ajouter les trois affectations suivantes :
texteReste = NbEnDizaines(reste, Langue)
nbCent stocke la partie entière de la division. Pour reprendre notre exemple précédent, la fonction nbEnCent reçoit 693. Donc nbCent stocke le chiffre 6. Le reste hérite donc de la différence, soit les dizaines. La variable stocke donc le nombre 93. Ces dizaines sont passées à la fonction NbEnDizaines pour les convertir en texte. Lorsque la fonction sera codée, la chaîne de caractères sera stockée dans la variable texteReste.
Pour la conversion en texte des centaines, il faut traiter les cas selon la valeur du chiffre des centaines. Souvenez-vous, s'il vaut 1, on ne le prononce pas, on dit Cent. S'il est supérieur à 1, l'accord est fait, seulement si les dizaines et unités qui suivent, sont nulles. L'instruction la plus appropriée pour traiter ces cas est donc de nouveau, un Select Case.
- A la suite du code, ajouter l'instruction Select Case comme suit :
Case 0
NbEnCent = texteReste
Case 1
If reste = 0 Then
NbEnCent = 'cent'
Else
NbEnCent = 'cent ' & texteReste
End If
Case Else
If reste = 0 Then
NbEnCent = tabUnites(nbCent) & ' cents'
Else
NbEnCent = tabUnites(nbCent) & ' cent ' & texteReste
End If
End Select
Logiquement, si le chiffre des centaines est nul, on conserve uniquement la valeur des dizaines convertie en texte (Case 0 NbEnCent = texteReste). Dans le cas de la centaine, on fait la distinction selon la valeur qui suit, celle du reste, pour ne pas ajouter un espace en trop dans la concaténation. Si elle est nulle, on ne retient que cent (If reste = 0 Then NbEnCent = 'cent'). Sinon, on l'associe au texte des dizaines converti (NbEnCent = 'cent ' & texteReste). Lorsque le chiffre des centaines est supérieur à 1, on fait l'accord lorsque les dizaines valent 0 (If reste = 0 Then NbEnCent = tabUnites(nbCent) & ' cents'), tout en convertissant les chiffres des centaines en texte, grâce au tableau des unités. Dans le cas contraire, on ne fait pas l'accord (NbEnCent = tabUnites(nbCent) & ' cent ' & texteReste).
Le code complet de la fonction NbEnCent est le suivant :
Dim tabUnites As Variant
Dim nbCent As Byte, reste As Byte : Dim texteReste As String
tabUnites = Array('', 'un', 'deux', 'trois', 'quatre', 'cinq', 'six', 'sept', 'huit', 'neuf', 'dix')
nbCent = Int(Nombre / 100) : reste = Nombre - (nbCent * 100)
texteReste = NbEnDizaines(reste, Langue)
Select Case nbCent
Case 0
NbEnCent = texteReste
Case 1
If reste = 0 Then
NbEnCent = 'cent'
Else
NbEnCent = 'cent ' & texteReste
End If
Case Else
If reste = 0 Then
NbEnCent = tabUnites(nbCent) & ' cents'
Else
NbEnCent = tabUnites(nbCent) & ' cent ' & texteReste
End If
End Select
Convertir les chiffres des dizaines et des unités en texte
Il s'agit de la fonction préférée de l'auteur Abdelaziz Chelbi, car il s'agit de la clé de l'énigme. Nous avons besoin de deux tableaux de valeurs pour stocker les chiffres des dizaines et des unités indépendamment, en vue de la conversion. Il faut aussi des variables pour traiter indépendamment le chiffre des dizaines et celui des unités, après extraction. Pour ce faire :
- Ajouter les déclarations suivantes dans les bornes de la fonction NbEnDizaines,
Dim nbUnites As Byte, nbDizaines As Byte
Dim texteLiaison As String
La variable texteLiaison sera utilisée pour ajouter un caractère de liaison dans la conversion textuelle, en fonction du cas et de la langue.
- Ajouter les affectations suivantes pour les tableaux :
tabDizaines = Array('', '', 'vingt', 'trente', 'quarante', 'cinquante', 'soixante', 'soixante', 'quatre-vingt', 'quatre-vingt')
Vous remarquez la répétition du soixante et du quatre-vingt qui doivent se combiner avec les unités dans le cas de la langue française et s'écrire autrement en Belge comme en Suisse. Il s'agirade traiter ces exceptions.
- Pour cela, ajouter les affectations suivantes en fonction des conditions :
tabDizaines(7) = 'septante'
tabDizaines(9) = 'nonante'
End If
If Langue = 2 Then
tabDizaines(8) = 'huitante'
Dans le cas du Belge (If Langue = 1 Then), nous remplaçons les valeurs de soixante et quatre-vingt pour soixante-dix et quatre-vingt-dix dans le tableau de valeurs. Nous faisons de même pour quatre-vingt dans le cas du Suisse (If Langue = 2 Then).
Il s'agit maintenant d'extraire les dizaines et les unités.
- Pour ce faire, ajouter les affectations suivantes :
texteLiaison = '-' : If nbUnites = 1 Then texteLiaison = ' et'
Pour reprendre l'exemple précédent, la fonction NbEnDizaines reçoit le nombre 93. La variable nbDizaines stocke donc le chiffre des dizaines, soit 9. En réalisant la différence, la variable nbUnites ne conserve que le chiffre des unités, soit 3. Pour la conversion nous initialisons la liaison sur le tiret (-), sauf quand l'unité vaut 1 pour vingt et un, trente et un etc...
Pour la suite du traitement, avant de réaliser la conversion en fonction des valeurs stockées dans les tableaux, il s'agit de gérer certaines exceptions. Il y a celle des dizaines (douze, treize, quatorze etc...), puis il y a celles pour les nombres à partir de 70 et 90. Nous allons donc, une fois de plus, exploiter l'instruction Select Case.
- A la suite du code, ajouter les lignes suivantes :
Case 0 texteLiaison = ''
Case 1 nbUnites = nbUnites + 10
texteLiaison = ''
Case 7
If Langue = 0 Then nbUnites = nbUnites + 10
Case 8
If Langue <> 2 Then texteLiaison = '-'
Case 9
If Langue = 0 Then
nbUnites = nbUnites + 10
texteLiaison = '-'
End If
End Select
Lorsque le chiffre des dizaines est nul (Case 0), il s'agit simplement d'annuler le texte de liaison puisque la transcription se fera sur le tableau des unités. Lorsque ce chiffre vaut 1 (Case 1), il s'agit de faire référence au tableau des unités (nbUnites = nbUnites + 10) qui a stocké ces particularités de la langue, jusqu'à dix-neuf. Dans le cas où la dizaine vaut 7 ou 9, il s'agira d'un assemblage entre le tableau des dizaines et de celui des unités, moyennant un texte de liaison (-) pour traduire par exemple soixante-douze et quatre-vingt-treize, dans la langue française en tous cas.
Après ces traitements particuliers, il ne reste plus qu'à transcrire le résultat en texte.
- Pour ce faire, ajouter les lignes de code suivantes :
If Langue <> 2 And Nombre = 80 Then NbEnDizaines = tabDizaines(nbDizaines) & 's'
If tabUnites(nbUnites) <> '' Then NbEnDizaines = NbEnDizaines & texteLiaison & tabUnites(nbUnites)
Nous commençons pas transcrire le chiffre des dizaines en texte (NbEnDizaines = tabDizaines(nbDizaines)), qui ne retourne rien si ce chiffre vaut 0 ou 1, conformément aux valeurs stockées dans le tableau. En français, nous ajoutons l'accord nécessaire spécifiquement pour quatre-vingts. Puis nous concaténons l'ensemble avec la conversion des unités, si celles-ci ne sont pas vides (NbEnDizaines = NbEnDizaines & texteLiaison & tabUnites(nbUnites)).
Il ne reste plus qu'Ã tester.
- Basculer sur la feuille Excel (ALT + F11),
- Sélectionner la première cellule du résultat, C4 ici,
- Taper le symbole = pour débuter le calcul,
- Saisir le nom de la fonction et ouvrir la parenthèse, soit =nbenlettres(,
- Sélectionner la valeur numérique à convertir, B4 ici,
- Taper un point-virgule (;) suivi du paramètre 1 pour la devise en Euros,
- Fermer la parenthèse et valider le calcul par CTRL + Entrée pour garder la cellule active,
- Tirer la poignée de la formule vers le bas sur les autres cellules de calcul,
Comme vous le constatez, toutes les traductions des valeurs numériques en textes sont parfaitement réalisées. Les accords se font lorsqu'ils sont nécessaires. La devise est bien intégrée dans la conversion ainsi que le suffixe pour désigner les centimes. Merci mille fois Abdelaziz Chelbi sans qui ce programme n'aurait pas vu le jour.
Le code complet de la fonction NbEnDizaines est le suivant :
Dim tabUnites As Variant, tabDizaines As Variant
Dim nbUnites As Byte, nbDizaines As Byte
Dim texteLiaison As String
tabUnites = Array('', 'un', 'deux', 'trois', 'quatre', 'cinq', 'six', 'sept', 'huit', 'neuf', 'dix', 'onze', 'douze', 'treize', 'quatorze', 'quinze', 'seize', 'dix-sept', 'dix-huit', 'dix-neuf')
tabDizaines = Array('', '', 'vingt', 'trente', 'quarante', 'cinquante', 'soixante', 'soixante', 'quatre-vingt', 'quatre-vingt')
If Langue = 1 Then
tabDizaines(7) = 'septante'
tabDizaines(9) = 'nonante'
End If
If Langue = 2 Then tabDizaines(8) = 'huitante'
nbDizaines = Int(Nombre / 10) : nbUnites = Nombre - (nbDizaines * 10)
texteLiaison = '-' : If nbUnites = 1 Then texteLiaison = ' et '
Select Case nbDizaines
Case 0
texteLiaison = ''
Case 1
nbUnites = nbUnites + 10
texteLiaison = ''
Case 7
If Langue = 0 Then nbUnites = nbUnites + 10
Case 8
If Langue <> 2 Then texteLiaison = '-'
Case 9
If Langue = 0 Then
nbUnites = nbUnites + 10
texteLiaison = '-'
End If
End Select
NbEnDizaines = tabDizaines(nbDizaines)
If Langue <> 2 And Nombre = 80 Then NbEnDizaines = tabDizaines(nbDizaines) & 's'
If tabUnites(nbUnites) <> '' Then NbEnDizaines = NbEnDizaines & texteLiaison & tabUnites(nbUnites)
Complément VBA Excel pour fonctions de calculs portables
Il demeure une dernière étape très simple à réaliser afin que cette fonction soit désormais disponible avec toutes les utilisations à venir d'Excel. Pour ce faire, vous devez en créer un complément VBA à rattacher à l'application Excel.
- Cliquer sur l'onglet Fichier en haut à gauche de la fenêtre Excel,
- Puis, choisir Enregistrer Sous et Parcourir,
- Dans la zone Type, en bas de la boîte de dialogue qui apparaît, sélectionner Complément Excel (*.xlam),
- Nommer le fichier : convertisseur-excel.xlam, par exemple puis valider par Enregistrer,
- Cliquer sur l'onglet Développeur en haut de la fenêtre Excel pour activer son ruban,
- Dans le ruban, cliquer sur le bouton Compléments Excel,
- Dans la boîte de dialogue qui suit, cocher la case du Convertisseur-Excel et cliquer sur Ok,
Désormais votre fonction de conversion de montants numériques en toutes lettres est disponible en tapant simplement son nom, depuis une cellule de la feuille et ce, pour toutes les utilisations à venir d'Excel, comme pour la fonction la plus simple qu'est la somme automatique. Nous venons donc d'enrichir la bibliothèque Excel, d'une fonction de calcul fort puissante et intéressante.
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales
