В данном сообщении речь пойдет об операторе VBA
где 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 на значения:
имеет массу "побочных эффектов" для:
Надежнее использовать специальную вставку значений!
В заключение автор выражает искреннюю признательность Владимиру (), обсуждения с которым способствовали началу подбора материалов по данной теме.
| Код |
|---|
cell.Value=arg |
Задача (тривиального?) исследования заключается в оценке последствий выполнения указанного оператора. Отмечу, что предлагаемые выводы являются следствием эмпирических опытов и автор будет признателен за указанные ошибки и неточности.
К сообщению приложен "стенд" - книга 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 |
- текстов, начинающихся на апостроф или знак равенства
- текстов "похожих" на числа, даты, логические, ошибочные значения и т.п.
Надежнее использовать специальную вставку значений!
В заключение автор выражает искреннюю признательность Владимиру (), обсуждения с которым способствовали началу подбора материалов по данной теме.
Изменено: - 03.05.2021 18:41:24
Владимир
