Vardo ataskaitos kūrimas naudojant VBA kodą

Jei darbaknygėje naudojate daug pavadintų langelių ir diapazonų, galite nustebti sužinoję, kad „Excel“ nepateikia būdo pateikti kiekvieno vardo išsamios informacijos. Naudinga informacija rodoma dialogo lange Vardų tvarkyklė, tačiau nėra būdo pateikti šią informaciją taip, kad ją būtų galima atspausdinti.

Čia siūlomas VBA kodas sukuria naudingą ataskaitą, kurioje aprašomi bet kurioje darbaknygėje apibrėžti pavadinimai.

Pavadinimo ataskaitos pavyzdys

Čia matote vardo ataskaitos pavyzdį.

Ataskaitoje, kuri sukuriama naujame darbalapyje, pateikiama tokia informacija apie kiekvieną pavadinimą:

• Vardas: Vardas.

• RefersTo: pavadinimo apibrėžimas. Paprastai tai bus langelis arba diapazonas, tačiau pavadinimas taip pat gali apibrėžti formulę.

• Langeliai: pavadintame diapazone esančių langelių skaičius. Vardinėms formulėms šiame lauke rodoma #N/A.

• Taikymo sritis: pavadinimo apimtis – darbaknygė arba konkretaus darbalapio, kuriame pavadinimas galioja, pavadinimas.

• Paslėpta: tiesa, jei vardas paslėptas. Paslėptus pavadinimus sukuria kai kurie priedai (pvz., Solver) ir jie nerodomi dialogo lange Vardų tvarkyklė.

• Klaida: tiesa, jei pavadinime yra klaidinga nuoroda.

• Nuoroda: hipersaitas, kurį spustelėjus suaktyvinamas pavadintas diapazonas. Tik pavadinimai, kurie nurodo langelius arba diapazonus, apima nuorodą.

• Komentaras: pavadinimo komentaras, jei toks.

VBA kodas

Norėdami naudoti šį kodą, paspauskite Alt + F11, kad suaktyvintumėte Visual Basic redaktorių. Tada pasirinkite Įterpti → Modulis, kad įterptumėte naują VBA modulį. Nukopijuokite kodą ir įklijuokite jį į naują modulį.

Sub GenerateNameReport()
Sugeneruoja visų darbaknygės vardų ataskaitą
“ (Neįtraukiami lentelių pavadinimai)
  Dim n As Name
  Dim Row As Long
  Dim CellCount kaip variantas
Išeik, jei nėra vardų
  Jei ActiveWorkbook.Names.Count = 0 Tada
    MsgBox "Aktyvi darbaknygė neturi apibrėžtų pavadinimų."
    Išeiti iš sub
  Pabaiga, jei
Išeikite, jei darbaknygė apsaugota
  Jei ActiveWorkbook.ProtectStructure Tada
    MsgBox "Negalima pridėti naujo lapo, nes darbaknygė yra apsaugota."
    Išeiti iš sub
  Pabaiga, jei
Įdėkite naują ataskaitos lapą
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Lapai(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = Netiesa
' Pridėti pirmą pavadinimo eilutę
  Diapazonas („A1:H1“). Sujungti
  Su diapazonu („A1“)
    .Value = "Vardo ataskaita: " & ActiveWorkbook.Name
    .Šriftas.Dydis = 14
    .Šriftas.Bold = Tiesa
    .HorizontalAlignment = xlCenter
  Baigti su
' Pridėti antrą pavadinimo eilutę
  Diapazonas („A2:H2“). Sujungti
  Su diapazonu („A2“)
    .Value = „Sukurta“ ir dabar
    .HorizontalAlignment = xlCenter
  Baigti su
' Pridėti antraštes
  Diapazonas ("A4:H4") = masyvas ("pavadinimas", "nuoroda į", "ląstelės", _
    „Taikymo sritis“, „Paslėpta“, „Klaida“, „Nuoroda“, „Komentaras“)
' Perskaitykite pavadinimus
  Eilė = 4
  Įvykus klaidai, tęskite toliau
  Kiekvienam n ActiveWorkbook.Names
    Eilutė = eilutė + 1
    „A stulpelis: pavadinimas
    Jei n.Vardas Kaip „*!*“ Tada
      Langeliai (eilutė, 1) ​​= Padalyti (n.Pavadinimas, "!") (1) ' Pašalinti lapo pavadinimą
    Kitas
      Langeliai (eilutė, 1) ​​= n.Pavadinimas
    Pabaiga, jei
    „B stulpelis: RefersTo
    Langeliai (eilutė, 2) = "'" & n. RefersTo
    „C stulpelis: langelių skaičius
    CellCount = CVERr(xlErrNA) ' Įvardintos formulės grąžinimo reikšmė
    CellCount = n.RefersToRange.CountLarge
    Ląstelės (eilutė, 3) = ląstelių skaičius
    D stulpelis: Taikymo sritis
    Jei n.Vardas Kaip „*!*“ Tada
      Ląstelės (eilutė, 4) = padalinti (n.Pavadinimas, "!") (0) ' ištraukos lapo pavadinimas
      Langeliai (eilutė, 4) = Pakeisti (Ląstelės (eilutė, 4), „'“, „“) 'pašalinti apostrofus
    Kitas
      Langeliai (eilutė, 4) = „Darbo knyga“
    Pabaiga, jei
    „E stulpelis: paslėpta būsena
    Langeliai (eilutė, 5) = ne n. Matoma
    „F stulpelis: klaidingas pavadinimas
    Langeliai (eilutė, 6) = n. RefersTo Like "*[#]REF!*"
    „G stulpelis: hipersaitas
    Jei ne taikymas.IsNA(Cells(Row, 3)) Tada
      ActiveSheet.Hyperlinks.Add _
        Anchor:=Ląstelės (eilutė, 7), _
        Adresas:=““, _
        SubAdresas:=n.Vardas, _
        TextToDisplay:=n.Vardas
    Pabaiga, jei
    „H stulpelis: komentaras
    Langeliai (eilutė, 8) = n.Komentuoti
  Kitas n
' Konvertuokite jį į lentelę
  ActiveSheet.ListObjects.Add _
    Šaltinio tipas:=xlSrcRange, _
    Šaltinis:=Rangas(“A4”).DabartinisRegionas
' Sureguliuokite stulpelių plotį
  Stulpeliai („A:H“).Visa stulpelis.Automatinis pritaikymas
Pabaigos sub