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

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

А хотелось бы как минимум так:

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

• Добавление описания к UDF без «кодинга»
• Добавление описания к UDF кодом VBA(все версии)
• Добавление описания к UDF и её аргументам в Excel(Excel 2010 и выше)
• Добавление описания к UDF и её аргументам в Excel(Excel 2007 и ниже)

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

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

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

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

После этого переходим в окно Excel -вкладка Разработчик(Developer) -Макросы(Macros). Находим там переименованную в Sub функцию -нажимаем кнопку Параметры(Options) и в появившемся окне заполняем поле Описание(Description):

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

Добавление описания к 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, перечислены в таблице ниже:

Например, чтобы добавить нашу функцию «ТекущаяДата» в категорию Дата и Время необходимо указать номер категории 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, но нашла применение и в такой экзотической форме. Основные моменты:

Все остальное должно быть уже интуитивно понятно. Особое внимание кавычкам и запятым: ничего лишнего здесь нет, все как положено, даже если между кавычками ничего не записано или где-то кажется много или мало кавычек.
И в итоге получаем текст 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 КиБ, 584 скачиваний)