Автоматизация и моделирование бизнес-процессов в Excel

Глава 04
Автоматизация рабочего процесса на примере оформления и регистрации командировочных удостоверений

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

Основным элементом приложения, на основании данных которого будут генерироваться командировочные удостоверения, является журнал регистрации.

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

Для создания приложения откройте новую книгу Excel и присвойте ей имя, которое будет понятно не только создавшему ее. Например, Генератор Командировочных Удостоверений, и сохраните файл на жестком диске компьютера.

В новой книге будут находиться два рабочих листа:

• КомандировочноеУдостоверение - с лицевой и обратной стороной командировочного удостоверения;
• ЖурналРегистрацииКУ - с журналом регистрации командировочных удостоверений.

Журнал регистрации командировочных удостоверений

Журнал регистрации представляет обыкновенную табличную базу данных, или с позиции Excel - список. Поля базы данных расположите в любом, удобном для пользователя порядке, например, как показано на рис. 4.1. и 4.2.

Рис. 4.1. Левая область журнала регистрации командировочных удостоверений на рабочем листе ЖурналРегистрацииКУ

Рис. 4.2. Правая область журнала регистрации командировочных удостоверений на рабочем листе ЖурналРегистрацииКУ

Наименование предприятия введите в ячейку А1.

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

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

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

Для удобства в работе со списком желательно использовать некоторые сервисные возможности Excel, например, закрепление областей рабочей таблицы.

Закрепление областей позволяет видеть одновременно на экране строку заголовков и, например, два левых столбца, с расположенными в них порядковым номером и фамилией. Для закрепления областей рабочей таблицы в рассматриваемом примере поместите табличный курсор в ячейку С4 и выполните команду Окно/Закрепить области.

Формулы на листе ЖурналРегистрацииКУ

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

Определение порядкового номера записи

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

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

Автоматизация ввода текста даты и номера командировочного удостоверения

Следующий элемент возможного повышения эффективности ввода данных в журнал регистрации, формула в столбце Н - Дата и номер командировочного удостоверения. В ячейку Н4 может быть введена формула, которая соединяет первые элементы текста в столбце G - Дата и номер приказа, и порядковый номер документа в журнале регистрации:

=СЦЕПИТЬ(ЛЕВСИМВ(G4;14);A4)

Функция ЛЕВСИМВ из текста 19.01.2003г. № 01-К, введенного в ячейку G4, возвращает левые 14 символов (в том числе и пробелы). Функция СЦЕПИТЬ производит соединение выделенного текста с порядковым номером, находящимся в ячейке А4.

Но данная формула имеет существенный недостаток - предполагается, что текст даты и номера приказа в ячейку G4 введен правильно и из этого текста нужно выделить только 14 левых символов. Если же при наборе текста с клавиатуры введены лишние пробелы, то формула возвратит совсем не ту текстовую строку, которая предполагалась. И второй недостаток -значение 14 (номер позиции символа № в тексте) введено в формулу в виде константы.

Недостатки устраняются вводом в формулу функции СЖПРОБЕЛЫ, которая удаляет лишние пробелы в тексте, введенном в ячейку G4, после чего функция НАЙТИ производит поиск номера позиции символа №. Возвращенный номер позиции и является тем количеством символов, которые будут возвращены далее функцией ЛЕВСИМВ. Полученная усовершенствованная формула:

=СЦЕПИТЬ(ЛЕВСИМВ(СЖПРОБЕЛЫ(G4);НАЙТИ("№";СЖПРОБЕЛЫ(G4)));A4)

Таким образом, текст 19.01.2003г. №1 в ячейке Н4, содержащий дату и номер командировочного удостоверения, формируется в полуавтоматическом режиме.

Определение продолжительности командировки

В зависимости от методологии принятой на предприятии, возможен:

• либо расчет продолжительности командировки как разность, определяемая датами выбытия и прибытия;
• либо, наоборот, по дате выбытия и продолжительности командировки, определяется дата прибытия.

