Nimearuande loomine VBA koodiga

Kui kasutate oma töövihikus palju nimega lahtreid ja vahemikke, võite olla üllatunud, kui avastate, et Excel ei paku võimalust iga nime üksikasju loetleda. Dialoogiboksis Nimehaldur kuvatakse kasulikku teavet, kuid seda teavet pole võimalik printida.

Siin pakutav VBA kood loob kasuliku aruande, mis kirjeldab mis tahes töövihikus määratletud nimesid.

Nimearuande näide

Siin näete nimearuande näidet.

Aruanne, mis luuakse uuel töölehel, sisaldab iga nime kohta järgmist teavet.

• Nimi: nimi.

• RefersTo: nime määratlus. Tavaliselt on see lahter või vahemik, kuid nimi võib määratleda ka valemi.

• Lahtrid: nimetatud vahemikus sisalduvate lahtrite arv. Nimega valemite puhul kuvatakse sellel väljal #N/A.

• Ulatus: nime ulatus — kas töövihik või konkreetse töölehe nimi, millel nimi kehtib.

• Varjatud: tõene, kui nimi on peidetud. Varjatud nimed loovad mõned lisandmoodulid (nt Solver) ja neid ei kuvata dialoogiboksis Nimehaldur.

• Viga: Tõene, kui nimi sisaldab ekslikku viidet.

• Link: hüperlink, millel klõpsamine aktiveerib nimetatud vahemiku. Linki sisaldavad ainult nimed, mis viitavad lahtritele või vahemikele.

• Kommentaar: nime kommentaar, kui see on olemas.

VBA kood

Selle koodi kasutamiseks vajutage Visual Basic Editori aktiveerimiseks Alt+F11. Seejärel valige uue VBA mooduli sisestamiseks Lisa → Moodul. Kopeerige kood ja kleepige see uude moodulisse.

Alam GenerateNameReport()
Loob aruande kõigi töövihiku nimede kohta
(Ei sisalda tabelite nimesid)
  Dim n As Name
  Hämar rida nii pikk
  Dim CellCount as Variant
Väljuge, kui nimesid pole
  Kui ActiveWorkbook.Names.Count = 0 Siis
    MsgBox "Aktiivsel töövihikul pole määratletud nimesid."
    Välju Sub
  Lõpeta Kui
Väljuge, kui töövihik on kaitstud
  Kui ActiveWorkbook.ProtectStructure Siis
    MsgBox "Uut lehte ei saa lisada, kuna töövihik on kaitstud."
    Välju Sub
  Lõpeta Kui
Sisestage aruande jaoks uus leht
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Tabelid(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = Vale
Lisage pealkirja esimene rida
  Vahemik (“A1:H1”). Ühenda
  Vahemikuga (“A1”)
    .Value = "Nimeraport: " & ActiveWorkbook.Name
    .Font.Size = 14
    .Font.Bold = Tõene
    .HorizontalAlignment = xlCenter
  Lõpeta
Lisage pealkirja teine ​​rida
  Vahemik (“A2:H2”). Ühenda
  Vahemikuga (“A2”)
    .Value = "Loodud" & kohe
    .HorizontalAlignment = xlCenter
  Lõpeta
Lisage päised
  Vahemik(“A4:H4”) = Massiiv (“Nimi”, “Viita”, “Cells”, _
    "Ulatusala", "Peidetud", "Viga", "Link", "Kommentaar")
' Sirvige nimesid
  Rida = 4
  Vea korral Jätka järgmisena
  Iga n jaoks jaotises ActiveWorkbook.Names
    Rida = rida + 1
    "Veerg A: nimi
    Kui n.Nimi Nagu “*!*” Siis
      Lahtrid(rida, 1) = Poolita(n.Nimi, "!")(1) ' Eemalda lehe nimi
    Muidu
      Lahtrid(rida, 1) = n.Nimi
    Lõpeta Kui
    "Veerg B: viitab
    Lahtrid (rida, 2) = "'" & n. RefersTo
    "Veerg C: lahtrite arv
    CellCount = CVERr(xlErrNA) ' Nimega valemi tagastusväärtus
    CellCount = n.RefersToRange.CountLarge
    Rakud (rida, 3) = CellCount
    Veerg D: ulatus
    Kui n.Nimi Nagu “*!*” Siis
      Lahtrid(rida, 4) = Poolita(n.Nimi, "!")(0) ' väljavõttelehe nimi
      Lahtrid(rida, 4) = Asenda(Cells(Row, 4), "'", "") 'eemalda apostroofid
    Muidu
      Lahtrid (rida, 4) = "töövihik"
    Lõpeta Kui
    Veerg E: peidetud olek
    Lahtrid (rida, 5) = pole n. Nähtav
    Veerg F: vigane nimi
    Lahtrid (rida, 6) = n. Viitab meeldimisele „*[#]REF!*”
    "Veerg G: hüperlink
    Kui ei ole rakendus.IsNA(Cells(Row, 3)) Siis
      ActiveSheet.Hyperlinks.Add _
        Ankur:=lahtrid(rida, 7), _
        Aadress:=““, _
        Alamaadress:=n.Nimi, _
        TextToDisplay:=n.Name
    Lõpeta Kui
    "Veerg H: kommentaar
    Lahtrid(rida, 8) = n.Kommentaar
  Järgmine n
' Teisendage see tabeliks
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Allikas:=Range(“A4”).CurrentRegion
' Reguleerige veergude laiust
  Veerud ("A:H"). Terve veerg. Automaatne sobitamine
Lõpeta alam