Исследование кода VBA при записи макроса ВставкаСтроки

После записи макроса не откладывайте на потом исследование записанного кода VBA, а открыв Редактор Visual Basic просмотрите записанные действия и при необходимости отредактируйте код VBA. Механически сгенерированный код показан на рис. 22.

Рис.21. Рабочий лист КомандировочноеУдостоверение с встроенными элементами управления

Между ключевыми словами начала и окончания выполнения подпрограммы находится восемь строк кода. По очереди рассмотрим каждую строку. Первая строка: Selection.Copy, в которой:

Selection - это неявная ссылка на свойство объекта Worksheet (рабочий лист) и означает выделенный (объектом выделения может быть ячейка, диапазон или несколько несмежных диапазонов ячеек, строка или столбец) диапазон.

Copy - название метода и означает команду копировать выделенный объект.

Рис.22. Механически сгенерированный код VBA

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

Следующая строка: Rows("14:14").Select, в которой Rows это объект - строки, в скобках указано какие именно строки, а Select это метод - выделить. Согласно заданным условиям разрабатываемого приложения это должна быть 200-я строка, поэтому замените значение 14 на значение 200.

Третья строка: ActiveSheet.Paste означает что операция вставки (Paste) выполняется на активном рабочем листе (ActiveSheet). В данном случае ActiveSheet - свойство объекта Workbook и означает, что у этого объекта есть такой текущий объект, как активный рабочий лист.

Обратите внимание на три рассмотренные строки в совокупности. Они предназначены для выполнения одной операции - копирования выделенной строки с целью вставки ее содержимого в другую строку. Но ту же операцию может выполнить всего одна строка, в которой в качестве аргумента метода Copy указано место вставки содержимого скопированного диапазона: Selection.Copy (Rows("200:200")) и создается она всего четырьмя действиями при редактировании сгенерированного механически кода:

• установите курсор в конце первой строки и введите открывающуюся скобку;
• нажмите клавишу Delete, и текст второй строки кода переместится в конец первой строки;
• введите закрывающуюся скобку после указания диапазона;
• с помощью клавиши Delete удалите дальнейший ненужный код.

При подобном редактировании кода макроса нет необходимости даже соблюдать интервал (пробелы) между операторами Copy и Rows. После окончания редактирования, установка интервалов между операторами при отсутствии ошибок выполняется Редактором Microsoft Visual Basic автоматически.

Следующая строка: Rows("4:14").Select выделяет диапазон строк для сортировки, в которой значение 14 замените на 200.

Строку: Range("A14").Activate Excel генерирует автоматически. Она указывает на то что прежде чем выполнять сортировку необходимо было указать по какой области будет происходить сортировка. Удаление этой строки никак не повлияет на выполнение макроса.

Следующую строку Excel генерирует также самостоятельно: Application.CutCopyMode = False и она носит в данном макросе больше информационный характер. Строка указывает что метод копирования (вырезания) CutCopyMode закончил свое действие и буфер обмена не содержит никаких данных, потому что ему присвоено значение False (Ложь). Удаление этой строки не повлечет никаких изменений в выполнении записанного макроса.

Следующая выполняемая операция - сортировка состоит из двух строк: Selection.Sort Key1:=Range("A14"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

В одной строке Редактора может находиться до 1024 символов. Но если все 1024 символа разместить в одной строке, то это затруднит ее чтение. Для переноса кода строки в следующую, используется символ подчеркивания (_), перед которым обязательно должен находиться пробел.

Excel позволяет выполнять операцию сортировки с использованием от одного до трех ключей и для сортировки диапазона с помощью VBA используется метод - Sort. В этой команде, выполняющей операцию сортировки, находятся следующие операторы:

• Selection - выделенная область рабочего листа, значения которой будут сортироваться;
• Sort - метод сортировка;
• Key1:=Range("A14") - произвести сортировку выделенного диапазона по столбцу А, где аргумент Key1- указывает; что это первый ключ сортировки. Как вы знаете Excel позволяет производить последнюю сортировку выделенного диапазона по трем полям с помощью диалогового окна Сортировка диапазона, которое вызывается с помощью команды Данные/Сортировка;
• Order1 - указывает на порядок сортировки диапазона по первому ключу. Аргумент xlAscending - произвести сортировку по возрастанию. При сортировке по убыванию присваивается значение xlDescending;
• Header - аргумент с помощью которого определяется: сортировать ли первую строку выделенного диапазона. При механической записи макроса этому аргументу присвоено значение xlGuess. В этом случае Excel самостоятельно определяет, является ли первая строка строкой заголовка и нужно ли ее сортировать. Для того чтобы указать, что первая строка является строкой заголовка, и ее сортировать не требуется, присвойте этому аргументу значение xlYes. Если присвоить значение xlNo, то в этом случае первая строка сортируется;
• OrderCustom - целое число, которое указывает порядок сортировки указанный пользователем на вкладке Списки диалогового окна Параметры, выводимое командой Сервис/Параметры. По умолчанию присваивается значение 1;
• MatchCase - указывает различать (True) или нет (False) при сортировке заглавные и строчные буквы;
• Orientation - указывает как проводить сортировку: по строкам (xlTopToBottom) или столбцам (xlLeftToRight).

Как видно из записанного кода для присвоения методам значений применяются знаки:= (двоеточие и равно).

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

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

Итоги

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

Показано создание простейших формул поиска данных, а также:

• создание и внедрение элементов управления Счетчик и Список, для автоматизации выбора необходимого документа из базы данных;
• автоматизация процесса вывода документа на печать;
• основы механической записи макроса и последующее редактирование сгенерированного кода VBA;
• введение в Редактор Microsoft Visual Basic;
• приемы запуска макросов из Excel на выполнение;
• создание элемента управления Кнопка и назначение ей выполняемого макроса;
• автоматический ввод содержимого выделенной. строки в конец списка.

Рис.23. Отредактированный код VBA подпрограммы вставки в конец списка содержимого выделенной строки

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