Занятие 4. Использование стандартных диалоговых окон

Использование стандартных диалоговых окон

От макросов часто требуется, чтобы они могли работать с различными значениями, но, как известно, макрос – это процедура без параметров (в отличие от функции пользователя). Выход в данном случае состоит в том, чтобы вводить необходимые данные при выполнении макроса, используя специальные диалоговые окна. Создавать такие окна мы будем позже, а сейчас рассмотрим стандартные средства, которые существуют для этих целей: функцию VBA InputBox и метод InputBox объекта Application.

Внешне эти инструменты очень похожи. При вызове на экране появляется окно, которое содержит сообщение, указывающее какие данные должен ввести пользователь, поле для ввода и две кнопки ОК и Отмена, которые используются для подтверждения или отмены ввода данных. Значением функции InputBox является строка, введенная пользователем, или пустая строка, если была нажата кнопка Отмена. В отличие от функции InputBox метод InputBox (тоже функция) позволяет задать тип возвращаемого значения и осуществляет проверку правильности введенных данных. При ошибке выдается сообщение. В случае выбора кнопки Отмена возвращается значение False.

Синтаксис функции и метода InputBox:

InputBox (Prompt [, Title ] [, Default ] [, Xpos ] [, Ypos ] [, Helpfile, Context ])

InputBox (Prompt [, Title ] [, Default ] [, Left ] [, Top ] [, HelpFile, HelpContextID ] [, Type ])

Основные параметры функции и метода совпадают: Prompt – сообщение, которое выводится в окне; Title – заголовок окна; Default – значение, которое помещается в поле для ввода, когда окно выводится на экран. Первый параметр является обязательным, остальные – нет. Метод имеет дополнительный параметр Type, который определяет тип возвращаемого значения: 0 – формула, 1 – число, 2 – текст (по умолчанию), 4 – логическое значение (True или False), 8 – ссылка на ячейку или диапазон (объект Range). Подробнее об этих и других параметрах можно прочитать в справочной системе.

Метод InputBox вызывается с помощью конструкции Application.InputBox, объект Application в данном случае указывается обязательно, так как если вы напишете в программе просто InputBox, будет вызвана функция.

Для вывода сообщения используется стандартная функция MsgBox, которая имеет следующий синтаксис:

MsgBox (Prompt [, B uttons ] [, T itle ] [, H elpfile, C ontext ])

Как и в предыдущем случае обязательным является только первый параметр, остальные – нет. Prompt – это сообщение, которое выводится в окне. Его длина ограничена 1024 символами. В тексте сообщения можно использовать константы vbCr (Chr(13)), vbLf (Chr(10)), или их комбинацию vbNewLine (Chr(13) & Chr(10)) для разделения сообщения на строки и символ табуляции vbTab. Title – заголовок окна сообщения. Buttons определяет набор кнопок, коды значков, кнопки по умолчанию в окне сообщения, а также модальность окна. Значение параметра – целое число, равное сумме значений, описанных ниже (из каждой группы можно использовать не более одной константы). По умолчанию значение Buttons равно 0.

Константы, используемые в параметре Buttons:

Константа	Значение	Описание
Группа «Кнопки»
vbOKOnly		только кнопка OK
vbOKCancel		OK и Cancel (Отмена)
vbAbortRetryIgnore		Abort (Прервать), Retry (Повторить) и Ignore (Пропустить)
vbYesNoCancel		Yes (Да), No (Нет) и Cancel (Отмена)
vbYesNo		Yes (Да) и No (Нет)
vbRetryCancel		Retry (Повторить) и Cancel (Отмена)
Группа «Значки»
vbCritical		важное сообщение
vbQuestion		запрос
vbExclamation		предупреждающее сообщение
vbInformation		информационное сообщение
Группа «Кнопка по умолчанию»
vbDefaultButton1		первая кнопка
vbDefaultButton2		вторая кнопка
vbDefaultButton3		третья кнопка
vbDefaultButton4		четвертая кнопка
Группа «Модальность окна»
vbApplicationModal		модальность уровня приложения: пока вы не нажмете одну из кнопок окна, вы не сможете вернуться в приложение, породившее это окно, но можно переключиться на другое приложение
vbSystemModal		модальность системного уровня: пока не будет закрыто это окно, никакие приложения не доступны

Функция возвращает значение, соответствующее выбранной пользователем кнопке в окне сообщения. Ниже в таблице приведены значения и имена возвращаемых констант:

Константа	Значение	Нажатая кнопка
vbOK		OK
vbCancel		Отмена (Cancel)
vbAbort		Прервать (Abort)
vbRetry		Повторить (Retry)
vbIgnore		Пропустить (Ignore)
vbYes		Да (Yes)
vbNo		Нет (No)

Пример 4.1. Рассмотрим, как функция MsgBox используется для отображения сообщения. В этом случае не нужно анализировать ответ пользователя, поэтому ее можно вызывать как процедуру.

В этом макросе мы сначала формируем нужную строку, а потом выводим ее в окне сообщений. Это делать не обязательно. Можно сразу при вызове функции в качестве первого параметра указывать нужное выражение. Второй параметр мы используем по умолчанию, поэтому его просто пропустили, но запятую указали, чтобы задать третий параметр – заголовок окна сообщения. Третий параметр тоже необязательный. Если его не задавать, то запятые писать не нужно. Тогда вызов функции может выглядеть так:

В нашей программе имеются 3 переменные разных типов (число, дата и текст). Мы выводим их значения в одном окне, но в разных строках. Для этого при формировании S используется константа vbNewLine. Также в S добавлены тексты с комментариями. Константы vbTab (табуляция) обеспечивают вывод данных с одной позиции.

При выводе даты используется функция Format. Результатом функции является строка, соответствующая отображению указанного значения (первый аргумент) в нужном формате (второй аргумент). У функции очень большие возможности по форматированию чисел и дат, о которых можно прочитать в справке. Можно создавать свои форматы или использовать стандартные (как в нашем случае), указав имя формата. Значение числовой переменной n мы не преобразовывали в строку, а просто использовали имя переменной в текстовом выражении. VBA в этом случае использует формат по умолчанию для соответствующего типа данных.

Пример 4.2. Рассмотрим, как можно организовать ввод чисел в своей программе с помощью метода InputBox:

Метод InputBox имеет 8 параметров, из которых мы используем только первый и последний. Чтобы не писать много запятых для обозначения отсутствующих необязательных параметров, при вызове функции для каждого фактического параметра указывается не только значение, но и его имя. В этом случае параметры можно перечислять в любой последовательности, а лишние запятые не нужны.

Как уже говорилось даты (и время) хранятся в памяти в виде десятичного числа. Поэтому это же окно используется в языке для ввода дат. Попробуйте ввести различные значения и посмотрите результат. Для этого введенное число выводится на экран дважды (в виде числа и в виде даты). В случае ошибки при вводе метод выдает сообщение. Приведенные ниже значения являются корректными с точки зрения метода:

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

При выводе введенного значения в виде даты используется стандартный формат "General Date", который преобразует ненулевую целую часть числа в дату, а ненулевую дробную часть числа – во время.

Пример 4.3. Рассмотрим, как с помощью метода InputBox можно организовать ввод формул:

При наборе формул можно использовать мышь и клавишу F4 для ввода ссылок, а вот названия функций придется вводить вручную. Формула вводится в том же формате, как при записи макроса, поэтому обычно введенное значение присваивается свойству FormulaR1C1 объекта типа Range. В нашем примере формула вводится в активную ячейку рабочего листа. При вводе можно использовать названия русскоязычных функций.

Попробуйте ввести различные формулы с помощью этого макроса. В качестве функций можно использовать функции Excel Сумм() или Макс().

Пример 4.4. Создадим макрос, который заданный диапазон ячеек копирует на новый рабочий лист.

Данный пример демонстрирует применение стандартных диалоговых окон для указания диапазона ячеек. Метод InputBox в этом случае удобнее функции, так как позволяет вводить ссылки с помощью мыши, в том числе на ячейки, находящиеся не на текущем листе, но мы рассмотрим и функцию, чтобы была понятна разница между ними. Также в примере показано, как можно обработать возможные ошибки при вводе и ситуацию, когда пользователь нажал на кнопку Отмена.

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

Рассмотрим самый простой вариант макроса, в котором для ввода диапазона используется метод InputBox:

Диапазон, введенный в диалоговом окне, сохраняется в переменной MyRng. Как видим, переменную можно не объявлять, хотя с объявленной переменной работать удобнее и безопаснее. Так как параметр Type метода InputBox равен 8, метод возвращает значение типа Range, то есть объект, поэтому при присваивании обязательно используем Set. В данном случае в переменной MyRng хранится не строка с адресом, а коллекция ячеек. Для копирования информации в буфер обмена у объектов типа Range используется метод Copy.

