Страницы: 1
RSS
О присвоении значения ячейке листа Excel
 
В данном сообщении речь пойдет об операторе VBA
Код
cell.Value=arg
где cell - объект типа Range, представляющий одну ячейку, arg - переменная типа Variant.

Задача (тривиального?) исследования заключается в оценке последствий выполнения указанного оператора. Отмечу, что предлагаемые выводы являются следствием эмпирических опытов и автор будет признателен за указанные ошибки и неточности.

К сообщению приложен "стенд" - книга TestValue.xlsm. Книга содержит макросы TestValueNum и TestValueStr, выполнив которые читатель может проверить выводы сообщения на своем экземпляре MS Excel. Книга прилагается с данными, полученными в системе Excel 2016 (32-) Windows 10 с кодовой страницей по умолчанию windows-1251 и стандартными региональными настройками для локализации ru-RU. Кроме этого, тестировались системы с другими региональными настройками, в том числе с кодовой страницей windows-1252 en-US. Системы Excel для Mac не проверялись (по причине наличия отсутствия) и автор будет признателен за соответствующее дополнение.
Классификация строится на основе типа переменной arg, который можно получить, например, с помощью функции TypeName. На результат исследуемого оператора в некоторых случаях влияет исходный формат ячейки (свойство NumberFormat).

1. arg имеет числовой тип (кроме Currency).

Если ячейка имеет формат даты, то свойство cell.Value после присвоения вернет тип Date, соответствующий Cdate(arg), при этом в ячейке отрицательные значения дат будут отображаться, как "##...#". Для других форматов cell.Value после присвоения будет равно arg. Формат ячейки (в том числе, текстовый) не будет изменен.

2. arg имеет тип Date.

Если ячейка имеет один из числовых форматов, то cell.Value после присвоения вернет число (Double), соответствующее Cdbl(arg). Если ячейка имеет текстовый формат, то дата превратится в текст с "американским" представлением даты (например, "4/30/2021" или "4/30/2021  2:27:16 PM"). Для других форматов cell.Value после присвоения будет равно arg. "Общий" (General) формат ячейки будет изменен на формат даты, стандартный для региональных настроек, в котором в случае дробного значения arg будет присутствовать компонента времени.

3. arg имеет тип Currency.

Редко используемый тип. Если ячейка имеет один из числовых форматов, то как в п.2. Для текстового формата ячейки arg преобразуется в текст по "американским" правилам (например, "$123,456.78"). Наконец, если ячейка имеет "Общий" формат или формат даты, то формат будет изменен на денежный.

4. arg имеет тип String

Попробуем сформулировать правила в порядке убывания приоритетов.

4.1. Первый символ arg- апостроф. После присвоения в cell.Value будет текст с удаленным первым символом апострофа.

4.2. Ячейка имеет текстовый формат. После присвоения cell.Value будет равна arg.

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

4.3. Первый символ arg равен "=". Excel однозначно считает, что это формула. Вначале делается попытка интерпретировать текст как формулу (нелокализованную!) в стиле "A1", затем как формулу (нелокализованную!) в стиле "R1C1". В случае успеха одной из попыток в ячейке cell появляется формула, иначе возникает ошибка (!) времени выполнения с кодом 1004.

4.4. Производится попытка интерпретации текста, как числа в локализации en-US: разделитель дробной доли точка, разделитель тысяч - запятая. Каждая из запятых должна отделять как минимум 3 цифры (больше можно). В случае успеха в ячейку заносится число, а не текст. В некоторых случаях изменится и формат ячейки: если arg представляет число в "экспоненциальном" формате, а формат ячейки "Общий" или Дата; если arg содержит запятую, а формат ячейки "Общий" и пр.

4.5. Если ячейка имеет один из числовых форматов, а arg имеет вид простой дроби (через "/") с ненулевым знаменателем, то cell.Value после присвоения будет содержать частное от деления.

4.6. Производится попытка интерпретации текста, как даты в локализации en-US: M/D/Y или YYYY-MM-DD с возможной компонентой времени (возможно, включая AM, PM).  Дата может содержать краткое или полное наименование месяца (на английском), в этом случае порядок компонент может быть иным. В случае, когда часть компонент опущена, Excel их присваивает по определенным (логичным) правилам, например 2/13 будет интерпретироваться как 13 февраля текущего года. В случае успеха интерпретации в ячейку заносится соответствующее число, при этом "Общий" формат ячейки будет изменен на формат даты с тем условием, чтобы свойство NumberFormat ячейки (нелокализованное) в максимальной степени соответствовало текcтовому представлению даты в arg.

