Utilisation de For ... EachNous allons parler de collection. Par exemple, chaque Classeur contient un certain nombre de feuilles. On dit que chaque classeur possède une COLLECTION de feuilles.
Pour pouvoir suivre et comprendre cet exemple, vous devez avoir sous les yeux une nouvelle feuille Excel composée de 3 onglets que vous nommerez : Un, Deux et Trois.
Allez ensuite dans un nouveau Module VBA, et créez une nouvelle procédure appelée Sub ComptageFeuille() :
Sub Comptagefeuille()
MsgBox Worksheets(1).Name
End Sub
Nous avons une série de feuilles (Worksheet). Nous en avons exactement 3. Cette série de feuilles s'appelle une Collection de feuilles.
Sub Comptagefeuille()
MsgBox Worksheets(1).Name
MsgBox Worksheets(2).Name
MsgBox Worksheets(3).Name
MsgBox Worksheets(4).Name ' Renvoie une erreur *
End Sub
* L'indice n'appartient pas à la collection
Maintenant, nous allons passer à un petit rappel de l'utilisation du For...To...Next. Créez une nouvelle procédure comme ceci :
Sub LeForToNext()
Dim Banane
For Banane = 1 To 4
MsgBox "Ce message apparaît 4 fois"
Next
End Sub
Exécutez-le : Vous devriez avoir à cliquer 4 fois sur OK. ca marche ?
La variable Banane prend la valeur 1 puis 2 puis 3 puis 4 à chaque "tour de boucle". Ainsi donc, si vous écrivez :
Sub LeForToNext()
Dim Banane
For Banane = 1 To 4
MsgBox Banane
Next
End Sub
Vous allez aveoir un message avec juste 1 puis 2 puis 3 puis 4. Essayez !
Ce qui fait que si on améliore encore notre programme, comme ceci :
Sub LeForToNext()
Dim Banane
For Banane = 1 To 4
MsgBox Worksheets(Banane).Name
Next
End Sub
Il va nous afficher les noms de nos 3 onglets (Un, Deux, Trois). Essayez. Ca marche ?
Bien. Maintenant, on ne sait pas forcément qu'il y a 3 onglets... (Bon, ici, oui, on le sait, mais admettons que nosu exécutions cette procédure dans un classeur dont nous ignorons le nombre de feuilles existantes). Eh bien dans ce cas, nous allons utiliser la propriété Count, de la collection Worksheets : Créez une procédure ComptageDesFeuilles, comme ceci :
Sub ComptageDesFeuilles()
MsgBox Worksheets.Count
End Sub
Il devrait vous afficher 3. C'est exact ?
Et maintenant, nous allons afficher les noms des feuilles les unes après les autres, quel qu'en soit le nombre (Souris est une variable tout droit sortie de mon imagination):
Sub ComptageDesFeuilles()
Dim Souris
For Souris = 1 To Worksheets.Count
MsgBox Worksheets(Souris).Name
Next
End Sub
Testez. Ca marche ? Il vous affiche bien les noms des 3 feuilles l'une après l'autre ? Essayez d'ajouter une feuille que vous appellerez "Quatre", et restestez la procédure sans rien changer. Ca marche toujours ?
Cette précédure pourrait rester tellequelle, car la suite de cette page n'aura d'autre but que de vous montrer une autre façon d'écrire la même chose de manière qui pourrait être considérée comme plus élégante.
Nous allons complètement la réécrire ce cette façon : Commencez par créer une variable de type objet :
Sub ComptageDesFeuilles2()
Dim JolieFeuille As Object
End Sub
A présent, nous allons parcourir chacune des feuilles (For Each = Pour Chaque)
Sub ComptageDesFeuilles2()
Dim JolieFeuille As Object
For Each JolieFeuille In Worksheets
MsgBox JolieFeuille.Name
Next
End Sub
En bref, nous avons créé une variable-objet appelé JolieFeuille (Le nom importe peu : C'aurait pu être Cafetiere, ou Herisson). Maintenant, nous avons une collection de feuilles appelée Worksheets, qui contient l'ensemble de toutes les feuilles du classeur actif : Worksheets(1) = La première feuille, Worksheets(2) = La 2ème feuille, etc.
Maintenant, plutôt que d'aller de 1 jusqu'à Worksheets.Count, nous allons parcourir les feuilles une par une, et à chaque passage, JolieFeuille sera égale à la feuille suivante : D'ailleurs, on n'a qu'à écrire JolieFeuille.Name pour que le nom change à chaque passage : Cet exemple est struictement identique au précédent (Worksheets.Count).
Cette manière d'écrire est plus élégante, et avec l'expérience, plus compréhensible que l'histoire du .Count.
Par exemple, admettons que nous voulions renommer toutes nos feuilles : a partir de maintenant, elles ne doivent plus s'appeller Un, Deux, Trois et Quatre, mais Un2002, Deux2002, Trois2002 et Quatre2002 : On ajoute donc 2002 à chaque feuille. On ferait ça comme ça :
Sub ComptageDesFeuilles2()
Dim JolieFeuille As Object
For Each JolieFeuille In Worksheets
JolieFeuille.Name = JolieFeuille.Name & "2002"
Next
End Sub
Essayez ! Ca marche ? Les feuilles sont renommées ? Attention : Ne l'exécutez pas 2 fois de suite, sinon vous aurez des feuilles nommées "Un20022002", "Deux20022002" etc. Evidemment !
Grâce à cette manière d'écrire, on peut facilement parcourir les feuilles afin de supprimer toutes les feulles qui commencnt par X, par exemple.
Pour essayer, il faut que vous renommiez vos 4 onglets comme suit :
XImpots, Impots, XDepense, XCinema
Il va donc falloiir effacer 3 onglets sur quatre : Ceux qui commencent par X. Créez cette pocédure :
Sub SuppressionX()
Dim JolieFeuille As Object
For Each JolieFeuille In Worksheets
If Left(JolieFeuille.Name, 1) = "X" Then
JolieFeuille.Delete
End If
Next
End Sub
La fonction Left permet de récupérer les 1 caractères de gauche d'une chaîne de caractère. En l'occurrence les 1 caractères de gauche de JolieFeuille.Name sont souvent : X
Mais alors attention : Nous allons maintenant effectuer exactement la même manoeuvre avec la méthode Count. Avant de continuer, j'aimerais que votre feuille Excel contiennennt les feuilles nommées de la manière suivante :
X1, 2, X3, X4
Et nous allons essayer de les supprimer mais avec la méthode de tout à l'heure :
Sub SuppressionX2()
Dim Melon
For Melon = 1 To Worksheets.Count
If Left(Worksheets(Melon).Name, 1) = "X" Then
Worksheets(Melon).Delete
End If
Next
End Sub
Etudiez-bien ce code ! Il n'y a rien qui vous parait bizarre ? Lisez-le bien ! Il n'a pas d'erreur de syntaxe, mais un problème de logique. Vous avez trouvé ?
Eh bien, il y a donc une variable (Melon) qui va parcourir les feuilles de la numéro 1 jusqu'à la numéro Worksheets.count : Ca, ça fonctionnait vachement bien dans l'exemple précédent ou il n'y avait pas d'interaction avec les feuilles : Juste un affichage !
Mais cette fois, c'est différent : En effet, au début de la procédure, il y a effectivement 4 feuilles (WorkSheets.Count = 4), mais dès qu'on va en effacer une avec Worksheets(Melon).Delete, il n'y aura plus 4 feuilles, mais plus que 3 !
Et le Melon = 1 to Worksheets.Count ne va pas se mettre à jour, et va tenter tout de même d'aller jusqu'à 4 ! Ce qui va donner une erreur de compilation : "L'indice n'appartient pas à la sélection" : Eh oui, il va trop loin !
Vous pouvez essayer !
Cette raison est l'argument principal pour utiliser de préférence le For Each, qui, lui, fonctionne sans erreur.
Maintenant, j'aimerais apporter juste une petite précision à la procédure SupressionX :
Sub SuppressionX()
Dim JolieFeuille As Object
For Each JolieFeuille In Worksheets
If Left(JolieFeuille.Name, 1) = "X" Then
JolieFeuille.Delete
End If
Next
End Sub
Nous av ons déclaré JolieFeuille comme étant "As Object". C'est à dire que cette variable est parée à recevoir n'importe quel type d'objet : Une feuille (WorkSheet), Un classeur entier (WorkBook), ou autre. C'est seulement lors de la ligne "For Each JolieFeuille In Worksheets" que le programme VBA se rend compte qu'i s'agit d'une feuille, car la collection WorkSheets contient des WorkSheet, c'est tout.
Ce qui fait que c'est seulement à l'exécution que le programme sait de quoi il s'agit. Bon, en soi, ce n'est pas grave, mais l'assistant de saisie ne vous propose pas les méthodes et propriétés correspondantes. Je m'explique :
Quand vous écrivez JolieFeuille.Delete,
vous écrivez d'abord JolieFeuille, puis vous ajoutez un point : JolieFeuille.
et dès que vous écrivez le point, vous avez normalement liste
de tout ce que vous pouvez écrire, comme ceci : 
.
Mais dans notre cas, vous n'avez pas cet assistant. Essayez : Effacez la ligne
JolieFeuille.Delete, et réécrivez-là.
C'est dû au fait que lors le l'écriture du code VBA, L'éditeur (là ou vous évrivez votre code VBA) ne sait pas encore qu'il s'agit d'un objet de type WorkSheet.
Alors, justement, nous allons changer l'initialisation de la variable de
Dim JolieFeuille As Object
en
Dim JolieFeuille As Worksheet
Faites-le. Cette fois, l'éditeur sait qu'il s'agit d'un WorkSheet. Réeffacez la ligne JolieFeuille.Delete, et Réécrivez-là : JolieFeuille. et cette fois, vous avez la liste des méthodes et propriétés utilisables, dont Delete dans le tas.