Oprettelse af en navnerapport med VBA-kode

Hvis du bruger masser af navngivne celler og områder i din projektmappe, kan du blive overrasket over at opdage, at Excel ikke giver mulighed for at angive detaljerne for hvert navn. Nyttige oplysninger vises i dialogboksen Navnehåndtering, men der er ingen måde at vise disse oplysninger på en måde, der kan udskrives.

VBA-koden, der tilbydes her, genererer en nyttig rapport, der beskriver de navne, der er defineret i enhver projektmappe.

Eksempel på navnerapport

Her ser du et eksempel på en navnerapport.

Rapporten, som oprettes på et nyt regneark, indeholder følgende oplysninger for hvert navn:

• Navn: Navnet.

• RefersTo: Definitionen af ​​navnet. Normalt vil dette være en celle eller et område, men et navn kan også definere formel.

• Celler: Antallet af celler i det navngivne område. For navngivne formler viser dette felt #N/A.

• Omfang: Omfanget af navnet — enten projektmappe eller navnet på det specifikke regneark, hvor navnet er gyldigt.

• Skjult: Sandt, hvis navnet er skjult. Skjulte navne oprettes af nogle tilføjelsesprogrammer (såsom Solver), og vises ikke i dialogboksen Navnehåndtering.

• Fejl: Sandt, hvis navnet indeholder en fejlagtig reference.

• Link: Et hyperlink, der, når der klikkes på det, aktiverer det navngivne område. Kun navne, der refererer til celler eller områder, indeholder et link.

• Kommentar: Kommentaren til navnet, hvis nogen.

VBA-koden

For at bruge denne kode skal du trykke på Alt+F11 for at aktivere Visual Basic Editor. Vælg derefter Indsæt → Modul for at indsætte et nyt VBA-modul. Kopier koden og indsæt den i det nye modul.

Sub GenerateNameReport()
' Genererer en rapport for alle navne i projektmappen
' (Inkluderer ikke tabelnavne)
  Dim n As Navn
  Dim række så længe
  Dim CellCount som variant
' Afslut hvis ingen navne
  Hvis ActiveWorkbook.Names.Count = 0 Så
    MsgBox "Den aktive projektmappe har ingen definerede navne."
    Afslut Sub
  Afslut Hvis
' Afslut, hvis projektmappen er beskyttet
  Hvis ActiveWorkbook.ProtectStructure Så
    MsgBox "Et nyt ark kan ikke tilføjes, fordi projektmappen er beskyttet."
    Afslut Sub
  Afslut Hvis
' Indsæt et nyt ark til rapporten
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = Falsk
' Tilføj første linje i overskriften
  Range(“A1:H1”).Flet
  Med rækkevidde ("A1")
    .Value = "Navnerapport for: " & ActiveWorkbook.Name
    .Skriftstørrelse = 14
    .Font.Fed = Sand
    .Horizontal Alignment = xlCenter
  Slut med
' Tilføj anden titellinje
  Range(“A2:H2”).Flet
  Med rækkevidde ("A2")
    .Value = "Genereret" & nu
    .Horizontal Alignment = xlCenter
  Slut med
' Tilføj overskrifterne
  Range(“A4:H4”) = Array(“Navn”, “RefersTo”, “Celler”, _
    "Omfang", "Skjult", "Fejl", "Link", "Kommentar")
' Gå gennem navnene
  Række = 4
  Ved fejl Genoptag Næste
  For hver n I ActiveWorkbook.Names
    Række = Række + 1
    'Kolonne A: Navn
    Hvis n.Name Som "*!*" Så
      Cells(Row, 1) = Split(n.Name, “!”)(1) ' Fjern arknavn
    Andet
      Cells(Row, 1) = n.Name
    Afslut Hvis
    'Kolonne B: ReferererTil
    Cells(Row, 2) = “'“ & n.RefersTo
    'Kolonne C: Antal celler
    CellCount = CVErr(xlErrNA) ' Returner værdi for navngivet formel
    CellCount = n.RefersToRange.CountLarge
    Cells(Row, 3) = CellCount
    ' Kolonne D: Omfang
    Hvis n.Name Som "*!*" Så
      Cells(Row, 4) = Split(n.Name, “!”)(0) ' udtræksarknavn
      Cells(Row, 4) = Replace(Cells(Row, 4), “'“, ““) 'fjern apostrof
    Andet
      Cells(Row, 4) = "Arbejdsbog"
    Afslut Hvis
    'Kolonne E: Skjult status
    Cells(Row, 5) = Ikke n.Visible
    'Kolonne F: Fejlagtigt navn
    Cells(Row, 6) = n.RefersTo Like "*[#]REF!*"
    'Kolonne G: Hyperlink
    Hvis ikke Application.IsNA(Cells(Row, 3)) Så
      ActiveSheet.Hyperlinks.Add _
        Anker:=Celler(række, 7), _
        Adresse:=““, _
        Underadresse:=n.Navn, _
        TextToDisplay:=n.Name
    Afslut Hvis
    'Kolonne H: Kommentar
    Cells(Row, 8) = n.Comment
  Næste n
' Konverter det til en tabel
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Kilde:=Range(“A4”).CurrentRegion
' Juster kolonnebredderne
  Kolonner(“A:H”).Helkolonne.Autotilpas
Slut Sub