La boucle For Each … Next

Cette boucle permet de parcourir les éléments d’une collection. Comme nous allons le voir, elle implique une certaine compréhension du modèle Excel, c’est-à-dire de la façon dont s’articulent les objets qui composent l’application. Par exemple, la procédure suivante, qui cherche à afficher successivement le nom de chaque feuille de calcul (objet Worksheet) du classeur actif (ActiveWorkbook),

Sub Test1()
Dim f As Worksheet
    For Each f In ActiveWorkbook
        MsgBox f.Name
    Next f
End Sub

provoque immanquablement le message d’erreur reproduit en figure 1 :

Fig. 1 - Message d’erreur obtenu en lançant la procédure “Test1”

Le message d’erreur offre 3 options :

“Fin” provoque l’arrêt de l’exécution du programme, sans information complémentaire.
“Débogage” interrompt l’exécution et fait ressortir en jaune vif la ligne responsable de l'erreur. La couleur jaune pour signaler les erreurs est l’option par défaut. Elle peut avoir été modifiée à l’aide des préférences (menu “Outils” de l’environnement VBA).
“Aide” affiche des informations sur l’erreur rencontrée (ici, erreur d’exécution 438 et ses causes possibles)

Si on clique sur le bouton “Débogage”, on voit que la ligne responsable de l’erreur est :

For Each f in ActiveWorkbook (Figure 2).

Fig. 2 - Mise en évidence de la ligne ayant provoqué l’erreur

Intuitivement, l’erreur peut surprendre, car la caractéristique essentielle d’un classeur est de regrouper des feuilles. Il paraît donc naturel de définir une variable de type objet, en précisant Worksheet comme nature de l’objet, puis d’utiliser cette variable pour parcourir les feuilles de calcul d’un classeur (objet Workbook). C’est peut-être intuitif, mais c’est faux !

Ouvrons l’explorateur d’objets (figure 3) pour examiner les membres (propriétés, événements et méthodes) de la classe Workbook, on y trouve une propriété Worksheets. Celle-ci renvoie un objet Worksheets.

Fig. 3 - Un objet Workbook possède parmi ses propriétés une collection Worksheets

Ceci explique l’erreur signalée en figure 1 : pour accéder aux feuilles de calcul du classeur, il ne faut pas essayer de parcourir un objet Workbook, qui n’est pas une collection, mais se référer à la collection Worksheets (procédure Test2).

Sub Test2()
Dim f As Worksheet
    For Each f In ActiveWorkbook.WorkSheets
        MsgBox f.Name
    Next f
End Sub

Il existe également une collection Sheets, qui regroupe les feuilles de calcul (Worksheet) et les feuilles graphiques (Chart). C’est pourquoi, dans un certain nombre de cas, la procédure Test3 peut également fonctionner.

Sub Test3()
Dim f As Worksheet
    For Each f In ActiveWorkbook.Sheets
        MsgBox f.Name
    Next f
End Sub

Si le classeur actif (désigné par ActiveWorkbook) ne contient que des feuilles de calcul, les procédures Test2 et Test3 seront équivalentes. Cependant, introduire une distorsion entre le type choisi pour parcourir une collection et cette dernière n’est pas une bonne idée. En effet, Test3 “plantera”, et le message de la figure 4 sera affiché si le classeur contient au moins une feuille graphique.

Fig. 4 - Message affiché si la procédure “Test3” est exécutée
alors que le classeur actif contient une feuille graphique

En effet, l’emploi de la boucle

For Each f … Next f

revient à dire que à chaque passage de la boucle, la variable définie pour explorer la collection, ici “f”, va se référer à un des objets la constituant. Si cette variable, a été définie comme étant de type Worksheet, elle ne peut se référer à un objet de type Chart, ce qui conduit à l’erreur “Type incompatible” (figure 4).

Nous venons de voir que la collection Sheets regroupe des objets de 2 types différents (Worksheet et Chart). Pour cette raison, il n’existe pas de type Sheet. Par conséquent, si l’on désire parcourir toutes les feuilles d’une collection Sheets, il faut définir “f” sans préciser un type de données, ce qui revient à lui attribuer le type Variant.

Sub Test4()
Dim f
    For Each f In ActiveWorkbook.Sheets
        MsgBox f.Name
    Next f
End Sub

Cependant plantage si feuille n’est pas une feuille de calcul : feuille graphique, macro xl4, …,

Par exemple dans au moins 80% des cas, la procédure suivante va fonctionner Mise à jour nom local pour paye, concerne les 12 mois, pas la récap

pas de test ds boucle, suppression nom inutile créé ds récap après boucle

2ème collection, Names de chaque Worksheet