Запись алгоритма форматирования отчета в макрос.

 

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

· Даем понятное имя макросу «Форматирование_БДР», в блоке описания записываем, что будет делать этот макрос (например, «Выделяет жирным курсивом итоги, форматирует на печать»). Жмем «ОК».

· Выделяем столбцы «А:С», ставим автофильтр: на закладке «Данные» находим кнопку «Фильтр».

· По столбцу «КОД» задаем условие «не содержит точку»: «Текстовые фильтры» - «Не содержит» и в поле текста ставим символ точки без пробелов (см. рис.5).

Рисунок 5. Использование автофильтра по столбцу «КОД».

· Выделяем отфильтрованный диапазон и задаем ему полужирный шрифт.

· Снимаем автофильтр (повторное нажатие на закладке «Данные» кнопки «Фильтр»).

· Заходим в меню форматирования на печать (Кнопка «Файл/Office» - «Печать» - «Предварительный просмотр» - «Параметры страницы») и задаем там три параметра:

o На вкладке «Страница» задаем масштаб 75%

o На вкладке «Поля» отмечаем пункт «Горизонтально» в блоке «Центрировать на странице»

o На вкладке «Колонтитулы» создаем верхний колонтитул с текстом «Бюджет на Январь»

· Выходим из параметров страницы.

· Заканчиваем запись макроса.

· Нажимаем Alt+F11 и смотрим, что получилось (см.Пример 1).

 

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

Любая команда макроса состоит из двух блоков, разделенных точкой:

«Оъект». «Действие с объектом или Свойство объекта»

 

Объектами в Excel являются, например:

o Книга: WorkBook, ActiveWorkbook

o Лист, листы: WorkSheet, ActiveSheet, Sheets

o Ячейка: Cells(1,1) – в скобках указываются номер строки (Row) и столбца (Column) ячейки на листе

o Диапазон ячеек (в том числе может быть и одна ячейка): Range(«А1:С5»), Range(«А1»)

o Строки (Rows)

o Столблцы (Columns)

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

 

 

Пример действий с объектами:

o ActiveWorkbook.Save – сохранить рабочую книгу (та, которая была активна в момент вызова пользователем макроса).

o Sheets("Лист3").Name = "Отчет" – переименовать «Лист3» в «Отчет»

o Sheets(«Отчет»). Activate – перейти на лист с названием «Отчет».

o Range("А1").Copy – скопировать в буфер обмена данные из ячейки А1.

o Rows("13:13").Delete Shift:=xlUp – удалить строку 13 со сдвигом вверх.

 

Пример свойств объектов:

o ActiveCell.FormulaR1C1 = "БДР" – в выделенной (активной) ячейке записан текст «БДР»

o ActiveCell.Row < 65 – номер ряда активной ячейки меньше 65.

 

Помнить все названия объектов, команд и свойств для написания несложных макросов Вам не обязательно. Вы всегда можете сначала провести запись Ваших стандартных действий с отчетами в Excel, а потом отформатировать код макроса, убрав из него лишние действия или заменив некоторые заданные параметры (как, например, длину диапазона) на переменные (которые в дальнейшем макрос будет запрашивать у пользователя или рассчитывать самостоятельно). О чем мы расскажем чуть ниже.

 

А сейчас обратимся к коду нашего первого макроса, состоящего из одной строки:

Selection.NumberFormat = "#,##0"

Суть его в следующем: Объекту «Выделенныйдиапазон» (у нас это была одна ячейка, но это может быть и весь столбец/строка или диапазон смежных ячеек) применить свойство «Числовой формат» вида "#,##0" (если помните, именно этот формат мы выбрали в списке всех форматов).

Теперь прочитаем код макроса «Форматирование_БДР»

Пример 1. Код макроса «Форматирование_БДР» и расшифровка строк кода.

Строка кода	Расшифровка
Sub Форматирование_БДР()	Начало макроса, имя макроса
'	Пустая строка комментариев (ставится автоматически при записи макроса)
' Форматирование_БДР Макрос	Автоматически добавленный комментарий при записи макроса - может быть удален без потери работоспособности макроса
' Выделяем жирным курсивом итоги, форматируем на печать	Автоматически добавленный комментарий при записи макроса из поля "Описание"
	Пустая строка - не влияет на суть исполнения макроса, но их полезно добавлять для разделения блоков команд внутри кода
