Parcourir les plages nommées, Excel VBA

Parcourir les plages nommées, Excel VBA

Voici une explication détaillée avec un exemple de code VBA pour parcourir les plages nommées dans Excel.
Comprendre les Plages Nommées dans Excel
Les plages nommées dans Excel sont des références à une plage spécifique de cellules qui ont été attribuées à un nom. Cela permet de référencer plus facilement des plages de cellules plutôt que d’utiliser des références classiques comme A1:C10. Par exemple, si vous avez une plage de cellules contenant des données de ventes, vous pouvez la nommer Ventes et l’utiliser dans des formules ou du code en utilisant ce nom au lieu d’une plage comme B2:B100.
Pourquoi parcourir les plages nommées avec VBA ?
Il y a plusieurs raisons pour lesquelles vous pourriez avoir besoin de parcourir les plages nommées dans Excel VBA :
1. Traitement dynamique des données : Si vous avez plusieurs plages nommées qui changent dynamiquement, il est plus simple de les parcourir dans une boucle et de leur appliquer des actions (par exemple, effectuer des sommes, appliquer des formules, formater) sans avoir à spécifier chaque plage manuellement.
2. Automatisation : En automatisant le processus d’accès à chaque plage nommée, vous gagnez du temps et vous réduisez les risques d’erreurs.
Parcourir les Plages Nommées dans VBA
Voici un exemple de code VBA qui parcourt toutes les plages nommées d’un classeur et effectue des opérations sur celles-ci (par exemple, afficher l’adresse de la plage dans la fenêtre immédiate). Le code gère également les cas où une plage nommée réfère à une formule ou à une plage de cellules invalide.
Explication étape par étape
1. Accéder aux Plages Nommées :
Dans VBA, vous pouvez accéder à la collection Names, qui contient toutes les plages nommées d’un classeur. Chaque élément de cette collection est un objet qui possède des propriétés telles que Name (le nom de la plage) et RefersTo (l’adresse ou la formule qui définit la plage).
2. Boucle à travers la collection Names :
Nous parcourons les éléments de la collection Names avec une boucle For Each, ce qui nous permet d’interagir avec chaque plage nommée.
3. Vérification des plages nommées valides :
Nous vérifions si la plage nommée fait référence à une plage valide. Si ce n’est pas le cas (par exemple, si c’est une formule ou une plage invalide), le code la saute ou la gère de manière appropriée.
4. Effectuer des actions sur les plages nommées :
À l’intérieur de la boucle, vous pouvez définir n’importe quelle action que vous souhaitez effectuer sur chaque plage nommée, comme modifier des valeurs, appliquer des formats, ou effectuer des calculs.
Exemple de Code VBA

Sub ParcourirPlagesNommees()
    Dim plageNommée As Name
    Dim plageRef As Range
    Dim ws As Worksheet
    ' Parcours de chaque plage nommée dans le classeur
    For Each plageNommée In ThisWorkbook.Names
        ' Vérifie si la plage nommée fait référence à une plage valide
        On Error Resume Next
        Set plageRef = Range(plageNommée.RefersTo)
        On Error GoTo 0
        ' Si la référence à la plage est valide
        If Not plageRef Is Nothing Then
            ' Affiche le nom et l'adresse de la plage dans la fenêtre immédiate (Ctrl+G)
            Debug.Print "Plage Nommée : " & plageNommée.Name & " fait référence à la plage : " & plageRef.Address
            ' Exemple : Effectuer une action sur la plage (par exemple, calculer la somme)
            ' Vous pouvez remplacer cela par toute autre action que vous souhaitez sur la plage nommée
            Debug.Print "Somme de " & plageNommée.Name & " : " & Application.WorksheetFunction.Sum(plageRef)
            ' Exemple : Changer la couleur de fond de la plage en jaune clair
            plageRef.Interior.Color = RGB(255, 255, 153) ' Jaune clair
        End If
        ' Réinitialiser la référence pour l'itération suivante
        Set plageRef = Nothing
    Next plageNommée
End Sub

Explication du Code

• Déclaration des variables :
• plageNommée : Une variable qui représente chaque plage nommée dans la collection Names.
• plageRef : Un objet Range qui contient la référence des cellules auxquelles la plage nommée fait référence.
• ws : Cette variable est déclarée, mais elle n’est pas utilisée dans cet exemple. Vous pouvez l’utiliser si vous souhaitez spécifier une feuille de calcul particulière où se trouvent les plages nommées.
• Boucle For Each :
• For Each plageNommée In ThisWorkbook.Names : La boucle parcourt chaque plage nommée dans le classeur.
• Gestion des erreurs (On Error Resume Next) :
• On Error Resume Next permet d’éviter que le code ne s’arrête en cas d’erreur si une plage nommée ne fait pas référence à une plage valide (par exemple, si elle fait référence à une formule ou à une plage externe). Si la référence est valide, elle est assignée à la variable plageRef; sinon, le code continue avec la plage suivante.
• Actions sur la Plage Nommée :
• Nous affichons le nom et l’adresse de la plage nommée dans la fenêtre immédiate avec Debug.Print.
• Nous effectuons une action exemple : calculer la somme des valeurs dans la plage nommée avec Application.WorksheetFunction.Sum().
• Nous changeons également la couleur de fond des cellules de la plage en jaune clair avec plageRef.Interior.Color = RGB(255, 255, 153).

Points à Noter
1. Portée des plages nommées :
Les plages nommées peuvent avoir une portée soit au niveau du classeur (disponible dans tout le classeur), soit au niveau de la feuille de calcul (disponible uniquement dans une feuille spécifique). Ce code parcourt toutes les plages nommées, peu importe leur portée.
2. Gestion des erreurs :
La ligne On Error Resume Next assure que si une plage nommée ne fait pas référence à une plage valide ou s’il y a une autre erreur, la boucle continue avec la plage suivante. Après avoir vérifié la plage, nous réinitialisons la référence avec Set plageRef = Nothing.
3. Personnalisation :
Vous pouvez personnaliser l’action effectuée sur chaque plage nommée. Par exemple, au lieu de calculer la somme, vous pourriez utiliser d’autres fonctions comme Average, Count, ou effectuer des actions plus complexes comme copier des données.
Conclusion
Cette approche est très pratique pour automatiser des tâches qui doivent être effectuées sur plusieurs plages nommées. Elle est efficace pour les classeurs volumineux avec des plages nommées dynamiques. Le code peut être étendu pour effectuer toute sorte d’opération, depuis la manipulation de données jusqu’à l’application de formats.