На этом шаге мы закончим разработку данного приложения.
После ввода примечаний полученный макрос ввода записи в журнал регистрации кассовых документов должен выглядеть, как показано на рисунке 1.
Рис.1. Полученный макрос ввода записи в журнал регистрации кассовых документов
Создание макроса заполнения записи приходного ордера в журнал регистрации кассовых документов
Для создания аналогичного макроса для заполнения области приходного ордера достаточно выделить область макроса РасходныйОрдер в окне программы, скопировать выделенный фрагмент в буфер обмена, поместив курсор в конец строки End Sub и произвести вставку из буфера обмена. После чего переименовать новый макрос в ПриходныйОрдер и отредактировать макрос (рисунок 2).
Рис.2. Макрос ПриходныйОрдер формирования записи приходного ордера в журнал регистрации кассовых документов
Усовершенствование созданного приложения
У созданных процедур, оказывающих помощь при заполнении журнала кассовых документов, есть два существенных недостатка:
- при вводе записи каждого кассового документа необходимо помещать табличный курсор в ячейку ввода даты этого документа;
- данные вводятся непосредственно в ячейки рабочего листа, что тоже не совсем удобно.
Для устранения первого недостатка необходимо найти алгоритм поиска первой пустой строки журнала, в которую будут вводиться данные, а для устранения второго - создать пользовательское диалоговое окно для ввода этих данных.
Автоматический поиск первой пустой строки журнала
В предыдущем примере ввод данных в конец списка журнала регистрации осуществлялся их вводом в самую последнюю строку предполагаемой к заполнению области списка, а затем с помощью операции сортировки по порядковому номеру содержимое этой строки перемещалось в конец журнала регистрации. Алгоритм создания такого алгоритма довольно прост и может использоваться в табличных базах данных, имеющих сравнительно небольшой объем, например, 200 строк. Но при необходимости произвести сортировку списка, состоящую из пяти - десяти тысяч строк, потери времени на выполнение этой операции будут существенны.
Выход из создавшейся ситуации заключается в создании алгоритма автоматического поиска первой незаполненной строки и активизации ячейки, в которую будет вводиться дата.
Усовершенствуем подпрограмму РасходныйОрдер. В связи с тем, что книга предназначена больше как учебное пособие, то не будем проводить редактирование подпрограммы, а создадим следующую РасходныйОрдерАвто.
Для создания новой подпрограммы прямо в Редакторе Microsoft Visual Basic выполните следующие действия:
- выделите все тело подпрограммы РасходныйОрдер и скопируйте в буфер обмена;
- с помощью комбинации клавиш Ctrl+End переместите курсор в конец окна программ и произведите вставку скопированного текста из буфера обмена;
- измените имя подпрограммы, дополнив ее текстом Авто.
Допишите в начале подпрограммы две строки кода VBA, приведенные на рисунке 3.
Рис.3. Код VBA поиска первой пустой строки и код активизации ячейки ввода даты для формирования записи расходного кассового ордера
Рассмотрим первую строку. Ее задача - присвоить переменной Row значение, которое равно количеству заполненных ячеек на активном рабочем листе в диапазонах В11:В200, F11:F200 увеличенному на значение "11".
Первая строка основана на применении функции СЧЕТЗ (рисунок 4), производящей подсчет непустых ячеек в указанном диапазоне.
Рис.4. Панель функции СЧЕТЗ
В нашем примере выбран диапазон ввода даты приходных и расходных кассовых ордеров. Значение 11 - количество строк с 1-й по 10-ю, в которые не вносятся записи о кассовых операциях, увеличенное на единицу.
Задача второй строки - выделить ячейку на пересечении номера определенной строки и шестого столбца. Номер строки задается переменной Row.
Переменные
Мы достаточно часто будем использовать переменные. Они применяются для управления данными при выполнении подпрограммы, что позволяет делать подпрограмму более читаемой, и, как правило, экономит время разработчика и время выполнения подпрограммы. По правилам, прежде чем использовать переменную в тексте программы, ее нужно описать. Описание производится до того, как эта переменная будет использоваться, как правило, в начале подпрограммы и заключается в присвоении типа данных, к которым принадлежит эта переменная. Это необходимо, для того чтобы в памяти было выделено место для хранения значения переменной во время выполнения программы. При описании переменной в большинстве случаев повышается скорость выполнения программы. Это выгодно, если на выполнение подпрограммы требуется какое-то значимое время.
называется время, в течение которого переменная может иметь значение. Значение переменной может меняться на протяжении ее времени жизни.
Но с другой стороны процедура описания переменной не является обязательной и в связи с тем, что разрабатываемые приложения преследуют другую цель, этому внимание не будет уделяться. Если же вам это будет интересно, то можно найти эту информацию в специализированной литературе по Visual Basic of Application.
Пользовательские диалоговые окна
В Excel есть возможность создания пользовательских диалоговых окон с помощью Редактора Visual Basic. Наиболее простой метод: воспользоваться готовыми средствами VBA для создания пользовательского диалогового окна показанного на рисунке 5.
Рис.5. Созданное пользовательское диалоговое окно Ввод данных для ввода суммы приходного кассового ордера в журнал кассовых документов
Такое диалоговое окно задается с помощью функции InputBox и служит для ввода одного значения при выполнении подпрограммы. Полное описание и синтаксис функции можно получить в справочной системе Visual Basic. Упрощенный вариант синтаксиса функции:
InputBox(prompt[, title] [, default])
- prompt - обязательный аргумент. Это строковое выражение, отображаемое как сообщение в диалоговом окне. Максимальная длина строки prompt составляет приблизительно 1024 символов и зависит от ширины используемых символов.
- title - необязательный аргумент. Строковое выражение, отображаемое в строке заголовка диалогового окна. Если этот аргумент опущен, в строку заголовка помещается имя приложения.
- default - необязательный аргумент. Строковое выражение, отображаемое в поле ввода как используемое по умолчанию, если пользователь не введет другую строку. Если этот аргумент опущен, поле ввода изображается пустым.
Именованные аргументы - это аргументы, имеющее имя, определенное в библиотеке объектов.
Строковое выражение - это любое выражение, значением которого является последовательность символов.
На рисунке 6 показан фрагмент подпрограммы с функцией InputBox для создания диалогового окна Ввод данных (рисунок 5), для ввода значения суммы расходного ордера:
InputBox("Введите сумму расходного документа", "Ввод данных")
Рис.6. Фрагмент макроса ПриходныйОрдерАвто с кодом ввода в активную ячейку суммы расходного ордера
Полный текст макроса ПриходныйОрдерАвто показан на рисунке 7.
Рис.7. Код VBA макроса ПриходныйОрдерАвто
Автоматизация заполнения данных расходного ордера
Подпрограмма РасходныйОрдерАвто создается аналогично. Макрос имеет незначительные отличия при относительных переходах при вводе данных.
Рис.8. Макрос РасходныйОрдерАвто
Расходный кассовый ордер
Расходный кассовый ордер находится на рабочем листе РасхОрдер (рисунок 9).
Рис.9. Рабочий лист РасхОрдер с расходным кассовым ордером
Расходный ордер занимает на рабочем листе диапазон ячеек А2:Н33.
В ячейку А4 (наименование предприятия) листа РасхОрдер введите ссылку на лист Журнал, в который введено наименование организации:
=КассаЖурнал!A1
Поиск искомых значений производится с помощью функции ВПР, по значению, вводимому в ячейку А14 (номер выводимого на экран расходного кассового ордера). Например, в ячейке В14 (ввод даты создания документа) формула:
=ВПР($A$14;КассаЖурнал!$E$11:$O$1005;2;ЛОЖЬ)
В остальных ячейках - D14, F14, В16, В18 и В30 аналогичные формулы поиска данных, вносимых в журнал регистрации, но изменяется только номер столбца в третьем аргументе функции.
Рабочий лист РасхОрдер дополнен модулем написания суммы прописью, вход которого находится в ячейке N19, в которую введена ссылка на ячейку F14 с суммой документа. Выход модуля - ячейка О19, на которую и производится ссылка из ячейки А19:
=O19
Для удобства просмотра документов созданы элементы управления - Счетчик и Полоса прокрутки. Создание и внедрение элемента управления Полоса прокрутки, ничем не отличается от элемента Счетчик, описанного на 232 шаге. Эти элементы управления помещают результат в ячейку А14, или изменяют нумерацию, по которой формируется соответствующий расходный ордер.
Приходный кассовый ордер
Для создания формы приходного кассового ордера примените копирование листа с расходным кассовым ордером. Для этого поместите указатель мыши на ярлык листа РасхОрдер и нажмите на левую кнопку мыши. Возле указателя мыши появится маленькое изображение листика. Нажмите клавишу Ctrl - на этом листике появится маленький крестик и, не отпуская клавишу Ctrl, перетяните мышью этот листик в сторону. Появится точная копия листа с расходным кассовым ордером с именем РасхОрдер(2). Присвойте листу имя ПрихОрдер.
Формы приходного и расходного практически одинаковы, с тем отличием, что приходный ордер состоит из двух частей - самого ордера и квитанции к нему, которые разделены линией отреза. Поэтому полученный рабочий лист ПрихОрдер нужно доработать. Для этого внесите изменения в название ордера и формулы.
В формулу, находящуюся в ячейке В14 (дата) внесите изменения начала области массива на листе КассаЖурнал, в которой будет происходить поиск информации. Для этого в строке формул замените английскую букву Е на А. Полученная формула в ячейке В14:
=ВПР($A$14;КассаЖурнал!$A$11:$O$1005;12;ЛОЖЬ)
Для одновременного изменения во всех формулах воспользуйтесь диалоговым окном Заменить (рисунок 9), которое вызывается командой Правка | Заменить или комбинацией клавиш Ctrl+Н. Последовательность выполнения операции:
- выделите рабочую область листа, в которой должны быть проведены замены;
- вызовите диалоговое окно Заменить;
- в поле Что введите с клавиатуры текст $E$, а в поле Заменить на - $A$;
- нажмите на кнопку Заменить все.
Рис.10. Диалоговое окно Заменить
Для формирования текста суммы прописью также используется модуль написания суммы прописью, входом которого является ячейка N18, а выходом - О18.
Для создания области ячеек с боковыми линиями и текстом Линия отреза:
- выделите область ячеек І2:І26;
- выполните команду Формат | Ячейки и перейдите на вкладку Выравнивание диалогового окна Формат ячеек;
- отметьте опции Переносить по словам и Объединение ячеек;
- в области Выравнивание укажите выравнивание По центру в полях По горизонтали и По вертикали;
- в области Ориентация с помощью счетчика градусов укажите значение 90;
- перейдите на вкладку Граница и с помощью кнопок в области Отдельные выберите боковое обрамление ячеек линиями.
Рис.11. Рабочий лист ПрихОрдер
Квитанция к приходному кассовому ордеру
В области J2:L26 создана квитанция к приходному кассовому ордеру. При форматировании этой облати воспользуйтесь возможностями Excel: Переносить по словам и Объединение ячеек диалогового окна Формат ячеек. Например, в области ячеек формирующую текст Основание задействованы ячейки J14:L17, которым после объединения ячеек присвоен адрес самой верхней левой ячейки J14. Это позволит разместить всю текстовую надпись с переносом по словам.
Если в области ячеек Квитанции находятся однотипные данные с левой частью ордера, то на них вводятся ссылки. Например, в ячейке J4 (наименование предприятия) ссылка:
=A4
Написание даты прописью осуществляется с помощью соответствующего модуля, расположенного в области N21:Р21.
Рабочая область листа с приходным кассовым ордером отличается от расходного ордера и занимает диапазон ячеек А2:L26. Поэтому выделите эту область выполните команду Файл | Область печати | Задать, что обеспечит вывод на печать только диапазона, на котором расположен приходный кассовый ордер.
Для того чтобы придать полностью пользовательский вид форме рабочих листов с расположенными на них ордерами, желательно убрать на экране монитора линии на рабочем листе, обозначающие границы ячеек электронной таблицы. Для этого выполните команду Сервис | Параметры и на вкладке Вид диалогового окна Параметры уберите в области Параметры окна галочку с опции Сетка.
Полный текст этого приложения можно взять здесь.
Со следующего шага мы начнем рассматривать автоматизацию формирования текстовых документов на примере типового договора купли-продажи.