Обратите также внимание, как задается параметр Default. По умолчанию поле для ввода будет выведено на экран пустым. Мы хотим, чтобы оно содержало ссылку на диапазон, в который входит текущая ячейка. Для этого используем свойство CurrentRegion объекта ActiveCell, которое вычисляет этот диапазон (границами диапазона являются пустые строки и столбцы или границы листа). Свойство Address возвращает текстовую строку, представляющую ссылку на найденный диапазон. Это значение и выводится в поле для ввода окна InputBox, когда оно появляется на экране.

Поработав с этим макросом, вы заметите, что закрыть окно, нажав ОК, можно только в том случае, если диапазон введен правильно. В противном случае система сообщит об ошибке и окно не закроет. Таким образом, нам в макросе не нужно проверять, что пользователь ввел корректные данные. Эту проверку за нас делает метод InputBox, что очень удобно. Если же вы выберете кнопку Отмена, метод возвращает логическое значение False, которое нельзя преобразовать в объект Range, и при присваивании возникнет ошибка, сообщение о которой вы увидите на экране. Ниже приведен текст макроса, в котором эта ошибка нейтрализуется:

Оператор On Error применяется в языке для указания действий при возникновении ошибки во время выполнения программы. Мы определили, что это инструкции, находящиеся после метки NoDate (макрос выдаст сообщение, что копирование не выполнено). Обратите внимание, что перед меткой помещена инструкция Exit Sub (она завершает выполнение процедуры), чтобы не выполнялась обработка ошибки в случае, когда диапазон введен правильно. Если инструкцию убрать, то сообщение будет выдаваться и в том случае, когда ошибки не было. Если мы захотим, чтобы при выборе кнопки Отмена наш макрос ничего не делал, то инструкцию MsgBox нужно удалить, Exit Sub тоже, так как она станет не нужна, но метку следует оставить.

Рассмотрим вариант макроса, в котором для ввода диапазона используется функция InputBox. Как уже говорилось, он менее удобен, так как при вводе нельзя использовать мышь.

Функция InputBox всегда возвращает значение типа String. Если нажата кнопка Отмена, то это пустая строка, если ОК, то это строка, введенная пользователем в поле ввода. Никаких проверок во время ввода не выполняется. В нашем макросе введенное значение будет присвоено переменной Ans. Далее мы должны будем проанализировать введенную строку, и в случае ввода ссылки на диапазон выполнить над ним необходимые действия, в противном случае – выдать сообщение об ошибке или же ничего не делать.

Используя встроенные функции для работы со строками можно запрограммировать проверку, является ли значение Ans ссылкой, но это непростая задача. Поэтому мы снова воспользуемся инструкцией On Error. При создании объекта Range(Ans), возникнет ошибка, если Ans не является ссылкой. Как и в предыдущем случае, мы заранее указали, что тогда нужно перейти на метку NoDate, тем самым нейтрализовав эту ошибку. Если мы захотим, чтобы при выборе кнопки Отмена наш макрос не выдавал сообщения, то после метки вместо вызова MsgBox следует использовать условный оператор:

Пример 4.5. Создадим макрос, который после добавления нового листа к рабочей книге выводит запрос о переименовании листа. Если ответ – Да, то запрашивается новое имя листа, в противном случае лист не переименовывается. Данный пример демонстрирует использование окна MsgBox для создания диалога с пользователем.

Обратите внимание, как в этом макросе добавляется лист. Обычно (по умолчанию) новый лист вставляется перед текущим. Однако у метода Add есть параметры (Before и After), которые позволяют определить перед каким листом или после какого листа помещается созданный лист. Sheets.Count – это количество листов, но, так как элементы в коллекции нумеруются с единицы, это и номер последнего листа. Поэтому параметр After указывает на последний лист, а новый лист будет размещен после него.

Вид окна сообщения определяет второй параметр функции MsgBox. В нашем случае оно будет иметь 2 кнопки Да и Нет, так как использована константа vbYesNo, значок со знаком вопроса (vbQuestion), и после вывода на экран активной будет первая кнопка Да (vbDefaultButton1). Чтобы узнать, на какую кнопку нажал пользователь, мы анализируем значение, которое возвращает функция MsgBox. Так окно имеет кнопки Да и Нет, функция может вернуть всего два значения vbYes и vbNo. Сравнив значение функции с константой vbYes, мы узнаем ответ пользователя.

Для ввода нового имени листа использована функция InputBox. Пользователь может нажать в этом окне на кнопку Отмена, поэтому, прежде чем присваивать новое значение свойству Name, мы сравниваем введенное значение с пустой строкой. Для работы с новым листом использован объект ActiveSheet, так как после вставки этот лист становится активным.

Отладка программ

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

Команды, которые нужны при отладке программ, находятся в меню Debug. Ниже перечислены основные действия, которые выполняются при отладке и способы их выполнения:

1. Чтобы установить или удалить в нужной строке точку останова щелкните в области слева от строки кода. Признаком установленной точки останова является красный кружок. Можно также использовать команду Debug /Toggle Breakpoint или клавишу F9;
2. Выполняйте программу как обычно. Когда программа дойдет до строки с установленной точкой останова, ее выполнение будет приостановлено. Строка, которая должна выполняться следующей, отмечается желтой стрелкой слева от строки и выделяется желтым цветом;
3. Чтобы узнать текущее значение переменной, наведите на нее курсор. Имя переменной и ее значение будут выведены в окне всплывающей подсказки;
4. Чтобы наблюдать за значением переменной или выражения постоянно, нужно добавить их в окно Watches. Для этого выделите нужное выражение в тексте и выполните команду Debug /Add Watch или перетащите его в окно Watches;
5. Чтобы перейти к следующей инструкции выполните команду Debug /Step Into или нажмите клавишу F8;
6. Чтобы остановиться на какой-то другой строке (не на следующей), установите курсор в этой строке и выберите команду Debug /Run To Cursor;
7. Чтобы остановить программу, выполните Run /Reset или нажмите на кнопку панели инструментов.

Пример 4.6. Выполните примеры данного семинара в режиме отладки.

Начните с примера 4_1. Точку останова установим на первой исполняемой инструкции макроса. Добавим в окно Watches переменные n, T, D. Если окно не видно на экране, его можно открыть командой View /Watch Window.

После запуска выполнение макроса будет приостановлено в точке останова. На рисунке вы видите результат, полученный после того, как дважды была нажата клавиша F8. Выполнились первые две инструкции, желтым подсвечена следующая исполняемая инструкция. В окне Watches видно, что переменные n и D уже имеют значения, а T пока нет. Если подвести курсор к любой переменной, то во всплывающем окне вы увидите текущее значение этой переменной. Продолжите пошаговое выполнение макроса до его завершения.

Работа с ячейками рабочего листа

Для доступа к отдельной ячейке листа обычно используются свойства Range или Cells. Например, мы можем присвоить значения ячейкам B10 и C10 текущего рабочего листа следующим образом:

или

Как уже говорилось свойство Value – это свойство по умолчанию, поэтому его можно не указывать. В приведенных примерах значение присваивается именно свойству Value объекта типа Range, который создается при обращении к свойствам Range и Cells объекта Application. Другие свойства или методы опускать нельзя:

Если ячейка находится не на текущем листе, то нужно указывать лист. Это тоже можно сделать несколькими способами:

В первом случае мы используем номер листа в коллекции. Этот способ не всегда удобен, так как при выполнении программы номер листа может измениться. Во втором случае Лист1 – это имя листа, указанное на его ярлычке (значение свойства Name объекта WorkSheet). В третьем случае Лист1 – это значение свойства (Name), которое определяет имя модуля рабочего листа. Не забывайте, что Name и (Name) – это разные свойства, и их значения могут отличаться. Для свойства Cells выражения аналогичны.

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

В первом случае значение будет присвоено ячейке C10 (в той же строке, номер столбца на 1 больше), во втором – ячейке, находящейся над активной ячейкой.

Для определения диапазона также используются свойства Range и Cells. Range("A2:C3") и Range(Cells(2,1), Cells(3,3)) – это один и тот же диапазон.

Задания для самостоятельной работы:

4.1. Напишите макрос, который для двух введенных чисел выводит в одном окне их сумму, произведение и максимальное значение.

4.2. Напишите макрос, который запрашивает у пользователя значение числа. Если число введено правильно, то вычисляется квадратный корень из этого числа и выводится окно сообщения с результатом. В случае отрицательного числа или другой ошибки выводится сообщение об ошибке. Далее запрашивается, нужно ли сохранять результат. Если пользователь ответит Да, то запрашивается адрес ячейки для значения.

4.3. Напишите макрос, который позволяет редактировать содержимое текущей рабочей ячейки с помощью функции InputBox. Если при работе с окном пользователь нажмет Отмена, но содержимое ячейки изменяться не должно.

4.4. Напишите макрос, который находит все решения уравнения ax ² + bx + c = 0. Значения a, b, c вводятся. Для ввода коэффициентов используйте стандартные окна ввода. Решение выводится на рабочий лист в виде

, или

в зависимости от значений коэффициентов. Предусмотрите все возможные случаи, например, при a = 0, b = 0, c = 0 x – любое число. Адрес ячейки со словом «Решение:» запрашивается у пользователя.