Мы продолжаем серию статей о работе с пользовательскими функциями. В наших предыдущих статьях мы познакомились с пользовательскими функциями и узнали, как их создавать и использовать. У пользовательских функций есть много преимуществ, но есть и много ограничений при их использовании.
В этой статье мы увидим преимущества и недостатки пользовательских функций в Excel. Вы также узнаете, что пользовательские функции и макросы VBA - это совсем не одно и то же, увидите разницу и выберете то, что вам больше подходит.
Мы надеемся, что эта статья расширит ваши знания об пользовательских функциях и поможет вам еще более эффективно использовать их в своих книгах Excel.
Пользовательская функция против макроса VBA
И пользовательские функции, и макросы VBA создаются при помощи редактора VBA. В чем же разница между ними и чему отдать предпочтение?
Самое главное отличие — функция производит вычисление, а макрос выполняет какое-то действие.
Поэтому функция работает только с текущей ячейкой, а макрос может производить действия вне ячейки.
Пользовательская функция, как и обычная функция Excel, должна быть записана в ячейке. В результате её выполнения ячейка получает какое-то значение. При этом невозможно изменить значения других ячеек, а также некоторые свойства текущей ячейки (в частности, форматирование). Однако, вы можете использовать пользовательскую функцию в формулах условного форматирования.
Макрос VBA использует другой вид процедур. Что это означает для нас? Когда вы создаете функцию в редакторе Visual Basic, то вы начинаете с оператора Function и заканчиваете End Function. При записи макроса вы ставите в начале оператор Sub и заканчиваете End Sub.
Не все операторы Visual Basic могут быть использованы при создании настраиваемых функций. По этой причине макрос является более универсальным решением.
Макрос Excel не требует передачи каких-либо аргументов, в отличие от пользовательской функции.
Если вы используете макросы, вам нужно быть очень внимательным и осторожными при изменении и форматировании вашей таблицы. Дело в том, что некоторые параметры макросов могут использовать адреса ячеек либо элементы форматирования (например, цвет). Если вы будете перемещать ячейки, добавлять либо удалять строки и столбцы, менять формат ячеек, то вы легко можете «сломать» ваши макросы. Особенно это возможно, если вы будете передавать ваш файл коллегам, которые не знают, как работают ваши макросы.
Например, у вас есть файл с отлично работающим макросом. Эта формула вычисляет процентное соотношение ячеек от A1 до A4. Макрос изменяет цвет этих ячеек на желтый. В активной ячейке устанавливается процентный формат.
Если вы или кто-то другой решите вставить новую строку, макрос продолжит поиск значения в ячейке (4,1), завершится ошибкой и вернет сообщение:
В данном случае ошибка возникла из-за деления на ноль. А если макрос будет выполнять, к примеру, суммирование, то вы просто получите неверный результат. Но не будете об этом знать, потому что просто не заметите.
В отличие от макросов, пользовательские функции не могут вызвать такую неприятную ситуацию.
Ниже вы видите те же вычисления, но с использованием UDF. Здесь вы можете указать ячейки ввода в любом месте рабочего листа, и вы не столкнетесь с какими-либо неожиданными проблемами при его изменении.
В ячейке C3 я написал формулу
= UDF_vs_Macro (A1, A4)
Затем я вставил пустую строку, и формула изменилась, как вы можете видеть на скриншоте выше.
Теперь мы можем перемещать ячейку ввода или ячейку с функцией куда угодно. Результат всегда будет верным.
Дополнительным преимуществом использования пользовательских функций является то, что они автоматически обновляются при изменении ячейки ввода. При использовании макросов пользователь всегда должен убедиться, все ли данные актуальны, и при необходимости перезапустить макрос.
Итак, исходя из этого, я предпочитаю использовать функции везде, где это возможно, и использовать макросы только для прочих действий, не связанных с расчетами.
Ограничения и недостатки использования пользовательских функций
Я уже упоминал о преимуществах пользовательских функций выше. Проще говоря, они умеют выполнять вычисления, которые невозможны со стандартными функциями Excel. Кроме того, они могут сохранять и использовать длинные и сложные формулы, превращая их в единую функцию. И вам не придется снова и снова писать сложные формулы либо корректировать старые. Рассмотрев все плюсы, давайте остановимся на минусах.
Поговорим подробнее о недостатках пользовательских функций:
- Создание их требует использования VBA. Нет никакого способа обойти это. Это означает, что пользователь не может записывать создаваемую функцию так же, как макрос Excel. Вы должны сами написать код. Однако вы можете копировать и вставлять части ранее записанного кода макроса в свою функцию. Вам просто нужно знать об ограничениях пользовательских функций.
- Еще одним недостатком является то, что, как и любая другая функция Excel, она может возвращать только одно значение или массив значений в ячейку. Он просто выполняет вычисления, не более того.
- Если вы хотите поделиться своей книгой с коллегами, обязательно сохраните созданную функцию в том же файле. В противном случае ваши пользовательские функции для них не будут работать.
- Пользовательские функции, созданные с помощью редактора VBA, работают медленнее, чем обычные функции. Особенно это заметно в больших таблицах. К сожалению, пока VBA — очень медленный язык программирования. Поэтому, если у вас много данных, по возможности старайтесь использовать стандартные функции или создавайте собственные с помощью функции LAMBDA.
Ограничения пользовательских функций:
- Они предназначены для выполнения вычислений и возврата значения. Их нельзя использовать вместо макросов.
- Они не могут изменять содержимое каких-либо других ячеек (только активной ячейки).
- Названия функций должны соответствовать определенным правилам. Например, вы не можете использовать имя, которое соответствует имени собственной функции Excel или адресу ячейки, например AB123.
- Ваша пользовательская функция не может содержать пробелов в имени, но может включать символ подчеркивания. Однако предпочтительным методом является использование заглавных букв в начале каждого нового слова (например, GetMaxBetween).
- Они не умеют копировать и вставлять ячейки в другие области рабочего листа.
- Они не могут изменить активный рабочий лист.
- Нет возможности изменить форматирование активной ячейки. Если вы хотите изменить форматирование ячейки при отображении разных значений, вам следует использовать условное форматирование.
- Они не могут открывать дополнительные книги.
- Их нельзя использовать для запуска макросов с помощью Application.OnTime.
- Пользовательская функция не может быть создана с помощью средства записи макросов.
- Функции не отображаются в диалоговом окне «Разработчик» > «Макросы».
- Ваши функции появятся в диалоговом окне (Вставить > Функция) и в списке функций, только если они объявлены как общедоступные (это значение по умолчанию, если не указано иное).
- Любые функции, объявленные как частные, не будут отображаться в списке функций.
Довольно медленная операция, а также некоторые ограничения в использовании могут заставить вас задуматься: «Какая польза от этих пользовательских функций?»
Они могут пригодиться, если мы помним о наложенных на них ограничениях. Если вы научитесь правильно их создавать и использовать, вы сможете написать свою библиотеку функций. Это значительно расширит ваши возможности по работе с данными в Excel.
На мой взгляд, пользовательские функции отлично экономят время. А что насчет вас? Вы уже пробовали создать свои собственные функции?
