Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

Как функцией пользователя изменить значение другой ячейки

 

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

Синтаксис функции:
=ChangeAnotherCell(G1;"новое значение")

rCell(G1) - ячейка, в которой необходимо поменять значение. Должна отличаться от адреса ячейки, в которую занесена функция.

vVal("новое значение") - значение для записи в ячейку rCell. Может быть как произвольным текстом или числом, так и ссылкой на другую ячейку. Значение в ячейке rCell не удаляется после удаления функции ChangeAnotherCell.

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

 
Если чуть модернизировать функцию, то помимо значения можно изменить еще и формат ячейки:

Синтаксис функции:
=ChangeAnotherCell(G1;"новое значение";ИСТИНА;E14)

rCell(G1) - ячейка, в которой необходимо поменять значение. Должна отличаться от адреса ячейки, в которую занесена функция.

vVal("новое значение") - значение для записи в ячейку rCell. Может быть как произвольным текстом или числом, так и ссылкой на другую ячейку. Значение в ячейке rCell не удаляется после удаления функции ChangeAnotherCell.

bRepFormat(ИСТИНА) - если указан как ИСТИНА или 1, то так же меняется формат ячейки rCell. Формат копируется из ячейки, указанной аргументом rFormatRng. Поэтому в данном случае аргумент rFormatRng обязателен к указанию. Если ЛОЖЬ, 0 или не указан - формат не копируется.

rFormatRng(E14) - ссылка на ячейку, формат которой необходимо копировать.


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 3 коммент.
  1. Андрей:

    Спасибо. То, что доктор прописал. Спасибище!!!!!

  2. Макс:

    Невозможно изменить данные в ячейках, для которых еще не выделена память.
    Память для ячеек выделяется динамически по следующим правилу:
    а) по столбцам - по самой правой ячейке на листе для которой уже определено какое-либо свойство: значение, формат и т.п.
    б) по строкам - по самой нижней строке плюс 10 строк, в которой имеется ячейка для которой определено какое либо свойство.
    Это значит, что вы без проблем сможете разместить значение в любой ячейке с номером столбца равным или меньше, чем номер столбца самой правой инициализированной ячейки на листе и с номером строки который меньше либо равен номеру строки самой нижней инициализированной ячейки на листе плюс 10 строк.
    Попытка изменить значение в ячейке расположенной правее и ниже указанных границ с использованием приведенного в статье алгоритма невозможно, т.к. их еще не существует в памяти компьютера, для них просто не выделена память.
    Вместе с тем во всех ячейках внутри указанного диапазона значения изменяются без проблем, даже если они не инициализированы, т.е. все если их свойства, включая Value имеют дефолтные значения.
    Описанные правила нашли полное экспериментальное подтверждение в Excel 2003, в других версиях не проверялось.

  3. Макс:

    Добавлю.
    Строка Application.Volatile
    Мною не использовалась, чтобы не инициировать излишние циклы вычислений.
    Ее отсутствие никак не отразилось на работе функции ChangeAnotherCell, которая в моем варианте меняла значение не в одной ячейке, а в последовательной цепочке из 20-30 ячеек в одной строке или одном столбце.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти