На этом шаге мы рассмотрим проблемы, связанные с вводом названий городов.
Первый элемент автоматизации создаваемого приложения, проверка - находится ли название выбранного населенного пункта (НП) в списке городов на листе Города.
Предварительное создание формул поиска информации
В ячейку D2 введите формулу, которая по порядковому, номеру, введенному в ячейку J1, производит поиск названия города в списке диапазона ячеек А2:В200 листа Города:
=ВПР(J1;$A$2:$B$200;2;ЛОЖЬ)
Аналогичная формула находится и в ячейке Е2, но порядковый номер названия этого города в списке вводится в ячейку J2.
Формула в ячейке G2 отличается от предыдущей тем, что находящаяся в первом аргументе функции ВПР функция СЦЕПИТЬ объединяет текст названия, введенных в ячейки D2 и Е2, городов. Объединенный текст названий городов и является искомым текстом поиска для определения введенного расстояния между этими НП в столбце D листа База. Если расстояния между этими городами введено в табличную базу данных на листе База, то это значение возвращается формулой:
=ВПР(СЦЕПИТЬ(D2;" ";E2);База!C:D;2;ЛОЖЬ)
Задача автоматизации ввода текста названия города
Задача автоматизации процесса ввода текста названий городов в ячейки D2:Е2 заключается в следующем:
- возможный отказ от ввода в эти ячейки текста названия НП с клавиатуры, если такое название имеется в списке на листе Города;
- если название НП отсутствует в списке на листе Города, то это должно визуально отражаться на экране монитора, и ввод названия этого НП в список должно осуществляться автоматически, после ввода названия в ячейки D2 или E2 с клавиатуры;
- если расстояние между выбранными названиями городов присутствует в табличной базе данных на листе База, то оно должно быть отражено в ячейке G2;
- автоматическое восстановление формул, введенных в ячейки D2, E2 и G2.
Необходимость автоматизации процесса ввода текста названия города
Процесс автоматизации любого процесса заключается в поиске наиболее оптимальной последовательности действий пользователя по вводу каких-либо данных и последующему отображению результатов ввода этих данных. Рассмотрим последовательность действий, которые должен совершить пользователь при выборе названий городов.
После ввода формул в ячейки D2, E2 и G2 пользователь должен просмотреть список названий НП на листе Города и, если присутствуют нужные ему наименования городов, то ввести их порядковые номера в списке в ячейки J1 и J2. Это не совсем удобно. На этом этапе можно применить элементы управления с раскрывающимся списком введенных названий НП и при выделении какого-либо из них, автоматическому помещению его порядкового номера в ячейки J1 и J2.
Если же название НП отсутствует в списке на листе Города, то должна присутствовать возможность вводить текст его названия в ячейки D2 и Е2 непосредственно с клавиатуры, несмотря на то, что эти ячейки содержат формулы. В таком случае это название НП должно автоматически вводиться в список названий НП, а формулы должны в последующем автоматически обновляться.
Может быть следующая ситуация - названия городов в столбце В на листе Города имеются, а расстояние между ними отсутствует на рабочем листе База. Тогда при вводе названий городов в ячейки D2 и Е2, формула в ячейке G2 возвратит значение ошибки #Н/Д. Поэтому должна присутствовать возможность ввода значения расстояния между этими НП в ячейку G2 поверх формулы, которая в последующем также должна автоматически обновляться.
Создание элементов управления для ввода названий городов
Для автоматизации ввода информации в ячейки D2 и E2 создайте на листе Города два элемента управления Список, задача которых будет состоять в том, чтобы:
- производить выбор в раскрывающемся списке названия городов;
- обновлять формулы в ячейках D2, E2, G2, Е4 и Е5.
После создания каждого элемента управления на рабочем листе выделите его и комбинацией клавиш Ctrl+1 вызовите диалоговое окно Формат элемента управления. На вкладке Элемент управления в поле Формировать список по диапазону введите адрес диапазона ячеек В2:В200 (раскрывающийся список названий городов). В поле Помещать результат в ячейку укажите ссылку на адреса ячеек J1 и J2 соответственно для каждого элемента управления. В таком случае порядковый номер названия города выбранного в раскрывающемся списке будет помещен в ячейку J1 или J2, а формулы в ячейках E2 и D2 по этому номеру определят название города.
Элементам управления назначены макросы ФормулаГород1 и ФормулаГород2 (рисунок 1), которые при обращении к этим элементам управления производят ввод формул в ячейки D2, G2 и в ячейки Е4 и Е5 (назначение этих ячеек и формул в них будет раскрыто далее).
Рис.1. Макросы обновления формул ввода наименований городов и расстояний между ними
Запись макросов заключается в:
- предварительном вводе в эти ячейки формул;
- для записи непосредственно кода VBA ввода этих формул в ячейки, после начала записи макроса, выделите поочередно указанные ячейки и нажмите клавиши F2 и Enter;
- после остановки записи макроса отредактируйте код VBA.
Ввод в список нового названия города
Следующий этап автоматизации - что делать, если пользователь не обнаружил в списке городов на листе Города нужное ему название. В этом случае он должен ввести текст названия НП в ячейку D2 или/и E2 с клавиатуры.
Формула в ячейке Е4 ответственна за отображение введенного в ячейку D2 названия НП, если таковой отсутствует в списке НП на листе Города:
=ЕСЛИ(ЕНД(ВПР(D2;$B:$B;1;ЛОЖЬ));D2;0)
Формула в первом аргументе функции ЕСЛИ производит с помощью функции ВПР поиск названия НП в списке городов в столбце В, и если не находит его, то функция ЕНД исправляет возвращенное функцией ВПР значение ошибки на значение ИСТИНА. Это значение и является разрешением для функции ЕСЛИ отобразить наименование НП, введенное в ячейку D2 с клавиатуры.
Задача формулы в ячейке Е4 - показать визуально пользователю, что название НП отсутствует в существующем списке НП. Следовательно, это название нужно ввести в этот перечень. Для автоматического ввода названия нового города в список НП, справа от ячейки Е4 расположена кнопка - Город1.
Задача этой кнопки - запуск на выполнение макроса НаименованиеГорода1, который несколько отличается от созданных ранее и выполняет следующее:
- присваивает переменной Row значение количества заполненных (непустых) ячеек в столбце В;
- определяет координаты первой пустой ячейку в конце списка в столбце В, имеющую координаты строки Row и столбца В (номер 2);
- присваивает этой ячейке значение, возвращенное формулой в ячейке Е4;
- выделяет диапазон ячеек от ячейки с определенными координатами (Row, 2), в которую введено название нового города и до ячейки В3;
- производит сортировку выделенного диапазона по убыванию, не считая ячейку В3 заголовком, о чем говорит то, что аргументу Header метода Sort присвоено значение xlNo;
- в ячейку столбца А вводит порядковый номер для названия города, находящегося последним в списке. Для этого использована функция МАХ (Max) определения максимального номера в столбце А, увеличенному затем на единицу.
После ввода названия нового города, оно отображается в раскрывающемся списке НП элемента управления Список.
Рис.2. Подпрограмма введения нового названия НП в список
Для создания макроса поступайте комбинированным способом - сначала механическая запись описанных построчно действий с последующим редактированием кода VBA подпрограммы.
В ячейке Е5 находится формула аналогичная формуле в ячейке Е4, но анализирующая название НП, введенного в ячейку Е2.
Кнопка Город2 активизирует аналогичный макрос НаименованиеГорода2, по вводу названия НП из ячейки Е5 в список городов. Повторять действия по записи и редактированию макроса необходимости нет. И отличие этого макроса от предыдущего заключается только в замене адреса ячейки с Е4 на Е5 и изменении в имени макроса значения 1 на 2. Создание макроса будет заключаться в копировании текста макроса в окне программы Редактора Visual Basic. Для этого:
- в окне программ выделите текст макроса НаименованиеГорода1 и скопируйте в буфер обмена;
- при помощи комбинации клавиш Ctrl+End переместите курсор в конец макроса НаименованиеГорода1 и после нажатия на клавишу Enter (переход на следующую строку) произведите вставку скопированного;
- замените в имени макроса значение 1 на 2: НаименованиеГорода2;
- выделите текст нового макроса и выполните команду Правка | Заменить или наберите комбинацию клавиш Ctrl+H;
- в появившемся диалоговом окне Замена (рисунок 3) введите в поле Образец адрес ячейки Е4, а в поле Заменить на Е5;
Рис.3. Диалоговое окно Замена - нажмите кнопку Заменить все;
- появившееся диалоговое окно Microsoft Visual Basic (рисунок 4) сообщит, что выполнена одна замена;
Рис.4. Диалоговое окно с сообщением о выполнении операции - закройте диалоговые окна;
- копированием кнопки Город1 создайте кнопку Город2 и назначьте ей созданный макрос.
Для замены всего лишь одного значения можно было бы и не проделывать такую трудоемкую операцию с вызовом диалогового окна Замена. Но важно было отработать саму технологию создания макроса методом копирования и последующей заменой в нем отдельных деталей текста.
Диалоговое окно Замена
Квалифицированное использование диалогового окна Замена значительно повышает эффективность создания подпрограмм. Коротко опишем его возможности.
Поле Образец предназначено для ввода искомого текста либо с клавиатуры, либо с помощью раскрывающегося списка, который позволяет выбрать один из четырех последних использовавшихся образцов. Если при вызове диалогового окна Замена выделен текст или курсор находится на слове, этот текст будет помещен в поле Образец.
Поле Заменить на предназначено для ввода текста замены. Для удаления из документа текста, указанного в поле Образец, следует оставить поле Заменить на пустым. В эти поля возможна вставка скопированного текста из буфера обмена.
Использование переключателей Области поиска задает диапазон поиска и замены текста:
- Текущая процедура - поиск выполняется только в текущей процедуре;
- Текущий модуль - поиск выполняется только в текущем модуле;
- В текущей базе данных - поиск выполняется во всех модулях текущей базы данных;
- Выделенный текст - поиск выполняется в выделенном блоке текста.
Раскрывающийся список Просмотр устанавливает направление поиска: Вверх, Вниз от положения курсора или Все в выбранном диапазоне.
Опция Только слово целиком выполняет поиск целого слова и игнорирует его вхождения в более длинные слова.
Опция С учетом регистра выполняет поиск вхождений с комбинацией строчных и заглавных букв, указанной в поле Образец.
Опция Использовать шаблон выполняет поиск с использованием подстановочных символов.
При нажатии на кнопку Заменить запрашивается подтверждение перед каждой заменой искомого текста.
При нажатии на кнопку Заменить все выполняется замена всех вхождений искомого текста на текст замены, не запрашивая подтверждения.
Оптимизация операции ввода в список нового названия города
С одной стороны контроль за вводом нового названия НП в ячейки D2 и Е2 оправдан - наглядность. Визуально отображены новые названия городов в ячейках Е4 и Е5, не содержащиеся в списке названий НП. Но с другой стороны второй элемент управления Кнопка и второй макрос "утяжеляют" файл. Минимизацию можно осуществить вводом в ячейку Е4 одной формулы, заменяющей введенные ранее две формулы в ячейки Е4 и Е5:
=ЕСЛИ(ЕНД(ВПР(D2;$B:$B;1;ЛОЖЬ));D2;ЕСЛИ(ЕНД(ВПР(E2;$B:$B;1;ЛОЖЬ));E2;0))
- нажать кнопку Город1 для ввода в список первого НП;
- после этого формула в ячейке Е4 возвратит название второго НП, который вводится в список городов вторым нажатием на эту же кнопку.
Таким образом, необходимость в кнопке Город2 и назначенном ему макросе отпадает.
На следующем шаге мы рассмотрим ввод растояний между городами.
