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

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

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

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

Недостатки ручного заполнения этих первичных документов те же, что и описанные прежде при формировании командировочных удостоверений. Главный же — ручное заполнение ордеров, что занимает значительное время. Кроме того, эти документы, как и все, что, связано с деньгами, не терпят ошибок, при наличии которых они могут быть признаны недействительными. Или же внесенные ошибки могут нанести ущерб предприятию А такими ошибками могут быть неправильно указанная дата, сумма в числовом формате или прописью (встречается несоответствие этих сумм), фамилия, имя или отчество, человека, на которого они выписаны и его паспортные данные, или реквизиты предприятия.

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

Техническое задание

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

1. Ордера должны автоматически заполняться в зависимости от указанного их порядкового номера в соответствии с данными, введенными в журнал регистрации этих документов.
2. В зависимости от введенного в журнал регистрации числового значения денежной суммы, в ордерах должна автоматически генерироваться эта сумма прописью.
3. На квитанции к приходному кассовому ордеру должна автоматически формироваться дата прописью.
4. Заполнение журнала регистрации расходных и приходных кассовых ордеров должно производиться с минимальными потерями рабочего времени и минимальным количеством возможных ошибок, вводимых при его заполнении.
5. В журнале регистрации расходным и приходным кассовым ордерам должен автоматически присваиваться их порядковый номер.
6. Должна присутствовать возможность автоматического контроля оставшейся в кассе суммы денежных средств после формировании каждого ордера.

Состав приложения

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

Исходя из технического задания, рабочая книга с присвоенным именем Касса будет содержать три рабочих листа:

• КассаЖурнал с журналом регистрации приходных и расходных кассовых ордеров;
• РасхОрдер с формой расходного кассового ордера;
• ПрихОрдер с формой приходного кассового ордера.

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

На рабочем листе КассаЖурнал находится табличная база данных для одновременной регистрации приходных и расходных кассовых документов (рис. 5.1.). По горизонтали рабочая область всей таблицы — столбцы А:N, по вертикали — с 1-й до 200-ой строки.

Рис. 5.1. Рабочий лист КассаЖурнал для регистрации приходных и расходных кассовых ордеров

Рабочая область листа КассаЖурнал разделена на две части:

• информационная (строки 1:7);
• непосредственно табличная база данных (строка 8 и ниже).

Область табличной базы данных журнала регистрации кассовых документов

Область ввода в табличную базу данных можно условно разделить на четыре составляющих:

• область заполнения приходного ордера (столбцы А:D);
• область заполнения расходного ордера (столбцы Е:Н);
• область для ввода данных бухгалтерского учета (столбцы I:К);
• область, содержащую справочные данные (столбцы L:N).

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

Присвоение порядкового номера

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

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

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

Для выполнения всех этих условий создайте довольно простую формулу с использованием функций ЕСЛИ, ЕПУСТО и МАКС. В ячейке Е15, которая формирует порядковый номер расходных кассовых ордеров, формула:

=ЕСЛИ(ЕПУСТО(B15);ЕСЛИ(ЕПУСТО(F15);0;МАКС(E$11:E14)+1);0)

Функция ЕПУСТО в первом аргументе первой функции ЕСЛИ проверяет наличие даты в ячейке В15, которая относится к области ввода дат приходных кассовых ордеров. И если ячейка В15 заполнена, то тогда функция ЕСЛИ возвращает значение 0. Если ячейка В15 пуста, то формула переходит к решению второго аргумента первой функции ЕСЛИ.

Следующая функция ЕПУСТО в первом аргументе второй функции ЕСЛИ проверяет наличие какого-либо значения введенного в ячейку F15 (дата формирования расходного кассового ордера) и если дата отсутствует, то второй аргумент функции ЕСЛИ возвращает значение 0. Если же дата присутствует (или любое другое значение), то функция МАКС в третьем аргументе второй функции ЕСЛИ ищет максимальное значение порядкового номера в ячейках столбца Е расположенных выше и к этому значению добавляет единицу.

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

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

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

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

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

Информационная область