Автоматизация этого процесса избавит специалиста от расчета количества дней на пальцах или с помощью подсчета количества дней в настольном календаре.

В первом случае для автоматического определения продолжительности командировки в ячейку К5 (Срок командировки) введите формулу:

=J5-I5+1

которая вычитает из даты прибытия дату выбытия и прибавляет значение 1

Во втором случае в ячейке J6 может находиться формула:

=I6+K6-1

которая прибавляет к дате выбытия количество дней продолжительности командировки и вычитает значение 1.

Командировочное удостоверение

Создание формы удостоверения

При создании формы командировочного удостоверения (рис. 4.3.) на рабочем листе КомандировочноеУдостоверение, не следует пренебрегать объединением ячеек и переносом текста в ячейках с формулами, созданием границ различной толщины (линии ограничивающие границы ячеек), применением различных шрифтов и их размеров. Эти параметры можно ввести или изменить с помощью диалогового окна Формат ячеек, вызываемого командой Формат/Ячейки.

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

Рис. 4.3. Форма командировочного удостоверения

Формулы поиска информации

Функция, на которой базируется автоматизация заполнения командировочного удостоверения - функция поиска ВПР.

Описание формулы поиска ФИО

Рассмотрим формулу, введенную в ячейку Е3 (Фамилия, имя, отчество):

=ВПР(L1;'ЖурналРегистрацииКУ'!A4:J200;2;ЛОЖЬ)

Функция ВПР ищет значение, заданное в первом аргументе, в крайнем левом столбце диапазона, указанного во втором аргументе. Возвращает она значение из строки содержащей найденное в первом аргументе значение, но находящегося в столбце, который задан в третьем аргументе функции. Четвертый аргумент - логическое выражение ЛОЖЬ, задается для поиска точно соответствующей информации, введенной в первом аргументе.

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

Остальные формулы

Остальные формулы (кроме формулы в ячейке D5) отличаются от описанной выше только номером столбца в третьем аргументе. Значения аргументов:

• 5 - ячейка F7 (пункт назначения)
• 6 - ячейка D9 (наименование предприятия)
• 11 - ячейка G12 (срок командировки)
• 12 - ячейка D13 (цель командировки)
• 7 - ячейка G15 (приказ от)
• 13 - ячейка I16 (паспорт серии)
• 8 - ячейка В11 (дата и номер командировки)

Ввод текста места работы и должности

Формула в ячейке D5 использует комбинацию функций СЦЕПИТЬ, ВПР, а также ссылку и ввод текстовых данных.

=СЦЕПИТЬ(ЖурналРегистрацииКУ!A1;", ";ВПР(L1;ЖурналРегистрацииКУ!A4:J200;3;ЛОЖЬ);", "; ВПР(L1;ЖурналРегистрацииКУ!A4:J200;4;ЛОЖЬ))

По ссылке на ячейку А1 листа ЖурналРегистрацииКУ осуществляется ввод текста наименования предприятия.

Первая функция ВПР производит поиск должности работника, а вторая - наименования отдела, в котором он работает.

Все определенные текстовые значения (предприятие, должность и наименование отдела) соединены функцией СЦЕПИТЬ с разделением между собой знаками запятых и пробелами.

Обратная сторона командировочного удостоверения

Обратная сторона командировочного удостоверения расположена на том же рабочем листе - КомандировочноеУдостоверение, ниже области занимаемого лицевой стороной документа (рис. 4.4.).

Деталь, на которой следует остановиться - это заполнение прописью даты выбытия. На рис. 4.4. в строке формул видно, что в ячейке В25 находится ссылка на ячейку N25, которая является выходом модуля формирования даты прописью.

Рис. 4.4. Обратная сторона командировочного удостоверения

