Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

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

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

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
' Procedure : ChangeAnotherCell
'             http://www.excel-vba.ru
' Purpose   : Функция меняет значение указанной ячейки
' Аргументы:
'    rCell  - ячейка, в которой необходимо поменять значение.
'    vVal   - значение для записи в ячейку rCell.
'             Произвольный текст или ссылка на ячейку.
'---------------------------------------------------------------------------------------
Function ChangeAnotherCell(rCell As Range, vVal) As String
    Application.Volatile
    With rCell
        If .Value = Empty Then
            .Replace Empty, vVal, 1, , 0
        Else
            .Replace .Value, vVal, 1, , 0
        End If
    End With
End Function

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

rCell(G1) - ячейка, в которой необходимо поменять значение. Должна отличаться от адреса ячейки, в которой записана сама функция. Например, если функция записана в ячейке A1, то в качестве аргумента rCell не может быть так же A1. В лучшем случае получите ошибку цикличности, в худшем - Excel завершиться аварийно, а файл может быть при этом безвозвратно поврежден.

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

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

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
' Procedure : ChangeAnotherCell
' Purpose   : Функция меняет значение и формат указанной ячейки
' Аргументы:
'    rCell        - ячейка, в которой необходимо поменять значение.
'    vVal         - значение для записи в ячейку rCell.
'                   Произвольный текст или ссылка на ячейку.
'    bRepFormat   - ИСТИНА(TRUE) - будет скопирован формат из rFormatRng.
'    rFormatRng   - ссылка на ячейку, формат которой необходимо копировать.
'---------------------------------------------------------------------------------------
'
Function ChangeAnotherCell(rCell As Range, vVal, Optional bRepFormat As Boolean = False, Optional rFormatRng As Range = Nothing) As String
    Application.Volatile
 
    If bRepFormat Then
        If Not rFormatRng Is Nothing Then
            With Application.ReplaceFormat
                .Clear
                .Font.Bold = rFormatRng.Font.Bold
                .Font.ColorIndex = rFormatRng.Font.ColorIndex
                .Font.FontStyle = rFormatRng.Font.FontStyle
                .Borders.Color = rFormatRng.Borders.Color
                .Borders.Weight = rFormatRng.Borders.Weight
                If Val(Application.Version) >= 12 Then 'версии старше 2003 не поддерживают копирование заливки
                    .Font.Name = rFormatRng.Font.Name
                Else
                    .Interior.ColorIndex = rFormatRng.Interior.ColorIndex
                End If
            End With
        End If
    End If
    With rCell
        If .Value = Empty Then
            .Replace Empty, vVal, 1, , 0, , , bRepFormat
        Else
            .Replace .Value, vVal, 1, , 0, , , bRepFormat
        End If
    End With
    Application.ReplaceFormat.Clear
End Function

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

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

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

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

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


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

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

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции 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> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти