Boucle d'instruction For Each en VBA Excel

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


Sujets que vous pourriez aussi aimer :


Les boucles en VBA Excel

En programmation, les boucles permettent d'optimiser le code. On les utilise pour réaliser des traitements récurrents. Si vous devez par exemple parcourir un tableau de plusieurs centaines de lignes, à la recherche d'informations, vous n'allez pas écrire une ligne de code pour chaque ligne du tableau. Vous allez écrire une seule ligne de code, à l'intérieur d'une boucle parcourant le tableau, qui s'adaptera en fonction de la ligne. Nous allons introduire la notion de ces boucles au travers d'un cas pratique.

• Télécharger le classeur boucle_foreach-vba.xlsm, en cliquant sur son lien,
• L'ouvrir dans Excel,

La feuille qui nous intéresse dans un premier temps, est la feuille doublons. Elle présente un petit tableau de valeurs sans aucune mise en forme comme l'illustre la capture ci-dessus. A l'issue, nous souhaitons faire ressortir toutes les valeurs redondantes, doublons, triplons et plus. Plus le nombre est répété, plus sa taille de police doit grossir et la couleur de fond de la cellule tendre vers le rouge. Nous obtiendrons ainsi une lecture aisée des données pour identifier les valeurs récurrentes. Nous allons donc utiliser une boucle de programmation pour parcourir toutes les données du tableau ainsi que des instructions If. Ces dernières permettront de vérifier certains critères, comme savoir si la donnée en cours est déjà présente dans le tableau. Ce cas pratique est intéressant à double titre. Tout d'abord, il permet de présenter la syntaxe des boucles de programmation afin d'optimiser le code et réaliser des programmes intelligents. Et puis, il permet la mise en forme des données récurrentes au-delà des doublons. Il existe une fonctionnalité de mise en forme conditionnelle des doublons dans un tableau Excel. Mais cette fonctionnalité s'arrête aux doublons. Donc nous allons construire un code qui dépasse ces limites et qui offre une solution que ne propose pas Excel lui-même.

La boucle For Each
Comme son nom l'indique For Each (Pour chaque), cette boucle permet de parcourir tous les éléments d'un objet désigné. Il peut s'agir de toutes les cellules d'un tableau, ou mieux encore, de toutes les cellules d'une sélection. C'est exactement ce que nous allons faire.

• Réaliser la combinaison de touches ALT + F11 pour basculer dans l'éditeur de code,
• Dans le volet de gauche de l'éditeur, double cliquer sur Feuil1 (doublons),

Nous affichons ainsi la page de code attachée à la feuille doublons sur laquelle nous souhaitons travailler.

• Créer la procédure cherche_frequence(),

La syntaxe de la boucle For Each est la suivante :

For Each Element In Groupe_Elements
Traitements
Next Element

For Each, In et Next sont les mots clés de la boucle. Groupe_Elements est une variable ou un objet qui désigne tous les éléments que nous devons parcourir, il peut s'agir d'une plage de cellules comme une sélection, dans ce cas, nous le remplacerions par Selection. Souvenez-vous, Selection est l'objet VBA Excel qui désigne l'ensemble des cellules sélectionnées au moment où le code s'exécute. Le support de formation pour débuter en VBA Excel présente cet objet avec ses propriétés et méthodes. Element serait alors un objet ou une variable du même type que Groupe_Elements, désignant l'un des éléments de la liste que nous parcourons. Dans notre cas, pour parcourir la sélection, il faut une variable que nous pourrions nommer cellule par exemple qui désignerait ainsi seulement l'une des cellules de la plage. Ainsi nous pourrions parcourir les cellules une à une dans la sélection. Cette variable cellule doit alors avoir le même type que l'objet Selection. Selection désigne une plage de cellules, c'est donc un Range. Nous devons déclarer la variable en tant que telle avant de l'utiliser dans la boucle. Enfin, Traitements désigne l'ensemble des actions qui seront traitées de façon récurrente par la boucle. Selon ce principe :

• Déclarer la variable cellule en tant que Range,
• Puis écrire les bornes de la boucle For Each selon la syntaxe énoncée,

Avant de faire un test du code, nous allons l'associer à un bouton sur la feuille.

• Revenir sur la feuille doublons,
• Activer le ruban Développeur,
• Dans la zone Contrôles, cliquer sur le bouton Insérer,
• Dans la liste, choisir le premier bouton de formulaire,
• Le tracer sur la feuille,
• Dans la boîte de dialogue qui suit, choisir la macro cherche_frequence,
• Valider en cliquant sur Ok,
• Changer le texte du bouton en Fréquences par exemple,