Модуль написания даты прописью с формулами показан на рис. 4.5. и формирует этот текст на основании даты в формате Excel, введенной в столбец I журнала регистрации командировочных удостоверений. В свою очередь поиск этой даты осуществляется формулой, введенной в ячейку L25 с помощью функции ВПР по порядковому номеру, введенному в ячейку L1.

Модуль имеет три ячейки:

• L25 - вход модуля. Это ячейка для ввода даты, которая будет отображена в виде текстовой строки. В ячейку входа модуля может быть введена дата с клавиатуры, указана ссылка на ячейку содержащей дату или формула, возвращающая эту дату;
• M25 - ячейка проведения промежуточных вычислений;
• N25 - выход модуля. В этой ячейке, окончательно формируется текстовая запись введенной на вход даты. На выход модуля может быть указана ссылка, из ячейки в которой должен находиться текст даты.

Рис. 4.5. Модуль написания даты прописью с формулами

Создание элементов управления

Созданный генератор командировочных удостоверений имеет недостаток - для просмотра на листе КомандировочноеУдостоверение записей, введенных в журнал регистрации, каждый раз необходимо вводить в ячейку L1 с клавиатуры порядковый номер этой записи в журнале. А это не совсем удобно.

Для устранения этого недостатка на листе КомандировочноеУдостоверение, с правой стороны от формы командировочного удостоверения, создайте элементы управления, с помощью которых можно будет изменять значение, содержащееся в ячейке L1. Такими элементами управления могут являться Список и Счетчик

Элемент управления Список

Для создания элементов управления используется панель инструментов Формы (рис. 4.6.), для активизации которой выполните команду Вид/Панели инструментов/Формы.

Рис. 4.6. Панель инструментов Формы

Для внедрения элемента управления Список щелкните левой кнопкой мыши на панели Формы по указателю элемента управления Список . Указатель мыши, после перевода его на поле рабочего листа, приобретет вид крестика. Выбрав место расположения элемента управления на листе, и нажав левую кнопку мыши, очертите контур будущего элемента управления (рис. 4.7.).

Рис. 4.7. Элемент управления Список

Для управления с помощью элемента управления Список содержимым ячейки L1, поместите указатель мыши на элемент управления и нажмите правую кнопку мыши. В появившемся контекстном меню выделите команду Формат объекта, что вызовет появление диалогового окна Формат элемента управления, в котором перейдите на вкладку Элемент управления (рис. 4.8.). Это окно вызывается также комбинацией клавиш [Ctrl+1].

Рис. 4.8. Диалоговое окно Формат элемента управления для элемента управления Список

Поле Формировать список по диапазону предназначено для ввода диапазона, по которому будет формироваться список по этому элементу управления. Применимо к нашему приложению в этом поле должен быть указан диапазон журнала регистрации командировочных удостоверений, в котором формируются порядковые номера записей, содержащих данные командировочных удостоверений. Для этого поместите курсор в поле Формировать список по диапазону и на рабочем листе ЖурналРегистрацииКУ выделите область ячеек в столбце А, в котором будут расположены порядковые номера записей командировочных удостоверений, например $А$4:$А$200. В этом поле можно указать не только область формирования порядковых номеров, но и любую другую область журнала регистраций, но тогда этот список не будет иметь желательную информационность, а будет отражать, например, населенные пункты назначения или место работы сотрудника.

Выделенный в списке элемента управления порядковый номер удостоверения должен автоматически вводиться в ячейку L1, для чего поместите курсор в поле Помещать результат в ячейку и на листе КомандировочноеУдостоверение, выделите мышью ячейку L1. В области Возможен выбор активизируйте переключатель Только одного значения, после чего нажмите кнопку ОК. Следует заметить, что значение помещаемого результата в ячейку L1 будет зависеть от выбранного значения в списке, начиная сверху, независимо от самого значения указанного порядкового номера введенного в столбце А. Это следует учитывать при формировании порядковых номеров командировочного удостоверения.

