Accueil | Ce site | CV | Excel | Livre d’or | Macros XL4 | Modèles | VBA

 Mise à jour du plafond de cotisations

Le plafond est utilisé pour l’établissement des payes. Fixé annuellement, sa valeur mensuelle est de 2 352 € pour 2002. Nous allons voir comment le mettre à jour avec VBA dans un classeur où il est associé à un nom : .

3 méthodes viennent à l’esprit : l'emploi de la fonction InputBox, de la méthode Application.InputBox ou l’utilisation d’un UserForm (une zone de dialogue).

Fonction InputBox

InputBox est une fonction qui affiche une boite de dialogue permettant à l’utilisateur d’entrer des données et renvoie celles-ci. Son intérêt par rapport à un UserForm est sa simplicité de mise en œuvre.

Pour récupérer la nouvelle valeur du plafond, on peut écrire le code suivant :

Private Sub MAJPlafond()
Dim Plafond
    Plafond= InputBox("Nouveau plafond")
End Sub

qui affiche la fenêtre ci-dessous :

Fig. 1 - Fenêtre obtenue avec la fonction InputBox

Cette approche présente une grave faiblesse :

Dim Plafond

Dim Plafond as Integer

Malheureusement, la solution est pire que le mal : préciser le type a pour conséquence essentielle de faire apparaître un message d’erreur (figure 2) si l’information fournie n’est pas de type numérique, y compris en cas de valisation sans avoir rien entré ou si l’utilisateur, par excès de bonne volonté, entre une valeur suivie du caractère €.

Fig. 2 - Message affiché si l’information entrée dan le dialogue de la figure 1 n’est pas numérique

Il existe des moyens de gérer les erreurs prévisibles de l’utilisateur, afin de prévenir l’affichage de messages comme celui de la figure 2 en cas de problème, mais il faut écrire des lignes de codes supplémentaires. On perd alors l’avantage que la fonction InputBox est censé apporter par rapport à un UserForm, la simplicité de mise en œuvre.

Méthode Application.InputBox

L’objet Application possède une méthode InputBox, qui est très proche de la fonction éponyme. Les différences évidentes sont l’aspect de la fenêtre affichée (cf figures 1 et 3) et le fait que le titre par défaut est “Entrée” au lieu du nom de l’application. Ces 2 éléments sont accessoires. L’essentiel est que la méthode InputBox possède un argument facultatif supplémentaire, permettant de préciser le type de l’information attendue.

Ce qui permet la variante ci-dessous de la procédure :

Private Sub MAJPlafond()
Dim Plafond
    Plafond = Application.InputBox("Nouveau plafond", , , , , , , 1)
End Sub

Les 7 virgules consécutives dans le code découlent de l’existence de 6 arguments facultatifs inutilisés ici (titre de la fenêtre, valeur par défaut, position des fenêtres horizontale et verticale, fichier d’aide et référence de l’aide dans ce fichier).

Fig. 3 - La fenêtre affichée par la méthode InputBox diffère
légèrement de celle qu’affiche la fonction éponyme (figure 1)

Le contrôle du type empêche de valider une information non numérique. Si par exemple l’utilisateur entre “2 4OO” (emploi du "o" majuscule au lieu du zéro, faute de frappe classique sur les portables sans pavé numérique), la validation provoque le message ci-dessous, parfaitement explicite.

Fig. 4 - Message affiché si l’on tente de valider une information non numérique

Si l'utilisateur entre “2400 €”, il risque de voir apparaître également le message d’erreur de la figure 4. Cela dépend de la version d’Excel dont il dispose, et du paramétrage de celle-ci.

Le symbole "€“ utilisé ici, est accepté si l’Euro est le symbole monétaire en vigueur. Avec les paramètres par défaut, la saisie de “2400 €” est acceptée par Excel XP et X, mais provoque le message d’erreur de la figure 4 avec une version plus ancienne.

La méthode Application.InputBox semble donc résoudre les problèmes évoqués lors de l’examen de la fonction InputBox. Malheureusement, il reste un cas qui n’est pas satisfaisant : si l’utilisateur valide sans avoir entré aucune information, un message est affiché, qui contrairement au précédent, n’est absolument pas limpide.

Au contraire, le début du message aurait plutôt tendance à induire en erreur l’utilisateur : “La formule que vous avez tapée contient une erreur.” Or, ce message est affiché exclusivement quand l’utilisateur n’a rien tapé !

Fig. 5 - Message affiché, si l’on tente de valider sans avoir saisi d’information

