Lister ou supprimer tous les noms de plages

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


Sujets que vous pourriez aussi aimer :


Lister ou supprimer les noms

Le VBA Excel est doué de toutes les prouesses. Grâce à lui, nous allons voir comment manipuler les noms qui ont été donnés aux plages de cellules sur un classeur tout entier. L'idée est de pouvoir par exemple nettoyer rapidement une feuille ou même un classeur complet des nombreux noms obsolètes qu'il renferme.

Classeur Excel à télécharger
Pour la découverte de cette nouvelle technique, nous suggérons d'appuyer l'étude sur un classeur Excel exploitant quelques plages nommées à analyser.

• Télécharger le classeur fonction-vba-noms-des-feuilles.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• Dans le menu contextuel, cliquer sur la rubrique Propriétés,
• En bas de la boîte de dialogue, cocher la case Débloquer puis valider par Ok,
• Dès lors, double cliquer sur le fichier pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous découvrons un classeur fait de trois feuilles. C'est la dernière qui est active par défaut. Elle se nomme nomsFeuilles. Elle présente un tableau vide en attente des renseignements sur les plages nommées enregistrées dans ce classeur. Pour manipuler ces noms de plages, trois boutons se suggèrent sur la droite de la feuille. Ils sont tous associés à une procédure événementielle. Mais chacune est fort naturellement vierge de code à ce stade.



Si vous déployez la zone Nom en haut à gauche de la feuille Excel, vous constatez que quelques plages nommées ont été définies. Elles sont réparties entre les deux premières feuilles de ce classeur.

La procédure pour lister les noms
C'est la vocation du premier des trois boutons de les débusquer. Dans le tableau vide, il doit énumérer le nom de chaque plage, avec sa feuille d'appartenance et sa zone d'influence (coordonnées).

• Réaliser le raccourci ALT + F11 pour basculer dans l'éditeur VBA Excel,

Dans un module nommé module1, nous y trouvons en effet les trois procédures :

Sub listerNoms()

End Sub

Sub supprNoms()

End Sub

Sub supprNomsFeuille()

End Sub

Et comme nous l'avons dit, les associations avec les boutons sont déjà réalisées, par Clic droit / Affecter une macro.

Les variables
Pour piloter ces plages, nous avons besoin de quelques variables. La première d'entre elles doit représenter une plage nommée au sens large.

• Dans les bornes de la première procédure, ajouter les déclarations et affectations suivantes :

...
Dim nomP As Name
Dim i As Byte: Dim pos As Byte

i = 4: Range("C4:E100").Value = ""
...

Nous créons l'objet nomP que nous typons comme un Name, soit un nom de plage de cellules. Les variables entières sont destinées à suivre les cellules de la feuille pour les renseigner et à déceler la position d'un caractère spécial dans les plages nommées pour isoler les noms des feuilles. Ensuite, nous partons de la ligne 4 comme l'impose le tableau (i = 4). Puis, nous effaçons, sur une plage suffisamment haute, les potentielles précédentes extractions de noms.

Parcourir toutes les plages nommées
Comme nous l'avons déjà fait pour analyser tous les contrôles d'un formulaire, toutes les feuilles d'un classeur, toutes les cellules d'une plage sélectionnée, c'est une boucle For Each que nous devons dégainer une fois de plus pour parcourir la collection des plages nommées dans ce classeur.

• A la suite du code VBA, créer la boucle For Each suivante :

...
For Each nomP In ActiveWorkbook.Names
pos = InStr(1, nomP, "!")

If pos > 0 Then

End If
Next nomP
...

Grâce à l'objet nomP que nous avons déclaré, nous parcourons toutes les plages nommées, dans la collection des noms enregistrés dans ce classeur (ActiveWorkbook.Names). Pour chaque plage passée en revue, nous cherchons la position du point d'exclamation, grâce à la fonction InStr. En effet, ce symbole suffixe chaque nom de feuille. C'est ce séparateur qui doit nous permettre d'isoler d'une part le nom de la feuille et d'autre part les coordonnées de la plage pour le nom en cours d'analyse. Si ce point d'exclamation est décelé (pos > 0), alors (Then), nous poursuivons l'analyse de ce nom de plage pour le décortiquer.

Lister les plages nommées
En travaillant sur l'objet nomP représentant chaque plage tour à tour, nous allons maintenant pouvoir l'exploiter pour lui extorquer les renseignements que le tableau de la dernière feuille attend.

• Dans l'instruction conditionnelle, ajouter les lignes VBA suivantes :

...
Cells(i, 3).Value = nomP.Name
Cells(i, 4).Value = Replace(Left(nomP, pos - 1), "=", "")
Cells(i, 5).Value = Replace(Mid(nomP, pos + 1), "$", "")
i = i + 1
...

La propriété Name de l'objet nomP permet de connaître le nom de la plage en cours d'analyse. Grâce aux fonctions Left et Mid exploitant la position (pos) du point d'exclamation, nous prélevons ce qui est situé avant (Le nom de la feuille) et après (Les coordonnées de la plage). La fonction Replace est utilisée pour effacer quelques caractères résiduels.

• Pour finir, il peut paraître opportun de détruire l'objet une fois la boucle terminée,

...
Next nomP

Set nomP = Nothing

End Sub
...

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Dès lors, cliquer sur le premier bouton intitulé Lister Noms,

Nous obtenons bien les noms des plages présentes dans ce classeur avec leurs feuilles d'appartenance mais aussi leurs coordonnées.

Et si vous ouvrez le gestionnaire de noms depuis le ruban Formules, vous constatez que ces résultats sont parfaitement exacts.



Supprimer les noms d'une feuille
Maintenant que nous savons parcourir toutes les plages nommées d'un classeur, il apparaît très simple d'implémenter les deux autres procédures événementielles.

• Dans la procédure supprNomsFeuille, ajouter le code VBA suivant :

Sub supprNomsFeuille()
On Error Resume Next
Dim nomP As Name: Dim feuille As String

feuille = InputBox("Quel est le nom de la feuille qui doit être purgée?")
For Each nomP In ActiveWorkbook.Names
If InStr(1, UCase(nomP), UCase("=" & feuille & "!")) > 0 Then nomP.Delete
Next nomP
End Sub

Nous demandons tout d'abord à l'utilisateur d'inscrire le nom de la feuille (feuille = InputBox) à purger de ses noms de plage. Puis, nous parcourons tous les noms du classeur (For Each nomP). Si la fonction InStr décèle la position du nom de la feuille dans la plage en cours, nous exploitons la méthode Delete sur son objet nomP pour le supprimer.

Si vous enregistrez les modifications, que vous cliquez sur le bouton intitulé Suppr 1 Feuil, que vous mentionnez la feuille Equipes dans la boîte de dialogue et que vous cliquez sur le bouton Lister Noms à l'issue du traitement, vous constatez qu'il ne subsiste plus que les plages de la première feuille nommée quelTableau.

Supprimer toutes les plages nommées
Forcément, pour supprimer tous les noms de plage, le code VBA est encore plus simple puisqu'il n'y a aucune exception à gérer dans la boucle.

• Dans la procédure supprNoms, ajouter le code VBA suivant :

Sub supprNoms()
On Error Resume Next
Dim nomP As Name

For Each nomP In ActiveWorkbook.Names
nomP.Delete
Next nomP
End Sub