Boucles Excel VBA - Pour chacun, pour le suivant, faire pendant, imbriquées et plus

Pour travailler efficacement en VBA, vous devez comprendre les boucles.

Les boucles vous permettent de répéter un bloc de code un nombre défini de fois ou de répéter un bloc de code sur chaque objet d'un ensemble d'objets.

Nous allons d'abord vous montrer quelques exemples pour vous montrer de quoi les boucles sont capables. Ensuite, nous vous apprendrons tout sur les boucles.

Exemples rapides de boucle VBA

Pour chaque boucles

For Each Loops parcourt chaque objet d'une collection, comme chaque feuille de calcul d'un classeur ou chaque cellule d'une plage.

Parcourir toutes les feuilles de calcul du classeur

Ce code parcourra toutes les feuilles de calcul du classeur, affichant chaque feuille :

12345678

Feuilles de sous-boucle()Dim ws As Feuille de calculPour chaque ws dans les feuilles de calculws.Visible = VraiProchainFin du sous-marin

Boucle à travers toutes les cellules de la plage

Ce code parcourra une plage de cellules, en testant si la valeur de la cellule est négative, positive ou zéro :

1234567891011121314

Sous-If_Loop()Dim Cell comme plagePour chaque cellule de la plage ("A2:A6")Si Cell.Value > 0 AlorsCell.Offset(0, 1).Value = "Positive"ElseIf Cell.Value < 0 ThenCell.Offset(0, 1).Valeur = "Négatif"AutreCell.Offset(0, 1).Valeur = "Zéro"Fin siCellule suivanteFin du sous-marin

Pour les prochaines boucles

Un autre type de boucle « For » est la boucle For Next. La boucle For Next vous permet de parcourir des entiers.

Ce code parcourra les entiers 1 à 10, affichant chacun avec une boîte de message :

123456

Sous ForLoop()Diminuer en tant qu'entierPour i = 1 à 10MsgBox jeEnsuite jeFin du sous-marin

Faire les boucles While

Les boucles Do While boucleront tant qu'une condition est remplie. Ce code parcourra également les entiers 1 à 10, affichant chacun une boîte de message.

12345678

Sub DoWhileLoop()Dim n en tant qu'entiern = 1Faire tant que n < 11MsgBox nn = n + 1BoucleFin du sous-marin

Faire jusqu'à ce que les boucles

Inversement, Do Until Loops bouclera jusqu'à ce qu'une condition soit remplie. Ce code fait la même chose que les deux exemples précédents.

12345678

Sous DoUntilLoop()Dim n en tant qu'entiern = 1Faire Jusqu'à n >= 10MsgBox nn = n + 1BoucleFin du sous-marin

Nous en discuterons ci-dessous, mais vous devez être extrêmement prudent lorsque vous créez des boucles Do While ou Do Until afin de ne pas créer une boucle sans fin.

Générateur de boucles VBA

Ceci est une capture d'écran du « Loop Builder » de notre complément VBA Premium : AutoMacro. Le Loop Builder vous permet de créer rapidement et facilement des boucles pour parcourir différents objets ou nombres. Vous pouvez effectuer des actions sur chaque objet et/ou sélectionner uniquement les objets répondant à certains critères.

Le complément contient également de nombreux autres générateurs de code, une bibliothèque de codes VBA complète et un assortiment d'outils de codage. C'est un must pour tout développeur VBA.

Nous allons maintenant couvrir les différents types de boucles en profondeur.

VBA pour la prochaine boucle

Pour la syntaxe de la boucle

La boucle For Next vous permet de répéter un bloc de code un nombre spécifié de fois. La syntaxe est :

12345

[Dim Counter en tant qu'entier]Pour le compteur = du début à la fin [valeur de l'étape][Faire quelque chose]Suivant [Compteur]

Où les éléments entre parenthèses sont facultatifs.

• [Dim Counter aussi long] - Déclare la variable compteur. Obligatoire si Option Explicit est déclarée en haut de votre module.
• Contrer - Une variable entière utilisée pour compter
• Début - La valeur de départ (Ex. 1)
• Finir - La valeur finale (Ex. 10)
• [Valeur de pas] - Vous permet de compter tous les n entiers au lieu de chaque 1 entier. Vous pouvez également faire marche arrière avec une valeur négative (ex. Step -1)
• [Faire quelque chose] - Le code qui se répétera
• Suivant [Compteur] - Déclaration de clôture de la boucle For Next. Vous pouvez inclure le compteur ou non. Cependant, je recommande fortement d'inclure le compteur car cela rend votre code plus facile à lire.

