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

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

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

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

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

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

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

Изменение параметров 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. Панель функции СЕГОДНЯ

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

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

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

Рис.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

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

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

установите курсор в любую пустую ячейку, и выполните команду Правка | Копировать (или комбинация клавиш 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 (рисунок 8).

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

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

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

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

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

Для соединения макросов в один примените метод копирования. Для этого в окне Редактора 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 тех элементов подпрограмм, которые невозможно сгенерировать при помощи записи макросов, а они вводятся при последующем редактировании этих подпрограмм. Опишем на примере написания кода перемещения по рабочему листу при записи относительной ссылки. Соблюдайте следующую последовательность ввода кода:

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

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

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

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

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

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

Примечания

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

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

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

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

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

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

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

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

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