Забыли пароль?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (65)
Разное (42)
Баги и глюки Excel (4)

Как добавить описание(подсказку) к UDF и аргументам UDF

Почти каждый, кто освоил написание собственных функций пользователя(UDF)(если еще не знаете что это такое - Что такое функция пользователя(UDF)?), рано или поздно сталкивается с тем, что справка для них недоступна, описание функции и её аргументов тоже. Т.е. по умолчанию окно вызова UDF, в отличии от встроенных функций, выглядит очень неинформативно:
Стандартное окно вызова UDF

А хотелось бы как минимум так:
Окно вызова UDF с описанием

И возникает вопрос – а возможно ли вообще добавить описание к функции и аргументам? Можно. И есть как разные способы, так и нюансы у каждого из них:


Добавление описания к UDF без «кодинга»
Это самый простой способ. Покажу на примере простой функции:

Function ТекущаяДата()
    ТекущаяДата = Date
End Function

Что надо сделать? Для начала надо заменить Function на Sub:

Sub ТекущаяДата()
    ТекущаяДата = Date
End Sub

После этого переходим в окно Excel -вкладка Разработчик(Developer) -Макросы(Macros). Находим там переименованную в Sub функцию -нажимаем кнопку Параметры(Options) и в появившемся окне заполняем поле Описание(Description):
Добавление описания к UDF без макросов
Нажимаем Ок, закрываем первое окно(с перечнем макросов). После этого переименовываем обратно Sub в Function. Сохраняем файл. Переходим в диспетчер функций и в категории Определенные пользователем(User Defined) находим нашу функцию «ТекущаяДата». Выделяем и видим, что для неё теперь есть описание:
UDF с подсказкой



Добавление описания к UDF кодом VBA(все версии)

Тоже самое можно сделать и при помощи кода. Он несложный и работает(как и следовало ожидать) во всех версиях:

Sub RegisterUDF_AllVersions()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату"
End Sub

Основные моменты:

  • Macro – здесь записываем имя функции. Текст буковка в буковку должен совпадать с именем нашей функции, иначе получим ошибку «Метод ˈMacro Optionsˈ of object ˈ_Applicationˈ failed», т.к. VBA не найдет сопоставления записанного текста с реальной процедурой или функцией.
  • Description – это и есть наше описание. Здесь маленький нюанс: текст описания не должен превышать 255 символов. Иначе получим все ту же ошибку «Метод ˈMacro Optionsˈ of object ˈ_Applicationˈ failed».

И очень важный момент: вызов Application.MacroOptions должен происходить исключительно тогда, когда открыта и отображена хотя бы одна книга. Иначе опять же получим все ту же ошибку «Метод ˈMacro Optionsˈ of object ˈ_Applicationˈ failed». И конечно, код регистрации функций таким образом надо делать каждый раз при открытии книги с UDF, т.к. сразу после закрытия книги с UDF регистрация функций сбрасывается(что логично, т.к. сами UDF становятся недоступными). Поэтому процедуру RegisterUDF_AllVersions правильнее всего запускать в момент открытия книги через событие Workbook_Open(о том где искать это событие можно почитать в этой статье: Модуль книги)
В отличии от «ручного» метода – через MacroOptions можно указать в какую категорию добавить свою функцию. Добавить можно как в существующие(Математический, Дата и время и т.п.), так и в собственную. Правильнее всего либо не указывать категорию, либо делать это в собственной(чтобы не было путаницы среди встроенных функций).
Код добавления в собственную категорию:

Sub RegisterUDF_AllVersions()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату", _
            Category:="Excel-VBA.ru"
End Sub

Но если все же необходимо добавить в имеющиеся категорию свою функцию, в параметре Category необходимо указать порядковое число нужной категории. Числа, используемые Excel в MacroOptions, перечислены в таблице ниже:

Номер Категория Категория на англ
1 Финансовые Financial
2 Дата и время Date & Time
3 Математические Math & Trig
4 Статистические Statistical
5 Ссылки и массивы Lookup & Reference
6 Работа с базой данных Database
7 Текстовые Text
8 Логические Logical
9 Информация Information
14 Определенные пользователем User Defined

Например, чтобы добавить нашу функцию «ТекущаяДата» в категорию Дата и Время необходимо указать номер категории 2:

Sub RegisterUDF_AllVersions()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату", _
            Category:=2
End Sub


Добавление описания к UDF и её аргументам в Excel(Excel 2010 и выше)