Le bouton “Aide” n’apporte rien de plus dans ce contexte. Le bouton “OK” fait disparaître le message et la fenêtre destinée à la saisie de la nouvelle valeur du plafond (figure 3) repasse au premier plan. L’immense majorité des utilisateurs va comprendre, immédiatement ou non, que le message d’erreur est lié à la tentative de valider sans avoir rien saisi. La solution consiste à entrer la valeur réclamée ou à choisir le bouton “Annuler”

Valider un dialogue sans avoir entré l’information demandée, peut paraître absurde. Pourtant, voici au moins 2 circonstances où cela doit être envisagé : l’utilisateur a lancé la commande affichant le dialogue, et se rend compte qu’il ne connaît pas la nouvelle valeur du plafond, ou que celle-ci est à jour.

Globalement, pour ce que nous voulons faire ici, saisir une valeur numérique, la méthode Application.InputBox est clairement mieux adaptée que la fonction InputBox. Toutefois, le risque que l’utilisateur se retrouve devant le message trop peu explicite de la figure 5, m’incite à en déconseiller également l’usage.

Pour être parfaitement honnête, je noircis ici un peu le tableau, dans la mesure où il est possible de bloquer l’affichage des messages d’Excel, en utilisant la popriété DisplayAlerts de l’objet Application. Voici le code modifié :

Sub MAJPlafond()
Dim Plafond
    Application.DisplayAlerts = False
    Plafond = Application.InputBox("Nouveau plafond", , , , , , , 1)
    Application.DisplayAlerts = True
End Sub

L’inconvénient de cette technique est son manque de finesse : on ne bloque aucun message (.DisplayAlerts = True), ou on les bloque tous (.DisplayAlerts = False). Résultat, pour échapper au message de la figure 5, insuffisamment clair, il faut renoncer également à celui de la figure 4, qui lui est utile.

UserForm

Plus lourd à mettre en œuvre que les variantes d’InputBox, un UserForm présente toutefois de nombreux avantages. La saisie de données se fait en utilisant on objet Control, en l’occurrence un TextBox. La saisie peut être contrôlée au fur et à mesure que l’utilisateur entre l’information requise : chaque caractère saisi au clavier déclenche l’événement Change, auquel une procédure peut être associée. Cela permet de s’assurer aisément que le plafond indiqué est numérique.

Fig. 6 - Aspect d’un UserForm permettant l’entrée d’une valeur

Le UserForm de la figure 6 a été nommé “DPlafond”. Il contient 4 objets appartenant tous à la collection Controls :

• 1 Label, le texte en bleu indiquant la nature de l’information à entrer.
• 1 TextBox nommé “TPlafond”, la case destinée à recevoir la valeur du plafond.
• 2 CommandButton, les boutons “OK” et “Annuler”, respectivement nommés “BOK” et “BAnnuler”.

Voici le code associé à l’événement Change de TPlafond (le TextBox destiné à la saisie du nouveau montant) :

Private Sub TPlafond_Change()
        If TPlafond <> "" Then
            ' Contrôle que le plafond indiqué est bien un nombre
                If Not IsNumeric(TPlafond) Then
                    MsgBox "Le plafond doit être un nombre !", 48, "Mise à jour du plafond"
                    ' Suppression du dernier caractère entré
                    TPlafond = Left(TPlafond, Len(TPlafond) - 1)
                ElseIf Not (Int(TPlafond) - TPlafond = 0) Then
                    MsgBox "Le plafond doit être un nombre entier !", 48, "Mise à jour du plafond"
                    ' Suppression des 2 derniers caractères entrés, car Int(TPlafond) - TPlafond = 0
                    ' n'est vrai qu’après la saisie du séparateur décimal ET d’un chiffre
                    TPlafond = Left(TPlafond, Len(TPlafond) - 2)
                End If
        End If
End Sub

Voici le dialogue (figure 7) affiché par le code VBA ci-dessus, en cas de saisie d’un caractère non numérique :

Fig. 7 - Le dialogue affiché en cas de saisie d’un caractère non numérique.
C'est l’équivalent de celui de la figure 4, avec un InputBox.

Le dialogue de la figure 8 apparaît en cas de tentative de saisie d’une décimale.

Fig. 8 - Dialogue apparaissant en cas de saisie d’une décimale.

Il est également possible d’associer la fonction Controle à l’événement Click du bouton OK. Cela permet de vérifier qu’une valeur a bien été saisie, et également que le plafond comporte exactement 4 Chiffres.