В ячейку А1 вводится наименование организации.

Контроль остатка денежных средств в кассе

В ячейку А2 введите формулу:

=СУММ(C11:C200)-СУММ(G11:G200).

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

Контроль лимита кассы

Если на предприятии установлен лимит кассы, то тогда размер этой суммы введите в ячейку L2 (или любую другую по усмотрению).

Для того, что видеть, не превышается ли лимит кассы, введите в ячейку F1 формулу, основанную на функции ЕСЛИ:

=ЕСЛИ(A2<L2;"";"Превышение лимита кассы!")

которая сравнивает значение лимита кассы со значением остатка по кассе. И если лимит превышен, то выдается предупреждающая надпись: Превышение лимита кассы!. В противном случае никакого значения эта ячейка не возвращает.

Применение методов дальнейшей автоматизации заполнения журнала регистрации

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

Для этого разработайте последовательность ввода данных. Пример будет описан на примере заполнения строки 16 по вводу данных расходного ордера №4.

Главное условие — автоматизация этого процесса должна уменьшить трудоемкость операции ввода данных и уменьшить возможность ввода ошибки. Далее предполагаем, что заполнение журнала и формирование ордеров происходит не задним числом, а день в день.

Приемы автоматизации заполнения журнала кассовых документов

Автоматизация заполнения журнала кассовых документов будет разделена на несколько этапов:

• запись макросов ввода отдельных формул и последующая замена формул на определенные ими значения;
• создание кода VBA поиска первой пустой строки в списке и перемещения по этой строке;
• соединение всего кода VBA созданных макросов в одну подпрограмму, выполняющую всю последовательность действий в автоматическом режиме.

Изменение параметров Excel перед записью макросов

Завершить ввод данных в ячейки, если это не редактирование их содержимого, можно нажатием на самые различные клавиши: клавиши перемещения на одну ячейку (вниз, вверх, влево, вправо), Home, End, Page Up или Page Down. Самый же классический способ завершения ввода данных в Excel — нажатие на клавишу Enter, после чего, как правило, табличный курсор перемещается на ячейку ниже. И это довольно удобно. Большинство пользователей полагают, что это неизменяемое свойство Excel. На самом же деле этот параметр устанавливается при инсталляции Excel по умолчанию и при желании может быть изменен.

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

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

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

Поэтому, прежде чем записывать макросы, связанные с операциями ввода данных, с помощью команды Сервис/Параметры откройте диалоговое окно Параметры и на вкладке Правка (рис. 5.2.) удалите флажок с опции Переход к другой ячейке после ввода. Иначе при записи макроса, перемещение табличного курсора после нажатия на клавишу [Enter] на ячейку, по заданному этой опцией в направлении, выбранном в раскрывающемся списке В направлении, будет отражено в сгенерированном коде макроса.

Рис. 5.2. Диалоговое окно Параметры вкладке Правка с открытым раскрывающимся списком В направлении при активизированной опции Переход к другой ячейке после ввода

Запись кода VBA при вводе формул в процессе записи макроса

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

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

Автоматизация ввода текущей даты

Согласно правил заполнения журнала первым действием должен быть ввод даты проведения этой операции. Если операция заполнения журнала производится в день ее выполнения, то для автоматического ввода даты достаточно ввести в ячейку F16 функцию ввода текущей даты. Для этого используется функция СЕГОДНЯ (рис. 5.4.), которую можно ввести либо с помощью кнопки Мастер функций на стандартной панели инструментов, либо с клавиатуры:

=СЕГОДНЯ()

Функция СЕГОДНЯ не имеет аргументов и возвращает текущую дату.

Рис. 5.4. Панель функции СЕГОДНЯ

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

• введите в ячейку В16 функцию СЕГОДНЯ и не перемещайте табличный курсор;
• выполните процедуру начала записи макроса, которому присвойте имя РасходныйОрдер и при необходимости введите его описание;
• запись макроса заключается в последовательном нажатии на клавишу F2 (редактирование содержимого ячейки) и клавишу Enter;
• произведите остановку записи макроса.

