Доработка макроса III

Доработка макроса III в vba excelВ прошлой статье мы записали макрос и начали его правку — удалили лишние записи, обеспечив, тем самым, сокращение текста программы более чем на 95%!

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

Параметры шрифта и колонок

Следующий блок With … End With устанавливает ориентацию надписей 0 градусов. Это я понял по слову .Orientation = 0. Здесь нам, собственно, тоже целый блок не нужен. Оставляем и здесь одну строку: Selection.Orientation = 0

Далее мы с вами устанавливали параметры шрифта. Шрифт по английски — Font. Тут у нас тоже целый блок, но его я не стал трогать, поскольку мы изменяли две настройки, а находить их в блоке мне уже лень.

Следующие команды, это серия манипуляций с колонками — назначение ширины, удаление лишних колонок. Можно, конечно, с помощью клавиши F8 просматривать выполнение этих команд, но тут уже все очевидно — выделить (Select) колонки (Columns), удалить (Delete) выделенное (Selection) со смещением влево (Shift:=xlToLeft). Здесь все оставляем без изменений.

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

Затем мы писали наименования колонок. Команда Range(«A16»).Select выделяет нужную ячейку, а команда  ActiveCell.FormulaR1C1 = «Департамент» вносит в эту ячейку слово «Департамент«. И так с каждой колонкой. Две следующие команды выделяют и удаляют лишние строки.

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

Ручная запись макроса

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

С помощью макрорекордера можем выяснить как обозначается свойство «Нижняя толстая граница«:

  1. Открываем новый файл;
  2. Запускаем макрорекордер;
  3. В закладке меню «Главная» находим и жмем пункт «Нижняя толстая граница»;
  4. Останавливаем макрорекордер.

Таким образом выясняем, что свойство обозначается «.LineStyle = xlContinuous«.

В VBA есть три вида циклов:

  • по счетчику;
  • по условию;
  • по виду обрабатываемых объектов.

Я выбираю цикл по условию, поскольку в качестве условия мы уже выбрали нижнюю толстую границу ячейки. Цикл по условию выглядит так:

Do

команды, которые необходимо повторять

Loop Until условие, при котором цикл должен прекратиться

И еще одно новшество, которое мы ранее не использовали — назначение переменных. Это что-то похожее на присвоение имен ячейкам. Только имена мы присваиваем не ячейкам, а значениям, которые не появляются в таблице, но используются для вычислений. В нашем случае будет одна переменная, которую мы назовем, например, rngX (от слова Range — диапазон).

Назначаем переменные с помощью команды Dim … As …, где после Dim указываем имя, которое мы придумали переменной, а после As указываем тип данных. Тип данных может иметь много  значений:

String  Используется для хранения строк. До миллиона символов.
Integer Целое число от -32768 до 32767
Long Целое число от -2 147 483 648 до 2 147 483 647
Single Дробное число одинарной точности (до 8-го знака. Целые числа и запятая тоже считаются за знаки. )
Double Дробное число двойной точности (до 16-го знака. Целые числа и запятая тоже считаются за знаки.)
Date Полная дата, включая время. Пишется между решетками — #06/15/2012 10:00:00# Сначала пишется месяц, потом день, потом год.
Boolean Хранить только два значения — True и False
VARIANT Объявление переменной без указания типа. Требует много памяти, поэтому не рекомендуется использовать часто. Кроме того, может неверно определить данные.
Range Адрес ячейки или ссылка на диапазон

Нам подходит тип Range.

Запись 1-го цикла

Итак, после обозначения переменной, присваиваем ей адрес первой ячейки обрабатываемого диапазона — А4 (Set rngX = Range(«A4»)). Затем объявляем начало цикла словом Do. В следующей строке, копируем значение, которое находится в переменной, который мы указывали ранее: rngX.Copy

Далее необходимо переместить переменную вниз. Для этого мы используем команду Set (которая закрепляет за переменной какой-либо объект) и команду Offset (которая смещает объект по горизонтали или вертикали). Вот как это выглядит: Set rngX = rngX.Offset(1, 0)

Следующий шаг — вставка скопированного значения. Мы могли бы ограничиться командой PasteSpecial, но она вставит полностью всю ячейку, и, в этом случае, мы потеряем толстую нижнюю границу в соответствующих ячейках. Поэтому используем ограничение, которое пишется так: Paste:=xlPasteValues. Полностью строка команды будет такой: rngX.PasteSpecial Paste:=xlPasteValues

И, последнее, установим условие прекращения цикла. Как мы помним, таким условием является толстая граница ячейки, обозначаемая LineStyle = xlContinuous. Но, прежде чем указывать свойство, нам нужно обозначить объект, которым является нижняя граница нашей переменной. А обозначается она rngX.Borders(xlEdgeBottom).

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

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *