Узнайте, как отлаживать пользовательские функции в Excel

Поскольку вы уже знаете, как создавать UDF (и, я надеюсь, вы также пробовали применять их в своем Excel), давайте копнем немного глубже и посмотрим, что можно сделать, если ваша пользовательская функция не работает.

Чтобы решить некоторые проблемы при создании пользовательской функции, вам, скорее всего, потребуется запустить отладку. Тогда вы можете быть уверены, что функция работает корректно.

Мы рассмотрим следующие методы отладки:

Когда вы создаете пользовательскую функцию, всегда есть вероятность, что вы сделаете ошибку. Пользовательские функции обычно довольно сложны. И они не всегда сразу начинают правильно работать. Формула может вернуть неверный результат или ошибку #ЗНАЧ! ошибка. В отличие от стандартных функций Excel, никаких других сообщений вы не увидите.

Есть ли способ пройти пользовательскую функцию шаг за шагом, чтобы проверить, как работает каждый из ее операторов? Конечно! Для этого используется отладка.

Я предложу вам несколько способов отладки вашей пользовательской функции, чтобы вы могли выбрать тот, который работает для вас.

В качестве примера используем пользовательскую функцию GetMaxBetween из одной из наших предыдущих статей, которая вычисляет максимальное число в указанном диапазоне значений:

Функция GetMaxBetween(rngCells As Range, MinNum, MaxNum) Dim NumRange As Range Dim vMax Dim arrNums() Dim i As Integer ReDim arrNums(rngCells.Count) Для каждого NumRange в rngCells vMax = NumRange Select Case vMax Case MinNum + 0,01 To MaxNum – 0,01 arrNums(i) = vMax i = i + 1 Case Else GetMaxBetween = 0 End Select Next NumRange GetMaxBetween = WorksheetFunction.Max(arrNums) End Function

Аргументами функции являются диапазон ячеек, в которые записываются числа, а также верхний и нижний предел значений.

Разместите функцию MsgBox в важных местах

Для того чтобы следить за выполнением расчетов, вы можете выводить значения наиболее важных переменных на экран в нужных местах. Это можно сделать с помощью всплывающих диалоговых окон.

MsgBox это диалоговое окно, которое вы можете использовать, чтобы показать какое-либо сообщение пользователю.

Синтаксис MsgBox аналогичен другим функциям VBA:

MsgBox(подсказка [, buttons] [, title] [, helpfile, context])

быстрый является обязательным аргументом. Он содержит сообщение, которое вы видите в диалоговом окне. Его также можно использовать для отображения значений отдельных переменных.

Все остальные аргументы являются необязательными.

[buttons] – определяет, какие кнопки и значки отображаются в MsgBox. Например, если мы используем опцию vbOkOnlyто только ХОРОШО будет отображаться кнопка. Даже если вы пропустили этот аргумент, эта кнопка используется по умолчанию.

[title] – здесь вы можете указать заголовок окна сообщения.

Перейдем от слов к практике и приступим к отладке. Чтобы отобразить сообщение, добавьте следующую строку в код GetMaxBetween определяемая пользователем функция перед Дело еще оператор:

MsgBox vMax,, “Count -” & я

Вот что мы получим в результате:

Функция GetMaxBetween(rngCells As Range, MinNum, MaxNum) Dim NumRange As Range Dim vMax Dim arrNums() Dim i As Integer ReDim arrNums(rngCells.Count) Для каждого NumRange в rngCells vMax = NumRange Select Case vMax Case MinNum + 0,01 To MaxNum – 0,01 arrNums(i) = vMax i = i + 1 MsgBox vMax,, “Count -” & i Case Else GetMaxBetween = 0 End Select Next NumRange GetMaxBetween = WorksheetFunction.Max(arrNums) End Function

С использованием vМакс. переменной в диалоговом окне, мы увидим, какие числа соответствуют критериям выбора, чтобы мы могли выбрать наибольшее из них. С помощью выражения «Count -» & I в строке заголовка мы указываем, сколько чисел мы уже выбрали для определения максимального значения. Счетчик будет увеличиваться с каждым новым значением.

После того, как у нас есть набор UDF, мы применяем приведенную ниже формулу к диапазону дат:

= GetMaxBetween (A1:A6,10,50)

После нажатия кнопки Enter вы увидите сообщение, как на скриншоте ниже:
Окно сообщений отладки UDF Excel

Это первое число в диапазоне A1:A6, отвечающее критериям: больше 10, но меньше 50.

После того, как вы нажмете ОК, появится второе сообщение с цифрой 14. Остальные цифры не соответствуют критериям выбора. Поэтому функция завершается и возвращает наибольшее из двух значений, 17.

MsgBox можно использовать в самых важных местах вашей пользовательской функции, чтобы контролировать, как меняются значения отдельных переменных. Окна сообщений могут быть очень полезны, когда у вас есть большая функция и много вычислений. В этом случае вам будет легко определить, в какой части кода возникает ошибка.

