Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1
О присвоении значения ячейке листа 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
Владимир
Об особенностях метода Range.Find c параметром LookIn:=xlValues
 
Добрый день!

Вопросы по применению метода Range.Find c параметром LookIn:=xlValues сравнительно часто задаются на форуме. Владимир (ZVI) давал разъяснения здесь. Попробуем продолжить изучение этого проблемы.

Первую задачу можно в начале решать "без доски". Что будет выдано в результате выполнения следующего макроса. Почему?
Код
Sub TestRangeFind()
    Dim rg As Range, txt As String, y As Long
    y = 2021
    Workbooks.Add
    With Range("A1")
      .NumberFormat = "D MMMM YYYY"
      .Value = DateSerial(y, 1, 1)
      Set rg = .Find(y, LookIn:=xlValues, LookAt:=xlPart)
      Debug.Print "Текст " & y & IIf(rg Is Nothing, " не", "") & " найден в ячейке со значением " & .Value
    End With
End Sub
Скрытый текст

Следующая задача значительно труднее. Ее решение дает ключ к ответу по данной теме.
Выполнить макрос. Если есть различия в сообщениях по месяцам, то объяснить эти различия.

Код
Sub TestRangeFind2()
    Dim rg As Range, m As Long, y As Long, txt As String
    y = 2021
    txt = y   ' текст для поиска в ячейке A1
    Workbooks.Add
    With Range("A1")
        .NumberFormat = "D MMMM YYYY"
        For m = 1 To 12
            .Value = DateSerial(y, m, 1)
            .EntireColumn.AutoFit
            Set rg = .Find(txt, LookIn:=xlValues, LookAt:=xlPart)
            Debug.Print "Текст " & txt & IIf(rg Is Nothing, " не", "") & " найден в ячейке со значением " & .Value
        Next m
    End With
End Sub

Сделаем паузу на пару дней, чтобы дать возможность читателям темы (если таковые будут) провести самостоятельные исследования.
Владимир
Как определить версию Excel, которой был сохранен файл
 
Вопрос часто возникает, особенно у помогающих на форуме. Привожу используемый мною (актуальный на сегодня) алгоритм, основанный на анализе файла как архива.
1. Анализируем две первых цифры значения тэга AppVersion файла docProps\app.xml. Если 12, то файл сохранялся Excel 2007, 14 - 2010, 15 - 2013, 16 - переходим к пункту 2 (у Microsoft всегда все не просто).
2. Анализируем значение атрибута lastEdited тэга fileVersion файла xl\workbook.xml. Если 7, то Excel 2019, иначе Excel 2016.
В этом же файле ищем контекст: xcalcf:feature name="microsoft.com
Нахождение свидетельствует о подписке Office 365.
Изменено: sokol92 - 14.06.2020 17:25:48
Владимир
О равенстве значений ячеек
 
Задачки для первого класса.

1. Рефлексивность равенства

Всегда ли значением формулы

=A1=A1

будет ИСТИНА (TRUE)?

2. Симметричность равенства.

Всегда ли формулы

=A1=B1
и
=B1=A1

выдают одинаковые результаты?

3. Транзитивность равенства

Может ли формула
=ЕСЛИ(И(B1=A1; C1=A1); B1=C1; ИСТИНА)

выдавать ЛОЖЬ (FALSE)?

О терминологии - здесь.