Si cela prête à confusion, ne vous inquiétez pas. Nous allons passer en revue quelques exemples :

Compte jusqu'à 10

Ce code comptera jusqu'à 10 en utilisant une boucle For-Next :

12345678

Sous ForEach_CountTo10()Dim n en tant qu'entierPour n = 1 à 10MsgBox nSuivant nFin du sous-marin

Pour l'étape de boucle

Comptez jusqu'à 10 - Uniquement des nombres pairs

Ce code comptera jusqu'à 10 en ne comptant que les nombres pairs :

12345678

Sous ForEach_CountTo10_Even()Dim n en tant qu'entierPour n = 2 à 10 Étape 2MsgBox nSuivant nFin du sous-marin

Notez que nous avons ajouté « Etape 2 ». Cela indique à la boucle For de « passer » à travers le compteur de 2. Nous pouvons également utiliser une valeur de pas négative pour faire un pas en arrière :

Pas de boucle For - Inverse

Compte à rebours à partir de 10

Ce code comptera à rebours à partir de 10 :

123456789

Sous ForEach_Countdown_Inverse()Dim n en tant qu'entierPour n = 10 à 1 pas -1MsgBox nSuivant nMsgBox "Décoller"Fin du sous-marin

Supprimer des lignes si la cellule est vide

J'ai le plus souvent utilisé une boucle For-Loop négative pour parcourir des plages de cellules, en supprimant les lignes qui répondent à certains critères. Si vous passez des rangées du haut aux rangées du bas, au fur et à mesure que vous supprimez des rangées, vous gâcherez votre compteur.

Cet exemple supprimera les lignes avec des cellules vides (en commençant par la ligne du bas) :

12345678910

Sub ForEach_DeleteRows_BlankCells()Dim n en tant qu'entierPour n = 10 à 1 pas -1Si Plage("a" & n).Valeur = "" AlorsRange("a" & n).EntireRow.DeleteFin siSuivant nFin du sous-marin

Boucle For imbriquée

Vous pouvez « imbriquer » une boucle For dans une autre boucle For. Nous allons utiliser Nested For Loops pour créer une table de multiplication :

1234567891011

Sous Nested_ForEach_MultiplicationTable()Dim row As Integer, col As IntegerPour la ligne = 1 à 9Pour col = 1 à 9Cells(ligne + 1, col + 1).Valeur = ligne * colCol suivantLigne suivanteFin du sous-marin

Quitter pour

L'instruction Exit For vous permet de quitter immédiatement une boucle For Next.

Vous utiliserez généralement Exit For avec une instruction If, en quittant la boucle For Next si une certaine condition est remplie.

Par exemple, vous pouvez utiliser une boucle For pour rechercher une cellule. Une fois cette cellule trouvée, vous pouvez quitter la boucle pour accélérer votre code.

Ce code parcourra les lignes 1 à 1000, à la recherche de « erreur » dans la colonne A. S'il est trouvé, le code sélectionnera la cellule, vous alertera de l'erreur trouvée et quittera la boucle :

12345678910111213

Sous-ExitFor_Loop()Diminuer en tant qu'entierPour i = 1 à 1000Si Plage("A" & i).Valeur = "erreur" AlorsPlage ("A" & i).SélectionnezMsgBox "Erreur trouvée"Quitter pourFin siEnsuite jeFin du sous-marin

Important : dans le cas des boucles For imbriquées, Exit For ne fait que quitter la boucle For actuelle, pas toutes les boucles actives.

Continuer pour

VBA n'a pas la commande "Continuer" qui se trouve dans Visual Basic. Au lieu de cela, vous devrez utiliser « Quitter ».

VBA pour chaque boucle

La boucle VBA For Each Loop parcourt tous les objets d'une collection :

• Toutes les cellules d'une plage
• Toutes les feuilles de calcul dans un classeur
• Toutes les formes dans une feuille de calcul
• Tous les classeurs ouverts

Vous pouvez également utiliser Nested For Each Loops pour :

• Toutes les cellules d'une plage sur toutes les feuilles de calcul
• Toutes les formes sur toutes les feuilles de calcul
• Toutes les feuilles de tous les classeurs ouverts
• etc…

La syntaxe est :

