Programmer dans le tableur (VBA) : les boucles et les programmes de gestion complets

Exemples corrigés

Cas 1 : For ... Next : total, compteur, maximum et moyenne

La feuille Ventes contient 12 montants en D2:D13 (ceux du cours 26).

Option Explicit

Sub AnalyserVentes()
    ' Parcourt les ventes de la ligne 2 à la ligne 13 de la feuille Ventes
    Dim ws As Worksheet
    Dim i As Long               ' compteur de la boucle : numéro de ligne
    Dim total As Double         ' cumul des montants
    Dim nbVentes As Long        ' nombre de ventes lues
    Dim nbGrosses As Long       ' nombre de ventes d'au moins 5000
    Dim maxi As Double          ' plus grande vente

    Set ws = Worksheets("Ventes")
    total = 0
    nbVentes = 0
    nbGrosses = 0
    maxi = ws.Range("D2").Value         ' le maximum démarre avec la première valeur

    For i = 2 To 13
        total = total + ws.Cells(i, 4).Value        ' cumul
        nbVentes = nbVentes + 1                     ' compteur
        If ws.Cells(i, 4).Value >= 5000 Then
            nbGrosses = nbGrosses + 1               ' compteur conditionnel
        End If
        If ws.Cells(i, 4).Value > maxi Then
            maxi = ws.Cells(i, 4).Value             ' mise à jour du maximum
        End If
    Next i

    ws.Range("F2").Value = total
    If nbVentes > 0 Then
        MsgBox "Total : " & total & " ; moyenne : " & total / nbVentes & _
               " ; ventes d'au moins 5000 : " & nbGrosses & " ; plus grande vente : " & maxi
    End If
End Sub

Tableau de suivi (valeurs après chaque passage) :

Sorties : F2 contient 40 650 ; le message affiche « Total : 40650 ; moyenne : 3387,5 ; ventes d'au moins 5000 : 3 ; plus grande vente : 7300 » (40 650 / 12 = 3 387,5, avec la virgule décimale de la configuration française). Après la boucle, i vaut 14.

Les trois ventes d'au moins 5 000 sont celles des lignes 4, 8 et 11, comme le calcul de NB.SI du cours 26.

À retenir

Conclusion à destination du responsable du contrôle de gestion : le programme retrouve les valeurs obtenues par formule (total de 40 650, trois ventes de plus de 5 000). Il est cependant limité aux lignes 2 à 13 : si une 13e vente est ajoutée, elle n'est pas lue. Pour une liste qui s'allonge, on remplace la borne 13 par la dernière ligne remplie (4.3) ou on utilise la boucle jusqu'à la première cellule vide (cas 2).

Cas 2 : Do While jusqu'à la première cellule vide : relances de factures

La feuille Factures contient (ligne 1 : en-têtes ; en G1 la date de référence du 01/10/2026) :

Règle : retard = date de référence moins échéance ; pas de relance si le retard est nul ou négatif ; Relance 1 jusqu'à 30 jours de retard inclus, Relance 2 de 31 à 60 jours inclus, Mise en demeure au-delà. Le programme écrit le retard en colonne D et le niveau en colonne E, puis affiche un récapitulatif.

Option Explicit

Sub RelancerClients()
    ' Parcourt la liste des factures jusqu'à la première cellule vide de la colonne A,
    ' calcule le retard et le niveau de relance de chaque facture
    Dim ws As Worksheet
    Dim ligne As Long               ' numéro de la ligne en cours
    Dim dateRef As Date             ' date d'arrêté lue dans G1
    Dim retard As Long              ' retard en jours
    Dim nbRelances As Long          ' nombre de factures à relancer
    Dim totalRelance As Double      ' montant TTC à relancer
    Dim retardMax As Long           ' plus grand retard rencontré
    Dim clientMax As String         ' client concerné par ce retard

    Set ws = Worksheets("Factures")
    dateRef = ws.Range("G1").Value
    ligne = 2                       ' la ligne 1 contient les en-têtes
    nbRelances = 0
    totalRelance = 0
    retardMax = 0
    clientMax = ""

    Do While ws.Cells(ligne, 1).Value <> ""
        retard = dateRef - ws.Cells(ligne, 2).Value        ' retard en jours
        If retard <= 0 Then
            ws.Cells(ligne, 4).Value = 0
            ws.Cells(ligne, 5).Value = "Non échue"
        Else
            ws.Cells(ligne, 4).Value = retard
            If retard <= 30 Then
                ws.Cells(ligne, 5).Value = "Relance 1"
            ElseIf retard <= 60 Then
                ws.Cells(ligne, 5).Value = "Relance 2"
            Else
                ws.Cells(ligne, 5).Value = "Mise en demeure"
            End If
            nbRelances = nbRelances + 1                                    ' compteur
            totalRelance = totalRelance + ws.Cells(ligne, 3).Value         ' cumul
            If retard > retardMax Then                                     ' maximum
                retardMax = retard
                clientMax = ws.Cells(ligne, 1).Value
            End If
        End If
        ligne = ligne + 1                                                  ' passage à la ligne suivante
    Loop

    MsgBox nbRelances & " facture(s) à relancer pour " & totalRelance & _
           " euros. Plus grand retard : " & retardMax & " jours (" & clientMax & ")."
