VBA Excel. Изменение значений других ячеек из функции

Изменение значений других ячеек из пользовательской функции VBA Excel с помощью методов Range.Replace и Application.Volatile. Примеры кода.

В первых двух параграфах описано нетрадиционное использование процедуры Function, поэтому не применяйте его в серьезных проектах. Тестирование проводилось в Excel 2016.

Функция с методом Range.Replace

Пользовательская функция не предназначена для изменения значений ячеек, кроме той, в которой она расположена. Попытка присвоить какое-либо значение из функции другой ячейке приводит к неработоспособности функции и отображению в ячейке, где она расположена, сообщения «#ЗНАЧ!».

Но, как ни странно, внутри процедуры Function работает метод Range.Replace, которым мы воспользуемся для изменения значений других ячеек из пользовательской функции.

Пример 1
Эта функция заменяет значение ячейки Cell1 на значение ячейки Cell2 увеличенное на 100. Сама функция размещается в третьей ячейке, чтобы не возникла циклическая ссылка.

В этом примере мы не присваиваем пользовательской функции значение, поэтому отображается значение по умолчанию – 0. Если объявить эту функцию как строковую: Function Primer1(Cell1 As Range, Cell2 As Range) as String, будет возвращена пустая строка.

Изменение значения ячейки C1 (Cell2) приведет к пересчету значения ячейки B1 (Cell1).

Попробуйте очистить или перезаписать ячейку B1 (Cell1), ничего не получится, так как функция Primer1 вновь перезапишет ее значением C1 (Cell2) + 100.

Метод Application.Volatile

Application.Volatile – это метод, который запускает пересчет функции при изменении значения любой ячейки рабочего листа, а не только той, которая присвоена объявленной в функции переменной. Метод Application.Volatile используется только в функциях.

Рассмотрим пересчет функции на следующем примере:

Пример 2

Эта функция будет пересчитываться только при изменении значений ячеек B1 и C1, присвоенных переменным Cell1 и Cell2. При изменении значения ячейки C2, значение ячейки B2 не изменится, так как не будет запущен пересчет функции Primer2.

Функция Primer2 начнет вести себя по-другому, если добавить в нее оператор Application.Volatile (переименуем ее в Primer3):

Пример 3

Теперь при смене значения в ячейке C2, значение ячейки B2 тоже изменится.

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

Безопасное использование функции

Если вам все-таки хочется в важном проекте VBA Excel с помощью одной пользовательской функции изменять значения нескольких ячеек, есть полностью безопасный вариант.

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

В простых случаях для выбора можно использовать функцию Choose или, в более сложных, оператор If…Then…Else и оператор Select Case.

Пример 4
Используем функцию Choose для выбора способа вычисления пользовательской функции в зависимости от значения дополнительного аргумента:

В функцию Primer4 добавлен дополнительный аргумент a, от которого зависит, какое действие будет произведено со значениями ячеек B1 и C1:

На следующем скриншоте представлены результаты вычисления функции в зависимости от значения аргумента a:

  1. В ячейке A1 вычисляется сумма значений ячеек B1 и C1 – аргумент a=1.
  2. В ячейке A2 вычисляется разность значений ячеек B2 и C2 – аргумент a=2.
  3. В ячейке A3 вычисляется произведение значений ячеек B3 и C3 – аргумент a=3.

Пример 5
Используем оператор If…Then…Else в сокращенном виде (If…Then…) для выбора способа вычисления функции в зависимости от значения дополнительного аргумента:

Результаты будут те же, что и в четвертом примере.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

3 комментария для “VBA Excel. Изменение значений других ячеек из функции”

  1. Вы же стремились изменить ячейку B1 и C1 функцией расположенной в ячейке А1 в зависимости от параметра «а». А изменили ячейку A1 в зависимости от параметра «a», и в качестве ее двух аргументов, использовав значения записанные пользователем в ячейки B1 и С1. Спрашивается в чем «новизна» такой функции ! Что-то пошло видимо у вас не так на «безопасном режиме» на примерах 4 и 5. А первые вполне годятся, но почему они не безопасны?

    1. Евгений

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

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

      1. Да спасибо. Мне все таки сейчас нужен любой способ именно изменения других ячеек из функции. Способ вызова

        привел лишь к возвращению функцией значения «#ЗНАЧ!». Благодарю вас в любом случае за проделанную работу. Изменение в другой ячейке преследую в первую очередь, потому что в ячейке где «лежит» эта функция изменение не нужно, а отдельный запуск макроса руками тоже плохая вещь, причем иногда зависящий выполнена ли функция(UDF) успешно или нет и после того уже запускать sub. Буду пробовать примеры с 1-3.

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

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