Ustvarjanje poročila o imenu s kodo VBA

Če v delovnem zvezku uporabljate veliko poimenovanih celic in obsegov, boste morda presenečeni, ko boste ugotovili, da Excel ne ponuja načina za navedbo podrobnosti za vsako ime. Koristne informacije se prikažejo v pogovornem oknu Upravitelj imen, vendar teh informacij ni mogoče prikazati na način, ki bi ga bilo mogoče natisniti.

Tukaj ponujena koda VBA ustvari uporabno poročilo, ki opisuje imena, definirana v katerem koli delovnem zvezku.

Primer poročila o imenu

Tukaj vidite primer poročila o imenu.

Poročilo, ki je ustvarjeno na novem delovnem listu, vključuje naslednje informacije za vsako ime:

• Ime: Ime.

• RefersTo: definicija za ime. Običajno bo to celica ali obseg, vendar lahko ime definira tudi formulo.

• Celice: število celic v imenovanem obsegu. Za imenovane formule to polje prikazuje #N/A.

• Obseg: obseg imena — bodisi delovni zvezek ali ime določenega delovnega lista, na katerem je ime veljavno.

• Skrito: Res je, če je ime skrito. Skrita imena ustvarijo nekateri dodatki (kot je Solver) in se ne prikažejo v pogovornem oknu Upravitelj imen.

• Napaka: True, če ime vsebuje napačen sklic.

• Povezava: Hiperpovezava, ki ob kliku aktivira imenovani obseg. Samo imena, ki se nanašajo na celice ali obsege, vključujejo povezavo.

• Komentar: Komentar za ime, če obstaja.

Koda VBA

Če želite uporabiti to kodo, pritisnite Alt+F11, da aktivirate urejevalnik Visual Basic. Nato izberite Vstavi → Modul, da vstavite nov modul VBA. Kopirajte kodo in jo prilepite v nov modul.

Sub GenerateNameReport()
' Ustvari poročilo za vsa imena v delovnem zvezku
' (Ne vključuje imen tabel)
  Dim n As Name
  Dim Row As Long
  Dim CellCount kot različica
' Izhod, če ni imen
  Če je ActiveWorkbook.Names.Count = 0 Potem
    MsgBox »Aktivni delovni zvezek nima definiranih imen.«
    Izhod Sub
  Konec Če
' Zapri, če je delovni zvezek zaščiten
  Če ActiveWorkbook.ProtectStructure Potem
    MsgBox »Novega lista ni mogoče dodati, ker je delovni zvezek zaščiten.«
    Izhod Sub
  Konec Če
' Vstavite nov list za poročilo
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = False
' Dodajte prvo vrstico naslova
  Obseg(»A1:H1«).Spoji
  Z razponom (»A1«)
    .Value = "Poročilo o imenu za: " & ActiveWorkbook.Name
    .Velikost pisave = 14
    .Font.Bold = Res
    .HorizontalAlignment = xlCenter
  Končaj z
' Dodajte drugo vrstico naslova
  Obseg(»A2:H2«).Spoji
  Z razponom (»A2«)
    .Value = »Ustvarjeno« & zdaj
    .HorizontalAlignment = xlCenter
  Končaj z
' Dodajte glave
  Obseg(“A4:H4”) = Array(“Ime”, “RefersTo”, “Cells”, _
    "Obseg", "Skrito", "Napaka", "Povezava", "Komentar")
« Prelistajte imena
  Vrstica = 4
  Ob napaki Nadaljuj naprej
  Za vsako n V ActiveWorkbook.Imena
    Vrstica = vrstica + 1
    Stolpec A: Ime
    Če n.Ime Kot »*!*« Potem
      Celice(Vrstica, 1) = Split(n.Name, “!”)(1) ' Odstrani ime lista
    Drugače
      Celice (vrstica, 1) = n.Ime
    Konec Če
    „Stolpec B: Sklicuje se na
    Celice (vrstica, 2) = "'" & n.RefersTo
    „Stolpec C: Število celic
    CellCount = CVERr(xlErrNA) ' Vrnjena vrednost za imenovano formulo
    CellCount = n.RefersToRange.CountLarge
    Celice (vrstica, 3) = Število celic
    ' Stolpec D: Obseg
    Če n.Ime Kot »*!*« Potem
      Celice(Vrstica, 4) = Split(n.Name, “!”)(0) ' ime lista izvlečka
      Celice(Vrstica, 4) = Zamenjaj(Celice(Vrstica, 4), “'“, ““) 'odstrani apostrofe
    Drugače
      Celice (vrstica, 4) = "Delovni zvezek"
    Konec Če
    »Stolpec E: Skrit status
    Celice (vrstica, 5) = Ni n.Vidno
    „Stolpec F: napačno ime
    Celice (vrstica, 6) = n. Nanaša se na »*[#]REF!*«
    Stolpec G: Hiperpovezava
    Če ni Application.IsNA(Celice(Vrstica, 3)) Potem
      ActiveSheet.Hyperlinks.Add _
        Sidro:=Celice (vrstica, 7), _
        Naslov:=““, _
        Podnaslov:=n.Ime, _
        TextToDisplay:=n.Ime
    Konec Če
    Stolpec H: Komentar
    Celice (vrstica, 8) = n.Komentar
  Naslednji n
' Pretvori ga v tabelo
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Vir:=Obseg(“A4”).Trenutna regija
' Prilagodite širino stolpcev
  Stolpci(»A:H«).Celoten stolpec.Samodejno prilagajanje
Končni sub

Ustvarjanje poročila

Izvedite postopek GenerateNameReport in poročilo se ustvari na novem delovnem listu v aktivnem delovnem zvezku. Ni nujno, da je koda v delovnem zvezku, ki vsebuje imena za poročilo.

Če se vam zdi ta koda uporabna, jo boste morda želeli shraniti v delovni zvezek osebnih makrov ali ustvariti dodatek.