123	Pour chaque objet de la collection[Faire quelque chose]Suivant [Objet]

Où:

• Objet - Variable représentant une plage, une feuille de calcul, un classeur, une forme, etc. (ex. rng)
• Collection - Collection d'objets (ex. Range ("a1:a10")
• [Faire quelque chose] - Bloc de code à exécuter sur chaque objet
• Suivant [Objet] - Déclaration de clôture. [Objet] est facultatif, mais fortement recommandé.

Pour chaque cellule de la plage

Ce code parcourra chaque cellule d'une plage :

123456789

Sous ForEachCell_inRange()Cellule tamisée en tant que plagePour chaque cellule dans la plage ("a1:a10")cellule.Valeur = cellule.Décalage(0,1).ValeurCellule suivanteFin du sous-marin

Pour chaque feuille de calcul dans le classeur

Ce code parcourra toutes les feuilles de calcul d'un classeur, en déprotégeant chaque feuille :

123456789

Sous ForEachSheet_inWorkbook()Dim ws As Feuille de calculPour chaque ws dans les feuilles de calculws.Déprotéger le "mot de passe"WS suivantFin du sous-marin

Pour chaque classeur ouvert

Ce code enregistrera et fermera tous les classeurs ouverts :

123456789

Sub ForEachWB_inWorkbooks()Dim wb comme classeurPour chaque wb dans les classeurswb.Close SaveChanges:=TrueWb suivantFin du sous-marin

Pour chaque forme dans la feuille de calcul

Ce code supprimera toutes les formes de la feuille active.

123456789

Sous pour chaque forme()Dim shp en tant que formePour chaque shp dans ActiveSheet.Shapesshp.DeleteSuivantFin du sous-marin

Pour chaque forme de chaque feuille de calcul du classeur

Vous pouvez également imbriquer des boucles For Each. Ici, nous allons parcourir toutes les formes de toutes les feuilles de calcul du classeur actif :

1234567891011

Sub ForEachShape_inAllWorksheets()Dim shp As Shape, ws As WorksheetPour chaque ws dans les feuilles de calculPour chaque shp dans ws.Shapesshp.DeleteSuivantWS suivantFin du sous-marin

Pour chaque - boucle IF

Comme nous l'avons mentionné précédemment, vous pouvez utiliser une instruction If dans une boucle, en effectuant des actions uniquement si certains critères sont remplis.

Ce code masquera toutes les lignes vides d'une plage :

12345678910

Sous ForEachCell_inRange()Cellule tamisée en tant que plagePour chaque cellule dans la plage ("a1:a10")Si cellule.Valeur = "" Alors _cell.EntireRow.Hidden = TrueCellule suivanteFin du sous-marin

Boucle VBA Do While

Les fonctions VBA Do While et Do Until (voir la section suivante) sont très similaires. Ils répéteront une boucle pendant (ou jusqu'à ce qu'une condition soit remplie).

La boucle Do While répétera une boucle tant qu'une condition est remplie.

Voici la syntaxe Do While :

123	Faire en condition[Faire quelque chose]Boucle

Où:

• État - La condition à tester
• [Faire quelque chose] - Le bloc de code à répéter

Vous pouvez également configurer une boucle Do While avec la condition à la fin de la boucle :

123	Faire[Faire quelque chose]Boucle en condition

Nous allons faire une démonstration de chacun et montrer en quoi ils diffèrent :

Faire pendant

Voici l'exemple de boucle Do While que nous avons démontré précédemment :

12345678

Sub DoWhileLoop()Dim n en tant qu'entiern = 1Faire tant que n < 11MsgBox nn = n + 1BoucleFin du sous-marin

Boucle pendant

Exécutons maintenant la même procédure, sauf que nous allons déplacer la condition à la fin de la boucle :

12345678

Sub DoLoopWhile()Dim n en tant qu'entiern = 1FaireMsgBox nn = n + 1Boucle tant que n < 11Fin du sous-marin

VBA faire jusqu'à la boucle

Do Until Loops répétera une boucle jusqu'à ce qu'une certaine condition soit remplie. La syntaxe est essentiellement la même que celle des boucles Do While :

123	Faire jusqu'à condition[Faire quelque chose]Boucle

et de même la condition peut aller au début ou à la fin de la boucle :

123	Faire[Faire quelque chose]Boucle jusqu'à la condition

Faire jusqu'à ce que

