Programmer dans le tableur (VBA) : les structures alternatives (tests simples, imbriqués et composés)

Exemples corrigés

Cas 1 : une fonction de remise par tranches

Règle de gestion : pas de remise sous 1 000 € ; 3 % à partir de 1 000 € et jusqu'à 5 000 € exclus ; 5 % à partir de 5 000 €.

Option Explicit

Const SEUIL_1 As Double = 1000   ' début de la tranche à 3 %
Const SEUIL_2 As Double = 5000   ' début de la tranche à 5 %
Const TAUX_1 As Double = 0.03    ' taux de la tranche intermédiaire
Const TAUX_2 As Double = 0.05    ' taux de la tranche haute

Function TauxRemise(montant As Double) As Double
    ' Renvoie le taux de remise applicable à un montant de commande
    If montant < SEUIL_1 Then
        TauxRemise = 0               ' en dessous du premier seuil
    ElseIf montant < SEUIL_2 Then
        TauxRemise = TAUX_1          ' de SEUIL_1 inclus à SEUIL_2 exclu
    Else
        TauxRemise = TAUX_2          ' à partir de SEUIL_2
    End If
End Function

Utilisation dans une cellule : =TauxRemise(B5).

Trace d'exécution (le programme s'arrête au premier test vrai) :

La borne de 1 000 tombe dans la tranche à 3 %, celle de 5 000 dans la tranche à 5 % : le test utilise < et respecte « à partir de ». Si l'on avait écrit <=, le montant de 1 000 aurait été traité sans remise : erreur de borne.

Variante avec Select Case : celle de la section 4, qui donne les mêmes résultats.

À retenir

Conclusion à destination de la responsable des ventes : la fonction applique le barème sans ambiguïté sur les bornes, et les seuils et taux sont regroupés en tête du module : une évolution du barème ne demande de modifier que quatre lignes. Pour que les commerciaux puissent changer le barème sans ouvrir le code, il serait préférable de lire les seuils dans des cellules de paramètres et de les transmettre comme arguments de la fonction.

Cas 2 : statut d'un client avec des conditions composées

Règles, appliquées dans cet ordre :

1. retard de paiement supérieur à 60 jours : « Bloqué » ;
2. sinon, retard supérieur à 30 jours, ou retard (même court) pour un client de moins d'un an d'ancienneté : « À surveiller » ;
3. sinon, chiffre d'affaires d'au moins 20 000 et aucun retard : « Privilégié » ;
4. sinon : « Standard ».

Function StatutClient(ca As Double, retardJours As Long, anciennete As Long) As String
    ' Renvoie le statut d'un client selon son chiffre d'affaires, son retard et son ancienneté (en années)
    If retardJours > 60 Then
        StatutClient = "Bloqué"
    ElseIf retardJours > 30 Or (retardJours > 0 And anciennete < 1) Then
        StatutClient = "À surveiller"
    ElseIf ca >= 20000 And retardJours = 0 Then
        StatutClient = "Privilégié"
    Else
        StatutClient = "Standard"
    End If
End Function

Résultats attendus (trace sur six cas) :

Détail du troisième cas : retardJours > 60 est faux ; dans le deuxième test, retardJours > 30 est faux et (retardJours > 0 And anciennete < 1) est (Vrai And Vrai), donc vrai : le statut est « À surveiller ».

Effet de l'ordre des tests. Si l'on avait placé le test « À surveiller » avant le test « Bloqué », le client de la cinquième ligne (retard de 70 jours) aurait reçu « À surveiller » au lieu de « Bloqué », car retardJours > 30 est vrai et les tests suivants ne sont jamais lus.

Piège de l'évaluation complète. Écrire ElseIf retardJours > 0 And ca / retardJours > 100 ferait échouer le programme pour un retard nul (division par zéro), car And évalue ses deux membres. On sépare alors en deux If imbriqués.

À retenir

Conclusion à destination du responsable du crédit clients : la règle de blocage doit rester en tête : c'est le test le plus restrictif et il ne doit jamais être masqué. Sur les six clients testés, un est bloqué, deux sont à surveiller, deux sont privilégiés et un est standard : ces statuts sont à recouper avec le service commercial avant toute décision de blocage.

Cas 3 : valider une saisie avant de l'utiliser

On veut enregistrer en B2 une quantité entière comprise entre 1 et 999.

Sub VerifierQuantite()
    ' Demande une quantité et ne l'enregistre que si elle est valide
    Dim saisie As String       ' texte saisi par l'utilisateur
    Dim quantite As Double     ' valeur numérique de la saisie

    saisie = InputBox("Quantité commandée (entier de 1 à 999) ?")
    If saisie = "" Then
        MsgBox "Saisie annulée ou vide."
    ElseIf Not IsNumeric(saisie) Then
        MsgBox "La saisie n'est pas un nombre."
    Else
        quantite = CDbl(saisie)
        If quantite < 1 Or quantite > 999 Or quantite <> Int(quantite) Then
            MsgBox "Quantité refusée : entier de 1 à 999 attendu."
        Else
            Range("B2").Value = quantite
            MsgBox "Quantité enregistrée : " & quantite
        End If
    End If
End Sub

Int(x) renvoie la partie entière de x (vers le bas) : Int(12.5) vaut 12, et quantite <> Int(quantite) est vrai pour un nombre décimal.

Trace sur huit saisies (configuration française : la virgule est le séparateur décimal) :

Les tests sont imbriqués : on vérifie d'abord que la saisie existe, puis qu'elle est numérique, puis seulement alors qu'elle respecte les bornes. L'ordre est nécessaire : CDbl("abc") provoquerait une erreur d'exécution.

À retenir