Однако описанными способами можно добавить описание только к самой функции – аргументы так и останутся без пояснений.
Но начиная с версии 2010 в Excel появилась возможность через MacroOptions добавить описание и к аргументам. Это делается несложно.
Добавим для нашей функции ТекущаяДата аргумент:

Function ТекущаяДата(Только_месяц As Boolean)
    If Только_месяц Then
        ТекущаяДата = Month(Date)
    Else
        ТекущаяДата = Date
    End If
End Function

Аргумент Только_месяц будет отвечать за то, как записать дату: либо как дату в формате даты-времени Excel, либо только номер месяца. Т.к. тип назначен Boolean, то и указывать при записи функции мы должны либо ИСТИНА(TRUE) либо ЛОЖЬ(FALSE). Добавим описание через MacroOptions:

Sub RegisterUDF_2010()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату", _
            Category:="Excel-VBA.ru", _
            ArgumentDescriptions:=Array("ИСТИНА, если в ячейку необходимо записать только номер месяца")
End Sub

Подсказка к аргументам
За описания к аргументам отвечает параметр ArgumentDescriptions. Обращаю внимание, что передавать описания необходимо именно через массив(Array). И текст внутри кавычек не должен превышать 255 символов. Иначе явной ошибки выполнения хоть и не будет, но и описание к аргументам не добавится.
Если у нас более одного аргумента – то в Array мы записываем текст в кавычках через запятую. Например, добавим еще один аргумент: МесяцКакЧисло. Он отвечает за то, записать месяц его порядковым числом или записать его имя. Функция примет вид:

Function ТекущаяДата(Только_месяц As Boolean, МесяцКакЧисло As Boolean)
    If Только_месяц Then
        If МесяцКакЧисло Then
            ТекущаяДата = Month(Date)
        Else
            ТекущаяДата = MonthName(Month(Date))
        End If
    Else
        ТекущаяДата = Date
    End If
End Function

И соответственно, добавляем описание для второго аргумента:

Sub RegisterUDF_2010()
    Application.MacroOptions _
            Macro:="ТекущаяДата", _
            Description:="Возвращает в ячейку текущую дату", _
            Category:="Excel-VBA.ru", _
            ArgumentDescriptions:= _
                Array("ИСТИНА, если в ячейку необходимо записать только номер месяца", _
                      "ИСТИНА, если в ячейку необходимо записать имя месяца(если аргумент 'Только_месяц' = ИСТИНА)")
End Sub

Здесь важно помнить, что описания к аргументам должны идти в том же порядке, в котором записаны сами аргументы для функции. Иначе описания будут назначены неправильно. Длина описания для каждого аргумента(не всего текста внутри Array!) не должна превышать 255 символов.



Добавление описания к UDF и её аргументам в Excel(Excel 2007 и ниже)

А как быть с пользователями, у которых нет 2010 Excel и выше? Да, остались еще и такие. И их немало. Здесь все несколько сложнее и печальнее. Парочка отличающих моментов:

  1. Необходимо обязательно указать имя библиотеки DLL на ПК, в которой будем хранить все описания. Здесь лучше указывать DLL вроде user32.dll, т.к. она есть на всех ПК под Windows
  2. Для каждой UDF необходимо указывать еще и функцию в выбранной DLL, в которой мы будем хранить наше описание. Что куда сложнее, т.к. далеко не все знают какие функции в какой DLL вообще хранятся
  3. Если в методе MacroOptions мы могли для каждого аргумента и для самого описания функции указать до 255 символов, то здесь ограничение в 255 символов относится ко всему тексту вообще. Т.е. и описание самой функции и описание к её аргументам все вместе не должны превышать 255 символов(и более того: сюда еще входит и имя DLL и имя функции и имена аргументов и еще чутка попутного хлама)). Это значительно усложняет выдумывание описаний, т.к. они должны быть краткими и понятными одновременно
  4. Отменять регистрацию описаний к функции нам придется самостоятельно

Итак, сам метод на примере все той же функции ТекущаяДата с двумя аргументами:

