VBA pour Excel - Les boucles

Les instructions de boucle
Comment présenter les instructions de boucle
Exemple 1: boucle de collection
Exemple 2: boucle avec compteur et accumulateur
Exemple 3: boucle de lecture

Les instructions de boucle

Les instructions d'un programme sont normalement exécutées les unes à la suite des autres, séquentiellement.
Les instructions de boucle, de test et de branchement permettent de modifier cet ordre naturel.

On utilise les instruction de boucle pour que le programme exécute un bloc d'instructions plusieurs fois.

For Each objet In collection
[instructions]
[Exit For]
[instructions]
Next [objet]

Où:

objet Variable utilisée pour être répétée sur tous les éléments d'une collection ou d'un tableau. Dans le cas d'une collection, la variable element peut uniquement être une variable de type Variant, une variable objet générique ou toute variable objet spécifique. Dans le cas d'un tableau, il peut uniquement s'agir d'une variable de type Variant.
collection Nom d'une collection ou d'un tableau d'objets (à l'exception d'un tableau de type défini par l'utilisateur).
instructions Une ou plusieurs instructions exécutées pour chaque élément de la collection.

Le bloc d'instructions est exécuté autant de fois qu'il y a d'objets dans la collection. À chaque itération, l'objet suivant de la collection collection est assigné à la variable objet.

La boucle est terminée lorsque chaque objet a été traité, ou lorsque l'instruction Exit For est exécutée. Le programme se poursuit à l'instruction suivant le Next.

Accueil
Concepts de programmation
VBE l'éditeur VBA
Déclarations, types, références, expressions et assignations
Tests et branchements
Les boucles
Gestion d'erreur
Collections et tableaux
Dialogues et formulaires
Objets et événements Excel
Conseils de programmation
Liste d'instructions

Word 2003
Word 2007
Excel
Excel
PowerPoint 2003
PowerPoint 2007
Access
Access 2010
Internet
Général
Intelligence financière
Vidéos

Précédent
Suivant
Page Principale
FAQ
Nouveautés
Plan du site
Références
Motivations
Manuels de formation
Fichiers de démonstration

Par courriel

Abonnez-vous à
l'infolettre LCI

Cette première version de l'instruction For ... Next est la plus utilisée pour manipuler les plages de cellules Excel. Elle permet de traiter simplement toutes les cellules d'une plage, sans se soucier de leur adresse absolue ou relative.
Elle manipule aussi plus difficilement les tableaux VBA.

Une plus ancienne version de l'instruction For ... Next est plus générale, mais requiert l'utilisation de compteurs:

For compteur = début To fin[Step valeur]
    [instructions]
    [Exit For]
    [instructions]
Next [compteur]

Où:

compteur Variable numérique utilisée comme un compteur.
début Valeur assignée au compteur lors de la première exécution des instructions.
fin Valeur qui détermine la fin de la boucle.
valeur Valeur ajoutée au compteur à chaque exécution de la boucle.
1 par défaut.
instructions Une ou plusieurs instructions entre For et Next.

En principe, les instructions entre For et Next sont exécutées (fin - début + 1) fois, en ajoutant valeur à compteur à chaque itération. Bien noter que les bornes fin et début sont inclues. L'instruction Exit For peut être utilisée pour une sortie exceptionnelle de la boucle.

À la fin de l'exécution de la boucle (après le Next), compteur vaut fin + valeur, et non fin.

DANGER: ne jamais assigner de valeur à compteur à l'intérieur des instructions.

Cette 2e version de For...Next est plus ancienne que la précédente, et est plus complexe à utiliser pour des collections d'objet.
Par contre, elle est plus adaptée à la manipulation de tableaux VBA.
Elle correspond au code VBA suivant:

compteur = début
Do Until compteur > fin
    [instructions]
    [Exit Do]
    [instructions]
    compteur = compteur + valeur
Loop

Ce qui nous amène aux instructions de répétition plus générales:

Do While condition
    [instructions]
    [Exit Do]
    [instructions]
Loop

Do Until condition
    [instructions]
    [Exit Do]
    [instructions]
Loop

Do
    [instructions]
    [Exit Do]
    [instructions]
Loop While condition

Do
    [instructions]
    [Exit Do]
    [instructions]
Loop Until condition

Où

condition Expression VBA. Si elle vaut Null, elle est considérée Faux.
instructions Une ou plusieurs instructions répétées tant que condition est Vrai (While), ou jusqu'à ce qu'elle le devienne (Until).

Ces quatre instructions implantent en VBA les structures de boucle structurées. Elles sont peu utilisées, sauf pour les boucles de lecture.

Présentation des instructions de boucle

En VBA, il est convenu de présenter en retrait les instructions à l'intérieur d'une boucle.

Exemple 1: boucle de collection

Supposons une plage de cellules Excel nommée ListeProvinces:

On peut écrire une fonction VBA qui retourne le nom associé au sigle:

Function fnNomProvince4(SigleProvince)
'Auteur: Michel Berthiaume
'Retourner le nom d'une province

Dim sSigleProvince As String
Dim rCellule As Range

    fnNomProvince4 = "sigle inconnu" 'Réponse par défaut

    'D'abord tester la validité du paramètre
    If TypeName(SigleProvince) = "Range" Then
        sSigleProvince = UCase (SigleProvince.Value)
    ElseIf TypeName(SigleProvince) = "String" Then
        sSigleProvince = UCase(SigleProvince)
    Else
        Exit Function
    End If

    'Rendu ici, sSigleProvince contient une version en majuscules du sigle
    'Pour chaque cellule de colonne 1 de la plage nommée ListeProvinces
    For Each rCellule In Range("ListeProvinces").Columns(1).Cells
        If sSigleProvince = UCase(rCellule.Value) Then 'Valeur trouvée
            fnNomProvince4 = rCellule.Offset(0, 1).Value 'Valeur de la cellule à droite
            Exit For 'inutile de continuer: on sort de la boucle.
        End If
    Next
End Function

La même, en utilisant un compteur de lignes:

Function fnNomProvince5(SigleProvince)
'Auteur: Michel Berthiaume
'Retourner le nom d'une province

Dim sSigleProvince As String
Dim lLigne as Long

    fnNomProvince5 = "sigle inconnu" 'Réponse par défaut

    'D'abord tester la validité du paramètre
    If TypeName(SigleProvince) = "Range" Then
        sSigleProvince = UCase (SigleProvince.Value)
    ElseIf TypeName(SigleProvince) = "String" Then
        sSigleProvince = UCase(SigleProvince)
    Else
        Exit Function
    End If

    'Rendu ici, sSigleProvince contient une version en majuscules du sigle
    'On fait varier lLigne de 1 au nombre de lignes de la plage nommée ListeProvinces
    For lLigne = 1 to Range("ListeProvinces").Rows.Count
        If sSigleProvince = Range("ListeProvinces").Cells(lLigne, 1).Value Then
            fnNomProvince5 = Range("ListeProvinces").Cells(lLigne, 2).Value 'Valeur de la cellule à droite
            Exit For 'inutile de continuer: on sort de la boucle.
        End If
    Next
End Function

Exemple 2: boucle avec compteur et accumulateur

On veut une fonction fnMoyennePlus qui calcule la moyenne des valeurs positives d'une plage.

Function fnMoyennePlus(Plage)
'Auteur: Michel Berthiaume
'Calculer la moyenne des valeurs positives d'une plage

Dim cSomme as Currency
Dim cCompteur as Currency
Dim rCellule as Range

    'D'abord tester la validité du paramètre
    If TypeName(Plage) <> "Range" Then
        Exit Function
    End If

    For each rCellule in Plage
        If rCellule.Value > 0 Then
            cSomme = cSomme + rCellule.Value
            cCompteur = cCompteur +1
        End If
    Next
    If cCompteur > 0 Then    'Éviter les divisions par 0
        fnMOyennePlus = cSomme/cCompteur
    EndIf
End Function

Une variante de la même fonction:

Function fnMoyennePlus1(Plage)
'Auteur: Michel Berthiaume
'Calculer la moyenne des valeurs positives d'une plage

Dim lNoCellule As Long
Dim cSomme as Currency
Dim cCompteur as Currency
Dim rCellule as Range

    'D'abord tester la validité du paramètre
    If TypeName(Plage) <> "Range" Then
        Exit Function
    End If

    For lNoCellule = 1 to Plage.Cells.Count
        If Plage.Cells(lNoCellule).Value > 0 Then
            cSomme = cSomme + Plage.Cells(lNoCellule).Value
            cCompteur = cCompteur + 1
        End If
    Next
    If cCompteur > 0 Then    'Éviter les divisions par 0
        fnMoyennePlus1 = cSomme/cCompteur
    EndIf
End Function

Finalement, une variante plus classique de cette solution, utilisant un compteur de lignes et un compteur de colonnes:

Function fnMoyennePlus2(Plage)
'Auteur: Michel Berthiaume
'Calculer la moyenne des valeurs positives d'une plage

Dim lNoLigne As Long

Dim lNoColonne As Long
Dim cSomme as Currency
Dim cCompteur as Currency
Dim rCellule as Range

    'D'abord tester la validité du paramètre
    If TypeName(Plage) <> "Range" Then
        Exit Function
    End If

    For lNoLigne = 1 to Plage.Rows.Count              'Pour chaque ligne
        For lNoColonne = 1 to Plage.Columns.Count     'Pour chaque colonne
            If Plage.Cells(lNoLigne,lNoColonne).Value > 0 Then
                cSomme = cSomme + Plage.Cells(lNoLigne,lNoColonne).Value
                cCompteur = cCompteur + 1
            End If
        Next
    Next
    If cCompteur > 0 Then    'Éviter les divisions par 0
        fnMoyennePlus1 = cSomme/cCompteur
    EndIf
End Function

Exemple 3: boucles de lecture

Dans une boucle de lecture, le nombre d'itérations n'est pas connu au départ. L'utilisation de For...Next n'est donc pas approprié.

Ici, on veut saisir une série de nombres qui seront inscrits dans la feuille Excel.

Sub SaisirNombres()
'Auteur: Michel Berthiaume
'Saisir des nombres et les inscrire dans une feuille Excel

Dim sSaisie As String
Dim lNoLigne As Long

    'Dans la boucle de lecture "standard", la première valeur est lue AVANT d'entrer dans la boucle
    sSaisie = InputBox("Entrer une valeur", "Inscription de valeurs dans une feuille")

    Do Until sSaisie = ""             'Le bouton Annuler de InputBox renvoie la chaîne nulle
        If IsNumeric(sSaisie) Then
            lNoLigne = lNoLigne + 1   'Prochaine ligne de la feuille Excel
            Range("A1").Offset(lNoLigne, 0).Value = sSaisie
        End If
        sSaisie = InputBox("Entrer une valeur", "Inscription de valeurs dans une feuille")
    Loop
End Sub

Ce type de boucle est utilisé pour lire des données venant de requêtes SQL, de fichiers conventionnels, etc.
Notez qu'il y a lecture AVANT d'entrer dans la boucle, puis une autre immédiatement avant la fin, et que le test de fin de lecture est en début de boucle.
La première instruction de lecture est automatique (implicite) dans l'ouverture d'une requête SQL.

Une autre façon d'arriver au même résultat:

Sub SaisirNombres1()
'Auteur: Michel Berthiaume
'Saisir des nombres et les inscrire dans une feuille Excel

Dim sSaisie As String
Dim lNoLigne As Long

    Do
        Do
            sSaisie = InputBox("Entrer une valeur", "Inscription de valeurs dans une feuille")
        Loop Until IsNumeric(sSaisie) Or Len(sSaisie) = 0
        If Len(sSaisie) = 0 Then
            lNoLigne = lNoLigne + 1   'Prochaine ligne de la feuille Excel
            Range("A1").Offset(lNoLigne, 0).Value = sSaisie
        End If
    Loop Until sSaisie = ""             'Le bouton Annuler de InputBox renvoie la chaîne nulle
End Sub

Abonnez-vous à l'infolettre

Abonnez-vous à l'infolettre du site pour recevoir les dernières nouvelles et aussi des formations à votre courriel. Vous pouvez voir la liste des dernières infolettres ici.

Cliquez ici pour vous abonner à l'infolettre

Ce site est hébergé par 1&1.com