Conclusion à destination du responsable des commandes : le programme refuse 0, les décimaux, les valeurs hors bornes et les textes, et accepte les deux bornes. Les règles sont les mêmes que celles d'une validation de données du tableur (cours 29) ; la macro y ajoute des messages adaptés et pourra servir de base à un contrôle plus riche.

Cas 4 : compléter un programme

Énoncé. Une prime d'ancienneté est versée : 0 € en dessous de 2 ans ; 150 € de 2 ans à moins de 5 ans ; 300 € à partir de 5 ans. Une prime de performance de 100 € s'y ajoute pour tout salarié ayant au moins 2 ans d'ancienneté et un chiffre d'affaires d'au moins 15 000 €. Compléter les lignes marquées ____.

Function PrimeTotale(anciennete As Long, ca As Double) As Double
    ' Renvoie la prime d'ancienneté et de performance
    Dim prime As Double
    prime = 0
    If anciennete >= 5 Then
        prime = ____
    ElseIf anciennete ____ Then
        prime = 150
    End If
    If ____ And ca >= 15000 Then
        prime = prime + 100
    End If
    PrimeTotale = prime
End Function

Corrigé :

Function PrimeTotale(anciennete As Long, ca As Double) As Double
    ' Renvoie la prime d'ancienneté et de performance
    Dim prime As Double
    prime = 0                                  ' initialisation : aucune prime
    If anciennete >= 5 Then
        prime = 300                            ' 5 ans et plus
    ElseIf anciennete >= 2 Then
        prime = 150                            ' de 2 ans à moins de 5 ans
    End If
    If anciennete >= 2 And ca >= 15000 Then
        prime = prime + 100                    ' prime de performance : cumul avec la précédente
    End If
    PrimeTotale = prime
End Function

Justification : le premier test regarde la tranche la plus haute, sans quoi anciennete >= 2 capterait aussi les salariés de plus de 5 ans ; le deuxième bloc If est indépendant du premier (un If distinct et non un ElseIf), car la prime de performance se cumule avec la prime d'ancienneté.

Trace sur six cas :

À retenir

Conclusion à destination de la direction des ressources humaines : la fonction respecte les bornes fixées (2 ans, 5 ans, 15 000 €). Le salarié de 1 an qui réalise 20 000 € de chiffre d'affaires ne touche rien : c'est la conséquence de la règle, à confirmer si elle n'est pas voulue. Si le ElseIf avait été remplacé par un second If indépendant, le salarié de 6 ans verrait sa prime d'ancienneté ramenée de 300 € à 150 €, la seconde affectation écrasant la première : erreur typique de l'énoncé à compléter.

Vocabulaire essentiel

Points clés à retenir

1. Un bloc If se termine par End If ; ElseIf et Else forment la cascade.
2. Dans une cascade, le premier test vrai l'emporte : on place le plus restrictif en premier.
3. Une borne se traite par < ou <= selon la règle « à partir de » ou « jusqu'à ».
4. And, Or, Not combinent les conditions ; priorité : Not, And, Or, et on utilise des parenthèses dans le doute.
5. And et Or évaluent toujours les deux conditions : on protège une division par des If imbriqués.
6. On teste la nature d'une saisie (IsNumeric, texte vide) avant de la convertir ou de la calculer.
7. Un If indépendant (et non un ElseIf) permet de cumuler deux règles.
8. Un ElseIf ou un Else final garantit qu'aucun cas n'échappe au traitement.

Pièges fréquents

1. Mauvais ordre des tests : le cas le plus restrictif est masqué par un test plus large.
2. < au lieu de <= (ou l'inverse) : la valeur exacte du seuil change de tranche.
3. ElseIf à la place d'un If indépendant : la seconde règle n'est plus cumulée.
4. Oublier End If ou Then : erreur de syntaxe.
5. Croire que And s'arrête à la première condition fausse : la division par zéro se produit quand même.
6. Comparer des textes sans tenir compte de la casse : "Sud" et "sud" diffèrent.
7. Convertir une saisie avant de la tester : CDbl("abc") provoque une erreur.
8. Écrire = dans un test en pensant affectation (ou l'inverse).

Q&R pour le tuteur IA

Q : Comment écrire en code une remise de 3 % à partir de 1 000 € et de 5 % à partir de 5 000 € ? R : If montant < 1000 Then taux = 0 ElseIf montant < 5000 Then taux = 0.03 Else taux = 0.05 End If, écrit en bloc. La borne de 1 000 tombe dans la tranche à 3 % et celle de 5 000 dans la tranche à 5 %, grâce à <.

Q : Quelle est la différence entre ElseIf et un second If ? R : Avec ElseIf, le second test n'est évalué que si le premier est faux : les cas s'excluent. Avec un If indépendant, il est toujours évalué : on peut cumuler deux règles, comme une prime d'ancienneté et une prime de performance.

Q : Pourquoi If retard > 0 And ca / retard > 100 Then peut-il échouer ? R : Parce que And évalue ses deux membres même si le premier est faux. Si retard vaut 0, la division par zéro produit une erreur d'exécution. Il faut imbriquer deux If.

Q : Comment vérifier qu'une saisie est un entier entre 1 et 999 ? R : On teste d'abord que la saisie n'est pas vide, puis IsNumeric, puis on convertit avec CDbl et on refuse si quantite < 1 Or quantite > 999 Or quantite <> Int(quantite).

Q : Comment traduire =SI(ET(B5>=8000;C5>=5000);100;0) en code ? R : If b5 >= 8000 And c5 >= 5000 Then prime = 100 Else prime = 0. ET devient And, OU devient Or, NON devient Not.

Q : Select Case est-il au programme ? R : Il n'est pas cité par le programme. On peut le rencontrer comme variante d'une cascade If ... ElseIf, mais on ne l'exige jamais ; savoir écrire If, ElseIf et Else suffit.