Sub CallRegister2007()
    Dim szArgString As String
    szArgString = "REGISTER(""user32.dll"",""CharNextA"",""" & String$(3, "P") _
        & """,""ТекущаяДата"",""Только_месяц,МесяцКакЧисло""," & 1 & ",""Excel-VBA.ru" _
        & """,,,""Возвращает в ячейку текущую дату"",""Записать только номер месяца"",""Записать имя месяца если 'Только_месяц'=True"")"
 
    'Длина описания к аргументам и самой функции(szArgString) не должна превышать 255 символов
    If Len(szArgString) <= 255 Then
        Application.ExecuteExcel4Macro szArgString
    Else
        MsgBox "Описание содержит более 255 символов", vbExclamation, "www.excel-vba.ru"
    End If
End Sub

Здесь мы используем весьма древнюю функцию REGISTER из набора макрокоманд. Эта команда использовалась ранее для регистрации DLL, но нашла применение и в такой экзотической форме. Основные моменты:

  • user32.dll – существующая на компьютере библиотека DLL, в которой есть общедоступные функции API
  • CharNextA – имя функции API внутри указанной DLL(если не вдаваться в подробности, то с ней ассоциируем описания для нашей функции)
  • String$(3, "P") – функция String повторяет указанный символ(Р) заданное число раз(3). Число 3 получается из: 2 аргумента + описание к самой функции. Буква P вписана не случайно: функция REGISTER требует именно эту букву для регистрации функций(есть еще другие обозначения, но нам они не нужны)
  • Все остальное должно быть уже интуитивно понятно. Особое внимание кавычкам и запятым: ничего лишнего здесь нет, все как положено, даже если между кавычками ничего не записано или где-то кажется много или мало кавычек.
    И в итоге получаем текст szArgString, который и не должен превышать 255 символов. Поэтому каждый символ на счету и имеет смысл не прописывать функцию в отдельную категорию, а либо оставить её в категории по умолчанию(Определенные пользователем), либо указать любую иную из существующих. Тогда надо указать лишь число и мы сэкономим чутка символов. Запись будет такая:

    szArgString = "REGISTER(""user32.dll"",""CharNextA"",""" & String$(3, "P") _
            & """,""ТекущаяДата"",""Только_месяц,МесяцКакЧисло""," & 1 & "," & 14 _
            & ",,,""Возвращает в ячейку текущую дату"",""Записать только номер месяца"",""Записать имя месяца если 'Только_месяц'=True"")"

    Так же, в отличии от метода MacroOptions, зарегистрированные таким образом функции необходимо принудительно «выключать» при закрытии файла(для этого как правило используется событие Workbook_BeforeClose(о том где искать это событие можно почитать в этой статье: Модуль книги). Код для выключения:

    Sub CallUnregister2007()
        'Для исключения ошибки перерегестрируем функцию
        Application.ExecuteExcel4Macro "REGISTER(""user32.dll"",""CharNextA"",""P"",""ТекущаяДата"",,0)"
        'удаляем функцию
        Application.ExecuteExcel4Macro "UNREGISTER(ТекущаяДата)"
    End Sub

    Однако все эти коды не делают одного: они не работают в режиме записи функций в ячейку без использования диспетчера функций(т.е. непосредственный ввод функций в ячейку без использования мастера функций). Ни описание функции, ни описания аргументов показаны не будут и обойти это нельзя никак. Этот функционал для собственных функций доступен исключительно при написании функций в надстройках XLL(надстройки функций, которые можно создать только в языках вроде С, в VBA не доступно).

    В приложенном к статье файле Вы найдете усложненный код регистрации функций. Он автоматически определяет версию Excel и применяет нужный метод, проверяет кол-во символов и конечно, запускается при открытии книги и при закрытии "убирает" описания(если необходимо). Сами описания и имена функций создаются на отдельном листе RegisterUDF_Description, где необходимо записать имена нужных функций, имена аргументов и описания к функции и аргументам. Так же там предзаполнены 14 функций API из user32.dll для возможности самостоятельной регистрации.
    Скачать пример:

      Создание подсказок для UDF.xls (97,5 KiB, 23 скачиваний)


    Статья помогла? Поделись ссылкой с друзьями!
      Плейлист   Видеоуроки

    Поиск по меткам

    Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
    Поделитесь своим мнением

    Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


    Для оформления сообщений Вы можете использовать следующие тэги:
    <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

    Тренинги

    Заказать
    Юридическая информация

    Использование материалов сайта

    Политика Конфиденциальности

    ИП Щербаков Дмитрий Валентинович
    ОГРНИП: 318502700083307
    ИНН: 504013350772

    Наши партнеры

    Перейти
    Перейти

    Счетчики

    Рейтинг@Mail.ru Яндекс.Метрика
    © 2020 Excel для всех   Войти