Boucles For Next en VBA Excel pour traitements automatisés

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Boucles aux bornes strictes - boucles For Next

Dans la formation précédente nous avons appris les boucles avec l'instruction For Each qui permettait de parcourir tous les éléments d'un groupe. En l'occurrence, nous avons utilisé l'instruction For Each pour parcourir toutes les cellules d'une sélection afin de faire ressortir toutes les valeurs redondantes. Ici, nous allons découvrir la boucle For...Next. Cette dernière permet de parcourir tous les éléments en fonction des bornes que nous lui indiquons. Exemple :

For ligne=1 To 10
Actions
Next ligne

Sa syntaxe est très similaire à celle de la boucle For Each. Le mot clé For permet d'initialiser la boucle d'instruction tandis que le mot clé Next permet de fermer la boucle. Les lignes situées entre ces deux mots clés, sont les traitements, les actions récurrentes à reproduire tant que les bornes supérieures de la boucle ne sont pas atteintes. Dans notre exemple, les actions se répéteront 10 fois. La boucle démarre avec la variable ligne affectée à la valeur 1 et incrémentée à chaque passage, jusqu'à ce qu'elle atteigne la valeur 10. To est donc un mot clé de la boucle For. Littéralement : Pour la variable Ligne variant de 1 à 10, réaliser les actions.

Cas pratique

• Télécharger le classeur boucles-for-bornes-strictes.xlsm, en cliquant sur son lien,
• L'ouvrir dans Excel.

Son extension .xlsm signifie qu'il s'agit d'un classeur gérant les macros. Donc nous allons pouvoir y créer nos procédures Visual Basic pour présenter la boucle for.


La feuille 1 de ce classeur est constituée de trois petits tableaux sans mise en forme. Comme les boucles d'instruction permettent d'optimiser les actions en réalisant des traitements récurrents, nous allons exploiter la boucle For afin de détecter les bornes des tableaux et les mettre en forme automatiquement.

• Basculer dans l'éditeur de code Visual Basic en enfonçant les touches Alt et F11 par exemple,
• Dans le volet de gauche, double-cliquer sur Feuil1 pour créer le code attaché à la feuille,
• Dans la page de code, créer la procédure boucles,

Comme la boucle For permet de réaliser des actions récurrentes en définissant des bornes, c'est-à-dire le point de départ et celui d'arrivée, nous allons devoir commencer par définir ces bornes. Il existe une méthode Excel SpecialCells() de l'objet Cells qui permet de connaître la dernière cellule ayant du contenu dans la feuille. La ligne et la colonne de cette dernière cellule définiront donc les bornes de fin de la boucle. En revanche, il n'existe pas de méthode pour connaître la première cellule de la feuille avec du contenu. Donc nous ferons démarrer la boucle de la première cellule d'indice 1 pour la ligne comme pour la colonne.

• Déclarer les variables der_ligne et der_colonne comme des entiers, Integer,
• De même, déclarer les variables ligne et colonne en Integer,

Dim der_ligne As Integer: Dim der_colonne As Integer
Dim ligne As Integer: Dim colonne As Integer

Le type Byte, entier court, est vraisemblablement trop petit. Il permet de stocker un entier jusqu'à la valeur 255. Nous pourrions très bien avoir des tableaux au-delà de la ligne 255. Donc nous avons besoin, au cas où, de variables permettant de réaliser les traitements récurrents sur des valeurs plus grandes. Le support de formation sur les variables en VBA Excel présente les types de données importants.

• Ajouter les deux lignes de code suivantes :

der_colonne = Cells.SpecialCells(xlCellTypeLastCell).Column
der_ligne = Cells.SpecialCells(xlCellTypeLastCell).Row