Определите точки остановки и выполняйте шаг за шагом

Вы можете добавить точки останова в код вашей функции, где выполнение кода остановится. Таким образом, вы можете шаг за шагом следить за процессом расчета. При этом вы можете видеть, как меняются значения переменных.

Чтобы добавить точку останова, поместите курсор на строку, содержащую оператор, на котором вы хотите сделать паузу. Затем щелкните правой кнопкой мыши и выберите Отладка -> Переключить точку останова или просто нажмите F9. Вы также можете щелкнуть в нужном месте на вертикальной серой области слева от кода функции.

Появится красный круг, как вы можете видеть на скриншоте ниже. Строка кода, на которой расчет будет остановлен, выделена красным цветом.
Как добавить точку останова для отладки

Теперь окно редактора VBA будет открываться при запуске функции. Курсор будет находиться в том месте, где вы остановились.

Если вы наведете курсор мыши на любую из переменных в коде функции, вы увидите их текущее значение:
Отладка UDF с точкой останова

Нажмите F5, чтобы продолжить расчет.

Примечание. После точки останова вы можете начать отслеживать ход вычислений шаг за шагом. Если вы нажмете кнопку F8, будет выполнена только одна следующая строка кода VBA. Желтая линия со стрелкой также переместится на позицию последнего выполненного кода.

Так как выполнение функции снова приостановлено, вы можете просмотреть текущие значения всех переменных функции с помощью курсора мыши.

Следующее нажатие F8 продвинет нас на один шаг вперед. Таким образом, вы можете нажать F8 до конца расчета. Или нажмите F5, чтобы продолжить расчет до следующей точки останова.

При возникновении ошибки курсор останавливается в том месте кода, где произошла ошибка. И вы также увидите всплывающее сообщение об ошибке. Это позволяет легко определить причину проблемы.

Указанные вами точки останова будут применяться до тех пор, пока вы не закроете файл. Когда вы снова откроете его, вам нужно будет установить их снова. Не самый удобный метод, вам не кажется?

Однако эту проблему можно решить. Вставьте Останавливаться оператор в код функции в нужных точках, и вы можете остановить выполнение программы так же, как и при использовании точек останова.
Оператор Stop для точки останова

Когда VBA встречает Останавливаться оператор, он остановит выполнение программы и будет ждать вашего действия. Проверьте значения переменных, затем нажмите F5, чтобы продолжить.

Или нажмите F8, чтобы выполнить функцию шаг за шагом, как описано выше.

Останавливаться оператор является частью программы и поэтому не удаляется, как в случае с точкой останова. Когда вы закончите отладку, удалите его самостоятельно. Или превратите его в комментарий, поставив перед ним одинарную кавычку (‘).

Отладка с использованием оператора Debug.Print

Вы можете разместить Отладка.Печать в коде функции в нужном месте. Это полезно для проверки значений переменных, которые циклически меняются.

Вы можете увидеть пример производительности Debug.Print на скриншоте ниже.
Добавьте оператор Debug.Print в пользовательскую функцию.

Заявление Отладка. Печать i, vMax печатает значения и их порядковые номера.

В окне Immediate вы видите два числа (17 и 14) из выбранного диапазона, что соответствует установленным ограничениям и среди которых будет выбрано максимальное. Цифры 1 и 2 означают, что функция завершила 2 цикла, в которых были выбраны числа. Мы видим значения наиболее важных переменных, как мы это делали ранее с MsgBox. Но это не остановило функцию.

Вызов функции из процедуры

Пользовательскую функцию можно вызывать не из ячейки рабочего листа, а из процедуры. В этом случае все ошибки будут отображаться в окне редактора Visual Basic.

Вот как можно вызвать определяемую пользователем функцию GetMaxBerween из процедуры:

Sub Test() Dim x x = GetMaxBetween(Range (“A1:A6”), 10, 50) MsgBox(x) End Sub

Установите курсор в любом месте кода и нажмите F5. Если в функции нет ошибки, вы увидите всплывающее окно с результатом расчета.

В случае ошибки вы увидите соответствующее сообщение в редакторе VBA. Расчет будет остановлен, а строка кода, в которой произошла ошибка, будет выделена желтым цветом. Вы можете легко определить, где и почему произошла ошибка.

Это все. Теперь вы создали свою надстройку, добавили ее в Excel и можете использовать в ней UDF. Если вы хотите использовать больше пользовательских функций, просто напишите код в модуле надстройки в редакторе VBA и сохраните его.

Это все на сегодня. Мы рассмотрели различные способы отладки пользовательских функций и узнали, как использовать их в книге. Мы очень надеемся, что вы найдете эти рекомендации полезными. Если у вас остались вопросы, пишите в комментариях к этой статье.

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *