Mise en Forme des Nombres dans Excel VBA

Formatage 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 qui permet d’utiliser différents formats numériques :

Le formatage des nombres facilite leur lecture et leur compréhension. Le format par défaut d’Excel pour les nombres saisis dans les cellules est le format « Standard », ce qui signifie que le nombre est affiché exactement comme vous l’avez saisi.

Par exemple, si vous saisissez un nombre entier, par exemple 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 sera pas aligné dans la colonne avec les nombres entiers et qu’il aura l’air très 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. Ce sont des millions ou des dizaines de millions? Du point de vue de l’utilisateur qui regarde une colonne de chiffres, cela rend la lecture et la comparaison assez difficiles.

En VBA, vous avez accès exactement à la même gamme de formats que ceux dont vous disposez dans la partie graphique d’Excel. Cela s’applique non seulement à une valeur saisie dans une cellule d’une feuille de calcul, mais aussi à des éléments tels que les boîtes de message, les contrôles UserForm, les diagrammes et les graphiques, ainsi que la barre d’état Excel située dans le coin inférieur gauche de la feuille de calcul.

La fonction Format est une fonction extrêmement utile en VBA en termes de présentation, mais elle est également très complexe en termes de flexibilité offerte dans la façon dont les chiffres sont affichés.

Comment Utiliser la Fonction Format en VBA 

Si vous affichez une boîte de message, la fonction Format peut être utilisée directement :

MsgBox Format(1234567.89, "#,##0.00")

Cela affichera un grand nombre en utilisant des virgules pour séparer les milliers et pour afficher deux décimales. Le résultat sera 1 234 567,89. Les zéros à la place du dièse garantissent que les décimales seront affichées sous la forme de 00 dans les nombres entiers et qu’il y a un zéro de tête pour un nombre inférieur à 1