При выделении мышью значения порядкового номера в списке, например "3", это значение будет автоматически помещено в ячейку L1, в результате чего на экране монитора на листе КомандировочноеУдостоверение будет выведено командировочное удостоверение, имеющее порядковый номер записи 3 в журнале регистрации.

Элемент управления Счетчик

Рядом с элементом Список поместите элемент управления Счетчик (рис. 4.9.). С помощью счетчика можно последовательно "перебирать" в любом направлении командировочные удостоверения, находящиеся в журнале регистрации с шагом изменения равным единице или другим, заданным пользователем.

Диалоговое окно Формат элемента управления (рис. 4.10.) для элемента управления Счетчик, несколько отличается от предыдущего. В поле Начальное значение отображается значение, которое содержится в этот момент времени в ячейке L1. В поле Минимальное значение введите начальный номер, с которого Счетчик начинает перебирать номера с шагом, значение которого будет введено в поле Шаг изменения. При желании введите значение в поле Максимальное значение, в противном случае Excel введет его самостоятельно.

Рис. 4.9. Элемент управления Счетчик

Рис. 4.10. Диалоговое окно Формат элемента управления

Вывод на печать бланка командировочного удостоверения

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

• выделите мышью область А1:J20 (лицевая сторона командировочного удостоверения);
• нажмите на клавишу Ctrl и, удерживая ее, выделите область А22:J69 (обратная сторона);
• выполните команду Файл/Область печати/Задать.

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

Вывод командировочного удостоверения на печать с использованием стандартных методов

Возможно два метода последовательного вывода пользователем на печать нескольких командировочных удостоверений, введенных предварительно в журнал регистрации.

• 1-й метод. Вывод на печать одного командировочного удостоверения, печатая последовательно лицевую сторону, затем обратную. Для этого желательно, чтобы в принтере находился один лист бумаги. Тогда нажав на кнопку Печать на стандартной панели инструментов нужно лист, после вывода его из принтера с распечатанной лицевой стороной, перевернуть и "пропустить" через принтер с другой стороны. Далее повторить эту операцию с оставшимися командировочными удостоверениями.
• 2-й метод заключается в том, чтобы отпечатать партию командировочных удостоверений с одной стороны, и затем пропустить эти листы бумаги через принтер с другой стороны. Недостаток этого метода заключается в том, что тогда каждый раз отправляя документ на печать нужно применять команду Файл/Печать, а после этого в диалоговом окне Печать отмечать номера печатаемых страниц, например, с 1-й по 1-ю, что тоже не совсем удобно.

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

Справочная система Microsoft Excel

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

Перед тем как записать макрос, необходимо спланировать шаги и команды, которые он будет выполнять."

Запись макроса вывода документа на печать

Чтобы писать макросы, не обязательно быть опытным (продвинутым) пользователем. Можно с помощью активизации средства записи макроса записать последовательность выполняемых действий. При этом программа записи автоматически преобразует последовательность действий пользователя в код на языке Visual Basic of Application (VBA), который является основой записи макроса.

Планирование шагов и команд автоматизации процесса печати

Принимаем за основу для автоматизации 2-й метод. Предполагаем, что необходимо отправить на печать лицевую сторону сформированных командировочных удостоверений с порядковыми номерами с 1-го по 3-й, после чего повторить ту же операцию с выводом на печать обратной стороны.. Исходя из этого однотипные действия 2-го метода по выводу на печать каждой стороны нужно повторить три раза. Значит было бы удобно отправлять документ на печать одним действием, например, нажатием на кнопку, после чего Excel должен самостоятельно вызвать диалоговое окно Печать, определить выводимые на печать страницы и нажать на кнопку ОК.

Макрос вывода документа на печать

Для того чтобы начать запись макроса, выполните команду Сервис/Макрос/Начать запись. Появится диалоговое окно Запись макроса (рис. 4.11.). Если это первый записываемый макрос в открываемой книге, то тогда в поле Имя макроса будет указано его имя - Макрос1.

Рис. 4.11. Диалоговое окно Запись макроса

Многие пользователи не изменяют имя макроса и через некоторое время может возникнуть недоразумения - какую операцию выполняет этот макрос. Поэтому сразу измените его имя на ПечатьЛицевойСтороны. Имя макроса должно начинаться с заглавной буквы и между элементами текста имени не должно быть пробелов.

В поле Сочетание клавиш введите букву на английском языке, например, "z" (рис. 4.12.). Это позволит сразу же после записи макроса, нажав на комбинацию клавиш [Ctrl+z] запустить макрос на автоматическое выполнение записанной операции.

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

В раскрывающемся списке Сохранить в находятся:

• Личная книга макросов;
• Новая книга;
• Эта книга;

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

Рис. 4.12. Диалоговое окно Запись макроса после внесения изменений в поля Имя макроса, Описание и Сочетание клавиш

После нажатия на кнопку ОК или клавишу Enter Excel начинает запись макроса, для чего фиксирует все действия пользователя, независимо от того, правильны ли они. Фиксация действий производится на языке кода VBA и будет осуществляться до остановки записи. Для остановки записи предназначена появляющаяся после начала записи кнопка Остановка (рис. 4.13.).

Рис. 4.13. Кнопка Остановка

Для записи кода действий, выполняемых макросом, последовательно выполните процедуры по выводу на печать лицевой стороны командировочного удостоверения:

• выполните команду Файл/Печать;
• в появившемся диалоговом окне Печать в области Печатать введите номера страниц с 1-й по 1-ю и нажмите кнопку ОК;
• для остановки записи макроса нажмите кнопку Остановка или выполните команду Сервис/Макрос/Остановка записи.

Для записи макроса печати оборотной стороны командировочного удостоверения нужно проделать аналогичную операцию по выводу на печать страницы 2.

Просмотр записанного макроса

Просмотр записанного макроса заключается в просмотре непосредственно сгенерированного текста кода VBA при его записи. Для этого, выполнив команду Сервис/Макрос/Редактор Visual Basic или нажав комбинацию клавиш [Alt+F11], откройте приложение Microsoft Visual Basic (рис. 4.14.).

Рис. 4.14. Приложение Microsoft Visual Basic с записанными макросами вывода на печать лицевой и обратной стороны документа

При запуске Редактора Visual Basic в этом приложения присутствуют три основных окна:

• окно проекта;
• окно свойств;
• окно программы.

Окно проекта

Окно проекта расположено слева вверху (рис. 4.14.) и содержит несколько папок. В нашем случае имеются папка Microsoft Excel Объекты, (рис. 4.15.) в которой находятся объекты, с которыми производятся действия:

• Лист1 (КомандировочноеУдостоверение);
• Лист2 (ЖурналРегистрацииКУ);
• ЭтаКнига (в которой записаны эти макросы).

Вторая папка - Модули, в которой находится элемент Модуль1 (рис. 4.14.), содержащий записанные макросы вывода на печать 1-й и 2-й страницы.

При создании диалоговых окон и форм может находиться и третья папка - Формы, но в этой книге она рассмотрена не будет.

Окно проекта предназначено для управления различными элементами проекта VBA и открывается с помощью команды Вид/Окно проекта.

Рис. 4.15. Окно проекта

Если обратиться к окну проекта, показанному на рис. 4.15. то в объекте Модули находится один элемент Модуль1. Это тот элемент, который создан автоматически при записи макросов вывода документов на печать.

Окно свойств

Окно свойств находится в левом нижнем углу (рис. 4.14.).

В окне свойств перечисляются характеристики элементов проекта. Для изменения свойств элемента, выделите его в окне проекта и тогда его характеристики отобразятся в окне свойств. Если выделить элемент Модуль1, то в окне свойств отобразится его имя: Модуль1. Для изменения имени модуля в окне свойств измените значение свойства Name, введя новое имя - Печать (рис. 4.16.).

