Formater des nombres dans Excel VBA
Les nombres se présentent sous toutes sortes de formats dans les feuilles de calcul Excel. Vous connaissez peut-être déjà la fenêtre contextuelle d'Excel permettant d'utiliser différents formats numériques :
Le formatage des nombres rend les nombres plus faciles à lire et à comprendre. La valeur par défaut d'Excel pour les nombres entrés dans les cellules est le format « Général », ce qui signifie que le nombre est affiché exactement comme vous l'avez tapé.
Par exemple, si vous entrez un nombre rond, par ex. 4238, il sera affiché sous la forme 4238 sans point décimal ni séparateur de milliers. Un nombre décimal tel que 9325.89 sera affiché avec le point décimal et les décimales. Cela signifie qu'il ne s'alignera pas dans la colonne avec les chiffres ronds et qu'il aura l'air extrêmement désordonné.
De plus, sans afficher les séparateurs de milliers, il est difficile de voir la taille réelle d'un nombre sans compter les chiffres individuels. Est-ce en millions ou en dizaines de millions ?
Du point de vue d'un utilisateur qui regarde une colonne de chiffres, cela la rend assez difficile à lire et à comparer.
En VBA, vous avez accès à exactement la même gamme de formats que vous avez sur le front-end d'Excel. Cela s'applique non seulement à une valeur entrée dans une cellule d'une feuille de calcul, mais également à des éléments tels que les boîtes de message, les contrôles UserForm, les graphiques et les graphiques et la barre d'état Excel dans le coin inférieur gauche de la feuille de calcul.
La fonction Format est une fonction extrêmement utile dans VBA en termes de présentation, mais elle est également très complexe en termes de flexibilité offerte dans la façon dont les nombres sont affichés.
Comment utiliser la fonction Format dans VBA
Si vous affichez une boîte de message, la fonction Format peut être utilisée directement :
| 1 | Format MsgBox (1234567.89, "#,##0.00") |
Cela affichera un grand nombre en utilisant des virgules pour séparer les milliers et pour afficher 2 décimales. Le résultat sera 1 234 567,89. Les zéros à la place du hachage garantissent que les décimales seront affichées sous la forme 00 en nombres entiers et qu'il y a un zéro non significatif pour un nombre inférieur à 1
Le symbole hashtag (#) représente un espace réservé à un chiffre qui affiche un chiffre s'il est disponible dans cette position, ou bien rien.
Vous pouvez également utiliser la fonction format pour adresser une cellule individuelle ou une plage de cellules pour modifier le format :
| 1 | Feuilles("Feuille1").Plage("A1:A10").NumberFormat = "#,##0.00" |
Ce code définira la plage de cellules (A1 à A10) sur un format personnalisé qui sépare les milliers par des virgules et affiche 2 décimales.
Si vous vérifiez le format des cellules sur le frontal Excel, vous constaterez qu'un nouveau format personnalisé a été créé.
Vous pouvez également formater des nombres dans la barre d'état Excel dans le coin inférieur gauche de la fenêtre Excel :
| 1 | Application.StatusBar = Format(1234567.89, "#,##0.00") |
Vous l'effacez de la barre d'état en utilisant :
| 1 | Application.StatusBar = "" |
Création d'une chaîne de format
Cet exemple ajoutera le texte « Total Sales » après chaque nombre, ainsi qu'un séparateur de milliers
| 1 | Sheets("Sheet1").Range("A1:A6").NumberFormat = "#,##0.00"" Total Sales""" |
Voici à quoi ressembleront vos chiffres :
Notez que la cellule A6 a une formule 'SUM', et cela inclura le texte 'Total Sales' sans nécessiter de formatage. Si le formatage est appliqué, comme dans le code ci-dessus, il ne mettra pas une instance supplémentaire de « Total Sales » dans la cellule A6
Bien que les cellules affichent désormais des caractères alphanumériques, les nombres sont toujours présents sous forme numérique. La formule « SUM » fonctionne toujours car elle utilise la valeur numérique en arrière-plan, et non la façon dont le nombre est formaté.
La virgule dans la chaîne de format fournit le séparateur des milliers. Notez que vous n'avez besoin de le mettre dans la chaîne qu'une seule fois. Si le nombre se chiffre en millions ou en milliards, il séparera toujours les chiffres en groupes de 3
Le zéro dans la chaîne de format (0) est un espace réservé à un chiffre. Il affiche un chiffre s'il est là, ou un zéro. Son positionnement est très important pour assurer l'uniformité avec la mise en forme
Dans la chaîne de format, les caractères dièse (#) n'afficheront rien s'il n'y a pas de chiffre. Cependant, s'il y a un nombre comme 0,8 (toutes les décimales), nous voulons qu'il apparaisse comme 0,80 afin qu'il s'aligne avec les autres nombres.
En utilisant un seul zéro à gauche de la virgule décimale et deux zéros à droite de la virgule décimale dans la chaîne de format, cela donnera le résultat requis (0,80).
S'il n'y avait qu'un seul zéro à droite de la virgule décimale, le résultat serait « 0,8 » et tout serait affiché avec une décimale.
Utilisation d'une chaîne de format pour l'alignement
Nous pouvons vouloir voir tous les nombres décimaux dans une plage alignée sur leurs points décimaux, de sorte que tous les points décimaux soient directement les uns sous les autres, quel que soit le nombre de décimales qu'il y a sur chaque nombre.
Vous pouvez utiliser un point d'interrogation (?) dans votre chaîne de format pour ce faire. Le « ? » indique qu'un numéro est affiché s'il est disponible, ou un espace
| 1 | Sheets("Sheet1").Range("A1:A6").NumberFormat = "#,##0.00 ??" |
Cela affichera vos numéros comme suit :
Tous les points décimaux s'alignent maintenant les uns sous les autres. La cellule A5 a trois décimales et cela annulerait l'alignement normalement, mais l'utilisation du caractère « ? » aligne tout parfaitement.
Utilisation de caractères littéraux dans la chaîne de format
Vous pouvez ajouter n'importe quel caractère littéral dans votre chaîne de format en le faisant précéder d'une barre oblique inverse (\).
Supposons que vous souhaitiez afficher un indicateur de devise particulier pour vos numéros qui ne soit pas basé sur votre région. Le problème est que si vous utilisez un indicateur de devise, Excel se réfère automatiquement à votre local et le remplace par celui approprié pour les paramètres régionaux définis dans le Panneau de configuration Windows. Cela peut avoir des implications si votre application Excel est distribuée dans d'autres pays et que vous voulez vous assurer que, quel que soit le paramètre régional, l'indicateur de devise est toujours le même.
Vous pouvez également indiquer que les nombres sont en millions dans l'exemple suivant :
| 1 | Feuilles("Feuille1").Plage("A1:A6").NumberFormat = "\$#,##0.00 \m" |
Cela produira les résultats suivants sur votre feuille de calcul :
En utilisant une barre oblique inverse pour afficher des caractères littéraux, vous n'avez pas besoin d'utiliser une barre oblique inverse pour chaque caractère individuel dans une chaîne. Vous pouvez utiliser:
| 1 | Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00 \mill" |
Cela affichera « mill » après chaque nombre dans la plage formatée.
Vous pouvez utiliser la plupart des caractères comme littéraux, mais pas les caractères réservés tels que 0, #, ?
Utilisation de virgules dans une chaîne de format
Nous avons déjà vu que les virgules peuvent être utilisées pour créer des séparateurs de milliers pour les grands nombres, mais elles peuvent également être utilisées d'une autre manière.
En les utilisant à la fin de la partie numérique de la chaîne de format, ils agissent comme des scalers de milliers. En d'autres termes, ils diviseront chaque nombre par 1 000 à chaque fois qu'il y aura une virgule.
Dans l'exemple de données, nous le montrons avec un indicateur indiquant qu'il est en millions. En insérant une virgule dans la chaîne de format, nous pouvons afficher ces nombres divisés par 1 000.
| 1 | Feuilles("Feuille1").Plage("A1:A6").NumberFormat = "\$#,##0.00,\m" |
Cela affichera les nombres divisés par 1 000 bien que le nombre d'origine soit toujours en arrière-plan dans la cellule.
Si vous mettez deux virgules dans la chaîne de format, les nombres seront divisés par un million
| 1 | Feuilles("Feuille1").Plage("A1:A6").NumberFormat = "\$#,##0.00,,\m" |
Ce sera le résultat en utilisant une seule virgule (diviser par 1 000) :
Création d'une mise en forme conditionnelle dans la chaîne de format
Vous pouvez configurer une mise en forme conditionnelle sur le front-end d'Excel, mais vous pouvez également le faire dans votre code VBA, ce qui signifie que vous pouvez manipuler la chaîne de format par programme pour apporter des modifications.
Vous pouvez utiliser jusqu'à quatre sections dans votre chaîne de format. Chaque section est délimitée par un point-virgule (;). Les quatre sections correspondent à positif, négatif, zéro et texte
| 1 | Plage("A1:A7").NumberFormat = "#,##0.00;[Rouge]-#,##0.00;[Vert] #,##0.00;[Bleu]" |
Dans cet exemple, nous utilisons les mêmes caractères dièse, virgule et zéro pour fournir des séparateurs de milliers et deux points décimaux, mais nous avons maintenant des sections différentes pour chaque type de valeur.
La première section concerne les nombres positifs et n'est pas différente de ce que nous avons déjà vu précédemment en termes de format.
La deuxième section pour les nombres négatifs introduit une couleur (rouge) qui est contenue dans une paire de crochets. Le format est le même que pour les nombres positifs, sauf qu'un signe moins (-) a été ajouté devant.
La troisième section pour les nombres zéro utilise une couleur (vert) entre crochets avec la même chaîne numérique que pour les nombres positifs.
La dernière section concerne les valeurs de texte, et tout ce dont cela a besoin est une couleur (bleu) à nouveau entre crochets
Voici le résultat de l'application de cette chaîne de format :
Vous pouvez aller plus loin avec les conditions dans la chaîne de format. Supposons que vous vouliez afficher chaque nombre positif supérieur à 10 000 en vert et tous les autres nombres en rouge, vous pouvez utiliser cette chaîne de format :
| 1 | Plage ("A1:A7").NumberFormat = "[>=10000][Vert]#,##0.00;[<10000][Rouge]#,##0.00" |
Cette chaîne de format comprend des conditions pour >=10000 définies entre crochets afin que le vert ne soit utilisé que lorsque le nombre est supérieur ou égal à 10000
Voici le résultat :
Utilisation de fractions dans la mise en forme de chaînes
Les fractions ne sont pas souvent utilisées dans les feuilles de calcul, car elles correspondent normalement à des décimales que tout le monde connaît.
Cependant, ils servent parfois à quelque chose. Cet exemple affichera les dollars et les cents :
| 1 | Range("A1:A7").NumberFormat = "#,##0 "" dollars et "" 00/100 "" cents """ |
Voici le résultat qui sera produit :
N'oubliez pas que bien que les nombres soient affichés sous forme de texte, ils sont toujours là en arrière-plan sous forme de nombres et toutes les formules Excel peuvent toujours être utilisées dessus.
Formats de date et d'heure
Les dates sont en fait des nombres et vous pouvez utiliser des formats sur elles de la même manière que pour les nombres. Si vous formatez une date sous forme de nombre numérique, vous verrez un grand nombre à gauche de la virgule et un certain nombre de décimales. Le nombre à gauche de la virgule décimale indique le nombre de jours à partir du 01-janvier-1900, et les décimales indiquent l'heure sur la base de 24 heures
| 1 | Format MsgBox(Maintenant(), "jj-mmm-aaaa") |
Cela formatera la date actuelle pour afficher '08-Jul-2020'. L'utilisation de « mmm » pour le mois affiche les trois premiers caractères du nom du mois. Si vous voulez le nom complet du mois, vous utilisez 'mmmm'
Vous pouvez inclure des heures dans votre chaîne de format :
| 1 | Format MsgBox(Maintenant(), "jj-mmm-aaaa hh:mm AM/PM") |
Cela affichera '08-Jul-2020 13:25 PM'
'hh:mm' représente les heures et les minutes et AM/PM utilise une horloge de 12 heures par opposition à une horloge de 24 heures.
Vous pouvez incorporer des caractères de texte dans votre chaîne de format :
| 1 | Format MsgBox(Maintenant(), "jj-mmm-aaaa hh:mm AM/PM"" aujourd'hui""") |
Cela affichera '08-Jul-2020 13:25 PM aujourd'hui'
Vous pouvez également utiliser des caractères littéraux en utilisant une barre oblique inverse devant de la même manière que pour les chaînes de format numérique.
Formats prédéfinis
Excel a un certain nombre de formats intégrés pour les nombres et les dates que vous pouvez utiliser dans votre code. Ceux-ci reflètent principalement ce qui est disponible sur le front-end de formatage des nombres, bien que certains d'entre eux dépassent ce qui est normalement disponible sur la fenêtre contextuelle. De plus, vous n'avez pas la possibilité de choisir le nombre de décimales ou d'utiliser des milliers de séparateurs.
Numéro général
Ce format affichera le nombre exactement tel qu'il est
| 1 | Format MsgBox (1234567.89, "Numéro général") |
Le résultat sera 1234567.89
Monnaie
| 1 | Format MsgBox (1234567.894, "Devise") |
Ce format ajoutera un symbole de devise devant le nombre, par ex. $, £ en fonction de vos paramètres régionaux, mais il formatera également le nombre à 2 décimales et séparera les milliers par des virgules.
Le résultat sera de 1 234 567,89 $
Fixé
| 1 | Format MsgBox (1234567.894, "Fixe") |
Ce format affiche au moins un chiffre à gauche mais seulement deux chiffres à droite de la virgule décimale.
Le résultat sera 1234567.89
Standard
| 1 | Format MsgBox (1234567.894, "Standard") |
Cela affiche le nombre avec les milliers de séparateurs, mais seulement avec deux décimales.
Le résultat sera 1 234 567,89
Pour cent
| 1 | Format MsgBox (1234567.894, "Pourcentage") |
Le nombre est multiplié par 100 et un symbole de pourcentage (%) est ajouté à la fin du nombre. Le format s'affiche à 2 décimales
Le résultat sera 123456789,40 %
Scientifique
| 1 | Format MsgBox (1234567.894, "Scientifique") |
Cela convertit le nombre au format exponentiel
Le résultat sera 1.23E+06
Oui Non
| 1 | Format MsgBox (1234567.894, "Oui/Non") |
Cela affiche « Non » si le nombre est zéro, sinon affiche « Oui »
Le résultat sera « Oui »
Vrai faux
| 1 | Format MsgBox (1234567.894, "Vrai/Faux") |
Cela affiche « False » si le nombre est zéro, sinon « True »
Le résultat sera « vrai »
Allumé éteint
| 1 | Format MsgBox (1234567.894, "Activé/Désactivé") |
Cela affiche « Off » si le nombre est zéro, sinon affiche « On »
Le résultat sera « On »
Date générale
| 1 | Format MsgBox(Maintenant(), "Date générale") |
Cela affichera la date sous forme de date et d'heure en utilisant la notation AM/PM. Le mode d'affichage de la date dépend de vos paramètres dans le Panneau de configuration Windows (Horloge et région | Région). Il peut être affiché sous la forme « mm/jj/aaaa » ou « jj/mm/aaaa »
Le résultat sera '07/7/2020 15:48:25'
Date longue
| 1 | Format MsgBox(Maintenant(), "Date longue") |
Cela affichera une longue date telle que définie dans le Panneau de configuration de Windows (Horloge et région | Région). Notez qu'il n'inclut pas le temps.
Le résultat sera « mardi 7 juillet 2022 »
Date moyenne
| 1 | Format MsgBox(Maintenant(), "Date moyenne") |
Cela affiche une date telle que définie dans les paramètres de date courte tels que définis par les paramètres régionaux dans le Panneau de configuration Windows.
Le résultat sera '07-Jul-20'
Date courte
| 1 | Format MsgBox(Maintenant(), "Date courte") |
Affiche une date courte telle que définie dans le Panneau de configuration Windows (Horloge et région | Région). La façon dont la date est affichée dépend de votre région. Il peut être affiché sous la forme « mm/jj/aaaa » ou « jj/mm/aaaa »
Le résultat sera « 7/7/2020 »
Longtemps
| 1 | Format MsgBox (Maintenant(), "Longue durée") |
Affiche une longue durée telle que définie dans le Panneau de configuration Windows (Horloge et région | Région).
Le résultat sera "16:11:39"
Temps moyen
| 1 | Format MsgBox (Maintenant(), "Durée moyenne") |
Affiche une durée moyenne telle que définie par vos paramètres régionaux dans le Panneau de configuration de Windows. Ceci est généralement défini au format 12 heures en utilisant les heures, les minutes et les secondes et le format AM/PM.
Le résultat sera '16:15'
Court instant
| 1 | Format MsgBox(Maintenant(), "Court temps") |
Affiche une heure moyenne telle que définie dans le Panneau de configuration de Windows (Horloge et région | Région). Ceci est généralement défini au format 24 heures avec des heures et des minutes
Le résultat sera '16:18'
Dangers de l'utilisation des formats prédéfinis d'Excel dans les dates et les heures
L'utilisation des formats prédéfinis pour les dates et les heures dans Excel VBA dépend beaucoup des paramètres du Panneau de configuration Windows et également des paramètres régionaux.
Les utilisateurs peuvent facilement modifier ces paramètres, ce qui aura un effet sur la façon dont vos dates et heures sont affichées dans Excel
Par exemple, si vous développez une application Excel qui utilise des formats prédéfinis dans votre code VBA, ceux-ci peuvent changer complètement si un utilisateur se trouve dans un pays différent ou utilise des paramètres régionaux différents de vous. Vous constaterez peut-être que les largeurs de colonne ne correspondent pas à la définition de date ou que, sur un formulaire utilisateur, le contrôle Active X tel qu'un contrôle de zone de liste déroulante (déroulant) est trop étroit pour que les dates et les heures s'affichent correctement.
Vous devez tenir compte de la localisation géographique du public lorsque vous développez votre application Excel
Formats définis par l'utilisateur pour les nombres
Il existe un certain nombre de paramètres différents que vous pouvez utiliser lors de la définition de votre chaîne de format :
| Personnage | La description |
| Chaîne nulle | Pas de formatage |
| 0 | Espace réservé à un chiffre. Affiche un chiffre ou un zéro. S'il y a un chiffre pour cette position, il affiche le chiffre, sinon il affiche 0. S'il y a moins de chiffres que de zéros, vous obtiendrez des zéros de début ou de fin. S'il y a plus de chiffres après la virgule que de zéros, le nombre est arrondi au nombre de décimales indiqué par les zéros. S'il y a plus de chiffres avant la virgule que de zéros, ceux-ci seront affichés normalement. |
| # | Espace réservé aux chiffres. Cela affiche un chiffre ou rien. Il fonctionne de la même manière que l'espace réservé au zéro ci-dessus, sauf que les zéros de début et de fin ne sont pas affichés. Par exemple, 0,75 serait affiché en utilisant zéro espace réservé, mais ce serait 0,75 en utilisant # espaces réservés. |
| . Virgule. | Un seul autorisé par chaîne de format. Ce caractère dépend des paramètres du Panneau de configuration de Windows. |
| % | Espace réservé de pourcentage. Multiplie le nombre par 100 et place le caractère % là où il apparaît dans la chaîne de format |
| , (virgule) | Séparateur de milliers. Ceci est utilisé si des espaces réservés 0 ou # sont utilisés et que la chaîne de format contient une virgule. Une virgule à gauche de la virgule décimale indique un arrondi au millier le plus proche. Par exemple. ##0, Deux virgules adjacentes à gauche du séparateur de milliers indiquent un arrondi au million le plus proche. Par exemple. ##0,, |
| E-E+ | Format scientifique. Cela affiche le nombre de manière exponentielle. |
| : (deux points) | Séparateur d'heure - utilisé lors du formatage d'une heure pour diviser les heures, les minutes et les secondes. |
| / | Séparateur de date - utilisé lors de la spécification d'un format pour une date |
| - + £ $ ( ) | Affiche un caractère littéral.Pour afficher un caractère autre que celui répertorié ici, faites-le précéder d'une barre oblique inverse (\) |
Formats définis par l'utilisateur pour les dates et les heures
Ces caractères peuvent tous être utilisés dans votre chaîne de formatage lors du formatage des dates et des heures :
| Personnage | Sens |
| c | Affiche la date sous la forme ddddd et l'heure sous la forme ttttt |
| ré | Afficher le jour sous forme de nombre sans zéro non significatif |
| jj | Afficher le jour sous forme de nombre avec un zéro non significatif |
| jjj | Afficher le jour sous forme d'abréviation (Dim - Sam) |
| jjjj | Afficher le nom complet du jour (dimanche - samedi) |
| jjjjj | Afficher un numéro de série de date en tant que date complète selon la date courte dans les paramètres internationaux du panneau de configuration de Windows |
| jjjjj | Affiche un numéro de série de date en tant que date complète selon la date longue dans les paramètres internationaux du Panneau de configuration Windows. |
| w | Affiche le jour de la semaine sous forme de nombre (1 = dimanche) |
| ww | Affiche la semaine de l'année sous forme de nombre (1-53) |
| m | Affiche le mois sous forme de nombre sans zéro non significatif |
| mm | Affiche le mois sous forme de nombre avec des zéros non significatifs |
| mmm | Affiche le mois sous forme d'abréviation (jan-déc) |
| mmmm | Affiche le nom complet du mois (janvier - décembre) |
| q | Affiche le trimestre de l'année sous forme de nombre (1-4) |
| oui | Affiche le jour de l'année sous forme de nombre (1-366) |
| aa | Affiche l'année sous forme de nombre à deux chiffres |
| aaaa | Affiche l'année sous forme de nombre à quatre chiffres |
| h | Affiche l'heure sous forme de nombre sans zéro non significatif |
| hum | Affiche l'heure sous forme de nombre avec un zéro non significatif |
| m | Affiche la minute sous forme de nombre sans zéro non significatif |
| nn | Affiche la minute sous forme de nombre avec un zéro non significatif |
| s | Affiche la seconde sous forme de nombre sans zéro non significatif |
| ss | Affiche la seconde sous forme de nombre avec un zéro non significatif |
| tttt | Afficher un numéro de série de temps sous forme de temps complet. |
| MATIN APRÈS-MIDI | Utilisez une horloge de 12 heures et affichez AM ou PM pour indiquer avant ou après midi. |
| matin après-midi | Utilisez une horloge de 12 heures et utilisez am ou pm pour indiquer avant ou après midi |
| A/P | Utilisez une horloge de 12 heures et utilisez A ou P pour indiquer avant ou après midi |
| a/p | Utilisez une horloge de 12 heures et utilisez a ou p pour indiquer avant ou après midi |