End Sub

Tableau de suivi :

Détail des retards au 01/10/2026 : du 15/08 au 01/10, 16 + 30 + 1 = 47 jours ; du 05/07, 26 + 31 + 30 + 1 = 88 jours ; du 28/08, 3 + 30 + 1 = 34 jours ; du 01/09, 30 jours (borne : retard <= 30, donc Relance 1) ; la facture de Garnier SAS n'est échue que le 10/10/2026 (retard de -9 jours).

Sortie : « 5 facture(s) à relancer pour 9400,5 euros. Plus grand retard : 88 jours (Leroy et Fils). » Le montant s'affiche avec la virgule décimale de la configuration française.

Si la ligne ligne = ligne + 1 est oubliée, la condition du Do While reste vraie : le programme traite indéfiniment la ligne 2 (boucle infinie).

À retenir

Conclusion à destination du responsable du recouvrement : cinq factures sur six sont échues, pour 9 400,50 € TTC. La facture de Leroy et Fils (3 600 € à 88 jours) justifie une mise en demeure ; celles de Dupont SA et de Petit SA relèvent d'une deuxième relance. La facture de Moreau SA, à 30 jours exactement, reste en première relance : la borne a été fixée par l'énoncé (« jusqu'à 30 jours inclus »). Le programme est reproductible tant que la date de référence est lue dans G1, et il s'adapte à toute longueur de liste.

Cas 3 : For Each : commissions et meilleur vendeur

La feuille active contient en colonne A les noms, en colonne B le chiffre d'affaires des vendeurs (lignes 2 à 6) :

Barème : 2 % en dessous de 8 000 ; 3 % à partir de 8 000 jusqu'à 14 000 exclus ; 4 % à partir de 14 000. La commission est arrondie à deux décimales et écrite en colonne C.

Option Explicit

Sub CalculerCommissions()
    ' Calcule la commission de chaque vendeur et repère le meilleur chiffre d'affaires
    Const SEUIL_1 As Double = 8000     ' début de la tranche à 3 %
    Const SEUIL_2 As Double = 14000    ' début de la tranche à 4 %

    Dim cel As Range                   ' cellule en cours dans la plage parcourue
    Dim taux As Double
    Dim commission As Double
    Dim totalCommissions As Double
    Dim caMax As Double
    Dim meilleur As String

    totalCommissions = 0
    caMax = 0
    meilleur = ""

    For Each cel In Range("B2:B6")                 ' chaque cellule de la colonne B
        If cel.Value < SEUIL_1 Then
            taux = 0.02
        ElseIf cel.Value < SEUIL_2 Then
            taux = 0.03
        Else
            taux = 0.04
        End If
        commission = Round(cel.Value * taux, 2)
        cel.Offset(0, 1).Value = commission         ' écrit dans la colonne C, même ligne
        totalCommissions = totalCommissions + commission
        If cel.Value > caMax Then
            caMax = cel.Value
            meilleur = cel.Offset(0, -1).Value      ' nom lu dans la colonne A
        End If
    Next cel

    MsgBox "Total des commissions : " & totalCommissions & " euros. Meilleur chiffre d'affaires : " & meilleur
End Sub

Tableau de suivi :

Sortie : « Total des commissions : 1785 euros. Meilleur chiffre d'affaires : Durand ». Le chiffre d'affaires de 8 000 (borne) tombe dans la tranche à 3 %, parce que le test est < SEUIL_1.

Point d'attention : la fonction Round du langage arrondit « au pair » lorsque la décimale est exactement 5 (cours 32) ; les montants de ce cas ne tombent pas sur un demi-centime, donc le résultat est le même qu'avec ARRONDI du tableur.

À retenir

Conclusion à destination de la direction commerciale : la commission totale de 1 785 € est identique à celle d'un calcul manuel (670 + 382,50 + 334,50 + 158 + 240). Le vendeur à 8 000 € gagne 240 € alors qu'un euro de moins lui aurait rapporté environ 160 €, soit 80 € d'écart pour un euro de chiffre d'affaires : cet effet de seuil est une conséquence du barème par paliers, à rappeler lors de la présentation du dispositif. Les seuils sont déclarés en tête de procédure : si le barème change, on les modifie à cet endroit.

Cas 4 : Do While pilotée par un indicateur : cumul de saisies

On veut cumuler des montants saisis un à un, jusqu'à ce que l'utilisateur tape 0 ou annule ; les saisies non numériques sont ignorées avec un message.

Option Explicit

Sub CumulerSaisies()
    ' Cumule des montants saisis jusqu'à la saisie de 0 ou l'annulation
    Dim saisie As String        ' texte saisi
    Dim total As Double         ' cumul des montants
    Dim nb As Long              ' nombre de montants retenus
    Dim fin As Boolean          ' indicateur d'arrêt de la boucle

    total = 0
    nb = 0
    fin = False

    Do While Not fin
        saisie = InputBox("Montant à ajouter (0 pour terminer) ?")
        If saisie = "" Then
            fin = True                                       ' annulation ou saisie vide
        ElseIf Not IsNumeric(saisie) Then
            MsgBox "Saisie non numérique ignorée."
        ElseIf CDbl(saisie) = 0 Then
            fin = True                                       ' 0 : fin de la saisie
        Else
            total = total + CDbl(saisie)                     ' cumul
            nb = nb + 1                                      ' compteur
        End If
    Loop

    MsgBox nb & " montant(s) saisi(s), total : " & total
End Sub

Suivi sur la série de saisies 120, 45,5, abc, 300, 0 (configuration française) :

Sortie : « 3 montant(s) saisi(s), total : 465,5 ».

Le test saisie = "" est placé en premier : CDbl("") provoquerait une erreur. L'ordre des tests (vide, puis numérique, puis valeur zéro) suit les règles du cours 33.

Variante avec test en fin de boucle : Do ... Loop Until fin exécute la saisie au moins une fois avant de tester fin.

À retenir

Conclusion à destination du comptable : la boucle s'arrête sur un événement choisi par l'utilisateur et non sur un nombre de passages connu : c'est le cas où Do s'impose et où For ne convient pas. L'indicateur fin rend la condition d'arrêt lisible ; sans l'affectation fin = True dans les deux cas de sortie, la boucle serait infinie.

Vocabulaire essentiel

Points clés à retenir

1. For ... Next quand le nombre de passages est connu ; Do While ou Do Until quand il dépend d'une condition ; For Each pour parcourir une plage.
2. La valeur finale d'un For est incluse ; avec un départ supérieur à la fin, la boucle ne s'exécute pas.
3. Pour une liste de longueur variable, on boucle jusqu'à la première cellule vide.
4. Il faut toujours faire avancer la variable testée dans une boucle Do : sinon, boucle infinie.
5. Initialiser avant la boucle : total = 0, nb = 0, maximum à la première valeur.
6. Une moyenne divise le cumul par le compteur, après avoir vérifié que le compteur n'est pas nul.
7. Exit For et Exit Do quittent la boucle (utile pour une recherche).
8. Pour interpréter un programme : tableau de suivi, une ligne par passage.

Pièges fréquents

1. Oublier ligne = ligne + 1 dans une boucle Do While : le programme ne s'arrête jamais.
2. Oublier l'initialisation du cumul ou du compteur : sans effet pour une variable déclarée par Dim dans la procédure (elle part de 0), mais faux si le cumul doit repartir de zéro plusieurs fois ou si la variable est déclarée en tête de module (elle garde sa valeur d'une exécution à l'autre).
3. Initialiser un maximum à 0 : faux dès que toutes les valeurs sont négatives.
4. Diviser par un compteur nul lors du calcul d'une moyenne.
5. Une liste avec une cellule vide au milieu : la boucle jusqu'à la première cellule vide s'arrête trop tôt.
6. Modifier le compteur d'un For à l'intérieur de la boucle.
7. Une borne 13 écrite en dur dans For i = 2 To 13 : les lignes ajoutées sont ignorées.
8. Confondre Do While et Do Until : l'un répète tant que la condition est vraie, l'autre jusqu'à ce qu'elle le devienne.

Q&R pour le tuteur IA

Q : Quelle boucle choisir pour parcourir une liste dont on ne connaît pas la longueur ? R : Do While ws.Cells(ligne, 1).Value <> "" : on répète tant que la cellule de la première colonne n'est pas vide, en augmentant ligne à chaque passage. On peut aussi calculer la dernière ligne et utiliser For.

Q : Comment calculer la somme et le maximum d'une colonne dans une boucle ? R : On initialise total = 0 et maxi à la première valeur, puis, dans la boucle, total = total + valeur et If valeur > maxi Then maxi = valeur.

Q : Pourquoi un programme avec Do While peut-il ne jamais s'arrêter ? R : Parce que la variable testée n'est pas modifiée dans la boucle (par exemple on oublie ligne = ligne + 1) : la condition reste vraie à chaque passage. Il faut toujours vérifier que la condition d'arrêt peut être atteinte.

Q : Que vaut le compteur i après For i = 2 To 13 ... Next i ? R : 14, c'est-à-dire la valeur finale plus un pas. Pendant la boucle, il a pris les valeurs de 2 à 13.

Q : Comment éviter une erreur de division dans le calcul d'une moyenne ? R : En testant le compteur avant de diviser : If nb > 0 Then moyenne = total / nb.

Q : Comment dérouler un programme à la main dans une copie d'examen ? R : On dresse un tableau de suivi : une colonne par variable, une ligne par passage dans la boucle, et on recopie la valeur de chaque variable après chaque instruction qui la modifie. On conclut par les sorties (message et cellules écrites).