Columns("A:C").Select	Выделить (Select) Объект "Столбцы" (Columns) «А:С»
Selection.AutoFilter	Применить автофильтр (AutoFilter) для выделенного диапазона (Selection)
ActiveSheet.Range("$A$1:$C$34").AutoFilter Field:=1, Criteria1:="<>*.*", _	Задать критерий отбора "не содержит точку"
Operator:=xlAnd	Продолжение команды из предыдущей строки. Обратите внимание: предыдущая строка закончилась символом нижнего подчеркивания "_" - это значит, что текст команды не уместился на одну строку и окончание команды перенесено на следующую
Range("A1:C34").Select	Выделить (Select) Объект "Диапазон" (Range) «А1:С34». Обратите внимание:какой бы длины ни был ваш следующий отчет, для которого Вы будете применять этот макрос, выделится всегда только диапазон до 34 строки! Как сделать этот диапазон любой длины - обсудим ниже.
Selection.Font.Bold = True	Для выделенного диапазона (Объект Selection) установить Свойство "полужирный шрифт" (Font.Bold = True). Если нужно отменить какое-то свойство, то пишем False
Selection.AutoFilter	Снять автофильтр (при записи макроса это было повторное нажатие на кнопку "Фильтр" на закладке "Данные")
With ActiveSheet.PageSetup	Начало процедуры With (используется, если ниже перечисляются свойства или действия с одним и тем же объектом для компактности записи кода) Для Объекта ActiveSheet (Текущий лист) применить следующие параметры Свойства PageSetup (Параметры печати):
.PrintTitleRows = ""	Печатать на каждой странице сквозные строки - пусто (то есть данное свойство не задано пользователем)
.PrintTitleColumns = ""	Печатать на каждой странице сквозные столбцы – пусто
End With	Окончание процедуры With
ActiveSheet.PageSetup.PrintArea = ""	Заданная область печати - пусто (то есть пользователь не ограничил область печати, следовательно, на экран будет выведено все, что есть на листе
With ActiveSheet.PageSetup	Начало процедуры With Для объекта "Текущий лист" применить следующие параметры печати (цветом выделены те, которые мы изменили от стандартных):
.LeftHeader = ""	Левый верхний колонтитул - пусто
.CenterHeader = "Бюджет на январь"	Центральный верхний колонтитул - задан текст пользователем
.RightHeader = ""	Правый верхний колонтитул - пусто
.LeftFooter = ""	Левый нижний колонтитул - пусто
.CenterFooter = ""	Центральный нижний колонтитул - пусто
.RightFooter = ""	Правый нижний колонтитул - пусто
.LeftMargin = Application.InchesToPoints(0.708661417322835)	Размеры левого поля
.RightMargin = Application.InchesToPoints(0.708661417322835)	Размеры правого поля
.TopMargin = Application.InchesToPoints(0.748031496062992)	Размеры верхнего поля
.BottomMargin = Application.InchesToPoints(0.748031496062992)	Размеры нижнего поля
.HeaderMargin = Application.InchesToPoints(0.31496062992126)	Размеры верхнего колонтитула
.FooterMargin = Application.InchesToPoints(0.31496062992126)	Размеры нижнего колонтитула
.PrintHeadings = False	Не печатать заголовки строк и столбцов (False - пользователь не отметил этот пункт)
.PrintGridlines = False	Не печатать сетку
.PrintComments = xlPrintNoComments	Не печатать примечания
.PrintQuality = 600	Качество печати = 600 точек на дюйм
.CenterHorizontally = True	Центрировать на странице горизонтально (True - пользователь отметил этот пункт)
.CenterVertically = False	Не центрировать по вертикали
.Orientation = xlPortrait	Ориентация страницы = книжная
.Draft = False	Пользователь не отметил пункт "Черновая" в блоке "Печать"
.PaperSize = xlPaperA4	Размер бумаги А4
.FirstPageNumber = xlAutomatic	Номер первой страницы - автоматически
.Order = xlDownThenOver	Последовательность вывода страниц: вниз, потом вправо (пункт в блоке "Печать")
.BlackAndWhite = False	Пользователь не отметил пункт "Черно-белая" в блоке "Печать"
.Zoom = 75	Масштаб 75%
.PrintErrors = xlPrintErrorsDisplayed	Пункт в блоке "Печать" "Ошибки ячеек" = "Как на экране"
.OddAndEvenPagesHeaderFooter = False	Пользователь не задавал разные колонтитулы для четных и нечетных страниц (флажок в блоке "Колонтитулы")
.DifferentFirstPageHeaderFooter = False	Пользователь не задавал отдельный колонтитул для первой страницы (флажок в блоке "Колонтитулы")
.ScaleWithDocHeaderFooter = True	Флажок в блоке "Колонтитулы" "Изменять вместе с масштабом страницы" отмечен пользователем
.AlignMarginsHeaderFooter = True	Флажок в блоке "Колонтитулы" "Выровнять относительно полей страницы" отмечен пользователем
.EvenPage.LeftHeader.Text = ""	Текст колонтитулов для четных и первой страниц не задан
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With	Окончание процедуры With
End Sub	Окончание кода макроса

Редактирование макроса.

 

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

---------------------------------------------------

Sub Форматирование_БДР()

' Макрос выделяет жирным курсивом итоги, форматирует отчет на печать

'-----------------

' Выделяем столбцы и ставим фильтр по столбцу КОД

Columns("A:C").Select

Selection.AutoFilter

ActiveSheet.Range("$A$1:$C$34").AutoFilter Field:=1, Criteria1:="<>*.*", _

Operator:=xlAnd

' Выделяем отфильтрованный диапазон полужирным шрифтом, снимаем фильтр

Range("A1:C34").Select

Selection.Font.Bold = True

Selection.AutoFilter

' Форматируем на печать: верхний колонтитул, центрирование по горизонтали, масштаб 75%

With ActiveSheet.PageSetup

.CenterHeader = "Бюджет на январь"

.CenterHorizontally = True

.Zoom = 75

End With

End Sub

---------------------------------------------------