Excel UDF не работает: проблемы и решения
В этой статье мы рассмотрим проблемы, с которыми вы можете столкнуться при использовании пользовательских функций в своих книгах. Я постараюсь показать вам, что вызывает их и как легко их можно решить.
Вот о чем мы поговорим:
Ранее мы говорили о том, что такое пользовательская функция, как ее создать и использовать. Если вы чувствуете, что вам нужно заранее освежить базовые знания о UDF, сделайте паузу и просмотрите мою предыдущую статью.
Почему Excel UDF не пересчитывает?
Когда вы вносите какие-либо изменения в свою книгу, Excel не будет пересчитывать каждую формулу, которая у вас там есть. Он обновит результаты только для тех формул, которые связаны с измененными ячейками.
Но это касается стандартных функций Excel. Что касается пользовательских, Excel не может проверить код VBA и определить другие ячейки, которые также могут повлиять на результат пользовательской функции. Поэтому ваша пользовательская формула может не измениться при внесении изменений в книгу.
Чтобы решить эту проблему, вам просто нужно использовать Application.Volatile утверждение. Прочтите следующую главу, чтобы узнать пошаговые инструкции о том, как его применять.
Изменчивые и энергонезависимые пользовательские функции
По умолчанию пользовательские функции в Excel не являются энергозависимыми. Это означает, что UDF пересчитывается только в том случае, если изменяется значение любой из ячеек, на которые он ссылается. Но если изменится формат ячеек, имя рабочего листа, имя файла, то никаких изменений в UDF не произойдет.
Перейдем от слов к примерам. Например, вам нужно записать название вашей книги в ячейке. Для этого вы создаете пользовательскую функцию:
Функция WorkbookName() As String WorkbookName = ThisWorkbook.Name End Function
Теперь представьте следующий случай. Вы написали пользовательскую формулу =ИмяКниги() в ячейку и получил там имя файла. Через какое-то время вы решили переименовать файл и сохранили его под другим именем. Но вы смотрите на значение в ячейке и видите, что оно не изменилось. Есть еще старое имя файла, которое уже не подходит.
Поскольку в этой функции нет аргументов, функция не пересчитывается (даже если вы измените имя книги, закроете ее, а затем снова откроете).
Примечание. Чтобы пересчитать все функции в вашем файле, вы можете использовать сочетание клавиш Ctrl + Alt + F9.
Есть ли более простой способ? Чтобы формула пересчитывалась каждый раз при изменении рабочего листа, вам нужна дополнительная строка кода. Вставьте следующий фрагмент кода в начало вашей функции:
Application.Volatile
Итак, ваш код будет выглядеть так:
Функция WorkbookName() As String Application.Volatile WorkbookName = ThisWorkbook.Name End Function
Теперь ваш UDF является изменчивым, поэтому он будет автоматически пересчитан, если какая-либо ячейка на листе была пересчитана или в книге произошли какие-либо изменения. Как только вы измените имя файла, вы сразу же увидите это обновление.
Примечание. Имейте в виду, что слишком много нестабильных функций могут замедлить работу вашего Excel. В конце концов, существует слишком много пользовательских функций, которые выполняют сложные вычисления и постоянно работают с большими диапазонами данных.
Поэтому я рекомендую использовать волатильность только там, где это действительно необходимо.
Почему пользовательские функции недоступны
Когда вы вводите первые буквы имени пользовательской функции, она появляется в раскрывающемся списке рядом с ячейкой ввода, как и стандартные функции Excel.
Однако это происходит не всегда. Какие ошибки могут вызвать эту ситуацию?
Если у вас Excel 2003-2007, то UDF никогда не появляется в выпадающем списке. Там вы видите только стандартные функции.
Но даже если вы используете более новую версию Excel, вы можете случайно совершить еще одну ошибку.
Видите ли, пользовательская функция должна находиться в стандартном модуле VBA с именем Modules. Когда вы добавляете новый модуль для записи кода функции, автоматически создается папка Modules, в которую записываются все модули.
Но иногда бывает так, что новый модуль не создается. На следующем снимке экрана видно, что код пользовательской функции находится в модуле «Объекты Microsoft Excel» вместе с ThisWorkbook.
Дело в том, что вы не можете разместить пользовательскую функцию в области кода рабочего листа или рабочей книги. В этом случае функция работать не будет. Более того, он не появится в выпадающем списке функций. Поэтому код всегда должен быть в папке Модули.
Текст справки по пользовательской функции Excel не отображается
Другая проблема может возникнуть — это подсказка, которую вы видите при вставке пользовательской функции. Если вы используете стандартную функцию, вы всегда будете видеть всплывающую подсказку для функции и ее аргументов. Но как насчет UDF?
Если у вас много пользовательских функций, вам будет крайне сложно запомнить, какие вычисления делает каждая из них. Еще труднее будет запомнить, какие аргументы использовать. Я думаю, что было бы неплохо иметь описание ваших пользовательских функций в качестве напоминания.
Для этого я предлагаю использовать Application.MacroOptions метод. Это поможет вам показать описание не только функции, но и каждого из ее аргументов в окне Мастера функций. Вы видите это окно, когда нажимаете кнопку Fx в строке формул.
Давайте посмотрим, как добавить такую подсказку в ваши пользовательские функции. В предыдущей статье мы рассмотрели пользовательскую функцию GetMaxBetween. Он находит максимальное число в указанном диапазоне и принимает три аргумента: диапазон числовых значений, а также максимальное и минимальное значение для поиска.
Теперь мы добавим описание этой пользовательской функции. Для этого создайте и запустите Application.MacroOptions команда. Для GetMaxBetween функцию, вы можете запустить следующую команду:
Sub RegisterUDF() Dim strFuncName As String ‘имя функции, которую вы хотите зарегистрировать Dim strDescr As String’ описание самой функции Dim strArgs () As String ‘описание аргументов функции ‘ Register GetMaxBetween function ReDim strArgs (1 To 3)’ Количество аргументов в вашей функции strFuncName = “GetMaxBetween” strDescr = “Максимальное число в указанном диапазоне” strArgs (1) = “Диапазон числовых значений” strArgs (2) = “Нижняя граница интервала” strArgs (3) =” Верхний интервал border ” Макрос Application.MacroOptions: = strFuncName, _ Описание: = strDescr, _ ArgumentDescriptions: = strArgs, _ Категория: =” Мои пользовательские функции ” End Sub
или же
Sub RegisterUDF() Application.MacroOptions Macro: = “GetMaxBetween”, _ Описание: = “Максимальное число в указанном диапазоне”, _ Категория: = “Мои пользовательские функции”, _ Описание аргументов: = Массив (_ “Диапазон числовых значений” , _ “Нижняя граница интервала” , _ “Верхняя граница интервала”) End Sub
Переменная ул ИмяФункции это имя функции. strDescr – описание функции. стрАргс переменные содержат подсказки для каждого аргумента.
Вы можете задаться вопросом, что является четвертым аргументом Application.MacroOptions. Этот необязательный аргумент называется Категория и указывает класс функций Excel, которые наши пользовательские ПолучитьМаксМежду () Функция будет помещена в нее. Вы можете назвать ее в соответствии с любой из существующих категорий: Math & Trig, Statistical, Logical и т. д. Вы можете указать имя для новой категории, в которую вы поместите функции, которые вы создаете. Если вы не используете аргумент «Категория», пользовательская функция будет автоматически помещена в категорию «Определяется пользователем».
Вставьте код функции в окно модуля:
Затем нажмите на кнопку «Выполнить». Команда выполнит все настройки для использования FX кнопка с вашим ПолучитьМаксМежду() функция.
Если вы попытаетесь вставить функцию в ячейку с помощью Вставить функцию инструмент, вы увидите, что есть ваш GetMaxBetween функция находится в категории «Мои пользовательские функции»:
Вы можете просто начать вводить имя функции в ячейку, и вы увидите свою пользовательскую функцию в раскрывающемся списке функций для выбора.
Затем позвоните в Мастер функций с помощью кнопки Fx.
Кончик. Вы также можете использовать комбинацию клавиш CRTL + A, чтобы открыть мастер функций.
в Мастер функций вы увидите описание вашей функции, а также подсказку для первого аргумента. Если вы наведете курсор на второй или третий аргумент, вы также увидите подсказки для них.
Если вы хотите изменить текст этих подсказок, измените значения strDescr а также стрАргс переменные в ЗарегистрироватьUDF () код. Затем запустите ЗарегистрироватьUDF () команда снова.
Если вы хотите отменить все сделанные настройки и очистить описание функции, запустите этот код:
Sub UnregisterUDF() Application.MacroOptions Macro: = “GetMaxBetween”, _ Описание: = Empty, ArgumentDescriptions: = Empty, Category: = Empty End Sub
Есть еще один способ получить подсказку при входе в пользовательскую функцию. Введите имя функции, а затем нажмите Ctrl + Shift + A:
=GetMaxBetween( + Ctrl + Shift + A
Вы увидите список всех аргументов функции:
К сожалению, здесь вы не увидите описания функции и ее аргументов. Но если имена аргументов довольно информативны, они тоже могут быть полезны. Тем не менее, это лучше, чем ничего 🙂
Потребуется немного больше работы, чтобы создать IntelliSense для пользовательских функций, которые работают как стандартные функции Excel. К сожалению, Microsoft не предоставляет никаких вариантов. В настоящее время единственным доступным решением является расширение Excel-DNA IntelliSense. Дополнительную информацию вы можете найти на сайт разработчика.
Надеемся, что эти рекомендации помогут вам решить проблемы, когда ваша пользовательская функция не работает или работает не так, как вам хотелось бы. Однако, если ваш UDF по-прежнему не работает, пожалуйста, точно опишите проблему в разделе «Комментарии». Мы постараемся разобраться и найти решение для вас 😉
