Vytvorenie zostavy názvu pomocou kódu VBA

Ak v zošite používate veľa pomenovaných buniek a rozsahov, možno vás prekvapí, že Excel neposkytuje žiadny spôsob, ako uviesť podrobnosti pre každý názov. Užitočné informácie sa zobrazujú v dialógovom okne Správca názvov, ale neexistuje spôsob, ako zobraziť tieto informácie spôsobom, ktorý by sa dal vytlačiť.

Tu ponúkaný kód VBA generuje užitočnú zostavu, ktorá popisuje názvy definované v ľubovoľnom zošite.

Príklad správy o názve

Tu vidíte príklad zostavy mien.

Správa, ktorá sa vytvorí na novom pracovnom hárku, obsahuje pre každý názov nasledujúce informácie:

• Meno: Meno.

• Odkazuje na: Definícia názvu. Zvyčajne to bude bunka alebo rozsah, ale vzorec môže definovať aj názov.

• Bunky: Počet buniek obsiahnutých v pomenovanom rozsahu. Pre pomenované vzorce sa v tomto poli zobrazuje #N/A.

• Rozsah: Rozsah názvu — buď zošit, alebo názov konkrétneho pracovného hárka, na ktorom je názov platný.

• Skryté: Pravda, ak je názov skrytý. Skryté názvy vytvárajú niektoré doplnky (napríklad Riešiteľ) a nezobrazujú sa v dialógovom okne Správca mien.

• Chyba: True, ak názov obsahuje chybný odkaz.

• Odkaz: Hypertextový odkaz, ktorý po kliknutí aktivuje pomenovaný rozsah. Odkaz obsahujú iba názvy, ktoré odkazujú na bunky alebo rozsahy.

• Komentár: Komentár k názvu, ak existuje.

Kód VBA

Ak chcete použiť tento kód, stlačte Alt+F11, čím aktivujete editor jazyka Visual Basic. Potom vyberte Vložiť → Modul a vložte nový modul VBA. Skopírujte kód a vložte ho do nového modulu.

Sub GenerateNameReport()
' Vygeneruje zostavu pre všetky mená v zošite
“ (Nezahŕňa názvy tabuliek)
  Dim n As Name
  Dim Row As Long
  Dim CellCount ako variant
' Ak žiadne mená, odíďte
  Ak ActiveWorkbook.Names.Count = 0 Potom
    MsgBox "Aktívny zošit nemá definované názvy."
    Ukončiť Sub
  Koniec Ak
' Ukončite, ak je zošit chránený
  Ak ActiveWorkbook.ProtectStructure Then
    MsgBox "Nový hárok nemožno pridať, pretože zošit je chránený."
    Ukončiť Sub
  Koniec Ak
Vložte nový hárok pre správu
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = False
' Pridajte prvý riadok názvu
  Rozsah („A1:H1“). Zlúčiť
  S rozsahom („A1“)
    .Value = “Názov zostavy pre: “ & ActiveWorkbook.Name
    .Veľkosť písma = 14
    .Font.Tučné = True
    .HorizontalAlignment = xlCenter
  Koniec s
' Pridajte druhý riadok názvu
  Rozsah („A2:H2“). Zlúčiť
  S rozsahom („A2“)
    .Value = “Generated” & Now
    .HorizontalAlignment = xlCenter
  Koniec s
' Pridajte hlavičky
  Rozsah(“A4:H4”) = Pole(“Názov”, “Odkaz na”, “Bunky”, _
    „Rozsah“, „Skryté“, „Chyba“, „Odkaz“, „Komentár“)
' Prelistujte si mená
  Riadok = 4
  Pri chybe Pokračujte ďalej
  Pre každé n v ActiveWorkbook.Names
    Riadok = riadok + 1
    'Stĺpec A: Názov
    Ak n.Name Like “*!*” Potom
      Cells(Row, 1) = Split(n.Name, “!“)(1) ' Odstrániť názov listu
    Inak
      Cells(Row, 1) = n.Name
    Koniec Ak
    „Stĺpec B: Odkazuje na
    Cells(Row, 2) = „'“ & n.RefersTo
    'Stĺpec C: Počet buniek
    CellCount = CVErr(xlErrNA) ' Návratová hodnota pre pomenovaný vzorec
    CellCount = n.RefersToRange.CountLarge
    Cells(Row, 3) = CellCount
    Stĺpec D: Rozsah
    Ak n.Name Like “*!*” Potom
      Cells(Row, 4) = Split(n.Name, “!”)(0) ' extrahovaný názov listu
      Bunky (riadok, 4) = Nahradiť (bunky (riadok, 4), „'“, „“) 'odstrániť apostrofy
    Inak
      Bunky (riadok, 4) = „Zošit“
    Koniec Ak
    'Stĺpec E: Skrytý stav
    Bunky (riadok, 5) = nie je n. Viditeľné
    'Stĺpec F: Chybný názov
    Cells(Row, 6) = n.RefersTo Like “*[#]REF!*”
    'Stĺpec G: Hypertextový odkaz
    Ak nie Application.IsNA(Cells(Row, 3)) Potom
      ActiveSheet.Hyperlinks.Add _
        Anchor:=Cells(Rad, 7), _
        Adresa:=““, _
        Podadresa:=n.Name, _
        TextToDisplay:=n.Name
    Koniec Ak
    'Stĺpec H: Komentár
    Bunky(riadok, 8) = n.Komentár
  Ďalej n
' Premeňte to na tabuľku
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Zdroj:=Rozsah(“A4”).CurrentRegion
' Upravte šírku stĺpcov
  Columns(“A:H”).Celý stĺpec.AutoFit
End Sub