Створення звіту про назву за допомогою коду VBA

Якщо ви використовуєте багато іменованих комірок і діапазонів у своїй книзі, ви можете бути здивовані, виявивши, що Excel не надає можливості перерахувати деталі для кожного імені. Корисна інформація з’являється в діалоговому вікні Менеджера імен, але неможливо відобразити цю інформацію у спосіб, який можна роздрукувати.

Пропонований тут код VBA створює корисний звіт, який описує імена, визначені в будь-якій книзі.

Приклад звіту про назву

Тут ви бачите приклад звіту про назву.

Звіт, який створюється на новому аркуші, містить таку інформацію для кожного імені:

• Ім'я: Ім'я.

• RefersTo: визначення назви. Зазвичай це буде клітинка або діапазон, але ім’я також може визначати формулу.

• Комірки: кількість комірок, що містяться в названому діапазоні. Для іменованих формул у цьому полі відображається #N/A.

• Scope: Область назви — або Workbook, або назва конкретного робочого аркуша, на якому ім’я дійсне.

• Приховано: правда, якщо ім’я приховано. Приховані імена створюються деякими надбудовами (наприклад, Solver) і не відображаються в діалоговому вікні Менеджера імен.

• Помилка: True, якщо ім’я містить помилкове посилання.

• Посилання: гіперпосилання, натискання якого активує названий діапазон. Посилання містять лише імена, які посилаються на клітинки або діапазони.

• Коментар: коментар до імені, якщо є.

Код VBA

Щоб використати цей код, натисніть Alt+F11, щоб активувати редактор Visual Basic. Потім виберіть Вставити → Модуль, щоб вставити новий модуль VBA. Скопіюйте код і вставте його в новий модуль.

Sub GenerateNameReport()
' Створює звіт для всіх імен у книзі
' (Не містить назв таблиць)
  Dim n Як ім'я
  Dim Row As Long
  Dim CellCount як варіант
' Вийдіть, якщо немає імен
  Якщо ActiveWorkbook.Names.Count = 0 Тоді
    MsgBox «Активна книга не має визначених імен».
    Вийти з суб
  Кінець Якщо
' Вийдіть, якщо книга захищена
  Якщо ActiveWorkbook.ProtectStructure Тоді
    MsgBox «Неможливо додати новий аркуш, оскільки книга захищена».
    Вийти з суб
  Кінець Якщо
' Вставте новий аркуш для звіту
  ActiveWorkbook.Worksheets.Add
  ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
  ActiveWindow.DisplayGridlines = False
' Додайте перший рядок заголовка
  Діапазон (“A1:H1”). Об’єднати
  З діапазоном (“A1”)
    .Value = «Звіт про назву для: « & ActiveWorkbook.Name
    .Розмір шрифту = 14
    .Font.Bold = Правда
    .HorizontalAlignment = xlCenter
  Закінчити з
' Додайте другий рядок заголовка
  Діапазон (“A2:H2”). Об’єднати
  З діапазоном (“A2”)
    .Value = “Створено “ і зараз
    .HorizontalAlignment = xlCenter
  Закінчити з
' Додайте заголовки
  Діапазон(“A4:H4”) = Масив(“Ім’я”, “Посилається на”, “Клітинки”, _
    «Обсяг», «Прихований», «Помилка», «Посилання», «Коментар»)
' Перегляньте імена
  Ряд = 4
  Помилка Продовжити далі
  Для кожного n У ActiveWorkbook.Names
    Ряд = Ряд + 1
    «Колонка А: Ім’я
    Якщо n.Name Як «*!*», Тоді
      Cells(Row, 1) = Split(n.Name, “!”)(1) ' Видалити назву аркуша
    Інакше
      Комірки(Рядок, 1) = n.Name
    Кінець Якщо
    «Колонка B: посилання на
    Комірки (Рядок, 2) = «'» & n. RefersTo
    «Стовпець C: кількість клітинок
    CellCount = CVERr(xlErrNA) ' Повернене значення для іменованої формули
    CellCount = n.RefersToRange.CountLarge
    Комірки (Рядок, 3) = Кількість клітинок
    ' Колонка D: Область застосування
    Якщо n.Name Як «*!*», Тоді
      Клітинки(Рядок, 4) = Split(n.Name, “!”)(0) ' витягнути назву аркуша
      Комірки(Рядок, 4) = Замінити(Комірки(Рядок, 4), “’“, ““) 'вилучити апострофи
    Інакше
      Комірки (рядок, 4) = «Робочий зошит»
    Кінець Якщо
    «Стовпець E: прихований статус
    Комірки (Рядок, 5) = Не n. Видно
    "Колонка F: помилкова назва
    Клітинки (рядок, 6) = n. Відноситься до «*[#]REF!*»
    «Колонка G: гіперпосилання
    Якщо не Application.IsNA(Cells(Row, 3)) Тоді
      ActiveSheet.Hyperlinks.Add _
        Якір:=Комірки (Рядок, 7), _
        Адреса:=““, _
        Підадреса:=n.Назва, _
        TextToDisplay:=n.Name
    Кінець Якщо
    «Колонка H: коментар
    Комірки(Рядок, 8) = n.Коментар
  Далі н
' Перетворіть його в таблицю
  ActiveSheet.ListObjects.Add _
    SourceType:=xlSrcRange, _
    Джерело:=Діапазон(“A4”).Поточний регіон
' Налаштуйте ширину стовпців
  Columns(“A:H”).EntireColumn.AutoFit
Кінець суб