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

Запись макросов Макрос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. Полученный макрос ввода записи в журнал регистрации кассовых документов