En effet, il était de 2 352 € en 2002 et est passé à 2 432 € en 2003, ce qui correspond à une revalorisation de 3,4 %. À ce rythme, les 10 000 € (5 chiffres) ne seront dépassés qu’en 2046. Avec une revalorisation de 10 % par an, ce qui impliquerait un retour prolongé d’une inflation forte, le seuil de 10 000 € serait atteint en 2018.

Compte tenu de ces délais, un contrôle basé sur un plafond à 4 chiffres se justifie pleinement. Ce contrôle pourrait être associé à l’événement BeforeUpdate du TextBox TPlafond :

Private Sub TPlafond_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    ' Vérification que le plafond comporte bien 4 chiffres
        If TPlafond <> "" And Len(TPlafond) <> 4 Then
            MsgBox "Le plafond doit comporter 4 chiffres !", 48, "Mise à jour du plafond"
            Cancel = True
        End If
End Sub

Cependant cette approche présente l’inconvénient majeur d’empêcher l'usage du bouton “Annuler” après avoir saisi 1, 2 ou 3 caractères ou plus de 4). En effet l’instruction

Cancel = True

empêche de sortir du TextBox sauf si celui-ci est vide ou contient exactement 4 caractères, empêchant ainsi l’accès au bouton “Annuler”. C'est pourquoi il vaut mieux associer cette vérification au clic sur le bouton “OK” :

Private Sub BOK_Click()
    ControleInfos
        If Resultat Then
            ReportDonnees
            Unload Me
        End If
End Sub

Private Sub ControleInfos()
    Resultat = Controle(TPlafond = "", "la valeur du plafond.", TPlafond)
    If Resultat Then Resultat = Controle(Len(TPlafond) <> 4, , TPlafond, "En 2002, le plafond URSSAF était de 2 352 Euro." _
    & vbNewLine & vbNewLine & "Il devrait comporter exactement 4 chiffres.")
End Sub

Private Sub ReportDonnees()
    ' Traitement effectuant la mise à jour du plafond dans un ou plusieurs documents Excel
End Sub

Controle(), utilisée 2 fois dans la procédure “ControleInfos”, est une fonction que j’ai développée, pour contrôler les saisies dans un UserForm. Très puissante, elle fait partie du module MOutils. Plusieurs pages de ce site sont consacrées à la fonction Controle.

Voici les dialogues affichés par la fonction Controle, si la valeur du plafond n'a pas été renseignée, ou si le nombre entré ne comporte pas exactement 4 chiffres :

L’ajout d’un second TextBox, non modifiable, affichant le plafond en cours permettrait d’améliorer le dialogue (figure 11). Les cadres rouge et bleu servant de légende pour les couleurs sont également des TextBox.

3 TextBox ajoutés par rapport au UserForm de la figure 6. Pour éviter que l’utilisateur passe par ces TextBox avec la touche tabulation et/ou tente d’en modifier le contenu, il faut donner ces valeurs aux propriétés suivantes :

• Locked : True
• TabStop : False
• Enabled : False

Le fait de mettre à False la propriété “Enabled” a pour effet de griser  la valeur actuelle du plafond. Si on laisse “Enabled” à True (valeur par défaut), cette valeur, le 2352 de la figure 11 reste noire . Elle est ainsi plus lisible, mais l'utilisateur peut sélectionner le TextBox correspondant. Ce n’est pas très grave, car la propriété Locked, qui a pour valeur True, empêche toute modification.

Conclusion

La différence fondamentale entre les 2 variantes d’InputBox et un UserForm est que seul ce dernier est un objet (Application.InputBox est une méthode, tandis que InputBox est une fonction).

À un objet peuvent être associés des propriétés, des méthodes et des événements. Ce sont ces derniers qui offrent au développeur un contrôle beaucoup plus fin, permettant de mieux réagir aux éventuelles erreurs commises par l’utilisateur.

Personnellement, je déconseille toute utilisation de Inputbox, qu’il s’agisse de la fonction ou de la méthode : le gain de temps théorique (non-nécessité de créer et paramétrer un UserForm) ne compense pas les faiblesses inhérentes à InputBox, qui nécessitent divers contrôles pour prévenir un éventuel message d’erreur faisant perdre plus que le bénéfice espéré.

Je conseille vivement de créer un UserForm générique, comme celui de la figure 6, et de le conserver dans un classeur. Quand on en a besoin, on le récupère dans le classeur concerné, et il suffit d'adapter le texte à afficher, et la largeur du UserForm pour avoir quelque chose de directement prêt à l’emploi.