Création d'un rapport de nom avec code VBA

Si vous utilisez beaucoup de cellules et plages nommées dans votre classeur, vous pourriez être surpris de découvrir que Excel ne fournit aucun moyen de lister les détails pour chaque nom. Informations pratiques apparaît dans la boîte de dialogue Nom de gestionnaire, mais il n'y a aucun moyen d'afficher ces informations d'une manière qui peut être imprimé.

Le code VBA offert ici génère un rapport utile qui décrit les noms définis dans tout classeur.

Exemple Nom Rapport

Ici, vous voyez un exemple de rapport Nom.

Le rapport, qui est créé sur une nouvelle feuille de calcul, comprend les informations suivantes pour chaque nom:

• Nom: Le nom.

• 
  
  
  
  

  Fait référence à: La définition pour le nom. Habituellement, ce sera une cellule ou une plage, mais un nom peut également définir la formule.

• Cellules: Le nombre de cellules contenues dans la plage nommée. Pour les formules nommées, ce champ affiche # N / A.

• Champ d'application: La portée de ce nom - soit le classeur, ou le nom de la feuille de calcul spécifique sur lequel le nom est valide.

• Invisible: Vrai si le nom est caché. Noms cachés sont créés par certains add-ins (comme Solver), et ne figurent pas dans la boîte de dialogue Nom du Manager.

• Erreur: Vrai si le nom contient une référence erronée.

• Lien: Un lien hypertexte qui, lorsqu'on clique dessus, active la plage nommée. Seuls les noms qui se réfèrent à des cellules ou des gammes comprennent un lien.

• Commentaire: Le commentaire le nom, le cas échéant.

Le code VBA

Pour utiliser ce code, appuyez sur Alt + F11 pour activer Visual Basic Editor. Ensuite, choisissez Insertion - Module pour insérer un nouveau module VBA. Copiez le code et collez-le dans le nouveau module.

Sous GenerateNameReport () Génère un rapport pour tous les noms dans le classeur »(Ne comprend pas les noms de table) n Dim Comme NameDim Row Comme LongDim CellCount Comme sortie Variant 'si aucune namesIf ActiveWorkbook.Names.Count = 0 ThenMsgBox “ Le classeur actif n'a pas de noms définis ”. Quitter Quitter SubEnd Si «si classeur est protectedIf ActiveWorkbook.ProtectStructure ThenMsgBox “ Une nouvelle feuille ne peut pas être ajoutée car le classeur est protégé ” sortie SubEnd Si 'insérer une nouvelle feuille pour le reportActiveWorkbook.Worksheets.AddActiveSheet.Move Après:. = Sheets (ActiveWorkbook.Sheets.Count) ActiveWindow.DisplayGridlines = Faux 'Ajouter la première ligne de titleRange (“ A1: H1 ”) MergeWith Range (“ A1 ”) Valeur =.. “ Nom du rapport pour: “ ActiveWorkbook.Name.Font.Size = 14.Font.Bold = True.HorizontalAlignment = xlCenterEnd Avec 'Ajouter seconde ligne de titleRange (“ A2: H2 ”) MergeWith Range (“ A2 ”).. Valeur = “ Généré “ Now.HorizontalAlignment = xlCenterEnd Avec 'Ajouter le headersRange (“ A4: H4 ”) = Array (“ ” ;, Nom “ ” ;, RefersTo “ ” ;, cellules _ “ ” ;, Portée “ ” ;, Invisible “ ” ;, erreur “ ” ;, Lien “ Commentaire ”) 'boucle à travers le Error Resume namesRow = 4Sur NextFor Chaque n Dans ActiveWorkbook.NamesRow = Row + 1'Column A: Comme nameif n.Name “! * * ” ThenCells (Row, 1) = Split (n.Name, ! “ ”) (1) «Retirer la feuille nameElseCells (Row, 1) = n.NameEnd If'Column B: RefersToCells (Row, 2) = “ '“ n.RefersTo'Column C: Nombre de cellsCellCount = CVErr (xlErrNA) «valeur de retour pour named formulaCellCount = n.RefersToRange.CountLargeCells (Ligne 3) = CellCount« Colonne D: Comme ScopeIf n.Name “! * * ” ThenCells (Row, 4) = Split (n.Name, “! ”) (0) »Extrait nameCells (Row, 4) = REPLACE (cellules (Row, 4), “ '# 147 ;, “ “) 'éliminer apostrophesElseCells (ligne 4) = “ Cahier de travail ” Fin If'Column E: statusCells confidentiels (de rang, 5) = Non n.Visible'Column F: Erroroneous nameCells (Ligne 6) = Comme n.RefersTo “ * [#] réf * de ” 'Colonne G: Non HyperlinkIf Application.IsNA (cellules (Row, 3)) ThenActiveSheet.Hyperlinks.Add _Anchor: = cellules (ligne 7), _address: = # 147 ; # 147 ;, _SubAddress: = n.Name, _TextToDisplay: = n.NameEnd If'Column H: CommentCells (ligne 8) = n.CommentNext n 'Convertir à un tableActiveSheet.ListObjects.Add _SourceType: = xlSrcRange, _Source :. = Range (“ A4 ”) CurrentRegion 'Ajustez les widthsColumns de colonne (“ A: H ”) Sous EntireColumn.AutoFitEnd.

Génération d'un rapport

Exécutez le GenerateNameReport procédure, et le rapport est généré sur une nouvelle feuille de calcul dans le classeur actif. Le code n'a pas à être dans le classeur qui contient les noms pour le rapport.

Si vous trouvez ce code utile, vous pouvez stocker dans votre classeur de macros personnelles, ou de créer un add-in.