A ce stade, si vous cliquez sur le bouton, il ne se produit rien. En effet, le code a pour l'instant simplement initialisé une variable et la boucle dans laquelle aucun traitement ne figure. Pour vérifier que la boucle fonctionne, en guise de traitement nous allons réaliser un test. A chaque passage, nous allons stocker la valeur de la cellule dans une variable de type texte, un String. Chacune de ces valeurs sera séparée par un tiret pour bien les identifier séparément. Puis nous afficherons le résultat ainsi mémorisé.

• Déclarer la variable memoire comme un String,
• Dans la boucle, stocker la valeur lue suivie d'un tiret dans la variable memoire,
• Après la boucle, afficher le contenu de la variable avec une boîte de dialogue MsgBox,

Ce qui donne :

Dim cellule As Range: Dim memoire As String

For Each cellule In Selection
memoire = memoire & cellule.Value & '-'
Next cellule

MsgBox memoire

Dans la boucle, nous ajoutons à ce que contient déjà la variable memoire (memoire = memoire & ), la valeur de la cellule en cours suivie d'un tiret (cellule.Value & '-' ). Il s'agit donc d'une seule instruction pour un traitement qui concerne 40 valeurs. Il pourrait y avoir des milliers de cellules dans la sélection, le code ne changerait pas. C'est tout l'intérêt du traitement par les boucles. Nous utilisons le Et commercial (& : touche 1 du clavier) afin d'assembler ces informations. On parle de concaténation. La formation Excel sur la concaténation apporte d'autres informations précieuses à ce sujet. Enfin on affiche le résultat des valeurs stockées à l'aide d'une boîte de dialogue (MsgBox memoire). Bien sûr, nous commandons cet affichage une fois la boucle terminée et toutes les valeurs stockées. Si le MsgBox est écrit dans la boucle et que la sélection contient 30 valeurs, il s'affiche 30 fois à l'écran. Nous allons tester cette procédure :

• Revenir sur la feuille doublons,
• Sélectionner toutes les cellules contenant des valeurs soit A1:E8,
• Cliquer sur le bouton de macro,

A l'issue de l'exécution du code, la boîte de dialogue affiche toutes les valeurs de cellules contenues dans la sélection, de la première à la dernière. Notre test confirme donc que nous avons bien réussi à balayer l'ensemble des cellules grâce à la boucle For Each et l'objet Selection. Nous allons maintenant rechercher à identifier les cellules dont les valeurs se répètent. Nous utiliserons une variable frequence à déclarer comme un Byte pour compter la récurrence. Le Byte est un entier court, il accepte les valeurs de 0 à 255. Sur un tableau de 40 cellules, aucune valeur ne se répètera autant, donc le Byte permet de dimensionner correctement notre variable. La formation sur les variables en VBA Excel présente de nombreux types de données et explique pourquoi le dimensionnement au plus juste est important.

• Dans le code, supprimer la ligne du MsgBox, il s'agissait d'un test,
• Supprimer de même la ligne permettant de stocker les valeurs dans la variable mémoire à l'intérieur de la boucle,
• Puis, déclarer la variable frequence en tant que Byte,
• Déclarer de même la variable cellule2 en tant que Range,
• Enfin, déclarer les variables ligne et colonne en tant que Byte,

La variable cellule2 va nous permettre de parcourir une seconde fois les cellules de la sélection pour comparer leur valeur avec celle qui est traitée dans la première boucle. Les variables ligne et colonne vont servir à mémoriser les numéros de ligne et colonne de la cellule traitée pour être sûr de ne pas comparer sa valeur à elle-même dans la seconde boucle.

• Ajouter les initialisations suivantes des variables dans la boucle,

frequence = 0
ligne = cellule.Row: colonne = cellule.Column

A chaque nouvelle cellule, c'est-à-dire à chaque passage dans la boucle, la variable frequence est réinitialisée à 0 pour pouvoir recompter la fréquence de la valeur de la prochaine cellule. Les variables ligne et colonne sont affectées respectivement à l'indice de ligne et de colonne de la cellule en cours de traitement pour savoir où nous en sommes. La propriété Row d'un objet de type Range renvoie l'indice de ligne tandis que la propriété Column renvoie l'indice de colonne. Notez la présence des deux points pour réaliser l'affectation des variables sur une même ligne. Maintenant, pour chaque cellule de la première boucle For Each, nous souhaitons parcourir toutes les cellules à nouveau, de façon à pouvoir comparer la valeur de la cellule en cours dans la première boucle avec toutes les autres. Donc, après les affectations précédentes, dans la boucle :

• Ecrire le code de la seconde boucle qui parcourt de nouveau toutes les cellules de la sélection à l'aide de la variable cellule2,

For Each cellule2 In Selection

Next cellule2

