Хитрости »
7 Август 2013
Дмитрий 18152 просмотров
Как функцией пользователя изменить значение другой ячейки
Обычно в своих статьях и на форумах я пишу, что функция пользователя не умеет изменять значение других ячеек. Только возвращает значение в ту ячейку, в которой записана. В большинстве случаев это справедливо и в принципе правильно. Но есть обходной путь, который позволяет изменить данные другой ячейки из функции пользователя(не рекомендую применять этот подход в серьезных программах - метод ненадежен и недокументирован):
'---------------------------------------------------------------------------------------
' 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 |
'---------------------------------------------------------------------------------------
' 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) - ячейка, в которой необходимо поменять значение. Должна отличаться от адреса ячейки, в которую занесена функция.
vVal("новое значение") - значение для записи в ячейку rCell. Может быть как произвольным текстом или числом, так и ссылкой на другую ячейку. Значение в ячейке rCell не удаляется после удаления функции ChangeAnotherCell.
Но лучше не особо надеяться на данный метод. Он порой не срабатывает. Например, если в ячейку rCell ранее не было занесено никакое значение, то функция может просто отказаться менять значение ячейки.
Если чуть модернизировать функцию, то помимо значения можно изменить еще и формат ячейки:
'---------------------------------------------------------------------------------------
' Procedure : ChangeAnotherCell
' Purpose : Функция меняет значение и формат указанной ячейки
' Аргументы:
' rCell - ячейка, в которой необходимо поменять значение.
' vVal - значение для записи в ячейку rCell.
' Произвольный текст или ссылка на ячейку.
' bRepFormat - ИСТИНА - будет скопирован формат из 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 |
'---------------------------------------------------------------------------------------
' Procedure : ChangeAnotherCell
' Purpose : Функция меняет значение и формат указанной ячейки
' Аргументы:
' rCell - ячейка, в которой необходимо поменять значение.
' vVal - значение для записи в ячейку rCell.
' Произвольный текст или ссылка на ячейку.
' bRepFormat - ИСТИНА - будет скопирован формат из 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(ИСТИНА) - если указан как ИСТИНА или 1, то так же меняется формат ячейки rCell. Формат копируется из ячейки, указанной аргументом rFormatRng. Поэтому в данном случае аргумент rFormatRng обязателен к указанию. Если ЛОЖЬ, 0 или не указан - формат не копируется.
rFormatRng(E14) - ссылка на ячейку, формат которой необходимо копировать.
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}
Поиск по меткам
Access
apple watch
Multex
Outlook
Power Query и Power BI
VBA работа в редакторе
VBA управление кодами
Бесплатные надстройки
Дата и время
Диаграммы и графики
Записки
Защита данных
Интернет
Картинки и объекты
Листы и книги
Макросы и VBA
Надстройки
Настройка
Печать
Поиск данных
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Работа с файлами
Разработка приложений
Сводные таблицы
Списки
Тренинги и вебинары
Финансовые
Форматирование
Формулы и функции
Функции Excel
Функции VBA
Ячейки и диапазоны
акции MulTEx
анализ данных
баги и глюки в Excel
ссылки
Спасибо. То, что доктор прописал. Спасибище!!!!!
Невозможно изменить данные в ячейках, для которых еще не выделена память.
Память для ячеек выделяется динамически по следующим правилу:
а) по столбцам - по самой правой ячейке на листе для которой уже определено какое-либо свойство: значение, формат и т.п.
б) по строкам - по самой нижней строке плюс 10 строк, в которой имеется ячейка для которой определено какое либо свойство.
Это значит, что вы без проблем сможете разместить значение в любой ячейке с номером столбца равным или меньше, чем номер столбца самой правой инициализированной ячейки на листе и с номером строки который меньше либо равен номеру строки самой нижней инициализированной ячейки на листе плюс 10 строк.
Попытка изменить значение в ячейке расположенной правее и ниже указанных границ с использованием приведенного в статье алгоритма невозможно, т.к. их еще не существует в памяти компьютера, для них просто не выделена память.
Вместе с тем во всех ячейках внутри указанного диапазона значения изменяются без проблем, даже если они не инициализированы, т.е. все если их свойства, включая Value имеют дефолтные значения.
Описанные правила нашли полное экспериментальное подтверждение в Excel 2003, в других версиях не проверялось.
Добавлю.
Строка Application.Volatile
Мною не использовалась, чтобы не инициировать излишние циклы вычислений.
Ее отсутствие никак не отразилось на работе функции ChangeAnotherCell, которая в моем варианте меняла значение не в одной ячейке, а в последовательной цепочке из 20-30 ячеек в одной строке или одном столбце.