Рис. 5.5. Окно программы с кодом макроса РасходныйОрдер

При записи макроса РасходныйОрдер режим перехода на ячейку вниз отключен не был (рис. 5.2.). Поэтому на рис. 5.5. видите вторую строку кода:

Range("B17").Select

которую удалите при редактировании. В этой строке находится объект 4-го уровня иерархии — Range. Этот объект используется для ссылок на ячейку или диапазон ячеек.

Первая строка:

ActiveCell.FormulaR1C1 = "=TODAY()"

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

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

Запись макросов Макрос2 и Макрос3 осуществляется аналогично — сначала вводятся формулы в ячейку А16:

=ЕСЛИ(ЕПУСТО(IT17);ЕСЛИ(ЕПУСТО(B17);0;МАКС(A$11:A16)+1);0)

и Е16:

=ЕСЛИ(ЕПУСТО(J17);ЕСЛИ(ЕПУСТО(F17);0;МАКС(E$11:E16)+1);0)

после чего осуществляется запись кода этих формул, как и в предыдущем макросе (рис. 5.6.).

Рис. 5.6. Окно программы с кодом макросов Макрос2, Макрос3 и Макрос4

Следующий, четвертый макрос, предназначен для замены формул, вычисленными значениями.

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

• установите курсор в любую пустую ячейку, и выполните команду Правка/Копировать (или комбинация клавиш [Ctrl+С]);
• выполните команду Правка/Специальная вставка и в появившемся диалоговом окне Специальная вставка активизируйте переключатель Значения, после чего нажмите кнопку ОК;
• остановите запись макроса.

Метод Специальная вставка

Проанализируем записанный код VBA при вставке из буфера обмена скопированной области через диалоговое окно Специальная вставка:

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

PasteSpecial — метод диалоговое окно Специальная вставка.

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

Аргументу Paste (Вставить) могут присваиваться следующие значения:

• xlAll — все;
• xlFormulas — формулы;
• xlValues — значения;
• xlFormats — форматы.

Аргументу Operation (операция) могут присваиваться значения:

• xlNone — нет;
• xlAdd — сложить;
• xlSubtract — вычесть;
• xlMultiply — умножить;
• xlDivide — разделить.

Аргументам SkipBlanks (пропускать пустые ячейки) и Transpose (транспонировать) могут присваиваться два значения False (Ложь) и True (Истина).

В связи с тем, что задача Макрос4 произвести только вставку значений, удалите ненужный код VBA (рис. 5.9.).

Соединение макросов

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

Рис. 5.7. Последовательность действий полного макроса РасходныйОрдер

Следует заметить, что написанные макросы выполняют действия 1, 3, 5 и 7, которые только выполняют определенные операции. Действия 2, 4, 6 и 8 заключаются в переходе к ячейке или диапазону ячеек, с которыми будут производиться эти операции. Поэтому операцию соединения разделим на две части:

• перенос написанных макросов Макрос2, Макрос3 и Макрос4 в один макрос РасходныйОрдер в той последовательности, в которой они записывались;
• редактирование полученного макроса РасходныйОрдер и добавления процедур с целью последовательного выполнения операций показанных на рис. 5.7;
• ввод примечаний.

Для соединения макросов в один примените метод копирования. Для этого в окне программы Редактора Visual Basic выделите область от конца последнего символа вверх кода VBA, включая первый встречающийся знак апострофа, как это показано на рис. 5.8.

Рис. 5.8. Выделение фрагмента макроса для копирования и вставки в другой макрос

После соединения всех макросов получился макрос, показанный на рис. 5.9.

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

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

Абсолютная и относительная ссылки при выполнении макроса

Как правило, при механической записи макросов, Excel выполняет абсолютную запись, то есть сохраняет точные адреса ячеек при их активизации. В свою очередь "точный" адрес ячейки ведет свой отсчет от левого верхнего угла рабочего листа. Например, адрес ячейки В3 — (3,2) или пересечение третьей строки и второго столбца.

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