Рис. 4.16. Окна Проектов и Свойств с переименованным элементом Модуль1 в элемент Печать

Окно программы

Окно программы самое большое и находится справа (рис. 4.14.).

Именно в нем происходит запись текста макроса на языке Visual Basic of Application. Окно программы имеет два режима просмотра:

• представление полного модуля;
• представление процедуры.

Переключение между этими режимами осуществляется с помощью кнопок, находящимися в левом нижнем углу окна программы.

В режиме представления процедуры отображается только один макрос. Для перехода к следующему макросу, его имя нужно выбрать в раскрывающемся списке в верхней части окна программы или воспользоваться клавишами [Ctrl +Page Up] и [Ctrl+Page Down].

В режиме представления в окне программы находятся все макросы, в записанной последовательности. На рис. 4.14. показан режим представления.

Исследование записанного макроса

Ознакомимся с кодом VBA макроса ПечатьЛицевойСтороны. Если окно программ закрыто, то для просмотра макроса в окне проекта активизируйте двойным нажатием на правую кнопку мыши элемент Печать объекта Модули.

Текст макроса в окне программы содержит следующие элементы (рис. 4.14.):

• ключевые слова Sub и End Sub, означающие начало и конец текста макроса, шрифт которых выделен синим цветом
• имя макроса, которое следует за ключевым словом Sub начала макроса;
• комментарии, шрифт которых выделен зеленым цветом;
• код VBA или непосредственно записанные действия, которые выполняет макрос.

Комментарии. В нашем примере это те данные, которые вводились в диалоговое окно Запись макроса (рис. 4.12.). Строка комментария предназначена для описания действий и начинается с апострофа. Удаление комментариев никоим образом не влияет на выполнение макроса. Более подробно комментарии будут рассмотрены далее.

И, наконец, код записанных действий, выполняемых макросом:

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1

говорит о том, что с активного окна выделенного (активного) рабочего листа отправить на печать страницы с 1-й по 1-ю в количестве одной копии.

Текст макроса, который начинается и заканчивается ключевыми словами Sub и End Sub, называется подпрограммой или процедурой, что одно и то же.

Запуск записанных макросов на выполнение из приложения Excel

Запуск макросов на выполнение может осуществляться как из приложения Microsoft Visual Basic, так и из приложения Microsoft Excel. Из Excel выполнение макроса задается двумя способами:

• используя комбинации назначенных клавиш при записи макроса [Ctrl+z] - печать лицевой стороны или [Ctrl+x], для печати обратной стороны командировочного удостоверения;
• используя диалоговое окно Макрос.

Рис. 4.17. Диалоговое окно Макрос

Диалоговое окно Макрос (рис. 4.17.) вызывается командой Сервис/Макрос/Макросы или комбинацией клавиш [Alt+F8]. Для выполнения макроса выделите в списке имя макроса и нажмите кнопку Выполнить.

С помощью этого же диалогового окна можно войти в выделенный в списке макрос, для чего нажмите на кнопку Войти. Это позволит открыть приложение Microsoft Visual Basic и в выбранном макросе будет выделена первая строка с ключевым кодом начала подпрограммы и его именем. Этот режим называется режимом отладки.

Создание элемента управления Кнопка

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

Создание кнопки осуществляется аналогично созданию элементов Счетчик и Список, описанных выше. Для этого, вызвав панель Формы (рис. 4.6.), создайте элемент управления Кнопка, по завершению создания которой, появится диалоговое окно Назначить макрос объекту (рис. 4.18.).

Рис. 4.18. Диалоговое окно Назначить макрос объекту

Для назначения кнопке макроса выделите в диалоговом окне Назначить макрос объекту нужный макрос и нажмите кнопку ОК. Теперь нажатие на созданную кнопку (рис. 4.19.) вызовет выполнение назначенного ей макроса.