4.7. Производится попытка интерпретации arg как логического (True, False) или ошибочного значения (#DIV/0! и т.д). В случае успеха в ячейку будет занесено преобразованное значение.

5. arg является массивом. Будет обработан первый элемент массива.

6. arg является объектом. cell.Value будет присваиваться свойство объекта по умолчанию. Если свойства по умолчанию нет или свойство по умолчанию не может быть присвоено cell.Value, то возникнет ошибка времени выполнения.

Общий вывод - всё достаточно не просто и это надо учитывать при написании макросов. Например, любимая некоторыми авторами конструкция для замены формул диапазона rng на значения:
Код
rng.Value=rng.Value
имеет массу "побочных эффектов" для:

  • текстов, начинающихся на апостроф или знак равенства
  • текстов "похожих" на числа, даты, логические, ошибочные значения и т.п.

Надежнее использовать специальную вставку значений!

В заключение автор выражает искреннюю признательность Владимиру (ZVI), обсуждения с которым способствовали началу подбора материалов по данной теме.
Изменено: sokol92 - 03.05.2021 18:41:24
Владимир
 
Владимир, перепутаны 1 и 2й пункты, поправьте.
«Бритва Оккама» или «Принцип Калашникова»?
 
Добавлю 7 :)
При передаче в SQL запрос для Access
Из формы (из TextBox) значение передается в Cell. Нужно чтобы в Access ушел текст, а не число (многократно обсуждаемый вопрос о нулях перед числом). CStr не помогает, апостроф там нафиг не нужен - его потом удалять придется, варианты типа arg & ""  тоже. Помогает формат ячейки - текстовый (но это у меня). Конечно, его приходится менять принудительно макросом, но нужный результат достигается.
Если бы еще знать заранее о всей массе
Цитата
sokol92 написал:
"побочных эффектов... для...  текстов "похожих" на числа, даты, логические, ошибочные значения и т.п."
И судя по чтению интернета  не всегда понятно от чего это зависит. Тут же и региональные настройки могут быть, и версии ОС и... не буду высказывать ИМХО по этому поводу :)
Цитата
sokol92 написал:
Надежнее использовать специальную вставку значений
Намного надежней, но не факт что сработает во всех случаях   :(  
 
Цитата
bedvit написал:
Владимир, перепутаны 1 и 2й пункты, поправьте.
Здравствуйте, Виталий, мне кажется, не перепутаны. В первом пункте анализируется ситуация, когда arg имеет числовой тип vbDouble, vbSingle, vbLong, ...  в зависимости от формата ячейки Cell, которым может быть числовым, датой, текстовым, ... Аналогично, во втором пункте.
Изменено: sokol92 - 03.05.2021 17:39:16
Владимир
 
Цитата
_Igor_61 написал:
И судя по чтению интернета  не всегда понятно от чего это зависит
Сообщение #1 - это тоже интернет. :)  
Владимир
 
Цитата
sokol92 написал:
. В первом пункте анализируется ситуация, когда arg имеет числовой тип
извиняюсь, думал, что написано про дату, неправильно понял.
Изменено: bedvit - 03.05.2021 18:05:22
«Бритва Оккама» или «Принцип Калашникова»?
 
sokol92, благодарю за очередной подробный разбор!
Можно дополнить статью разбором .Value2 и вставкой в диапазон, а не 1 ячейку (обзор отличий и методов "обхода проблем")

Ну и дам ссылку на свою тему с обзором одной из описанных проблем:
При вставке пропадает разделитель целой и дробной части
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Здравствуйте, Алексей!

По поводу оператора
Код
cell.Value2=arg

То же, что в стартовом сообщении с той лишь разницей, что ситуации в пунктах 2 и 3 обрабатываются так же, как в пункте 1, поскольку при присвоениии свойства value2 значения типов Date и Currency преобразуются в Double. Тексты, похоже, обрабатываются так же.

О присвоении значений диапазонам ячеек не писал, поскольку это не соответствует заявленной теме. Понятно, что присвоение значений ячейкам диапазона сводится к последовательному присвоению значений каждой ячейке этого диапазона. Нужно сформулировать правила для разных типов диапазонов (Range) и разных типов arg (скалярные значения, одномерные массивы, двумерные массивы, ...).
Владимир
 
sokol92, согласен - особо расписывать не нужно, достаточно ремарки, что при .Value2
Цитата
sokol92: ситуации в пунктах 2 и 3 обрабатываются так же, как в пункте 1, поскольку при присвоениии свойства value2 значения типов Date и Currency преобразуются в Double
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Страницы: 1
Наверх