Създаване на отчет за име с VBA код

Ако използвате много именувани клетки и диапазони в работната си книга, може да се изненадате да откриете, че Excel не предоставя начин за изброяване на подробностите за всяко име. Полезна информация се появява в диалоговия прозорец Мениджър на имена, но няма начин да се покаже тази информация по начин, който може да бъде отпечатан.

Предлаганият тук VBA код генерира полезен отчет, който описва имената, дефинирани във всяка работна книга.

Примерен доклад за име

Тук виждате пример за отчет за имена.

Отчетът, който е създаден на нов работен лист, включва следната информация за всяко име:

• Име: Името.

• RefersTo: Определението за името. Обикновено това ще бъде клетка или диапазон, но името може също да дефинира формула.

• Клетки: Броят клетки, съдържащи се в посочения диапазон. За наименувани формули това поле показва #N/A.

• Обхват: Обхватът на името — или работна книга, или името на конкретния работен лист, на който името е валидно.

• Скрит: Вярно, ако името е скрито. Скритите имена се създават от някои добавки (като Solver) и не се появяват в диалоговия прозорец Мениджър на имена.

• Грешка: Вярно, ако името съдържа грешна препратка.

• Връзка: Хипервръзка, която при щракване активира посочения диапазон. Само имена, които се отнасят до клетки или диапазони, включват връзка.

• Коментар: Коментарът за името, ако има такъв.

VBA кодът

За да използвате този код, натиснете Alt+F11, за да активирате Visual Basic Editor. След това изберете Вмъкване → Модул, за да вмъкнете нов VBA модул. Копирайте кода и го поставете в новия модул.

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