Может быть, кому-нибудь задачи покажутся интересными. Просьба решения сегодня публиковать под спойлерами.
Изменено: sokol92 - 01.04.2020 13:55:02
Владимир
Обеспечение совместимости Excel VBA приложения для систем разной разрядности и с разными региональными установками.
 

   Сообщение посвящено проблеме обеспечения корректной работы приложения для Excel VBA в системах с различными версиями MS Windows (в том числе, с другими кодовыми страницами и/или региональными настройками) и Excel (2007+). Под Приложением понимается совокупность надстроек и книг Excel, объединенных определенной функциональностью.

   Приложение не должно требовать предварительной перенастройки параметров Windows и/или Excel и не должно само вносить изменения в параметры.

   Сообщение основано на практическом опыте автора и является (лаконичным) путеводителем по возможным "подводным камням". Не обсуждаются (хотя это возможно при наличии общественного интереса) проблемы поддержки мультиязычных интерфейсов. Для иллюстрации тезисов к сообщению приложен файл (далее - Пример).
   

   Основная часть проблем, разумеется, связана с кодировками текстов.
   

    Назовем символ юникода "нерелевантным" для текущего экземпляра Windows, если для него не "забронировано" место в кодовой странице по умолчанию. Для (нашей родной) кодировки windows-1251 нерелевантными являются буквы греческого, армянского, грузинского алфавитов, расширенной латиницы, китайские иероглифы и пр. Для кодировки windows-1252 (США и часть западноевропейских стран) нерелевантными являются, в том числе, буквы кириллицы.

  1. Использование API Windows

   Понятно, что при наличии ANSI (*A) и Unicode (*W) версий API следует использовать Unicode-версию. В противном случае, все нерелевантные символы во входных параметрах будут заменены на символ "?" (вопросительный знак).
   

   К сожалению, Microsoft (по инерции?) публикует описания заголовков именно ANSI-версий. Обычно переход к Unicode-версии не сложен: меняем в формальном параметре Byval xxx as String на Byval xxx as LongPtr (Long для Excel 2007), фактический параметр yyy меняем на StrPtr(yyy).

   2. Объекты Excel и MSO.

   Замеченных проблем не много.

   2.1. Именование объектов VBE-проекта.

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

   2.2. Проблемы с Userform.Caption

   Заголовок окна Userform вплоть до версии Office 2016 не поддерживает Unicode (а с заголовками элементов управления все хорошо). Для иллюстрации можно выполнить макрос  i_UserformCaption в Примере.

   Лечение. Если в Приложении заголовок формы имеет смысловую нагрузку и содержит потенциально нерелевантные символы, то для требуемого текста можно вместо заголовка формы можно использовать "яркий" элемент управления Label в верхней части формы.

   2.3. Проблемы с Application.Caption

   Замечены только для Windows XP. Не критично.

   2.4. Функция рабочего листа TEXT (ТЕКСТ)

   Второй параметр (Format) функции TEXT задается с учетом региональных настроек. В каких-то случаях можно заменить указанную функцию на FIXED  (ФИКСИРОВАННЫЙ), у которой, кстати, можно узнать без макросов региональные разделители дробных долей и тысяч (FIXED(1234)). Можно необходимый формат для TEXT хранить в ячейке и извлекать из нее через Range.NumberFormatLocal. Наконец, свойство Application.International дает весь спектр региональных настроек.
   

   3. Проблемы с VBA.

   На субботнике разработчиков по переводу Windows в Unicode на VBA времени явно не хватило...

   3.1. Проблемы с текстовыми литералами.

   К сожалению, Excel не хранит тексты модулей в юникоде. Для иллюстрации - см. ячейки A3 и B3 Примера. И если комментариями (например, на русском языке) можно пожертвовать, то с текстовыми (строковыми) литералами,  содержащими потенциально нерелевантные символы, шутки плохи.

   Разумный выход - перенести соответствующие тексты в ячейки листов надстройки (скрытых листов книги) или в пользовательские свойства надстройки (книги). На такую модификацию в больших приложениях потребуется определенное время. Полезно иметь макрос, который будет отлавливать "нехорошие" литералы. Литералы, содержащие имена (внешние) листов книги, можно вообще исключить, применяя внутренние имена листов (см. п. 2.1.) в соответствующих конструкциях.

   3.2. Функция Msgbox.

   Не поддерживает Unicode - выполните  i_msgbox в Примере. Явно базируется на ANSI-версии API MessageBoxA.
   

   Лечение - перейти к Unicode-версии (см. модуль ModMessage).

   Теоретически, можно перенакрыть Msgbox cвоей функцией с тем же  именем, чтобы не менять (в этой части) код приложения. Это относится и к  пунктам 3.3 - 3.4

   3.3. Функция InputBox.

   Не поддерживает Unicode - выполните i_inputbox в Примере.

   Штатный заменитель  - Application.Inputbox имеет два недостатка: странную реакцию на нажатие стрелочек (на форуме научили с этим бороться с помощью F2) и (существенно!) ограничение в 255 символов на длину параметра Prompt. Оптимальный выход - написать собственную форму с переменным размером окна для приглашения. Заодно можно добавить полезный сервис: подсказки для ввода, контроль введенных значений и т.п.

   3.4. Операторы и функции для работы с файловой системой (dir, kill, ...)

   Нельзя использовать, если обрабатываемые файлы содержат потенциально нерелевантные символы в пути (Path). Заменители: FileSystemObject.Scripting (FSO), Shell.Application, Win API (Unicode-версии).

   3.5. Чтение и запись текстовых файлов

   Нельзя использовать "штатные" конструкции, если текстовые файлы содержат потенциально нерелевантные символы. Заменители: FSO (умеет читать и писать в UTF16), ADODB.Stream (читает и пишет в любой кодировке, способен быстро изменить кодировку файла).

   3.6. Функции VBA, учитывающие региональные настройки

К таким функциям относятся Format, IsNumeric, IsDate. Для функции Format есть предопределенные форматы и ограниченные в возможностях переносимые заменители (FormatNumber, ...).  См. также п. 2.4.

  3.7 Динамическое занесение формул      

   В некоторых случаях требуется в VBA динамически занести формулу в локализованном виде (условное форматирование, ...). Один из возможных трюков: временно присвоить ячейке свойство Formula (FormulaR1C1) и забрать FormulaLocal. Как всегда, есть и другие способы.

  4. Объекты ActiveX

  Для автора было приятной неожиданностью, что объекты 64-разрядного офиса могут управляться через интерфейсы автоматизации из 32-разрядных приложений.

  4.1 Регулярные выражения

  Не используем потенциально нерелевантные символы (например, русские буквы). Синтаксис регулярных выражений предусматривает символы Unicode (\un)

   В заключение выскажу (оптимистическое) мнение, что даже объемное Приложение можно за относительно короткое время превратить в переносимое.

   Автор выражает глубокую благодарность Владимиру (ZVI), Андрею (Андрей VG) и Михаилу (БМВ) за консультации и проявленное внимание к теме.
   

Изменено: sokol92 - 09.08.2018 17:09:39
Владимир
Страницы: 1
Наверх