Рис. 4.19. Созданная конка управления макросом

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

Рис. 4.20. Кнопки управления макросами для вывода документа на печать

На рис. 4.21. показан рабочий лист КомандировочноеУдостоверение со всеми встроенными элементами управления.

Рис. 4.21. Рабочий лист КомандировочноеУдостоверение с встроенными элементами управления

Вставка содержимого выбранной строки в конец списка

Вернемся на рабочий лист ЖурналРегистрацииКУ.

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

Оставим за пользователем только выбор копируемой строки, а все остальное поручите выполнять Excel в автоматическом режиме при нажатии на созданную кнопку Копирование и вставка выделенной строки (рис. 4.1.), которой назначьте следующий записываемый макрос с именем ВставкаСтроки.

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

• перед записью макроса выделите выбранную строку, например, строку 9;
• выполните процедуру начала записи макроса, после чего задайте команду копирования, например, нажатием на кнопку Копирование на стандартной панели инструментов. Содержимое строки 9 будет помещено в буфер обмена.;
• в связи с тем, что процедура автоматического определения и выделения первой пустой строки списка еще не изучена, выделите самую последнюю строку списка. Согласно, заданным в начале главы, условиям - это строка 200. Но чтобы добраться до нее, нужно выполнить ряд действий, например, перемещений на несколько экранов монитора вниз. Не затрудняйте себя, а выделите, например, строку 14 и произведите вставку из буфера обмена, например, нажатием на кнопку Вставка на стандартной панели инструментов;
• выделите строки от строки вставки (14) до первой строки после заголовка списка (4) и нажмите на кнопку Сортировка по возрастанию на стандартной панели управления. По умолчанию Excel произведет сортировку по данным столбца А. Список расположится в порядке возрастания порядковых номеров и содержимое скопированной строки 9 будет находиться в конце списка;
• остановите запись макроса.

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

Исследование кода VBA при записи макроса ВставкаСтроки

После записи макроса не откладывайте на потом исследование записанного кода VBA, а открыв Редактор Visual Basic просмотрите записанные действия и при необходимости отредактируйте код VBA. Механически сгенерированный код показан на рис. 4.22.

Рис. 4.22. Механически сгенерированный код VBA

Между ключевыми словами начала и окончания выполнения подпрограммы находится восемь строк кода. По очереди рассмотрим каждую строку. Первая строка:

Selection.Copy

, в которой:

Selection - это неявная ссылка на свойство объекта Worksheet (рабочий лист) и означает выделенный (объектом выделения может быть ячейка, диапазон или несколько несмежных диапазонов ячеек, строка или столбец) диапазон.
Copy - название метода и означает команду копировать выделенный объект.

Обратите на точку, находящемуся между текстом кода. Она называется операция точка, и предназначена для разделения названия свойства и названия метода в этой строке.

Следующая строка:

Rows("14:14").Select

в которой Rows это объект - строки, в скобках указано какие именно строки, а Select это метод - выделить. Согласно заданным условиям разрабатываемого приложения это должна быть 200-я строка, поэтому замените значение 14 на значение 200.

Третья строка:

ActiveSheet.Paste

означает что операция вставки (Paste) выполняется на активном рабочем листе (ActiveSheet). В данном случае ActiveSheet - свойство объекта Workbook и означает, что у этого объекта есть такой текущий объект, как активный рабочий лист.

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

Selection.Copy (Rows("200:200"))

и создается она всего четырьмя действиями при редактировании сгенерированного механически кода:

• установите курсор в конце первой строки и введите открывающуюся скобку;
• нажмите клавишу Delete, и текст второй строки кода переместится в конец первой строки;
• введите закрывающуюся скобку после указания диапазона;
• с помощью клавиши Delete удалите дальнейший ненужный код.

