Skapa en namnrapport med VBA-kod

Om du använder många namngivna celler och intervall i din arbetsbok kan du bli förvånad över att upptäcka att Excel inte ger något sätt att lista detaljerna för varje namn. Användbar information visas i dialogrutan Namnhanterare, men det finns inget sätt att visa denna information på ett sätt som kan skrivas ut.

VBA-koden som erbjuds här genererar en användbar rapport som beskriver namnen som definieras i en arbetsbok.

Exempel på namnrapport

Här ser du ett exempel på en namnrapport.

Rapporten, som skapas på ett nytt kalkylblad, innehåller följande information för varje namn:

• Namn: Namnet.

• RefersTo: Definitionen för namnet. Vanligtvis kommer detta att vara en cell eller ett område, men ett namn kan också definiera formel.

• Celler: Antalet celler som finns i det namngivna området. För namngivna formler visar detta fält #N/A.

• Omfattning: Namnets omfattning — antingen arbetsbok eller namnet på det specifika kalkylblad där namnet är giltigt.

• Dolt: Sant om namnet är dolt. Dolda namn skapas av vissa tillägg (som Solver) och visas inte i dialogrutan Namnhanterare.

• Fel: Sant om namnet innehåller en felaktig referens.

• Länk: En hyperlänk som, när den klickas, aktiverar det namngivna området. Endast namn som refererar till celler eller intervall innehåller en länk.

• Kommentar: Kommentaren för namnet, om någon.

VBA-koden

För att använda den här koden, tryck på Alt+F11 för att aktivera Visual Basic Editor. Välj sedan Infoga → Modul för att infoga en ny VBA-modul. Kopiera koden och klistra in den i den nya modulen.

Sub GenerateNameReport()
' Genererar en rapport för alla namn i arbetsboken
' (Inkluderar inte tabellnamn)
  Dim n As Namn
  Dim rad så lång
  Dim CellCount som variant
' Avsluta om inga namn
  Om ActiveWorkbook.Names.Count = 0 Då
    MsgBox "Den aktiva arbetsboken har inga definierade namn."
    Avsluta Sub
  Avsluta om
' Avsluta om arbetsboken är skyddad
  Om ActiveWorkbook.ProtectStructure Då
    MsgBox "Ett nytt ark kan inte läggas till eftersom arbetsboken är skyddad."
    Avsluta Sub
  Avsluta om
' Infoga ett nytt ark för rapporten
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = False
' Lägg till första rubrikraden
  Range(“A1:H1”). Sammanfoga
  Med räckvidd(“A1”)
    .Value = "Namnrapport för: " & ActiveWorkbook.Name
    .Teckensnittsstorlek = 14
    .Font.Bold = Sant
    .HorizontalAlignment = xlCenter
  Sluta med
' Lägg till andra rubrikraden
  Range(“A2:H2”). Sammanfoga
  Med räckvidd ("A2")
    .Value = "Genererat" & nu
    .HorizontalAlignment = xlCenter
  Sluta med
' Lägg till rubrikerna
  Range(“A4:H4”) = Array(“Namn”, “RefersTo”, “Celler”, _
    "Omfattning", "Dold", "Fel", "Länk", "Kommentar")
' Gå igenom namnen
  Rad = 4
  Vid fel Resume Next
  För varje n In ActiveWorkbook.Names
    Rad = Rad + 1
    Kolumn A: Namn
    Om n.Name Som "*!*" Då
      Cells(Row, 1) = Split(n.Name, “!”)(1) ' Ta bort arknamn
    Annan
      Cells(Row, 1) = n.Name
    Avsluta om
    'Kolumn B: RefersTo
    Cells(Row, 2) = “'“ & n.RefersTo
    Kolumn C: Antal celler
    CellCount = CVErr(xlErrNA) ' Returnera värde för namngiven formel
    CellCount = n.RefersToRange.CountLarge
    Cells(Row, 3) = CellCount
    ' Kolumn D: Omfattning
    Om n.Name Som "*!*" Då
      Cells(Row, 4) = Split(n.Name, “!”)(0) ' extrahera arknamn
      Cells(Row, 4) = Replace(Cells(Row, 4), “'“, ““) 'ta bort apostrofer
    Annan
      Cells(Row, 4) = "Arbetsbok"
    Avsluta om
    Kolumn E: Dold status
    Cells(Row, 5) = Inte n.Visible
    Kolumn F: Felaktigt namn
    Cells(Row, 6) = n.RefersTo Like "*[#]REF!*"
    Kolumn G: Hyperlänk
    Om inte Application.IsNA(Cells(Row, 3)) Då
      ActiveSheet.Hyperlinks.Add _
        Ankare:=Celler(rad, 7), _
        Adress:=““, _
        Subadress:=n.Namn, _
        TextToDisplay:=n.Name
    Avsluta om
    Kolumn H: Kommentar
    Cells(Row, 8) = n.Comment
  Nästa n
' Konvertera det till en tabell
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Källa:=Räckvidd(“A4”).CurrentRegion
' Justera kolumnbredderna
  Kolumner(“A:H”). Hela kolumnen.Autopass
Avsluta Sub