Dans cette seconde boucle, il faut être capable de comparer la valeur de la cellule en cours dans la première boucle, mais en s'assurant qu'il ne s'agit pas d'elle-même. En effet la seconde boucle parcourt de nouveau toutes les cellules de la sélection sans exclure celle qui est en cours de lecture dans la première. C'est pourquoi nous allons nous assurer que les indices de ligne ou de colonne entre les deux cellules sont différents, certifiant qu'il ne s'agit pas des mêmes. Il s'agit d'un critère à vérifier. Les conditions en VBA Excel se testent à l'aide de l'instruction If End If. La formation pour gérer les conditions en VBA Excel démontre toute la puissance de cette instruction. Il s'agit donc d'écrire en VBA que si la ligne de la cellule en cours est différente de la ligne de la cellule testée ou que la colonne de la cellule en cours est différente de la cellule testée, alors nous saurons que les deux cellules ne sont pas les mêmes.

• A l'intérieur de la seconde boucle For Each, ajouter l'instruction If comme suit :

If (ligne<>cellule2.Row Or colonne<>cellule2.Column) Then

End If

Le mot clé Or permet de traduire le Ou en VBA. Il suffit que l'un des deux tests soit vérifié pour que la condition de l'instruction If soit validée. Si nous avions remplacé le Or par le mot clé And, il aurait fallu que les deux tests soit vérifiés pour que la condition soit validée. Si les deux cellules ne sont pas les mêmes, alors nous devons savoir si leurs valeurs sont les mêmes. Dans ce cas en effet, cela signifie qu'il s'agit au moins d'un doublon. C'est donc une fois encore l'instruction If qui va nous être utile pour valider le test consistant à comparer les valeurs des cellules parcourues. C'est la propriété Value d'un objet de type Range qui permet de connaître la valeur d'une cellule, cellule.Value et cellule2.Value.

• A l'intérieur du If de la seconde boucle, ajouter le test et le traitement suivant :

If(cellule.Value = cellule2.Value) Then
frequence = frequence + 1
End If

Si les valeurs des cellules sont identiques (If(cellule.Value = cellule2.Value)) alors (Then), nous incrémentons la valeur de la variable fréquence pour cette cellule (frequence = frequence + 1). C'est cette valeur, selon son chiffre qui permettra de savoir s'il s'agit d'un doublon, d'un triplon ou plus. Souvenez-vous que la variable frequence est réinitialisée à chaque passage en début de boucle puisque nous changeons de cellule. Il faut donc l'exploiter avant la fin de la boucle. Dans un premier temps, nous allons en profiter pour simplement mettre le texte en gras s'il s'agit au moins d'un doublon (si la fréquence est supérieure à 0). C'est une fois encore l'instruction If qui va permettre de poser le critère afin de savoir quoi faire.

• Après la seconde boucle, mais toujours dans les bornes de la première boucle For Each, écrire le test et l'action suivante :

If(frequence > 0) Then
cellule.Font.Bold = True
End If



Le code des boucles et des tests à ce stade est donné par la capture ci-dessus. La propriété Font de l'objet cellule permet de désigner sa police. La propriété dérivée Bold de la propriété Font signifie Gras. Cette propriété ne peut être que vraie ou fausse (True ou False). Une cellule est en gras ou ne l'est pas. Le fait de l'affecter à True passe la cellule en gras. Il ne reste plus qu'à essayer le code.

• Revenir sur la feuille doublons,
• Sélectionner toutes les cellules numériques et cliquer sur le bouton de la feuille,

Toutes les cellules dont la valeur est répétée apparaissent bien en gras. Toutes les autres conservent leur état d'origine, sans mise en forme. En revanche, pour l'instant rien ne différencie les doublons des triplons ou des quadruplons. Nous proposons d'augmenter la taille de la police de la cellule répétée en fonction de la valeur de la variable frequence. Ainsi, plus le texte sera gros, plus nous saurons d'un coup d'oeil, que la valeur est répétée un grand nombre de fois. La propriété dérivée de Font qui permet d'affecter une taille de police est la propriété Size.

• Dans le If, sous le traitement du Bold, ajouter la ligne suivante :

cellule.Font.Size = 11 + frequence * 2

Nous multiplions volontairement la valeur de la variable frequence (frequence * 2) pour grossir l'effet. Nous ajoutons cette valeur à la taille de police par défaut (11) dont nous affectons le résultat à la taille de police de la cellule en cours (cellule.Font.Size =).

• De retour sur la feuille, après avoir sélectionné les valeurs numériques, cliquer sur le bouton.

D'un seul coup d'oeil vous remarquez que les valeurs 36 et 0 sont les plus fréquentes. Suivent les valeurs 6 et 13. Par contre, très rapidement vous constatez que les valeurs 14, 24 et 42 par exemple ne sont pas répétées. Voilà tout l'intérêt des boucles. Avec un code léger et optimisé, vous obtenez un résultat puissant et intéressant.