Изменение значений других ячеек из пользовательской функции VBA Excel с помощью методов Range.Replace и Application.Volatile. Примеры кода.
Функция с методом Range.Replace
Пользовательская функция не предназначена для изменения значений ячеек, кроме той, в которой она расположена. Попытка присвоить какое-либо значение из функции другой ячейке приводит к неработоспособности функции и отображению в ячейке, где она расположена, сообщения «#ЗНАЧ!».
Но, как ни странно, внутри процедуры Function работает метод Range.Replace, которым мы воспользуемся для изменения значений других ячеек из пользовательской функции.
Пример 1
Эта функция заменяет значение ячейки Cell1 на значение ячейки Cell2 увеличенное на 100. Сама функция размещается в третьей ячейке, чтобы не возникла циклическая ссылка.
|
1 2 3 |
Function Primer1(Cell1 As Range, Cell2 As Range) Cell1.Replace Cell1, Cell2 + 100 End Function |
В этом примере мы не присваиваем пользовательской функции значение, поэтому отображается значение по умолчанию – 0. Если объявить эту функцию как строковую: Function Primer1(Cell1 As Range, Cell2 As Range) as String, будет возвращена пустая строка.
Изменение значения ячейки C1 (Cell2) приведет к пересчету значения ячейки B1 (Cell1).
Попробуйте очистить или перезаписать ячейку B1 (Cell1), ничего не получится, так как функция Primer1 вновь перезапишет ее значением C1 (Cell2) + 100.
Метод Application.Volatile
Рассмотрим пересчет функции на следующем примере:
Пример 2
|
1 2 3 4 |
Function Primer2(Cell1 As Range, Cell2 As Range) As String Cell1.Replace Cell1, Cell2 + 100 Range("B2").Replace Range("B2"), Range("C2") + Cell1 End Function |
Эта функция будет пересчитываться только при изменении значений ячеек B1 и C1, присвоенных переменным Cell1 и Cell2. При изменении значения ячейки C2, значение ячейки B2 не изменится, так как не будет запущен пересчет функции Primer2.
Функция Primer2 начнет вести себя по-другому, если добавить в нее оператор Application.Volatile (переименуем ее в Primer3):
Пример 3
|
1 2 3 4 5 |
Function Primer3(Cell1 As Range, Cell2 As Range) As String Application.Volatile Cell1.Replace Cell1, Cell2 + 100 Range("B2").Replace Range("B2"), Range("C2") + Cell1 End Function |
Теперь при смене значения в ячейке C2, значение ячейки B2 тоже изменится.
В предыдущих примерах функциям не присваивалось конечное значение, чтобы показать, что такое возможно. При присвоении значения пользовательской функции следует следить за тем, чтобы не возникало циклических ссылок между ячейками с изменяемыми функцией значениями, в том числе и той, в которой размещена функция.
Безопасное использование функции
Если вам все-таки хочется в важном проекте VBA Excel с помощью одной пользовательской функции изменять значения нескольких ячеек, есть полностью безопасный вариант.
Он заключается в добавлении дополнительного аргумента в пользовательскую функцию, с помощью которого для каждой ячейки можно вывести отдельный результат. Функция размещается в каждой изменяемой ячейке.
В простых случаях для выбора можно использовать функцию Choose или, в более сложных, оператор If…Then…Else и оператор Select Case.
Пример 4
Используем функцию Choose для выбора способа вычисления пользовательской функции в зависимости от значения дополнительного аргумента:
|
1 2 3 4 |
Function Primer4(Cell1 As Range, Cell2 As Range, a As Byte) On Error Resume Next Primer4 = Choose(a, Cell1 + Cell2, Cell1 - Cell2, Cell1 * Cell2) End Function |
В функцию Primer4 добавлен дополнительный аргумент a, от которого зависит, какое действие будет произведено со значениями ячеек B1 и C1:
На следующем скриншоте представлены результаты вычисления функции в зависимости от значения аргумента a:
- В ячейке A1 вычисляется сумма значений ячеек B1 и C1 – аргумент a=1.
- В ячейке A2 вычисляется разность значений ячеек B2 и C2 – аргумент a=2.
- В ячейке A3 вычисляется произведение значений ячеек B3 и C3 – аргумент a=3.
Пример 5
Используем оператор If…Then…Else в сокращенном виде (If…Then…) для выбора способа вычисления функции в зависимости от значения дополнительного аргумента:
|
1 2 3 4 5 |
Function Primer5(Cell1 As Range, Cell2 As Range, a As Byte) If a = 1 Then Primer5 = Cell1 + Cell2 If a = 2 Then Primer5 = Cell1 - Cell2 If a = 3 Then Primer5 = Cell1 * Cell2 End Function |
Результаты будут те же, что и в четвертом примере.
Вы же стремились изменить ячейку B1 и C1 функцией расположенной в ячейке А1 в зависимости от параметра «а». А изменили ячейку A1 в зависимости от параметра «a», и в качестве ее двух аргументов, использовав значения записанные пользователем в ячейки B1 и С1. Спрашивается в чем «новизна» такой функции ! Что-то пошло видимо у вас не так на «безопасном режиме» на примерах 4 и 5. А первые вполне годятся, но почему они не безопасны?
Четвертый и пятый пример показывают, как одна и та же функция может возвращать разные результаты. А так вы написали все правильно: функция с дополнительным параметром “a” не изменяет значения других ячеек.
Первые два способа являются нестандартными решениями, найденными пользователями, поэтому неизвестно, как они поведут себя в разных версиях Excel и разных операционных системах.
Да спасибо. Мне все таки сейчас нужен любой способ именно изменения других ячеек из функции. Способ вызова
привел лишь к возвращению функцией значения «#ЗНАЧ!». Благодарю вас в любом случае за проделанную работу. Изменение в другой ячейке преследую в первую очередь, потому что в ячейке где «лежит» эта функция изменение не нужно, а отдельный запуск макроса руками тоже плохая вещь, причем иногда зависящий выполнена ли функция(UDF) успешно или нет и после того уже запускать sub. Буду пробовать примеры с 1-3.
Обсуждение закрыто.