После выполнения записанной в Макрос1 операции с активной ячейкой F16, на которую предварительно устанавливается табличный курсор перед запуском макроса на выполнение, необходимо перейти на ячейку Е16 для проведения операции записанной в Макрос2. Для этого задайте относительную ссылку перехода на эту ячейку. При вводе метода Select записанная строка означает команду — передвинуться от активной ячейки на 1 столбец влево и на 0 строк вниз (вверх) и выделить (активизировать) ее. Код VBA операции записывается так:

ActiveCell.Offset(0, -1).Select

в которой знак минус указывает, что необходимо передвинуться на один столбец влево, а значение 0 — передвинуться на ноль значений вниз и считать эту ячейку активной (ActiveCell) и выделенной (Select). Эту строку введите перед фрагментом, скопированным из Макрос2.

Для выполнения фрагмента из Макрос3 необходимо передвинуться на 4 столбца влево:

ActiveCell.Offset(0, -4).Select

А перед выполнением фрагмента из Макрос4 необходимо не только добавить относительный переход, но и выделить диапазон следующим образом:

ActiveCell.Offset(0, 0).Range("A1:F1").Select

Если макрос записывается в относительном режиме, то предполагается, что ячейка, в которой находится табличный курсор, имеет адрес А1, а указанный адрес ячейки (или диапазона) после относительного перехода, принимается по отношению к этой ячейке А1, поэтому если указать адрес А1:F1, то будет выделено шесть ячеек вправо, включая и активную ячейку, в которой находился табличный курсор. Или в нашем примере — ячейки А16:F16.

И последний элемент выполнения подпрограммы — переход на ячейку G16 для ввода суммы приходного ордера. Следовательно, надо задать команду перехода от активной ячейки на 6 ячеек вправо:

ActiveCell.Offset(0, 6).Select

Методы ввода кода VBA

Запись кода VBA можно производить только с клавиатуры, а можно и с помощью диалогового окна Просмотр объектов (рис. 5.10.), которое активизируется командой Вид/просмотр объектов или нажатием на клавишу F2.

Рис. 5.10. Приложение Редактор Microsoft Visual Basic с открытым диалоговым окном Просмотр объектов в правом верхнем углу

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

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

• в раскрывающемся списке Проект/Библиотека выберите библиотеку Excel;
• в окне Компонент выделите объект ActiveCell и скопируйте в буфер обмена;
• перейдите в окно программы и, установив курсор в теле подпрограммы, произведите вставку скопированного;
• после ввода команды точка появится список свойств/методов, которые могут быть использованы для дальнейшего написания кода для этого объекта. С помощью полосы прокрутки найдите нужное свойство/метод. Поиск можно ускорить при вводе после точки первых символов кода. Для ввода кода свойства/метода в подпрограмму дважды клацните по нему правой кнопкой мыши и т.д.

Раскрывающийся список свойств/методов появляется при каждом вводе команды точка или же:

• при выполнении команды Правка/Список свойств/методов;
• при помощи контекстного меню;
• комбинации клавиш [Ctrl+J];
• нажатии на кнопку Список свойств/методов на панели инструментов Правка (рис. 5.11.).

Удобно при вводе текста кода VBA использовать возможность завершения его ввода при:

• нажатии на кнопку Завершить слово на панели инструментов Правка (рис. 5.11.);
• выполнении команды Правка/Завершить слово;
• используя контекстное меню;
• комбинации клавиш [Ctrl+Space].

Примечания

И последний элемент редактирования подпрограммы — добавление примечаний для описания действий выполняемых подпрограммой. Примечания не являются командами и при выполнении макроса игнорируются.

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

поместите курсор в начало строки и введите знак апострофа с клавиатуры; поместите курсор на строку или выделите блок строк и нажмите на кнопку Закомментировать блок на панели инструментов Правка (рис. 5.11.).

Для того чтобы убрать знак комментария удалите знак апострофа, используя клавиши Delete или Backspace, или выделив строку (блок строк) нажмите кнопку Раскомментировать блок, на панели инструментов Правка.

Панель инструментов Правка

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