Nous initialisons ainsi les deux variables précédemment déclarées aux indices de colonne et de ligne de la dernière cellule de la feuille. L'argument xlCellTypeLastCell passé à la méthode SpecialCells de l'objet Cells permet de désigner cette cellule. Pour connaître la liste des arguments de cette méthode, il suffit d'enfoncer la touche F1 du clavier après avoir sélectionné la méthode. Les propriétés Column et Row parlent d'elles-mêmes. Elles renvoient respectivement l'indice de colonne et l'indice de ligne de la cellule. Pour parcourir les cellules de la feuille à la recherche des tableaux, nous avons besoin de faire varier les indices de ligne et de colonne. En effet, une cellule, désignée par l'objet Cells() en VBA demande le numéro de ligne ainsi que le numéro de colonne en paramètre. Pour incrémenter ces deux paramètres à la fois, nous avons besoin d'imbriquer deux boucles l'une dans l'autre. Une première incrémentera la ligne, une seconde incrémentera la colonne.

• Ajouter les lignes de code suivantes à la suite :

For ligne = 1 To der_ligne
For colonne = 1 To der_colonne

Next colonne
Next ligne


For ligne = 1 To der_ligne : Cette première boucle fait varier l'indice de ligne en partant de 1 jusqu'à l'indice de la dernière cellule de la feuille. For colonne = 1 To der_colonne : Cette seconde boucle imbriquée dans la première, fait varier l'indice de colonne en partant de 1 jusqu'au dernier indice de colonne dans la feuille. Cela signifie que pour chaque indice de ligne, nous passons en revue tous les indices de colonnes. Au final, nous passons donc en revue toutes les cellules comprises entre la première et la dernière possédant du contenu. Mais pour passer en revue chacune de ces cellules, nous avons besoin de l'objet Cells qui exploite ces indices de ligne et de colonne. Et pour savoir si cette cellule appartient bien à un tableau, nous devons employer l'instruction If() qui permet de poser un test. Ici en l'occurrence la condition à vérifier sera de savoir si la cellule est vide ou non. La formation pour gérer les conditions en VBA Excel enseigne ces notions de façon détaillée. Pour réaliser ce test :

• Ajouter les lignes de codes suivantes à l'intérieur de la seconde boucle :

If (Cells(ligne, colonne).Value <>'') Then
MsgBox ligne & ' ' & colonne
Exit Sub
End If

If (Cells(ligne, colonne).Value <> '') Then : Nous commençons par réaliser le test pour savoir si la cellule en cours (Cells(ligne, colonne)) contient (.Value) quelque chose (<> ''). Alors nous affichons les numéros de ligne et de colonne de cette cellule (MsgBox ligne & ' ' & colonne). Souvenez-vous, le & commercial permet de concaténer afin d'assembler des bouts de chaîne. Puis nous stoppons tout de suite l'exécution du programme (Exit Sub). En effet, si nous n'arrêtons pas l'exécution, une boîte de dialogue se déclenchera à chaque cellule non vide. Dès que le premier test est vérifié, cela signifie que nous avons trouvé la toute première cellule non vide de la feuille, le début du premier tableau. Nous allons tester cela :

• Enfoncer la touche F5 du clavier pour exécuter le code,

La boîte de dialogue s'affiche renvoyant les indices de ligne 4 et de colonne 2, soit la cellule B4. Il s'agit bien de la première cellule du premier tableau dans laquelle il est écrit le texte TVA.


L'objectif pour réaliser la mise en forme automatique des tableaux est le suivant : S'il s'agit d'une cellule de la première ligne ou de la première colonne, nous passons le texte en gras sur un fond vert un peu plus foncé. Pour la première colonne, nous mettons une bordure sur la gauche, pour la première ligne, une bordure au-dessus. Il ne faut pas oublier les bordures pour la dernière ligne et la dernière colonne. Si la cellule sur la gauche est vide (colonne-1), il s'agit de la première colonne, si c'est celle du dessus (ligne - 1), il s'agit de la première ligne. Si la cellule sur la droite est vide (colonne + 1), il s'agit de la dernière colonne. Et enfin, si la cellule du dessous est vide (ligne + 1), il s'agit de la dernière ligne. Nous devons transcrire ces tests par le code :

• Effacer les quatre lignes de code de l'instruction If à l'intérieur de la seconde boucle,
• A la place, saisir le code suivant, en l'indentant :

If (ligne> 1) Then
If (Cells(ligne - 1, colonne).Value = '' And Cells(ligne,colonne).Value <> '') Then
With Cells(ligne, colonne)
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThick
.Font.Bold = True
.Interior.Color = RGB(70, 170, 30)
End With
End If
End If