При подобном редактировании кода макроса нет необходимости даже соблюдать интервал (пробелы) между операторами Copy и Rows. После окончания редактирования, установка интервалов между операторами при отсутствии ошибок выполняется Редактором Microsoft Visual Basic автоматически.

Следующая строка:

Rows("4:14").Select

выделяет диапазон строк для сортировки, в которой значение 14 замените на 200.

Строку:

Range("A14").Activate

Excel генерирует автоматически. Она указывает на то что прежде чем выполнять сортировку необходимо было указать по какой области будет происходить сортировка. Удаление этой строки никак не повлияет на выполнение макроса.

Следующую строку Excel генерирует также самостоятельно:

Application.CutCopyMode = False

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

Следующая выполняемая операция - сортировка состоит из двух строк:

Selection.Sort Key1:=Range("A14"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

В одной строке Редактора может находиться до 1024 символов. Но если все 1024 символа разместить в одной строке, то это затруднит ее чтение. Для переноса кода строки в следующую, используется символ подчеркивания (_), перед которым обязательно должен находиться пробел.

Excel позволяет выполнять операцию сортировки с использованием от одного до трех ключей и для сортировки диапазона с помощью VBA используется метод - Sort. В этой команде, выполняющей операцию сортировки, находятся следующие операторы:

• Selection - выделенная область рабочего листа, значения которой будут сортироваться;
• Sort - метод сортировка;
• Key1:=Range("A14") - произвести сортировку выделенного диапазона по столбцу А, где аргумент Key1- указывает; что это первый ключ сортировки. Как вы знаете Excel позволяет производить последнюю сортировку выделенного диапазона по трем полям с помощью диалогового окна Сортировка диапазона, которое вызывается с помощью команды Данные/Сортировка;
• Order1 - указывает на порядок сортировки диапазона по первому ключу. Аргумент xlAscending - произвести сортировку по возрастанию. При сортировке по убыванию присваивается значение xlDescending;
• Header - аргумент с помощью которого определяется: сортировать ли первую строку выделенного диапазона. При механической записи макроса этому аргументу присвоено значение xlGuess. В этом случае Excel самостоятельно определяет, является ли первая строка строкой заголовка и нужно ли ее сортировать. Для того чтобы указать, что первая строка является строкой заголовка, и ее сортировать не требуется, присвойте этому аргументу значение xlYes. Если присвоить значение xlNo, то в этом случае первая строка сортируется;
• OrderCustom - целое число, которое указывает порядок сортировки указанный пользователем на вкладке Списки диалогового окна Параметры, выводимое командой Сервис/Параметры. По умолчанию присваивается значение 1;
• MatchCase - указывает различать (True) или нет (False) при сортировке заглавные и строчные буквы;
• Orientation - указывает как проводить сортировку: по строкам (xlTopToBottom) или столбцам (xlLeftToRight).

Как видно из записанного кода для присвоения методам значений применяются знаки := (двоеточие и равно).

Полученный в результате редактирования код макроса показан на рис. 4.23., а остальной текст кода можно удалить.

За счет минимизации текста подпрограммы было увеличено его быстродействие. Ведь для Excel нужно было прочитать ранее 8 строк, по каждой из которых он должен был предпринять какие-то действия, что значительно дольше чем 3.

Рис. 4.23. Отредактированный код VBA подпрограммы вставки в конец списка содержимого выделенной строки

Итоги

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

Показано создание простейших формул поиска данных, а также:

• создание и внедрение элементов управления Счетчик и Список, для автоматизации выбора необходимого документа из базы данных;
• автоматизация процесса вывода документа на печать;
• основы механической записи макроса и последующее редактирование сгенерированного кода VBA;
• введение в Редактор Microsoft Visual Basic;
• приемы запуска макросов из Excel на выполнение;
• создание элемента управления Кнопка и назначение ей выполняемого макроса;
• автоматический ввод содержимого выделенной. строки в конец списка.

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

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