Il est de bon ton de se souhaiter la bonne année. Mais vous, et vous seul, pourrez faire en sorte que cette année soit bonne, meilleure que celle qui vient de s'écouler. Apprenez à ne compter que sur vous, car personne n'est plus qualifié que vous-même pour bâtir, réparer ou améliorer votre propre vie. Personne ne fera les choses à votre place. D'ailleurs, tout ce que les autres peuvent faire, c'est souhaiter que vous le fassiez. Et ne croyez pas que tout ceux qui vous entourent vous apporteront des solutions : certains font juste partie de vos problèmes. Transformez vos résolutions en actes, et dans douze mois, retournez-vous et souriez-vous fièrement : C'était long. C'était difficile. Mais ça y est : 2017 était une bonne année, merci Moi.

Utilisation de For ... Each

Nous 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

Rappel du For ... To ... Next

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.