Ces tests permettent de réaliser la mise en forme des cellules de la première ligne. Donc nous commençons par nous assurer que nous ne sommes pas sur la toute première ligne de la feuille (If (ligne > 1) Then). En effet, sinon cela signifie qu'il n'y a pas de ligne au-dessus et que le test suivant (If (Cells(ligne - 1,colonne).Value = '')) aurait planté l'application. Il n'existe pas de cellule à l'indice de ligne 0. Pour savoir si nous sommes bien dans une cellule de la première ligne du tableau, nous devons vérifier deux critères ensemble. Tout d'abord la cellule du dessus (ligne – 1) doit être vide (Cells(ligne - 1, colonne).Value = '') et en plus (mot clé And) la cellule elle-même (ligne) ne doit pas être vide (Cells(ligne,colonne).Value <> ''). Alors (Then), nous réalisons les opérations de mise en forme. Pour la cellule concernée, nous listons toutes ses propriétés à affecter grâce à l'instruction With (With Cells(ligne, colonne)) afin d'obtenir un code moins lourd. La formation pour débuter en VBA apprend à programmer avec cette instruction With. Nous définissons le type de trait de la bordure supérieure sur un trait continu (.Borders(xlEdgeTop).LineStyle= xlContinuous). Nous choisissons de plus une bordure épaisse pour ce trait (.Borders(xlEdgeTop).Weight = xlThick). Puis nous mettons le texte en gras (.Font.Bold = True). Ensuite nous réglons la couleur de fond de la cellule à l'aide de la fonction RGB() et de ses trois composantes de couleur attendues en paramètre (.Interior.Color = RGB(70,170, 30)). Bien sûr, nous n'oublions pas de fermer l'instruction With une fois le réglage des propriétés terminé (End With). Et nous fermons les deux tests sachant que le premier If englobe le second (End If End If).

• Enfoncer la touche F5 du clavier pour tester la macro,
• Activer la feuille Excel pour visualiser le résultat,

C'est parfait, la détection et la mise en forme de chaque première ligne de tableau se sont déroulées comme nous l'avons codé. Nous allons ajouter un bouton directement sur la feuille afin de pouvoir visualiser l'exécution du code en temps réel.

• Afficher la feuille 1 et activer le ruban Développeur,
• Dans la section Contrôles, cliquer sur le bouton Insérer,
• Choisir le premier bouton de formulaire et le tracer directement sur la feuille,
• Dans la boîte de dialogue qui suit, l'associer à la macro boucles et valider par Ok,
• Utiliser le bouton Reproduire la mise en forme du ruban Accueil pour retrouver la mise en forme d'origine des tableaux,

Concernant le code, le plus dur est fait. Il s'agit maintenant de mettre en forme la première colonne de chaque tableau. Le premier test ligne > 1 devient colonne > 1. Et la bordure à traiter n'est plus celle du dessus (xlEdgeTop) mais celle de gauche (xlEdgeLeft).

• Copier et coller le code précédent en l'adaptant de manière à mettre en forme chaque première colonne de tableau,

If(colonne > 1) Then
If (Cells(ligne, colonne - 1).Value = '' And Cells(ligne,colonne).Value <> '') Then
With Cells(ligne, colonne)
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlThick
.Font.Bold = True
.Interior.Color = RGB(70, 170, 30)
End With
End If
End If

• Revenir sur la feuille et cliquer sur le bouton,

Il nous reste à réaliser la bordure droite (xlEdgeRight) pour chaque dernière colonne de tableau ainsi que la bordure inférieure (xlEdgeBottom) pour chaque dernière ligne de tableau. La couleur de fond ne doit pas être changée et la police ne doit pas passer en gras. Il y a donc déjà deux lignes à exclure. De plus les tests de sécurité (ligne > 1 et colonne > 1) ne sont plus nécessaires. Donc l'adaptation du code pour ces deux derniers traitements est encore plus simple.

