Opprette en navnerapport med VBA-kode

Hvis du bruker mange navngitte celler og områder i arbeidsboken din, kan du bli overrasket over å oppdage at Excel ikke gir noen måte å liste opp detaljene for hvert navn. Nyttig informasjon vises i Name Manager-dialogboksen, men det er ingen måte å vise denne informasjonen på en måte som kan skrives ut.

VBA-koden som tilbys her genererer en nyttig rapport som beskriver navnene som er definert i en arbeidsbok.

Eksempel på navnerapport

Her ser du et eksempel på en navnerapport.

Rapporten, som er opprettet på et nytt regneark, inneholder følgende informasjon for hvert navn:

• Navn: Navnet.

• RefersTo: Definisjonen for navnet. Vanligvis vil dette være en celle eller et område, men et navn kan også definere formel.

• Celler: Antall celler i det navngitte området. For navngitte formler viser dette feltet #N/A.

• Omfang: Omfanget av navnet – enten arbeidsbok eller navnet på det spesifikke regnearket der navnet er gyldig.

• Skjult: Sant hvis navnet er skjult. Skjulte navn opprettes av enkelte tillegg (som Solver), og vises ikke i dialogboksen Navnebehandling.

• Feil: Sann hvis navnet inneholder en feilaktig referanse.

• Link: En hyperkobling som, når den klikkes, aktiverer det navngitte området. Bare navn som refererer til celler eller områder inkluderer en kobling.

• Kommentar: Kommentaren til navnet, hvis noen.

VBA-koden

For å bruke denne koden, trykk Alt+F11 for å aktivere Visual Basic Editor. Velg deretter Sett inn → Modul for å sette inn en ny VBA-modul. Kopier koden og lim den inn i den nye modulen.

Sub GenerateNameReport()
' Genererer en rapport for alle navnene i arbeidsboken
' (Inkluderer ikke tabellnavn)
  Dim n As Navn
  Dim rad så lenge
  Dim CellCount som variant
' Avslutt hvis ingen navn
  Hvis ActiveWorkbook.Names.Count = 0 Da
    MsgBox "Den aktive arbeidsboken har ingen definerte navn."
    Avslutt Sub
  Slutt om
' Avslutt hvis arbeidsboken er beskyttet
  Hvis ActiveWorkbook.ProtectStructure Da
    MsgBox "Et nytt ark kan ikke legges til fordi arbeidsboken er beskyttet."
    Avslutt Sub
  Slutt om
' Sett inn et nytt ark for rapporten
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = False
' Legg til første linje i tittelen
  Range(“A1:H1”). Slå sammen
  Med rekkevidde(“A1”)
    .Value = "Navnerapport for: " & ActiveWorkbook.Name
    .Skriftstørrelse = 14
    .Font.Fet = Sant
    .HorizontalAlignment = xlCenter
  Avslutt med
' Legg til andre linje i tittelen
  Range(“A2:H2”). Slå sammen
  Med rekkevidde(“A2”)
    .Verdi = "Generert" & nå
    .HorizontalAlignment = xlCenter
  Avslutt med
' Legg til overskriftene
  Range(“A4:H4”) = Array(“Navn”, “ReferererTil”, “Celler”, _
    "Omfang", "Skjult", "Feil", "Link", "Kommentar")
' Gå gjennom navnene
  Rad = 4
  Ved feil på Fortsett neste
  For hver n I ActiveWorkbook.Names
    Rad = Rad + 1
    'Kolonne A: Navn
    Hvis n.Name Liker "*!*" Da
      Cells(Row, 1) = Split(n.Name, “!”)(1) ' Fjern arknavn
    Ellers
      Cells(Row, 1) = n.Name
    Slutt om
    'Kolonne B: ReferererTil
    Cells(Row, 2) = “'“ & n.RefersTo
    'Kolonne C: Antall celler
    CellCount = CVErr(xlErrNA) ' Returner verdi for navngitt formel
    CellCount = n.RefersToRange.CountLarge
    Cells(Row, 3) = CellCount
    ' Kolonne D: Omfang
    Hvis n.Name Liker "*!*" Da
      Cells(Row, 4) = Split(n.Name, “!”)(0) ' ekstrakt arknavn
      Cells(Row, 4) = Replace(Cells(Row, 4), “'“, ““) 'fjern apostrof
    Ellers
      Cells(Row, 4) = "Arbeidsbok"
    Slutt om
    'Kolonne E: Skjult status
    Cells(Row, 5) = Ikke n.Synlig
    'Kolonne F: Feilaktig navn
    Cells(Row, 6) = n.RefersTo Like "*[#]REF!*"
    'Kolonne G: Hyperkobling
    Hvis ikke Application.IsNA(Cells(Row, 3)) Deretter
      ActiveSheet.Hyperlinks.Add _
        Anker:=Celler(rad, 7), _
        Adresse:=““, _
        Subadresse:=n.Navn, _
        TextToDisplay:=n.Navn
    Slutt om
    'Kolonne H: Kommentar
    Cells(Row, 8) = n.Comment
  Neste n
Konverter det til en tabell
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Kilde:=Range(“A4”).CurrentRegion
' Juster kolonnebreddene
  Kolonner(“A:H”).Helkolonne.Autotilpass
End Sub