La fonction Format et ses variantes dans VBA.
Date de publication : 15 janvier 2010
Par
Jean-Philippe ANDRÉ (http://jpcheck.developpez.com)
Cet article fait suite au constat que la fonction Format() est régulièrement utilisée dans les programmes VBA, mais qu'elle est bien souvent mal utilisée, ou pose des soucis quant à la façon d'appréhender les résultats. Les quelques chapitres, calqués sur l'aide en ligne Excel, qui suivent feront un inventaire des cas d'utilisation de la fonction Format(), de ses fonctions dérivées, et de ses alternatives dans certains cas précis.
I. Définition de la fonction Format
II. La fonction Format par type de valeur
II-a. Fonction Format( ) et les valeurs numériques (nombres)
II-a-i. Format basique
II-a-ii. Format par plages de valeurs
II-a-iii. Formats définis par l'utilisateur
II-b. Fonction Format( ) et les dates et heures
II-b-i. Format de date, constantes
II-b-ii. Format de dates nommés
II-b-iii. Formats de date/heure définis par l'utilisateur
II-c. Fonction Format( ) et les chaînes de caractères
III. Fonctions dérivées de la fonction Format( )
IV. Alternatives à la fonction Format( )
IV-a. Alternatives pour les dates et heures
IV-a-i. Year( ), Month( ), Day( ), Hour( ), Minute( ) et Second( )
IV-a-ii. Datepart( )
IV-b. Alternatives pour les chaînes de caractères
IV-b-i. UCase et LCase
V. Conclusion
VI. Remerciements
I. Définition de la fonction Format
Dans l'aide en ligne, Office nous indique les éléments suivants :
Renvoie une valeur de type Variant (String) contenant une expression formatée en fonction des instructions contenues dans l'expression de mise en forme.
Syntaxe
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
|
La syntaxe de la fonction Format comprend les éléments suivants :
| Élément |
Description |
| expression |
Toute expression valide. |
| format |
Facultatif. Expression de mise en forme valide, nommée ou définie par l'utilisateur. |
| firstdayofweek |
Facultatif. Constante indiquant le premier jour de la semaine. |
| firstweekofyear |
Facultatif. Constante indiquant la première semaine de l'année. |
Remarques
| Pour mettre en forme des |
Vous devez |
| Nombres |
Utiliser des formats numériques nommés prédéfinis ou créer des formats numériques définis par l'utilisateur. |
| Dates et heures |
Utiliser des formats de date et d'heure nommés prédéfinis ou créer des formats de date et d'heure définis par l'utilisateur. |
| Numéros de série de date et d'heure |
Utiliser des formats de date ou d'heure ou des formats numériques. |
| Chaînes |
Créer des formats de chaîne définis par l'utilisateur. |
Il s'agit donc d'une fonction qui met en forme la valeur (paramètre 1) au format spécifié (paramètre 2). Son type de retour n'est pas strictement défini, mais est par défaut une chaîne de caractères. Bien qu'il existe une infinité de combinaisons de format possible, nous allons étudier les plus usités aujourd'hui, et par conséquent ceux que les utilisateurs risqueront de rencontrer le plus fréquemment.
II. La fonction Format par type de valeur
II-a. Fonction Format( ) et les valeurs numériques (nombres)
Selon les endroits du globe, les nombres ne sont pas tous représentés avec le même format. Ils ont parfois des caractères (des virgules, des espaces, des points) parfois même des combinaisons de plusieurs de ces caractères.
II-a-i. Format basique
| Exemples de nombres : |
Ligne de code |
Résultat affiché |
| Nombre standard |
Format(123456789) |
123456789 |
| Nombre avec séparateur des milliers |
Format(123456789, " #,##0 ") |
123 456 789 |
| Nombre avec deux décimales |
Format(123456789, " 0.00") |
123456789,00 |
| Combinaison des deux |
Format(123456789, "#,##0.00") |
123 456 789,00 |
II-a-ii. Format par plages de valeurs
La fonction Format( ) peut aussi être plus subtile, en prenant en compte la plage de valeurs (positive, négative, zéro, Null) et lui attribuer un format spécifique automatiquement. Ces différents formats sont séparés par des points-virgules " ; ".
| Exemples : |
Ligne de code type |
Ligne de code exemple |
Valeur cas Positif |
Valeur cas Négatif |
Valeur cas Zéro |
Valeur cas Null |
| Exemple à une section |
Format(valeur,"FormatQuelleQueSoitLaValeur") |
Format(val,"###,##0") |
1 200 |
-1 200; |
0 |
|
| Exemple à deux sections |
Format(valeur,"FormatSiPositifOuNul;FormatSiNegatif") |
Format(val,"0.00;-#,##0") |
1 200,00 |
-1 200 |
0,00 |
|
| Exemple à trois sections |
Format(valeur,"FormatSiPositif;FormatSiNegatif;Formatsi0") |
Format(val,"0.00;;\Z\e\r\o") |
1200,00 |
-1200,00 |
Zero |
|
| Exemple à quatre sections |
Format(valeur,"FormatSiPositif;FormatSiNegatif;Formatsi0;FormatSiNull") |
Format(val,";;\J\P;\N\u\l\l\e") |
|
|
JP |
Nulle |
II-a-iii. Formats définis par l'utilisateur
| Caractère |
Description |
| Aucun |
Affichage du nombre sans mise en forme. |
| (0) |
Espace réservé à un chiffre. Affichage d'un chiffre ou d'un zéro. Si l'expression contient un chiffre à la position occupée par le 0 dans la chaîne de mise en forme, il est affiché ; sinon un zéro est affiché à cet emplacement.
Si le nombre possède moins de chiffres que de zéros (de part et d'autre du séparateur décimal) dans l'expression de mise en forme, les zéros non significatifs et les zéros à droite sont affichés. Si le nombre compte plus de chiffres à droite du séparateur décimal que de zéros à droite de ce séparateur dans l'expression de mise en forme, le nombre est arrondi à autant de chiffres après la virgule qu'il y a de zéros. Si le nombre compte plus de chiffres à gauche du séparateur décimal qu'il y a de zéros à gauche de ce séparateur dans l'expression de mise en forme, les chiffres supplémentaires sont affichés sans modification. |
| (#) |
Espace réservé à un chiffre. Affichage d'un chiffre ou d'aucun caractère. Si l'expression mise en forme contient un chiffre à la position occupée par le signe # dans la chaîne de mise en forme, il est affiché ; sinon, rien n'est affiché à cet emplacement.
Ce symbole se comporte comme l'espace réservé au chiffre 0 ; cependant, les zéros non significatifs et les zéros à droite ne sont pas affichés si le nombre compte un nombre de chiffres inférieur ou égal au nombre de caractères # situés de part et d'autre du séparateur décimal dans l'expression de mise en forme. |
| (.) |
Espace réservé au séparateur décimal. Dans certains paramètres régionaux, le séparateur décimal est représenté par une virgule. L'espace réservé au séparateur décimal détermine le nombre de chiffres affichés de part et d'autre de ce séparateur. Si l'expression de mise en forme ne contient que des signes de nombre à gauche de ce symbole, les nombres inférieurs à 1 commencent par ce séparateur. Pour afficher les nombres fractionnaires avec un zéro non significatif, indiquez un 0 comme premier espace réservé à gauche du séparateur décimal. Le caractère réservé au séparateur décimal dans la sortie mise en forme dépend du format des nombres utilisé par votre système. |
| (%) |
Espace réservé au signe pourcentage. L'expression est multipliée par 100. Le signe pourcentage (%) est inséré à la position où il apparaît dans la chaîne de mise en forme. |
| (,) |
Dans certains paramètres régionaux, le séparateur de milliers est représenté par un espace. Le séparateur de milliers sépare les milliers des centaines dans un nombre qui comporte au moins quatre chiffres à gauche du séparateur décimal. Le séparateur de milliers standard est utilisé si le format contient un séparateur de milliers entouré d'espaces réservés à un chiffre (0 ou #). Deux séparateurs de milliers adjacents ou un séparateur de milliers situé immédiatement à gauche du séparateur décimal (qu'une décimale soit précisée ou non) signifie "reformuler le nombre en le divisant par 1 000 et en l'arrondissant si nécessaire". Par exemple, vous pouvez utiliser la chaîne de mise en forme "##0,, " pour représenter 100 millions sous la forme 100. Pour les nombres inférieurs à 1 million, un 0 est affiché. Deux séparateurs de milliers adjacents occupant n'importe quelle position autre que celle immédiatement à gauche du séparateur décimal sont considérés comme un seul séparateur de milliers. Le caractère effectivement utilisé comme séparateur de milliers dans la sortie mise en forme dépend du format des nombres utilisé par votre système. |
| (:) |
Séparateur horaire. Dans certains paramètres régionaux, le séparateur horaire est représenté par d'autres caractères. Le séparateur horaire dissocie les heures, les minutes et les secondes lorsque des valeurs horaires sont mises en forme. Le caractère effectivement utilisé comme séparateur horaire dans la sortie mise en forme dépend des paramètres de votre système. |
| (/) |
Séparateur de date. Dans certains paramètres régionaux, le séparateur de date est représenté par d'autres caractères. Le séparateur de date dissocie le jour, le mois et l'année lorsque des valeurs de date sont mises en forme. Le caractère effectivement utilisé comme séparateur de date dans la sortie mise en forme dépend des paramètres de votre système. |
| (E- E+ e- e+) |
Format scientifique. Si l'expression de mise en forme contient au moins un espace réservé à un chiffre (0 ou #) à droite de E-, E+, e- ou de e+, le nombre est affiché au format scientifique et E ou e est inséré entre le nombre et son exposant. Le nombre d'espaces réservés à un chiffre sur la droite détermine le nombre de chiffres de l'exposant. Utilisez E- ou e- pour placer un signe moins devant des exposants négatifs. Utilisez E+ ou e+ pour placer un signe moins devant des exposants négatifs et un signe plus devant des exposants positifs. |
| - + $ ( ) |
Affichage d'un caractère littéral. Pour afficher un caractère différent de ceux répertoriés, faites-le précéder d'une barre oblique inverse (\) ou placez-le entre guillemets doubles (" "). |
| (\) |
Affichage du caractère suivant de la chaîne de mise en forme. Pour afficher un caractère doté d'une signification spéciale sous la forme de caractère littéral, faites-le précéder d'une barre oblique inverse (\). La barre oblique inverse n'est pas affichée. Son utilisation revient à placer entre guillemets doubles le caractère suivant. Pour afficher une barre oblique inverse, utilisez deux barres obliques inverses (\\).
Les caractères de mise en forme de date et heure (a, c, d, h, m, n, p, q, s, t, w, y, / et :), les caractères de mise en forme numérique (#, 0, %, E, e, séparateur décimal et séparateur des milliers) et les caractères de mise en forme de chaîne (@, &, <, > et !) sont des exemples de caractères qui ne peuvent être affichés comme caractères littéraux. |
| ("ABC") |
Affichage de la chaîne entre guillemets doubles (" "). Pour inclure une chaîne dans l'argument format à partir du code, vous devez utiliser la fonction Chr(34) pour placer le texte entre guillemets doubles (34 est le code de caractère correspondant aux guillemets doubles (")). |
II-b. Fonction Format( ) et les dates et heures
II-b-i. Format de date, constantes
| Constante |
Valeur |
Description |
Exemple avec le 19/01/2010 20:34:05 |
| vbGeneralDate |
0 |
Affiche une date et/ou une heure. Pour les nombres réels, affiche une date et une heure. En l'absence de partie décimale, affiche seulement une date. S'il n'y a pas de partie entière, affiche seulement l'heure. Le mode d'affichage de la date et de l'heure est fonction des paramètres de votre système. |
|
| vbLongDate |
1 |
Affiche une date en utilisant le format de date complet spécifié dans les paramètres régionaux de votre ordinateur. |
|
| vbShortDate |
2 |
Affiche une date en utilisant le format de date abrégé spécifié dans les paramètres régionaux de l'ordinateur. |
|
| vbLongTime |
3 |
Affiche une heure en utilisant le format d'heure complet spécifié dans les paramètres régionaux de votre ordinateur. |
|
| vbShortTime |
4 |
Affiche une heure en utilisant le format d'heure abrégé spécifié dans les paramètres régionaux de votre ordinateur. |
|
II-b-ii. Format de dates nommés
| Nom de format |
Description |
Exemple |
| Date, général |
Affichage de la date et/ou de l'heure. Pour des nombres réels, affichage de la date et de l'heure, par exemple 4/3/93 05:34 PM. S'il n'y a pas de partie décimale, affichage de la date seulement, par exemple 4/3/93. S'il n'y a pas de partie entière, affichage de l'heure seulement, par exemple, 05:34 PM. Le format de la date est déterminé par les paramètres de votre système. |
|
| Date, complet |
Affichage de la date complète selon le format défini dans votre système. |
|
| Date, réduit |
Affichage de la date selon le format intermédiaire conforme à la langue de l'application hôte. |
|
| Date, abrégé |
Affichage de la date abrégée selon le format défini dans votre système. |
|
| Heure, complet |
Affichage de l'heure complète selon le format défini dans votre système, comprenant les heures, les minutes et les secondes. |
|
| Heure, réduit |
Affichage de l'heure dans un format de 12 heures en utilisant les heures et les minutes ainsi que les indicateurs AM/PM. |
|
| Heure, abrégé |
Affichage de l'heure au format de 24 heures, par exemple 17:45. |
|
II-b-iii. Formats de date/heure définis par l'utilisateur
| Caractère |
Description |
Exemple avec la date 19/01/2010 20:34:45 |
Exemple avec la date 08/07/2010 07:01:01 |
| (:) |
Séparateur horaire. Dans certains paramètres régionaux, le séparateur horaire est représenté par un autre caractère. Le séparateur horaire dissocie les heures, les minutes et les secondes lorsque des valeurs horaires sont mises en forme. Le caractère effectivement utilisé comme séparateur horaire dans la sortie mise en forme est déterminé par les paramètres de votre système. |
|
|
| (/) |
Séparateur de date. Dans certains paramètres régionaux, le séparateur de date est représenté par un autre caractère. Le séparateur de date dissocie le jour, le mois et l'année lorsque des valeurs de date sont mises en forme. Le caractère effectivement utilisé comme séparateur de date dans la sortie mise en forme est déterminé par les paramètres de votre système. |
|
|
| c |
Affichage de la date sous la forme ddddd et de l'heure sous la forme ttttt, dans cet ordre. Affichage des informations relatives à la date seulement si le numéro de série de date ne comporte aucune partie décimale ; affichage des informations relatives à l'heure seulement en l'absence de partie entière. |
19/01/2010 20:34:05 |
08/07/2010 07:01:01 |
| d |
Affichage du jour sous la forme d'un nombre sans zéro non significatif (1 - 31). |
19 |
8 |
| dd |
Affichage du jour sous la forme d'un nombre avec un zéro non significatif (01 - 31). |
19 |
08 |
| ddd |
Affichage du jour sous sa forme abrégée (dim - sam). |
mar. |
jeu. |
| dddd |
Affichage du jour sous sa forme complète (dimanche - samedi). |
mardi |
jeudi |
| ddddd |
Affichage de la date complète (jour, mois et année), mise en forme conformément à l'option de format de date abrégé définie dans votre système. Le format de date abrégé par défaut est j/m/aa. |
19/01/2010 |
08/07/2010 |
| dddddd |
Affichage d'un numéro de série de date sous la forme d'une date complète (jour, mois et année) mise en forme conformément à l'option de format de date longue définie dans votre système. Le format de date longue par défaut est jj mmmm aaaa. |
mardi 19 janvier 2010 |
jeudi 08 juillet 2010 |
| w |
Affichage du jour de la semaine sous la forme d'un nombre (de 1 pour dimanche à 7 pour samedi). |
3 |
5 |
| ww |
Affichage de la semaine de l'année sous la forme d'un nombre (1 - 54). |
4 |
28 |
| m |
Affichage du mois sous la forme d'un nombre sans zéro non significatif (1 - 12). Si m suit immédiatement h ou hh, les minutes sont affichées à la place du mois. |
1 |
7 |
| mm |
Affichage du mois sous la forme d'un nombre avec un zéro non significatif (01 - 12). Si m suit immédiatement h ou hh, les minutes sont affichées à la place du mois. |
01 |
07 |
| mmm |
Affichage du mois sous sa forme abrégée (jan - déc). |
janv |
juil |
| mmmm |
Affichage du mois sous sa forme complète (janvier - décembre). |
janvier |
juillet |
| q |
Affichage du trimestre de l'année sous la forme d'un nombre (1 - 4). |
1 |
3 |
| y |
Affichage du jour de l'année sous la forme d'un nombre (1 - 366). |
19 |
189 |
| yy |
Affichage de l'année sous la forme d'un nombre à deux chiffres (00 - 99). |
10 |
10 |
| yyyy |
Affichage de l'année sous la forme d'un nombre à quatre chiffres (100 - 9999). |
2010 |
2010 |
| h |
Affichage de l'heure sous la forme d'un nombre sans zéro non significatif (0 - 23). |
20 |
7 |
| Hh |
Affichage de l'heure sous la forme d'un nombre avec un zéro non significatif (00 - 23). |
20 |
07 |
| N |
Affichage des minutes sous la forme d'un nombre sans zéro non significatif (0 - 59). |
34 |
1 |
| Nn |
Affichage des minutes sous la forme d'un nombre avec un zéro non significatif (00 - 59). |
34 |
01 |
| S |
Affichage des secondes sous la forme d'un nombre sans zéro non significatif (0 - 59). |
5 |
1 |
| Ss |
Affichage des secondes sous la forme d'un nombre avec un zéro non significatif (00 - 59). |
05 |
01 |
| AM/PM |
Affichage à l'aide du format 12 heures et avec l'indicateur AM en majuscules pour une heure antérieure à midi et de l'indicateur PM en majuscules pour une heure située entre midi et 11:59 P.M. |
|
|
| am/pm |
Affichage à l'aide du format 12 heures et avec l'indicateur AM en minuscules pour une heure antérieure à midi et de l'indicateur PM en minuscules pour une heure située entre midi et 11:59 P.M. |
|
|
| A/P |
Affichage à l'aide du format 12 heures et avec l'indicateur A en majuscule pour une heure antérieure à midi et de l'indicateur P en majuscule pour une heure située entre midi et 11:59 P.M. |
|
|
| a/p |
Affichage à l'aide du format 12 heures et avec l'indicateur A en minuscule pour une heure antérieure à midi et de l'indicateur P en minuscule pour une heure située entre midi et 11:59 P.M. |
|
|
| AMPM |
Affichage à l'aide du format 12 heures et avec le littéral AM sous la forme définie par votre système, pour une heure antérieure à midi et du littéral PM sous la forme définie par votre système, pour une heure située entre midi et 11:59 P.M. AMPM peut apparaître soit en minuscules, soit en majuscules, mais la casse de la chaîne affichée dépend de la chaîne définie par vos paramètres système. Le format par défaut est AM/PM. |
|
|
II-c. Fonction Format( ) et les chaînes de caractères
Vous pouvez utiliser l'ensemble des caractères suivants pour créer une expression de mise en forme pour chaînes :
| Caractère |
Description |
| @ |
Espace réservé à un caractère. Affichage d'un caractère ou d'un espace. Si la chaîne contient un caractère à la position occupée par le symbole @ dans l'expression de mise en forme, ce caractère est affiché ; sinon, un espace est affiché à cet emplacement. Les espaces réservés sont remplis de droite à gauche, à moins que la chaîne de mise en forme ne contienne un point d'exclamation (!). |
| & |
Espace réservé à un caractère. Affichage d'un caractère ou de rien d'autre. Si la chaîne contient un caractère à la position occupée par le signe & ce caractère est affiché ; sinon, rien n'est affiché. Les espaces réservés sont remplis de droite à gauche, à moins que la chaîne ne contienne un point d'exclamation (!). |
| < |
Mise en minuscules. Tous les caractères sont affichés en minuscules. |
| > |
Mise en majuscules. Tous les caractères sont affichés en majuscules. |
| ! |
Remplissage des espaces réservés de gauche à droite. Par défaut, le remplissage s'effectue de droite à gauche. |
III. Fonctions dérivées de la fonction Format( )
IV. Alternatives à la fonction Format( )
IV-a. Alternatives pour les dates et heures
IV-a-i. Year( ), Month( ), Day( ), Hour( ), Minute( ) et Second( )
Les fonctions spécifiques aux dates sont plus rapides lorsqu'il s'agit de ne prendre en compte qu'une partie de la valeur. Le temps de traitement est, dans le cas d'un seul élément à calculer, divisé par deux. Dès l'instant qu'on souhaite combiner plus d'un élément dans l'affichage (exemple " MM/YYYY ") la fonction Format( ) est bien plus efficace que la combinaison des fonctions dédiées (ici Month( ) et Year( ))
IV-a-ii. Datepart( )
Toutefois, la fonction DatePart( ), elle aussi spécifique aux variables de type DateTime, n'a pas les mêmes performances que la fonction Format( ).
IV-b. Alternatives pour les chaînes de caractères
IV-b-i. UCase et LCase
Alternatives respectivement à Format(chaine, ">") et Format(chaine, "<"), elles divisent par 2 le temps de réponse.
V. Conclusion
Dans sa très grande flexibilité, la fonction Format( ) permet aux utilisateurs de mettre en forme à peu près n'importe quel type de donnée, dans un format qui lui sera propre, et ceux en garantissant le meilleur temps de traitement possible. Néanmoins, on constate par l'expérience que la fonction n'est pas toujours la solution la plus adéquate et optimale lors d'affichages simples.
VI. Remerciements
Je tiens à remercier l'équipe de Developpez.com pour la qualité du site,
AAA,
BBB et
CCC pour la relecture de cet article, et de tous ceux qui contribuent à l'entraide autour du développement dans le cadre personnel et professionnel.
Copyright © 2010 Jean-Philippe ANDRÉ.
Aucune reproduction, même partielle, ne peut être faite
de ce site ni de l'ensemble de son contenu : textes, documents, images, etc.
sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à
trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.