• Ajouter le code permettant de traiter la dernière colonne de chaque tableau,
• Ajouter le code permettant de traiter la dernière ligne de chaque tableau,
• Tester ces ajustements avec le bouton de la feuille.

Vous devez remarquer que désormais les dernières lignes et colonnes sont traitées, parachevant la détection et la mise en forme automatiques par le code à l'aide des boucles de traitement For Next et des instructions If End If pour les critères.

Le pas d'une boucle For - Mot clé Step
Une boucle For Next permet bien sûr de réaliser des traitements entre des bornes définies grâce au mot clé To et aux valeurs inférieures et supérieures. Mais à l'intérieur de cette boucle, il n'est pas obligatoire de se déplacer avec une incrémentation d'une seule unité. Le mot clé Step permet de définir le pas de l'incrémentation de la boucle (For ligne = 2 To 10 Step 2). S'il n'est pas défini, il vaut 1 par défaut. Nous allons découvrir cela en proposant de réaliser des couleurs de fond alternées pour les cellules d'un tableau dont nous connaissons les bornes.

• Copier le tableau G4:I12 de la feuille 1 en cellule B2 sur la feuille 2,
• A l'aide de la balise active, choisir de recopier seulement les valeurs sans la mise en forme,

Le tableau ainsi copié ne doit disposer de plus aucune mise en forme, ni bordures, ni couleurs.

• Dans l'éditeur de code, double-cliquer sur Feuil2 dans le volet de gauche,
• Créer la procédure alterner(),
• Déclarer les variables ligne et colonne en tant que Byte,

Nous connaissons les bornes du tableau, de la ligne 2 à 10 et de la colonne 2 à 4. Le type Byte est donc largement suffisant.

• Ajouter la boucle qui permet de faire varier l'indice de ligne de 2 jusqu'à 10 avec un pas de 2,
• Puis imbriquer la seconde boucle qui fait varier la colonne de 2 à 4,
• Dans la seconde boucle, ajouter la ligne de code permettant de remplir le fond de la cellule en vert,
• Sur la feuil2, ajouter un bouton pour déclencher cette macro alterner(),
• Tester le code en cliquant sur le bouton,

La mise en forme alternée fonctionne parfaitement grâce au pas réglé par Step 2. Le code, très simple, est donné sur la figure suivante :


La boucle While
Outre la boucle For Each et la boucle For, il existe la boucle While (tant que). Tout comme les deux précédentes, son objectif est de réaliser des traitements récurrents et d'optimiser le code. La boucle While continue de tourner tant que le critère qui lui est passé dans les parenthèses est vérifié. Une boucle While doit toujours se terminer par le mot clé Wend dans sa syntaxe :

While(critere=ok)
Realiser_traitements
Wend

Bien sûr il faut apporter grand soin au critère en s'assurant que la condition finira par se vérifier. Le cas échéant, vous aurez affaire à une boucle infinie, c'est-à-dire à un code dont l'exécution ne se termine jamais. Néanmoins en VBA Excel le raccourci clavier CTRL + Pause permet d'arrêter brutalement un programme qui ne répond plus.

ligne = 1: colonne = 1
While (ligne < der_ligne)
colonne = 1
While (colonne < der_colonne)
{Traitements}
colonne = colonne + 1
Wend
ligne = ligne + 1
Wend

Les critères à poser consistent à savoir si la ligne (While (ligne < der_ligne)) et la colonne (While (colonne < der_colonne)) de la dernière cellule de la feuille avec du contenu sont atteintes. Comme ces critères ne permettent pas d'incrémenter les valeurs des indices comme le fait la boucle For, il faut écrire ce code, en fin de boucle While pour passer à l'indice suivant (colonne = colonne + 1 Wend et ligne =ligne + 1 Wend). La première boucle While imbriquant la seconde, il ne faut pas oublier de réinitialiser la valeur de colonne à 1 à chaque passage, car sinon pour chaque nouvelle ligne, le traitement repart de la dernière colonne (colonne = 1). Bien sûr, pour que ces indices puissent être incrémentés, il faut nécessairement les initialiser avant de débuter les boucles While (ligne = 1: colonne = 1). Le reste des traitements ne change pas.