Vytvoření sestavy názvu pomocí kódu VBA

Pokud v sešitu používáte hodně pojmenovaných buněk a oblastí, možná vás překvapí, že Excel neposkytuje žádný způsob, jak vypsat podrobnosti pro každý název. Užitečné informace se zobrazí v dialogovém okně Správce názvů, ale neexistuje způsob, jak tyto informace zobrazit způsobem, který lze vytisknout.

Zde nabízený kód VBA generuje užitečnou sestavu, která popisuje názvy definované v libovolném sešitu.

Příklad sestavy názvu

Zde vidíte příklad sestavy jmen.

Sestava, která je vytvořena na novém listu, obsahuje pro každý název následující informace:

• Jméno: Jméno.

• Odkazuje na: Definice názvu. Obvykle to bude buňka nebo rozsah, ale název může také definovat vzorec.

• Buňky: Počet buněk obsažených v pojmenovaném rozsahu. U pojmenovaných vzorců toto pole zobrazuje #N/A.

• Rozsah: Rozsah názvu — buď sešit, nebo název konkrétního listu, na kterém je název platný.

• Skrytý: Pravda, pokud je název skrytý. Skryté názvy jsou vytvářeny některými doplňky (jako je Řešitel) a nezobrazují se v dialogovém okně Správce názvů.

• Chyba: True, pokud název obsahuje chybný odkaz.

• Odkaz: Hypertextový odkaz, který po kliknutí aktivuje pojmenovaný rozsah. Odkaz obsahují pouze názvy, které odkazují na buňky nebo rozsahy.

• Komentář: Komentář ke jménu, pokud existuje.

Kód VBA

Chcete-li použít tento kód, stiskněte Alt+F11 a aktivujte Editor jazyka Visual Basic. Poté zvolte Vložit → Modul a vložte nový modul VBA. Zkopírujte kód a vložte jej do nového modulu.

Sub GenerateNameReport()
' Vygeneruje sestavu pro všechna jména v sešitu
“ (Nezahrnuje názvy tabulek)
  Dim n As Name
  Dim Row As Long
  Dim CellCount jako varianta
Pokud žádná jména, odejděte
  Pokud ActiveWorkbook.Names.Count = 0 Pak
    MsgBox "Aktivní sešit nemá žádné definované názvy."
    Exit Sub
  End If
' Ukončete, pokud je sešit chráněn
  Pokud ActiveWorkbook.ProtectStructure Then
    MsgBox "Nový list nelze přidat, protože sešit je chráněn."
    Exit Sub
  End If
' Vložte nový list pro sestavu
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = False
' Přidejte první řádek názvu
  Rozsah („A1:H1“). Sloučit
  S rozsahem („A1“)
    .Value = “Název sestavy pro: “ & ActiveWorkbook.Name
    .Velikost písma = 14
    .Font.Tučné = True
    .HorizontalAlignment = xlCenter
  Konec s
' Přidejte druhý řádek názvu
  Rozsah („A2:H2“). Sloučit
  S rozsahem („A2“)
    .Value = “Generováno” & Nyní
    .HorizontalAlignment = xlCenter
  Konec s
' Přidejte záhlaví
  Rozsah(“A4:H4”) = Array(“Název”, “Odkaz na”, “Buňky”, _
    "Rozsah", "Skrytý", "Chyba", "Odkaz", "Komentář")
' Projděte si jména
  Řada = 4
  Při chybě pokračovat dále
  Pro každé n v ActiveWorkbook.Names
    Řada = řada + 1
    'Sloupec A: Jméno
    If n. Name Like "*!*" Then
      Cells(Row, 1) = Split(n.Name, “!“)(1) ' Odebrat název listu
    Jiný
      Cells(Row, 1) = n.Name
    End If
    'Sloupec B: Odkazuje na
    Cells(Row, 2) = „'“ & n.RefersTo
    'Sloupec C: Počet buněk
    CellCount = CVErr(xlErrNA) ' Návratová hodnota pro pojmenovaný vzorec
    CellCount = n.RefersToRange.CountLarge
    Cells(Row, 3) = CellCount
    Sloupec D: Rozsah
    If n. Name Like "*!*" Then
      Cells(Row, 4) = Split(n.Name, “!”)(0) ' extrahovat název listu
      Cells(Row, 4) = Replace(Cells(Row, 4), „'“, „“) 'odstranit apostrofy
    Jiný
      Buňky (řádek, 4) = “Sešit”
    End If
    'Sloupec E: Skrytý stav
    Buňky (Řádek, 5) = Není n. Viditelné
    'Sloupec F: Chybný název
    Cells(Row, 6) = n.RefersTo Like „*[#]REF!*“
    'Sloupec G: Hypertextový odkaz
    If Not Application.IsNA(Cells(Row, 3)) Then
      ActiveSheet.Hyperlinks.Add _
        Kotva:=Cells(Řádek, 7), _
        Adresa:=““, _
        Podadresa:=n.Name, _
        TextToDisplay:=n.Name
    End If
    'Sloupec H: Komentář
    Buňky(Řádek, 8) = n.Komentář
  Další n
' Převeďte to na tabulku
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Zdroj:=Range(“A4”).CurrentRegion
' Upravte šířku sloupců
  Columns(“A:H”).Celý sloupec.AutoFit
End Sub