Вставка содержимого выбранной строки в конец списка

Вернемся на рабочий лист ЖурналРегистрацииКУ.

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

Оставим за пользователем только выбор копируемой строки, а все остальное поручите выполнять Excel в автоматическом режиме при нажатии на созданную кнопку Копирование и вставка выделенной строки (рис. 4.1.), которой назначьте следующий записываемый макрос с именем ВставкаСтроки.

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

• перед записью макроса выделите выбранную строку, например, строку 9;
• выполните процедуру начала записи макроса, после чего задайте команду копирования, например, нажатием на кнопку Копирование на стандартной панели инструментов. Содержимое строки 9 будет помещено в буфер обмена.;
• в связи с тем, что процедура автоматического определения и выделения первой пустой строки списка еще не изучена, выделите самую последнюю строку списка. Согласно, заданным в начале главы, условиям - это строка 200. Но чтобы добраться до нее, нужно выполнить ряд действий, например, перемещений на несколько экранов монитора вниз. Не затрудняйте себя, а выделите, например, строку 14 и произведите вставку из буфера обмена, например, нажатием на кнопку Вставка на стандартной панели инструментов;
• выделите строки от строки вставки (14) до первой строки после заголовка списка (4) и нажмите на кнопку Сортировка по возрастанию на стандартной панели управления. По умолчанию Excel произведет сортировку по данным столбца А. Список расположится в порядке возрастания порядковых номеров и содержимое скопированной строки 9 будет находиться в конце списка;
• остановите запись макроса.

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

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

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

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

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

Selection.Copy

, в которой:

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

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

Следующая строка:

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

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

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

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

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

Итоги

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

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

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

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

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