Creació dun informe de noms amb codi VBA

Si utilitzeu moltes cel·les i intervals amb nom al vostre llibre de treball, potser us sorprendrà descobrir que Excel no ofereix cap manera d'enumerar els detalls de cada nom. Apareix informació útil al quadre de diàleg Gestor de noms, però no hi ha manera de mostrar aquesta informació de manera que es pugui imprimir.

El codi VBA que s'ofereix aquí genera un informe útil que descriu els noms definits en qualsevol llibre de treball.

Exemple d'informe de noms

Aquí veieu un exemple d'informe de noms.

L'informe, que es crea en un full de treball nou, inclou la informació següent per a cada nom:

• Nom: El nom.

• RefersTo: la definició del nom. Normalment, serà una cel·la o un interval, però un nom també pot definir una fórmula.

• Cel·les: el nombre de cel·les contingudes a l'interval anomenat. Per a les fórmules amb nom, aquest camp mostra #N/A.

• Àmbit: l'abast del nom: Llibre de treball o el nom del full de treball específic en què el nom és vàlid.

• Ocult: és cert si el nom està amagat. Els noms ocults els creen alguns complements (com ara Solver) i no apareixen al quadre de diàleg Gestor de noms.

• Error: és cert si el nom conté una referència errònia.

• Enllaç: un hiperenllaç que, quan es fa clic, activa l'interval anomenat. Només els noms que fan referència a cel·les o intervals inclouen un enllaç.

• Comentari: el comentari del nom, si n'hi ha.

El codi VBA

Per utilitzar aquest codi, premeu Alt+F11 per activar Visual Basic Editor. A continuació, trieu Insereix → Mòdul per inserir un mòdul VBA nou. Copieu el codi i enganxeu-lo al mòdul nou.

Sub GenerateNameReport()
' Genera un informe per a tots els noms del llibre de treball
' (No inclou els noms de les taules)
  Dim n com a nom
  Dim Row As Long
  Dim CellCount com a variant
' Surt si no hi ha noms
  Si ActiveWorkbook.Names.Count = 0, aleshores
    MsgBox "El llibre de treball actiu no té noms definits".
    Sortir de Sub
  Acaba si
' Surt si el llibre de treball està protegit
  Si ActiveWorkbook.ProtectStructure Aleshores
    MsgBox "No es pot afegir un full nou perquè el llibre de treball està protegit".
    Sortir de Sub
  Acaba si
' Insereix un nou full per a l'informe
  ActiveWorkbook.Fulls de treball.Afegir
  ActiveSheet.Move After:=Fulls (ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = Fals
' Afegiu la primera línia del títol
  Interval ("A1:H1"). Fusionar
  Amb rang ("A1")
    .Value = "Informe de nom per a: " & ActiveWorkbook.Name
    .Font.Size = 14
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
  Acaba amb
' Afegiu la segona línia del títol
  Interval ("A2:H2"). Fusionar
  Amb rang ("A2")
    .Valor = "Generat" i ara
    .HorizontalAlignment = xlCenter
  Acaba amb
' Afegiu les capçaleres
  Interval ("A4:H4") = Matriu ("Nom", "RefersA", "Cèl·lules", _
    "Àmbit", "Ocult", "Error", "Enllaç", "Comentar")
' Recorre els noms
  Fila = 4
  En cas d'error Reprèn Següent
  Per a cada n a ActiveWorkbook.Names
    Fila = Fila + 1
    'Columna A: Nom
    Si n.Nom Com "*!*" Aleshores
      Cel·les(Fila, 1) = Dividir(n.Nom, “!”)(1) ' Elimina el nom del full
    Altrament
      Cel·les(Fila, 1) = n.Nom
    Acaba si
    'Columna B: es refereix a
    Cel·les (Fila, 2) = "'" & n.RefersTo
    'Columna C: nombre de cel·les
    CellCount = CVERErr(xlErrNA) ' Valor de retorn per a la fórmula anomenada
    CellCount = n.RefersToRange.CountLarge
    Cel·les (fila, 3) = CellCount
    ' Columna D: Àmbit
    Si n.Nom Com "*!*" Aleshores
      Cel·les (Fila, 4) = Dividir (n.Nom, “!”) (0) ' nom del full d'extracció
      Cel·les (Fila, 4) = Reemplaça (Cèl·lules (Fila, 4), “'“, ““) 'elimina apòstrofes
    Altrament
      Cel·les (fila, 4) = "Llibre de treball"
    Acaba si
    'Columna E: estat ocult
    Cel·les (Fila, 5) = No n.Visible
    'Columna F: Nom erroni
    Cel·les (fila, 6) = n. Es refereix a "*[#]REF!*"
    'Columna G: hiperenllaç
    Si no és l'aplicació.IsNA(Cèl·lules (fila, 3)) Aleshores
      ActiveSheet.Hyperlinks.Add _
        Àncora:=Cèl·lules (fila, 7), _
        Adreça:=““, _
        Subadreça:=n.Nom, _
        TextToDisplay:=n.Nom
    Acaba si
    'Columna H: Comentari
    Cel·les (fila, 8) = n.Comentari
  Següent n
' Converteix-lo en una taula
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Font:=Range(“A4”).CurrentRegion
' Ajusteu l'amplada de les columnes
  Columnes ("A:H").Columna sencera.Ajust automàtic
End Sub