Как правильно создавать пользовательские функции и где нужно размещать их код, мы подробно рассмотрели ранее в этой статье. Чтобы решить проблемы при создании пользовательской функции, вам скорее всего придется выполнить ее отладку. Тогда вы будете уверены, что функция работает правильно.
Мы рассмотрим несколько способов отладки пользовательской функции, чтобы вы могли выбрать наиболее подходящий для вас:
Вы можете выбрать для себя наиболее подходящий метод.
Что такое отладка функции?
Когда вы создаете пользовательскую функцию, всегда существует возможность, что вы допустите ошибку и она не сразу будет работать так, как вам нужно.
Пользовательские функции обычно достаточно сложные. И не всегда они сразу начинают работать правильно. Формула может возвратить неверный результат или ошибку #ЗНАЧ!. В отличие от стандартных функций Excel, никаких других сообщений вы не увидите.
Есть ли способ отследить работу пользовательской функции по шагам, чтобы проверить, как работает каждый оператор, входящий в нее? Да, есть. Для этого и используется отладка.
Я предложу вам несколько способов проверки работы пользовательской функции, чтобы вы могли выбрать наиболее подходящий для себя.
В качестве примера используем пользовательскую функцию GetMaxBetween из нашей предыдущей статьи, которая вычисляет максимальное число в указанном диапазоне значений:
Аргументы функции — это диапазон ячеек, в которых записаны числа, а также верхний и нижний предел значений.
Способ 1. Поместите в важных местах функцию MsgBox.
Для того, чтобы следить за выполнением расчетов, можно в нужных местах выводить на экран значения самых важных переменных. Это можно сделать при помощи всплывающих диалоговых окон.
MsgBox — это диалоговое окно, которое вы можете использовать, чтобы показывать пользователю какое-то сообщение.
MsgBox является функцией и имеет синтаксис, аналогичный другим функциям VBA:
MsgBox( prompt [, buttons ] [, title ] [, helpfile, context ] )
prompt — это обязательный аргумент. Он содержит сообщение, которое вы видите в диалоговом окне. Его также можно использовать для отображения значений отдельных переменных.
Все остальные аргументы являются необязательными.
[buttons] — определяет, какие кнопки и значки отображаются в MsgBox. Например, если я использую vbOkOnly, то будет отображаться только кнопка OK. Даже если вы пропустили этот аргумент, эта кнопка используется по умолчанию.
[title] — здесь вы можете указать заголовок окна сообщения.
Приступим к отладке. Чтобы показать сообщение, добавим в код пользовательской функции GetMaxBetween перед оператором Case Else следующую строку:
MsgBox vMax, , "Count - " & i
Получим следующий код:
Function GetMaxBetween(rngCells As Range, MinNum, MaxNum)
Dim NumRange As Range
Dim vMax
Dim arrNums()
Dim i As Integer
ReDim arrNums(rngCells.Count)
For Each NumRange In 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
С помощью переменной vMax в диалоговом окне мы увидим, какие числа соответствуют условиям для отбора, чтобы из них затем выбрать наибольшее. При помощи выражения "Count - " & i в заголовке окна мы указываем, сколько чисел мы уже выбрали, чтобы определить максимальное значение. С каждым новым значением счетчик будет увеличиваться.
В ячейку С1 нашего рабочего листа запишем формулу
=GetMaxBetween(A1:A6;10;50)
После того, как будет нажата клавиша Enter, вы увидите сообщение, как на скриншоте ниже:
Это первое число из диапазона A1:A6, которое соответствует условиям: больше 10, но меньше 50.
После того, как вы нажмете ОК, появится второе сообщение с числом 14. Остальные числа не соответствуют условиям отбора. Поэтому функция завершает работу и возвращает наибольшее из двух значений — 17.
Таким образом, мы шаг за шагом отследили всю работу нашей функции и выяснили, что она работает верно.
Функцию MsgBox можно использовать в самых важных местах кода пользовательской функции, чтобы контролировать изменение значений отдельных переменных. Окна с сообщениями могут оказаться очень полезными, когда у вас большая функция и много вычислений. В этом случае вам будет легко определить, в какой части кода происходит ошибка.
Способ 2. Определите точки остановки и выполните функцию пошагово.
Вы можете добавить в код вашей функции точки останова (Breakpoint), на которых будет останавливаться выполнение вычислений. Так можно пошагово проследить процесс расчёта. При этом вы можете посмотреть, как изменяются значения переменных.
Чтобы добавить точку остановки, поместите курсор в строку с оператором, в котором вы решили приостановить выполнение. Потом нажмите правую кнопку мыши и выберите команду Debug –> Toggle Breakpoint или просто нажмите клавишу F9. Также можно кликнуть в нужном месте на вертикальной серой области слева от кода функции.
Появится красный кружок, как вы видите на скриншоте ниже. Строка кода, где будет остановлен расчет, выделяется красным цветом.
Теперь при работе функции будет открываться окно редактора VBA. Курсор будет установлен в точке остановки.
Если вы наведете курсор мыши на любую из переменных в коде функции, вы сможете увидеть их текущее значение. Для контроля выполнения вычислений это очень полезно. Вы видите это действие на скриншоте ниже.
Чтобы продолжить вычисления, просто нажмите клавишу F5.
Примечание. После точки останова вы можете приступить к пошаговому отслеживанию хода вычислений. Если вы нажмете кнопку F8, будет выполнена только одна следующая строка кода VBA. Желтая линия со стрелкой также переместится в позицию последнего выполненного кода.
Поскольку выполнение функции снова приостанавливается, вы можете просматривать текущие значения всех переменных функции с помощью курсора мыши.
Следующее нажатие F8 продвинет нас на шаг вперед. Таким образом, вы можете нажимать F8 до конца расчета. Или нажмите F5, чтобы продолжить вычисление до следующей точки останова.
В случае возникновения ошибки курсор будет остановлен в той точке кода, где произошла ошибка. И вы также увидите всплывающее сообщение об ошибке. Это позволяет легко определить причину проблемы.
Указанные вами точки останова действуют, пока вы не закрыли файл. При повторном его открытии вам нужно снова их указывать. Согласитесь, что это не всегда удобно.
Эту проблему можно решить. Вставьте в код функции в нужных точках оператор Stop, и вы сможете прервать выполнение программы так же, как при использовании точек останова.
Когда VBA встретит оператор Stop, он остановит расчёты и будет ожидать ваших действий. Проверьте значения переменных, затем нажмите F5 для продолжения.
Оператор Stop является частью программы и поэтому не удаляется при закрытии книги, как это происходит с точкой останова. Когда вы закончите отладку, удалите его сами. Либо превратите в комментарий, поставив перед ним одинарную кавычку (‘).
Способ 3. Отладка при помощи оператора Debug.Print
В коде функции вы можете разместить оператор Debug.Print. Это полезно, чтобы проконтролировать значения переменных, которые циклически изменяются.
Пример такого кода вы видите на скриншоте ниже.
Оператор Debug.Print i, vMax выводит числа, которые были обработаны функцией, и их порядковые номера (начиная с 1).
В окне Immediate вы видите два числа (17 и 14) из выбранного диапазона, которые соответствуют установленным ограничениям и среди которых и будет определено максимальное. Цифры 1 и 2 означают, что функция выполнила 2 цикла, в которых были выбраны числа. Мы видим значения самых важных переменных, как ранее делали при помощи MsgBox. Но при этом не останавливали работу функции.
Способ 4. Вызвать функцию из процедуры
Можно вызвать пользовательскую функцию не из ячейки рабочего листа, а из процедуры. В этом случае все ошибки будут показаны в окне Visual Basic Editor.
Вот как можно вызвать из процедуры пользовательскую функцию GetMaxBerween:
Sub Test()
Dim x
x = GetMaxBetween(Range("A1:A6"), 10, 50)
MsgBox (x)
End SubУстановите курсор в любое место кода и нажмите F5. Если ошибки в функции нет, вы увидите всплывающее окно с результатом расчетов.
В случае ошибки вы увидите соответствующее сообщение в редакторе VBA. Расчет будет приостановлен, и строка кода, в которой произошла ошибка, будет выделена жёлтым цветом. Вы легко определите, где и почему возникла проблема.
Это все. Теперь вы создали свою надстройку, добавили ее в Excel и можете использовать в ней пользовательскую функцию. Если вы хотите использовать больше функций, просто напишите их код в модуле надстройки в редакторе VBA и сохраните его.
На сегодня все. Мы рассмотрели различные способы отладки пользовательских функций Excel и узнали, как использовать их в своей рабочей книге. Мы очень надеемся, что эти рекомендации будут вам полезны. Если возникнут вопросы, пишите в комментариях к этой статье.