Cette boucle do Until comptera jusqu'à 10, comme nos exemples précédents

12345678

Sous DoUntilLoop()Dim n en tant qu'entiern = 1Faire Jusqu'à n > 10MsgBox nn = n + 1BoucleFin du sous-marin

Boucle jusqu'à

Cette boucle Loop Until comptera jusqu'à 10 :

12345678

Sous DoLoopUntil()Dim n en tant qu'entiern = 1FaireMsgBox nn = n + 1Boucle jusqu'à n > 10Fin du sous-marin

Quitter la boucle

Similaire à l'utilisation de Exit For pour quitter une boucle For, vous utilisez la commande Exit Do pour quitter immédiatement une boucle Do

1	Quitter Faire

Voici un exemple de Exit Do :

123456789101112131415

Sous ExitDo_Loop()Diminuer en tant qu'entierje = 1Faire jusqu'à i > 1000Si Plage("A" & i).Valeur = "erreur" AlorsPlage ("A" & i).SélectionnezMsgBox "Erreur trouvée"Quitter FaireFin sije = je + 1BoucleFin du sous-marin

Terminer ou interrompre la boucle

Comme nous l'avons mentionné ci-dessus, vous pouvez utiliser Exit For ou Exit Do pour quitter les boucles :

1	Quitter pour

1	Quitter Faire

Cependant, ces commandes doivent être ajoutées à votre code avant d'exécuter votre boucle.

Si vous essayez de « casser » une boucle en cours d'exécution, vous pouvez essayer d'appuyer sur ESC ou CTRL + Pause sur le clavier. Cependant, cela peut ne pas fonctionner. Si cela ne fonctionne pas, vous devrez attendre la fin de votre boucle ou, dans le cas d'une boucle sans fin, utilisez CTRL + ALT + Effacer pour forcer la fermeture d'Excel.

C'est pourquoi j'essaie d'éviter les boucles Do, il est plus facile de créer accidentellement une boucle sans fin vous obligeant à redémarrer Excel, perdant potentiellement votre travail.

Plus d'exemples de boucles

Boucle dans les lignes

Cela va parcourir toutes les lignes d'une colonne :

123456789

Public Sub LoopThroughRows()Cellule tamisée en tant que plagePour chaque cellule dans la plage ("A:A")Ff cell.value "" puis MsgBox cell.address &": " & cell.valueCellule suivanteFin du sous-marin

Boucler les colonnes

Cela va parcourir toutes les colonnes d'affilée :

123456789

Public Sub LoopThroughColumns()Cellule tamisée en tant que plagePour chaque cellule dans la plage ("1:1")Si cell.Value "" Then MsgBox cell.Address & " : " & cell.ValueCellule suivanteFin du sous-marin

Parcourir les fichiers d'un dossier

Ce code parcourra tous les fichiers d'un dossier, créant une liste :

12345678910111213141516171819

Sous-LoopThroughFiles ()Dim OFSO en tant qu'objetDim oFolder en tant qu'objetDim oFile As ObjectDiminuer en tant qu'entierDéfinir oFSO = CreateObject("Scripting.FileSystemObject")Définir oFolder = oFSO.GetFolder("C:\Demo)je = 2Pour chaque oFile dans oFolder.FilesRange("A" & i).value = oFile.Nameje = je + 1Fichier suivantFin du sous-marin

Boucle à travers le tableau

Ce code parcourra le tableau 'arrList' :

123	Pour i = LBound(arrList) À UBound(arrList)MsgBox arrList(i)Ensuite je

La fonction LBound obtient la « limite inférieure » du tableau et UBound obtient la « limite supérieure ».

Boucles dans Access VBA

La plupart des exemples ci-dessus fonctionneront également dans Access VBA. Cependant, dans Access, nous parcourons l'objet Recordset plutôt que l'objet Range.

123456789101112131415161718

Sous-LoopThroughRecords()En cas d'erreur Reprendre ensuiteDim dbs en tant que base de donnéesDim d'abord en tant que jeu d'enregistrementsDéfinir dbs = CurrentDbDéfinir d'abord = dbs.OpenRecordset("tblClients", dbOpenDynaset)Avec d'abord.MoveLast.MoveFirstFaire jusqu'à ce que .EOF = TrueMsgBox (rst.Fields("ClientName")).MoveNextBoucleTerminer parpremier.FermerDéfinir en premier = RienDéfinir dbs = RienFin du sous-marin