Шаг 239.
VBA в MSExcel. Автоматизация рабочего процесса по формированию и учету кассовых документов. Дальнейшая автоматизация заполнения журнала

    На этом шаге мы рассмотрим некоторые особенности работы с кодом VBA.

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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


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

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

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

  =СЕГОДНЯ()


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

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

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


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

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

  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).


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

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

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

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

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

  Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ 
    SkipBlanks:=False, Transpose:=False
PasteSpecial - метод диалогового окна Специальная вставка.

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

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

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

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

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

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

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


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

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

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


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

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


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

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

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

    Как правило, при механической записи макросов, 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 можно производить только с клавиатуры, а можно и с помощью диалогового окна Просмотр объектов (рисунок 9), которое активизируется командой Вид | Просмотр объектов или нажатием на клавишу F2.


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

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

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

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


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

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

Примечания

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

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

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

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

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

    На следующем шаге мы усовершенствуем созданное приложение.




Предыдущий шаг Содержание Следующий шаг