• Список свойств/методов — выводит раскрывающийся список доступных для объекта свойств и методов;
• Список констант — выводит раскрывающийся список доступных констант для введенного свойства;
• Сведения — выводит синтаксис выбранной в окне модуля переменной, функции, инструкции, процедуры или метода;
• Параметры — при установке курсора на функции или инструкции выводит в окне модуля сведения об их параметрах;
• Завершить слово — если введено достаточно символов для распознавания текста кода, то завершает его ввод;
• Увеличить отступ — смещает все выделенные строки в следующую позицию табуляции;
• Уменьшить отступ — смещает все выделенные строки в предыдущую позицию табуляции;
• Точка останова — производит установку или удаление строки, в которой находится курсор;
• Закомментировать блок — добавляет в начале каждой строки символ комментария (') всего выделенного текста подпрограммы;
• Раскомментировать блок — удаляет в начале каждой строки символ комментария (') всего выделенного текста подпрограммы;
• Закладка — снимает или удаляет символ закладки строки, в которой находится курсор;
• Следующая закладка — перемещает курсор к строке следующей закладки;
• Предыдущая закладка — перемещает курсор к строке предыдущей закладки;
• Снять все закладки — удаляет все установленные закладки.

Рис. 5.11. Панель инструментов Правка

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

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

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

Создание макроса заполнения записи приходного ордера в журнал регистрации кассовых документов

Для создания аналогичного макроса для заполнения области приходного ордера достаточно выделить область макроса РасходныйОрдер в окне программы, скопировать выделенный фрагмент в буфер обмена, поместив курсор в конец строки End Sub и произвести вставку из буфера обмена. После чего переименовать новый макрос в ПриходныйОрдер и отредактировать макрос (рис. 5.13.).

Рис. 5.13. Макрос ПриходныйОрдер формирования записи приходного ордера в журнал регистрации кассовых документов

Усовершенствование созданного приложения

У созданных процедур, оказывающих помощь при заполнении журнала кассовых документов, есть два существенных недостатка:

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

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

Автоматический поиск первой пустой строки журнала

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

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

Усовершенствуйте подпрограмму РасходныйОрдер. В связи с тем, что книга предназначена больше как учебное пособие, то не будем проводить редактирование подпрограммы, а создадим следующую РасходныйОрдерАвто.

Для создания новой подпрограммы прямо в Редакторе Microsoft Visual Basic выполните следующие действия:

• выделите все тело подпрограммы РасходныйОрдер и скопируйте в буфер обмена;
• с помощью комбинации клавиш [Ctrl+End] переместите курсор в конец окна программ и произведите вставку скопированного текста из буфера обмена;
• измените имя подпрограммы, дополнив ее текстом Авто.

Допишите в начале подпрограммы две строки кода VBA, приведенные на рис. 5.14.

Рис. 5.14. Код VBA поиска первой пустой строки и код активизации ячейки ввода даты для формирования записи расходного кассового ордера

Рассмотрим первую строку. Ее задача — присвоить переменной Row значение, которое равно количеству заполненных ячеек на активном рабочем листе в диапазонах В11:В200, F11:F200 увеличенному на значение "11".

Первая строка основана на применении функции СЧЕТЗ (рис. 5.15), производящей подсчет непустых ячеек в указанном диапазоне. В нашем примере выбран диапазон ввода даты приходных и расходных кассовых ордеров. Значение 11 — количество строк с 1-й по 10-ю, в которые не вносятся записи о кассовых операциях, увеличенное на единицу.

Задача второй строки — выделить ячейку на пересечении номера определенной строки и шестого столбца. Номер строки задается переменной Row.

Рис. 5.15. Панель функции СЧЕТЗ

Переменные

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

Временем жизни переменной называется время, в течение которого переменная может иметь значение. Значение переменной может меняться на протяжении ее времени жизни.

Но с другой стороны процедура описания переменной не является обязательной и в связи с тем, что разрабатываемые приложения в этой книге преследуют другую цель, этому внимание не будет уделяться. Если же читателю это будет интересно, то он найдет эту информацию в специализированной литературе по Visual Basic of Application.

Пользовательские диалоговые окна

В Excel есть возможность создания пользовательских диалоговых окон с помощью Редактора Visual Basic. Наиболее простой метод воспользоваться готовыми средствами VBA для создания пользовательского диалогового окна показанного на рис. 5.16.

Рис. 5.16. Созданное пользовательское диалоговое окно Ввод данных для ввода суммы приходного кассового ордера в журнал кассовых документов

Такое диалоговое окно задается с помощью функции InputBox и служит для ввода одного значения при выполнении подпрограммы. Полное описание и синтаксис функции можно получить в справочной системе Visual Basic. Упрощенный же вариант синтаксиса функции:

InputBox(prompt[, title] [, default])

содержит следующие именованные аргументы:

• prompt - обязательный аргумент. Это строковое выражение, отображаемое как сообщение в диалоговом окне. Максимальная длина строки prompt составляет приблизительно 1024 символов и зависит от ширины используемых символов.
• title - необязательный аргумент. Строковое выражение, отображаемое в строке заголовка диалогового окна. Если этот аргумент опущен, в строку заголовка помещается имя приложения.
• default - необязательный аргумент. Строковое выражение, отображаемое в поле ввода как используемое по умолчанию, если пользователь не введет другую строку. Если этот аргумент опущен, поле ввода изображается пустым.

Именованные аргументы — это аргументы, имеющее имя, определенное в библиотеке объектов.

Строковое выражение — это любое выражение, значением которого является последовательность символов.

На рис. 5.17. показан фрагмент подпрограммы с функцией InputBox для создания диалогового окна Ввод данных (рис. 5.16.), для ввода значения суммы расходного ордера:

InputBox("Введите сумму расходного документа", "Ввод данных")

которое содержит обязательный аргумент prompt - Введите сумму расходного документа, и необязательный title - Ввод данных. Аргумент default опущен, но его удобно использовать, когда вводимое значение заранее известно. Значение (Value), введенное в поле ввода при нажатии на кнопку ОК будет введено в активную ячейку, при нажатии на кнопку Отмена — не введено.

Рис. 5.17. Фрагмент макроса ПриходныйОрдерАвто с кодом ввода в активную ячейку суммы расходного ордера

Полный текст макроса ПриходныйОрдерАвто показан на рис. 5.18.

Рис. 5.18. Код VBA макроса ПриходныйОрдерАвто

Автоматизация заполнения данных расходного ордера

Подпрограмма РасходныйОрдерАвто создается аналогично. Макрос имеет незначительные отличия при относительных переходах при вводе данных.

Рис. 5.19. Макрос РасходныйОрдерАвто

Расходный кассовый ордер

Расходный кассовый ордер находится на рабочем листе РасхОрдер (рис. 5.20.).

Рис. 5.20. Рабочий лист РасхОрдер с расходным кассовым ордером.

Расходный ордер занимает на рабочем листе диапазон ячеек А2:Н33.

В ячейку А4 (наименование предприятия) листа РасхОрдер введите ссылку на лист Журнал, в который введено наименование организации:

=КассаЖурнал!A1

Поиск искомых значений производится с помощью функции ВПР, по значению, вводимому в ячейку А14 (номер выводимого на экран расходного кассового ордера). Например, в ячейке В14 (ввод даты создания документа) формула:

=ВПР($A$14;КассаЖурнал!$E$11:$O$1005;2;ЛОЖЬ)

В остальных ячейках — D14, F14, В16, В18 и В30 аналогичные формулы поиска данных, вносимых в журнал регистрации, но изменяется только номер столбца в третьем аргументе функции.

Рабочий лист РасхОрдер дополнен модулем написания суммы прописью, вход которого находится в ячейке N19, в которую введена ссылка на ячейку F14 с суммой документа. Выход модуля — ячейка О19, на которую и производится ссылка из ячейки А19:

=O19

Для удобства просмотра документов созданы элементы управления — Счетчик и Полоса прокрутки. Создание и внедрение элемента управления Полоса прокрутки, ничем не отличается от элемента Счетчик (описано в главе 4). Эти элементы управления помещают результат в ячейку А14, или изменяют нумерацию, по которой формируется соответствующий расходный ордер.

Приходный кассовый ордер

Для создания формы приходного кассового ордера примените копирование листа с расходным кассовым ордером. Для этого поместите указатель мыши на ярлык листа РасхОрдер и нажмите на левую кнопку мыши. Возле указателя мыши появится маленькое изображение листика. Нажмите клавишу Ctrl — на этом листике появится маленький крестик и, не отпуская клавишу Ctrl, перетяните мышью этот листик в сторону. Появится точная копия листа с расходным кассовым ордером с именем РасхОрдер(2). Присвойте листу имя ПрихОрдер.

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

В формулу, находящуюся в ячейке В14 (дата) внесите изменения начала области массива на листе КассаЖурнал, в которой будет происходить поиск информации. Для этого в строке формул замените английскую букву Е на А. Полученная формула в ячейке В14:

=ВПР($A$14;КассаЖурнал!$A$11:$O$1005;12;ЛОЖЬ)

Для одновременного изменения во всех формулах воспользуйтесь диалоговым окном Заменить (рис. 5.20.), которое вызывается командой Правка/Заменить или комбинацией клавиш [Ctrl+Н]. Последовательность выполнения операции:

• выделите рабочую область листа, в которой должны быть проведены замены;
• вызовите диалоговой окно Заменить;
• в поле Что введите с клавиатуры текст $E$, а в поле Заменить на — $A$;
• нажмите на кнопку Заменить все.

Рис. 5.21. Диалоговое окно Заменить

Для формирования текста суммы прописью также используется модуль написания суммы прописью, входом которого является ячейка N18, а выходом — О18.

Для создания области ячеек с боковыми линиями и текстом — Линия отреза:

• выделите область ячеек І2:І26;
• выполните команду Формат/Ячейки и перейдите на вкладку Выравнивание диалогового окна Формат ячеек;
• отметьте опции Переносить по словам и Объединение ячеек;
• в области Выравнивание укажите выравнивание По центру в полях По горизонтали и По вертикали;
• в области Ориентация с помощью счетчика Градусов укажите значение 90;
• перейдите на вкладку Граница и с помощью кнопок в области Отдельные выберите боковое обрамление ячеек линиями.

Рис. 5.22. Рабочий лист ПрихОрдер

Квитанция к приходному кассовому ордеру

В области J2:L26 создана квитанция к приходному кассовому ордеру. При форматировании этой области воспользуйтесь возможностями Excel — Переносить по словам и Объединение ячеек диалогового окна Формат ячеек. Например, в области ячеек формирующую текст Основание задействованы ячейки J14:L17, которым после объединения ячеек присвоен адрес самой верхней левой ячейки J14. Это позволит разместить всю текстовую надпись с переносом по словам.

Если в области ячеек "Квитанции" находятся однотипные данные с левой частью ордера, то на них вводятся ссылки. Например, в ячейке J4 (наименование предприятия) ссылка:

=A4

Написание даты прописью осуществляется с помощью соответствующего модуля, расположенного в области N21:Р21.

Рабочая область листа с приходным кассовым ордером отличается от расходного ордера и занимает диапазон ячеек А2:L26. Поэтому выделите эту область выполните команду Файл/Область печати/Задать что обеспечит вывод на печать только диапазона, на котором расположен приходный кассовый ордер.

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

Итоги

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

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

Следующий элемент автоматизации — выделение ячейки и последующий автоматический ввод даты и номера при нажатии на кнопку, которой назначен макрос. В свою очередь описано создание одного макроса, путем копирования кода VBA и вставки из других макросов, которые выполняли только одну операцию, например, операцию ввода созданной ранее формулы. Далее этот макрос самостоятельно вводил данные в различные ячейки, для чего понадобилось изучение абсолютной и относительной формы записи кода. Для эффективного ввода кода изучены отдельные элементы Редактора Microsoft Visual Basic, например, окно Просмотр объектов и ввод примечаний в текст подпрограммы.

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