Le symbole dièse (#) représente un caractère de remplacement qui affiche un chiffre s’il est disponible à cette position, sinon rien.

Vous pouvez également utiliser la fonction de formatage pour modifier le format d’une cellule individuelle ou d’une plage de cellules:

Sheets("Feuil1").Range("A1:A10").NumberFormat = "#,##0.00"

Ce code définit la plage de cellules (A1 à A10) dans un format personnalisé qui sépare les milliers par des virgules et affiche deux décimales.

Si vous vérifiez le format des cellules dans l’interface graphique d’Excel, vous constaterez qu’un nouveau format personnalisé a été créé.

Vous pouvez également formater les nombres dans la barre d’état Excel située dans le coin inférieur gauche de la fenêtre Excel :

Application.StatusBar = Format(1234567.89, "#,##0.00")

Vous pouvez effacer ces chiffres de la barre d’état en utilisant :

Application.StatusBar = ""

Création d’une Chaîne de Format

Cet exemple ajoutera le texte « Ventes Totales » après chaque nombre, ainsi qu’un séparateur de milliers

Sheets("Feuil1").Range("A1:A6").NumberFormat = "#,##0.00"" Ventes Totales """

Voici à quoi ressembleront vos chiffres :

Notez que la cellule A6 contient une formule ‘SOMME’, qui inclura le texte ‘Ventes Totales’ sans nécessiter de mise en forme. Si le formatage est appliqué, comme dans le code ci-dessus, il ne mettra pas une instance supplémentaire de « Ventes Totales » dans la cellule A6

Bien que les cellules affichent désormais des caractères alphanumériques, les chiffres sont toujours présents sous forme numérique. La formule « SOMME » fonctionne toujours car elle utilise la valeur numérique en arrière-plan, et non le formatage du nombre.

La virgule dans la chaîne de format spécifie le caractère à utiliser comme séparateur de milliers. Notez que vous ne devez l’insérer qu’une seule fois dans la chaîne. Si le nombre se compte en millions ou en milliards, la formule séparera toujours les chiffres par groupes de 3

Le zéro de la chaîne de format (0) est un caractère de remplacement pour les chiffres. Il affiche un chiffre s’il est présent, ou un zéro. Son positionnement est très important pour assurer l’uniformité du formatage

Dans la chaîne de format, les caractères dièses (#) n’afficheront rien s’il n’y a pas de chiffre à leur position. Toutefois, s’il y a un chiffre comme .8 (toutes les décimales), nous voulons qu’il s’affiche sous la forme 0,80 afin qu’il soit aligné avec les autres chiffres. En utilisant un seul zéro à gauche de la virgule et deux zéros à droite de la virgule dans la chaîne de format, on obtient le résultat souhaité (0,80).

S’il n’y avait qu’un seul zéro à droite de la virgule, 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 souhaiter voir tous les nombres décimaux d’une plage alignés sur leurs points décimaux, de sorte que tous les points décimaux se trouvent directement les uns sous les autres, quel que soit le nombre de décimales de chaque nombre.

Pour ce faire, vous pouvez utiliser un point d’interrogation (?) dans votre chaîne de format. Le « ? » indique qu’un nombre est affiché s’il est disponible, ou un espace

Sheets("Feuil1").Range("A1:A6").NumberFormat = "#,##0.00??"

Cela affichera vos chiffres comme suit :

Tous les points décimaux sont maintenant alignés les uns en dessous des autres. La cellule A5 comporte trois décimales, ce qui devrait normalement fausser l’alignement, mais l’utilisation du caractère « ? » aligne tout parfaitement.

Utilisation de Caractères Littéraux dans une 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 chiffres, qui est différent de la devise utilisée dans vos paramètres régionaux. Le problème est que si vous utilisez un indicateur de devise, Excel se réfère automatiquement à vos paramètres régionaux et la modifie en fonction de ce qui est défini dans le panneau de configuration de Windows. Cela peut avoir des conséquences si votre application Excel est distribuée dans d’autres pays et que vous voulez vous assurer que l’indicateur de devise est toujours le même, quelle que soit la configuration de Windows.

Vous pouvez également vouloir indiquer que les chiffres sont en millions dans l’exemple suivant :

Sheets("Sheet1").Range("A1:A6").NumberFormat = "\$#,##0.00 \m"

Vous obtiendrez ainsi 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 :

Sheets("Feuil1").Range("A1:A6").NumberFormat = "\$#,##0.00 \mill"

Ceci 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 des 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, elles agissent comme des facteurs de milliers. En d’autres termes, elles divisent chaque nombre par 1 000 pour chaque virgule présente à la fin de la partie numérique de la chaine.

Dans les données de l’exemple, nous affichons les chiffres avec un indicateur spécifiant qu’ils sont en millions. En insérant une virgule dans la chaîne de format, nous pouvons afficher ces chiffres divisés par 1 000.

Sheets("Feuil1").Range("A1:A6").NumberFormat = "\$#,##0.00,\m"

Les nombres divisés par 1 000 seront affichés, mais le nombre original restera en arrière-plan dans la cellule. Si vous mettez deux virgules dans la chaîne de format, les chiffres seront divisés par un million

Sheets("Feuil1").Range("A1:A6").NumberFormat = "\$#,##0.00,,\m"

Voici le résultat obtenu en utilisant une seule virgule (divisés par 1 000) :

Création d’un Formatage Conditionnel dans une Chaîne de Format

Vous pouvez configurer le formatage conditionnel dans l’interface graphique d’Excel, mais vous pouvez également le faire dans votre code VBA, ce qui signifie que vous pouvez manipuler la chaîne de formatage de manière programmatique 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 aux valeurs positives, négatives, nulles et au texte.

Range("A1:A7").NumberFormat = "#,##0.00 ;[Red]-#,##0.00 ;[Green] #,##0.00 ;[Blue]"

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, la chaîne numérique étant la même que pour les nombres positifs.

La dernière section concerne les valeurs de texte, et tout ce dont elle a besoin, c’est d’une couleur (bleu) entre crochets.

Voici le résultat de l’application de cette chaîne de format :

Vous pouvez aller plus loin avec des conditions dans la chaîne de format. Supposons que vous vouliez afficher tous les nombres positifs supérieurs à 10 000 en vert, et tous les autres en rouge, vous pourriez utiliser cette chaîne de format :

Range("A1:A7").NumberFormat = "[>=10000][Green]#,##0.00 ;[<10000][Red]#,##0.00"

Cette chaîne de format inclut des conditions pour >=10000 entre crochets, de sorte que le vert ne sera utilisé que si le nombre est supérieur ou égal à 10000

Voici le résultat :

Utilisation des Fractions dans les Chaînes de Formatage

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, elles ont parfois leur utilité. Dans cet exemple, nous allons afficher des dollars et des cents :

Range("A1:A7").NumberFormat = "#,##0 "" dollars et "" 00/100 "" sous """

Voici le résultat qui sera produit :

N’oubliez pas que, bien que les chiffres soient affichés sous forme de texte, ils sont toujours présents en arrière-plan en tant que chiffres et toutes les formules Excel peuvent toujours être utilisées sur eux.

Formats de Date et Heure

Les dates sont en fait des nombres et vous pouvez utiliser des formats sur celles-ci de la même manière qu’avec les nombres. Si vous formatez une date comme un 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 indique le nombre de jours à partir du 01-Jan-1900, et les décimales indiquent l’heure sur la base de 24 heures

MsgBox Format(Now(), "dd-mmm-yyyy")

Ceci formatera la date actuelle pour l’afficher sous la forme ’08-nov-2021′. 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, utilisez ‘mmmm’

Vous pouvez inclure les heures dans votre chaîne de format :

MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM")

Ceci affichera ’08-nov-2021 01: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 :

MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM"" Aujourd'hui """)

Ceci affichera ’08-nov-2021 01:25 PM Aujourd’hui’

Vous pouvez également utiliser des caractères littéraux en les précédant d’une barre oblique inversée, de la même manière que pour les chaînes de format numériques.

Formats Prédéfinis

Excel dispose d’un certain nombre de formats intégrés pour les nombres et les dates que vous pouvez utiliser dans votre code. Ces formats reflètent principalement ce qui est disponible dans l’interface graphique de formatage des nombres, bien que certains d’entre eux aillent au-delà de ce qui est normalement disponible dans la boite de dialogue. Par exemple, vous n’avez pas la possibilité de modifier le nombre de décimales ou d’utiliser des séparateurs de milliers. Le noms des formats prédéfinis doivent être spécifiés en anglais lorsqu’ils sont utilisé dans la fonction Format de VBA.

Nombre Général (« General Number »)

Ce format affichera le nombre exactement tel qu’il est

MsgBox Format(1234567.89, "General Number")

Le résultat sera 1234567.89

Monnaie (« Currency »)

MsgBox Format(1234567.894, "Currency")

Ce format ajoutera un symbole monétaire devant le nombre, par exemple $, £, en fonction de votre langue, mais il formatera également le nombre à deux décimales et séparera les milliers par des virgules.

Le résultat sera 1 234 567,89 $

Fixe (« Fixed »)

MsgBox Format(1234567.894, "Fixed")

Ce format affiche au moins un chiffre à gauche mais seulement deux chiffres à droite du point décimal.

Le résultat sera 1234567.89

Standard

MsgBox Format(1234567.894, "Standard")

Cela affiche le nombre avec les séparateurs de milliers, mais seulement avec deux décimales.

Le résultat sera 1 234 567,89

Pourcentage (« Percent »)

MsgBox Format(1234567.894, "Percent")

Le nombre est multiplié par 100 et un symbole de pourcentage (%) est ajouté à la fin du nombre. Le format affiche jusqu’à 2 décimales

Le résultat sera 123456789.40%

Scientifique (« Scientific »)

MsgBox Format(1234567.894, "Scientific")

Ceci convertit le nombre en format exponentiel

Le résultat sera 1.23E+06

Oui/Non (« Yes/No »)

MsgBox Format(1234567.894, "Yes/No")

Ceci affiche ‘Non’ si le nombre est zéro, sinon affiche ‘Oui’

Le résultat sera ‘Oui’

Vrai/Faux (« True/False »)

MsgBox Format(1234567.894, "True/False")

Ceci affiche ‘Faux’ si le nombre est zéro, sinon affiche ‘Vrai’

Le résultat sera ‘True’

Actif/Inactif (« On/Off »)

MsgBox Format(1234567.894, "On/Off")

Ceci affiche ‘Inactif’ si le nombre est zéro, sinon affiche ‘Actif’

Le résultat sera ‘Actif’

Date Générale (« General Date »)

MsgBox Format(Now(), "General Date")

Cette opération affiche 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 de Windows (Horloge et région | Région). Elle peut être affichée sous la forme ‘mm/dd/yyyy’ ou ‘dd/mm/yyyy’

Le résultat sera ‘2021/11/08 3:48:25 PM’

Date Longue (« Long Date »)

MsgBox Format(Now(), "Long Date")

Cette commande affiche une date longue telle que définie dans le panneau de configuration de Windows (Horloge et région | Région). Notez qu’elle n’inclut pas l’heure.

Le résultat sera ‘8 Novembre 2021’

Date Moyenne (« Medium Date »)

MsgBox Format(Now(), "Medium Date")

Cela affiche une date telle que définie dans les paramètres de date courte selon la locale dans le panneau de configuration de Windows.

Le résultat sera ’08-nov-21′

Date Courte (« Short Date »)

MsgBox Format(Now(), "Short Date")

Affiche une date courte telle que définie dans le panneau de configuration de Windows (Horloge et région | Région). La façon dont la date est affichée dépend de votre région. Elle peut être affichée sous la forme ‘mm/dd/yyyy’ ou ‘dd/mm/yyyy’

Le résultat sera ‘2021-11-08’

Heure Longue (« Long Time »)

MsgBox Format(Now(), "Long Time")

Affiche une heure longue telle que définie dans le panneau de configuration de Windows (Horloge et région | Région).

Le résultat sera ‘4:11:39 PM’

Heure moyenne (« Medium Time »)

MsgBox Format(Now(), "Medium Time")

Affiche une heure moyenne telle que définie par vos paramètres régionaux dans le panneau de configuration de Windows. Elle est généralement définie comme un format de 12 heures utilisant les heures, les minutes et les secondes et le format AM/PM.

Le résultat sera ’04:15 PM’

Heure courte (« Short Time »)

MsgBox Format(Now(), "Short Time")

Affiche une heure moyenne telle que définie dans le panneau de configuration de Windows (Horloge et région | Région). Il s’agit généralement du format 24 heures avec les heures et les minutes

Le résultat sera ’16:18′

Dangers de l’Utilisation des Formats Prédéfinis d’Excel pour les Dates et les Heures

L’utilisation des formats prédéfinis pour les dates et les heures dans Excel VBA dépend fortement des paramètres du panneau de configuration de Windows et 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 autre pays ou utilise une autre locale que la vôtre. Vous pouvez constater que la largeur des colonnes ne correspond pas à la définition de la date ou que, sur un formulaire utilisateur, le contrôle Active X, tel qu’une boîte combo (liste déroulante), est trop étroit pour que les dates et les heures s’affichent correctement.

Vous devez tenir compte de la situation géographique de votre 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 pour définir votre chaîne de format :

Caractères	Description
Chaîne nulle	Pas de formatage
0	Caractère de remplacement d’un chiffre. Affiche un chiffre ou un zéro. S’il existe 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 tête ou de queue. 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 s’affichent normalement.
#	Caractère de remplacement d’un chiffre. Ceci affiche un chiffre ou rien. Il fonctionne de la même manière que le caractère de remplacement zéro ci-dessus, sauf que les zéros de tête et de queue ne sont pas affichés. Par exemple, 0,75 serait affiché avec le caractère de remplacement zéro, mais ce serait .75 avec le caractère de remplacement #.
. Point décimal.	Un seul caractère est autorisé par chaîne de format. Ce caractère dépend des paramètres du panneau de configuration de Windows.
%	Caractère de remplacement 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. Selon les paramètres le séparateur de milliers qui s’affiche peut varier. Par exemple, sur un sytème en francais, l’espace est souvent utilisé puisque la virgule sert généralement à séparer la partie entière de la partie décimale. La virgule est utilisée si les caractères de remplacement 0 ou # sont utilisés et que la chaîne de format contient une virgule. Une virgule à gauche du point décimal indique l’arrondissement 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 séparer les heures, les minutes et les secondes.
/	Séparateur de date – utilisé pour spécifier le format d’une date
– + £ $ ( )	Affiche un caractère littéral. Pour afficher un caractère autre que celui indiqué 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 pour formater les dates et les heures :

Caractère	Signification
c	Affiche la date sous la forme ddddd et l’heure sous la forme ttttt
d	Affiche le jour sous la forme d’un nombre sans le zéro initial
dd	Affiche le jour sous la forme d’un nombre avec un zéro devant
ddd	Affiche le jour sous la forme d’une abréviation (Sun – Sat)
dddd	Affiche le nom complet du jour (Dimanche – Samedi)
ddddd	Affiche le numéro de série d’une date sous forme de date complète, conformément à la fonction Date courte des paramètres internationaux du Panneau de configuration de Windows
dddddd	Affiche le numéro de série d’une date sous la forme d’une date complète selon la date longue dans les paramètres internationaux du panneau de configuration de Windows.
w	Affiche le jour de la semaine sous forme de chiffre (1 = dimanche)
ww	Affiche la semaine de l’année sous la forme d’un nombre (1-53)
m	Affiche le mois sous la forme d’un nombre sans le zéro initial
mm	Affiche le mois sous la forme d’un nombre avec des zéros en tête
mmm	Affiche le mois sous forme d’abréviation (Jan-Dec)
mmmm	Affiche le nom complet du mois (janvier – décembre)
q	Affiche le trimestre de l’année sous forme de nombre (1-4)
y	Affiche le jour de l’année sous forme de nombre (1-366)
yy	Affiche l’année sous la forme d’un nombre à deux chiffres
yyyy	Affiche l’année sous la forme d’un nombre à quatre chiffres
h	Affiche l’heure sous la forme d’un nombre sans le zéro initial
hh	Affiche l’heure sous la forme d’un nombre avec un zéro devant
n	Affiche les minutes sous la forme d’un nombre sans le premier zéro
nn	Affiche les minutes sous la forme d’un nombre avec un zéro devant
s	Affiche les secondes sous la forme d’un nombre sans le premier zéro
ss	Affiche les secondes sous la forme d’un nombre avec un zéro devant
ttttt	Affiche un numéro de série de l’heure sous la forme d’une heure complète.
AM/PM	Utilisez une horloge de 12 heures et affichez AM ou PM pour indiquer avant ou après midi.
am/pm	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