Макросы: как научить Excel работать за Вас.

Макросы: как научить Excel работать за Вас.

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

Как записать самый простой макрос.

 

Для начала давайте запишем самый легкий макрос: зададим в ячейке «А1» формат вида «12 345»:

· Открываем новую книгу, встаем в ячейку «А1» и набираем шестизначное число «123456». Сейчас оно выдается без разделителей разрядов. Запишем макрос, который ставит эти разделители.

· Заходим на панели инструментов в закладку «Вид»[1], находим кнопку «Макросы», жмем «Запись макроса»: в появившемся окне задаем имя макроса и книгу, в которой мы хотим этот макрос сохранить.

F ВАЖНО: Запустить макросы можно только из открытых книг, поэтому если Вы хотите использовать записанные Вами макросы довольно часто, то рекомендуется использовать специальную книгу макросов, которая автоматически открывается вместе с запуском сеанса Excel.

F Если Вы все-таки хотите хранить макросы в отдельном файле, эту книгу нужно сохранить, выбрав тип файла «Книга Excel с поддержкой макросов» - в противном случае, после закрытия книги макросы будут стерты из книги.

· Выбираем «Сохранить в…» - «Личная книга макросов» (см. Рис 1)

· Жмем «ОК»

Рисунок 1. Запись макроса в личную книгу макросов.

· Выполняем действия, которые мы хотим записать в макрос: вызываем контекстное меню «Формат ячеек» (можно воспользоваться комбинацией клавиш «ctrl+1»), задаем нужный нам формат числа: на закладке «Число» идем в блок «(все форматы)», и выбираем там формат вида “# ##0”. Примечание: Также этот формат можно задать в блоке «Числовой», но чуть позже Вам станет ясно, почему мы воспользовались блоком «Все форматы».

· На закладке «Вид» - «Макросы» выбираем пункт «Остановить запись»

F Второй, более быстрый, способ остановить макрос: нажать на появившийся в левом нижнем углу синий квадратик (см. рис 2.). Совет: всегда обращайте внимание на этот квадратик! Если он появился на панели, значит, сейчас идет запись всех ваших действий в Макрос, и не забывайте вовремя останавливать запись макроса, чтобы его потом не пришлось переделывать.

 

Проверяем, что макрос записан и работоспособен:

· Встаем в ячейку А2 и вбиваем любое шестизначное число

· Запускаем макрос одним из двух способов: на закладке «Вид» - «Макросы» выбираем пункт «Макросы» или нажимаем комбинацию клавиш «Alt+F8», далее находим в списке наш макрос и нажимаем кнопку «Выполнить».

 

Рисунок 2. Форматирование числа и остановка записи макроса.

Итак, Вы записали свой первый макрос! Примите поздравления. Теперь давайте познакомимся с личной книгой макросов и синтаксисом написания команд для макроса.

 

Личная книга макросов.

 

По умолчанию, Excel не отображает личную книгу макросов. Чтобы убедиться, что она открыта, выбираем на вкладке «Вид» кнопку «Отобразить» - в появившемся окне должна быть книга под именем “PERSONAL”. Мы убедились, что книга открыта, но отображать ее не будем, чтобы потом по ошибке не закрыть ее. По сути, в этой книге нас интересует так называемый «Исходный текст» - блок, в котором записываются макросы. Чтобы увидеть это окно, нажмите сочетание клавиш «Alt+F11» или кликните правой кнопкой мышки на ярлычке любого листа Excel и выберете в контекстном меню «Исходный текст». Откроется окно VBA-кодирования в Excel (рис. 3). Оно состоит из двух блоков:

1. В левой части экрана окно «Project – VBA Project» - это проводник, в котором отображаются все открытые в данный момент книги Excel (даже если Вы их не видите, как, например, книга “Personal”). Работа с этим блоком аналогичная работе в обычном проводнике – двойной клик по наименованию книги раскрывает ее содержимое – нас сейчас интересует блок «Modules – Module1». Кликаем левой кнопкой мышки дважды на этот объект.

2. В правой части экрана откроется блок записи и редактирования макросов. И здесь у Вас уже автоматически записался «Макрос1». Рассмотрим на его примере основную канву макроса.

Рисунок 3. Окно VBA-кодирования в Excel.

Синтаксис макроса.

 

Макросы – это команды, написанные на языке VBA, что расшифровывается как Visual Basic for Applications. Поэтому синтаксис кода макроса не отличается от записи кода в Visual Basic.

Любой макрос имеет следующий вид:

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

Sub Имя_Макроса_Без_Пробелов()

‘комментарии к макросу – они нужны для Вас, VBA не воспринимает такие строки как команды

команды, написанные на языке VBA

 

End Sub

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

Таким образом, макрос состоит из трех обязательных блоков:

1. Начало макроса. Всегда начинается с команды Sub и далее идет имя макроса – оно может быть на русском языке, но не должно содержать пробелы и специальные символы. В конце имени макроса всегда ставятся скобки «()» - они нужны, когда Вы создаете свою функцию, в них указываются аргументы функции, но об этом сейчас речь не пойдет.

2. Блок команд. В нашем примере он состоит из одной строки: Selection.NumberFormat = "#,##0"

Каждая команда должна начинаться с новой строки.

F Если текст команды очень длинный и визуально не помещается на экране, то его можно разбить на несколько строк, заканчивая строку символом нижнего полчеркивания «_». Ниже в примере мы это увидим.

3. Конец макроса. Всегда обозначается как End Sub.

И есть один необязательный блок – это комментарии, которые Вы можете оставлять в любом месте внутри кода макроса, поставив перед началом комментариев знак апострофа ('). Например, Вы можете описать, что именно делает тот или иной макрос. Имейте в виду: если Вы хотите разместить комментарии в несколько строк, каждую новую строку опять надо начинать с апострофа!

 

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

 

Задача1: Ваша информационная система выдает отчет «Бюджет на месяц» без выделения групповых значений каким-либо цветом или шрифтом. Необходимо выделить групповые строки полужирным шрифтом (см. рис. 4) и отформатировать на печать (расположить отчет по центру листа, задать масштаб 75%, вывести в колонтитулы название отчета).

 

Рисунок 4. Иллюстрация к задаче 1.

Рисунок 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

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

 

Функция InputBox.

Чтобы запросить у пользователя месяц отчета, воспользуемся функцией «Inputbox». Суть ее: вывести диалоговое окно, в котором пользователь может самостоятельно задать значение переменной, используемой в коде.

Синтаксис функции InputBox:

Zapros = InputBox("Текст запроса", <"Текст в шапке окна">, <Значение по умолчанию>,…)

 

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

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

F ВАЖНО: для удобства присвойте полученное значение функции InputBox какой-нибудь введенной Вами переменной. Это особенно полезно, если в коде макроса Вы будете несколько раз использовать значение этой функции.

В нашем примере мы присвоим результат вызова функции InputBox переменной Mes.

F ВАЖНО: имена вводимых Вами переменных не должны совпадать с уже занятыми VBA словами под название объектов, свойств или функций! Например, нельзя завести свою переменную Range или Cells – компилятор VBA предупредит Вас, что делать этого нельзя и не запустит макрос, пока Вы не устраните ошибку (см. рис. 6).

Рисунок 6. Пример ошибки при заведении переменной

Пользователь ввел свою переменную Month, имя которой совпадает со встроенной в VBA функцией, компилятор выдает ошибку.

Не забывайте: любой текст в коде должен быть взят в кавычки! В противном случае компилятор VBA будет воспринимать этот текст как команды и будет выдавать ошибку, так как не сможет их обработать!

 

Итак, приступим к редактированию кода макроса. Добавим в самое начало кода макроса строки:

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

' Запрашиваем у пользователя месяц отчета

Mes = InputBox("Введите месяц отчета",, "Январь")

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

 

Теперь при запуске макроса будет выводиться диалоговое окно, в котором пользователь самостоятельно сможет указать, за какой месяц этот отчет:

 

С помощью функции InputBox можно запросить у пользователя и длину отчета. Но мы научим Excel высчитывать ее самостоятельно. Для этого нам потребуется процедура WHILE.

 

Процедура WHILE.

Процедура WHILE используется в коде, если Вам надо сделать одинаковые действия несколько раз подряд до тех пор, пока выполняется какое-либо условие.

 

Синтаксис процедуры While

Do While<условие, которое должно быть верным для выполнения процедуры>

<список команд>

Loop

 

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

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

F Если после запуска макроса Вы видите, что макрос ушел в цикл, прервать его можно при помощи комбинации клавиш Ctrl+Break. И далее выбираете Ваши дальнейшие действия: или просто прекратить макрос (кнопка «End») или зайти в код макроса и исправить ошибку (кнопка «Debug»)

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

 

Рассмотрим применение процедуры While для поиска конца отчета БДР.

Как видно на рисунке 1, последняя строка отчета имеет код «500». Напишем процедуру, которая будет проверять значения в ячейках столбца «А» и остановит свои действия, когда найдет ячейку с кодом «500».

Обратите внимание: Excel иногда воспринимает числа как текст, поэтому включим два условия проверки значения ячейки и добавим «защиту от дурака» на случай, если в форме отчета случайно затрется код последней строки.

Помним, что все текстовые значения надо брать в кавычки. Числа записываем без кавычек.

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

' Ищем последнюю строку отчета

Dim Row_End As Integer ' Вводим переменную «счетчик номера строки»

Row_End = 1 ' Присваиваем ей номер 1

‘ Начинаем процедуру поиска последней строки отчета по коду «500»

Do While Cells(Row_End, 1).Value <> "500" And Cells(Row_End, 1).Value <> 500 And Row_End < 100

‘ До тех пор, пока код не равен 500 и номер строки меньше 100, выполняем наращивание счетчика

Row_End = Row_End +1

Loop

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

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

Теперь заменим в изначальном коде макроса номер последней ячейки (34) на переменную Row_End. Число 34 было частью текста «А1:С34», а теперь нам надо часть текста заменить на переменную. Делается это с помощью символа амперсанд «&» по такому правилу:

«текст» & переменная & «текст»

В нашем случае ссылка на диапазон «А1:С34» перепишется как: «А1:С» & Row_End.

Кроме того, вспоминаем, что и название отчета "Бюджет на январь" у нас тоже теперь содержит параметр и будет записываться в коде как: "Бюджет на "& Mes. Не забывайте оставлять пробелы в кавычках перед переменной-словом – иначе текст сольется!

Еще раз взглянем на получившийся код макроса:

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

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

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

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

' Запрашиваем у пользователя месяц отчета

Mes = InputBox("Введите месяц отчета",, "Январь")

 

' Ищем последнюю строку отчета

Dim Row_End As Integer ' Вводим переменную «счетчик номера строки»

Row_End = 1 ' Присваиваем ей номер 1

 

' Начинаем процедуру поиска последней строки отчета по коду «500»

' Критичным значением для «защиты от дурака» принимаем номер строки 100

Do While Cells(Row_End, 1).Value <> "500" And Cells(Row_End, 1).Value <> 500 And Row_End < 100

' До тех пор, пока код не равен 500 и номер строки меньше 100, выполняем наращивание счетчика

Row_End = Row_End +1

Loop

 

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

Columns("A:C").Select

Selection.AutoFilter

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

Operator:=xlAnd

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

Range("A1:C" & Row_End).Select

Selection.Font.Bold = True

Selection.AutoFilter

 

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

With ActiveSheet.PageSetup

.CenterHeader = "Бюджет на " & Mes

.CenterHorizontally = True

.Zoom = 75

End With

End Sub

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

Теперь Ваш макрос стал более универсальным в использовании.

 

Добавим в него еще одну «защиту от дурака». Если нам принципиально, правильно ли макрос нашел последнюю строку отчета, после окончания процедуры While (строка Loop) можно добавить блок проверки значения Row_End и запроса у пользователя подтверждения на продолжение макроса.

Для этого изучим функцию MsgBox, процедуру IF и команду Exit Sub.

 

Функция MsgBox.

MsgBox – это еще один способ общения с пользователем: сообщения ему какой-то информации по ходу выполнения макроса или запрос у него подтверждения по дальнейшим действиям макроса путем нажатия на кнопки вида «Yes», «No», «ОК», «Cancel».

Она имеет два вида записи:

1. Мы просто сообщаем пользователю какую-то информацию: аргументы функции перечисляются сразу же за функцией, без использования скобок:

MsgBox "Текст сообщения", <Вид кнопок>, <"Текст в шапке окна">,…

В этом случае на экране просто отобразится диалоговое окно и после нажатия пользователем кнопки «ОК» продолжится выполнение макроса.

Как и в случае с функцией InputBox, обязательным здесь является только первый аргумент – Текст сообщения. Остальные аргументы можно даже не указывать, сократив вид записи функции до вида:

MsgBox «Текст сообщения»

 

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

YesNo = MsgBox("Текст сообщения", <Вид кнопок>, <" Текст в шапке окна ">,…)

 

Варианты вида кнопок:

· vbOKOnly – только кнопка «ОК»

· vbOKCanel – кнопки «ОК» и «Cancel»

· vbYesNo – кнопки «Yes» и «No»

· vbYesNoCancel – кнопки «Yes», «No» и «Cancel»

 

Соответственно в зависимости от нажатой кнопки значения функции MsgBox могут быть vbOK, vbCancel, vbYes или vbNo.

 

Процедура IF.

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

Синтаксис процедуры IF

If<условие, которое должно быть верным> Then

<список команд при выполнении указанного условия>

else

<список команд при невыполнении указанного условия >

End If

 

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

Команда Exit Sub.

Данная команда прекращает выполнение макроса. Обычно ее используют в ветках процедуры If при проверке допустимости дальнейшего исполнения макроса. Обратите внимание: ни одно действие после команды Exit Sub выполнено не будет. Поэтому если Вы хотите сообщить пользователю о принудительном прекращении процедуры, команду MsgBox нужно расположить выше команды Exit Sub.

 

Итак, объединим новые знания в код «защиты от дурака». Запишем его после строки Loop.

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

' Проверяем, дошла ли процедура While до «критичной» строки 100 и

' спрашиваем подтверждение о продолжении выполнения макроса

If Row_End = 100 Then

YesNo = MsgBox(«Поиск последней строки отчета дошел до 100, продолжаем?», vbYesNo)

If YesNo = vbNo Then ' Если нажата кнопка «No», то…

MsgBox "Процедура прервана пользователем" '1. выдаем сообщение для пользователя,

Exit Sub '2. останавливаем макрос

End If

End If

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

Обратите внимание

F В нашем коде прошло вложение одной процедуры IF в другую:

1. Сначала мы проверили, дошел ли счетчик Row_End до значения 100.

2. Если дошел, мы спрашиваем у пользователя, продолжать ли нам выполнение макроса

3. Если пользователь ответил «нет» - мы останавливаем макрос.

F В обеих процедурах IF мы опустили второй блок (else): если Row_End не достиг значения 100 или пользователь ответил «да» - макрос просто покинет процедуру IF и продолжит выполнять команды, написанные ниже.

F Каждая процедура IF должна заканчиваться командой End If. Если количество строк с командой IF не будет соответствовать количеству строк с командой End If – компилятор VBA сообщит об ошибке, и Вам придется искать, где Вы потеряли конец процедуры IF. Поэтому рекомендуется сразу писать обе строки, а потом уже наполнять процедуру командами.

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

 

Подведем итоги:

Итак, мы с Вами научились:

1. Записывать макросы через команду «Вид» - «Макросы» - «Запись макрос»

2. Редактировать автоматически записанный макрос, удалив из него лишние команды

3. Унифицировать код макроса, вводя в него переменные, которые макрос запрашивает у пользователя или рассчитывает самостоятельно

4. Изучили функции InputBox и MsgBox, процедуры WHILE и IF, команду Exit Sub.

 

В следующем номере мы познакомимся с процедурой FOR, запишем макрос суммирования результирующих статей БДР, научимся тестировать макросы и выносить кнопки вызова макроса на панель быстрого доступа

 

 

Макросы: продолжение.

В предыдущем номере мы научились записывать и редактировать несложные макросы с помощью процедур WHILE и IF, функций InputBox и MsgBox и команды Exit Sub. В этом номере мы изучим процедуру FOR, запишем макрос суммирования результирующих статей БДР, научимся тестировать макросы и выносить кнопки вызова макроса на панель быстрого доступа.

Задача 2. Ваша информационная система выдает отчет «Бюджет на месяц» без формул суммирования итогов (то есть все данные представлены значениями), а мы хотим вносить изменения в выданный информационной системой бюджет – следовательно, надо проставить формулы в результирующих ячейках. Напишем макрос, который расставляет формулы Суммы в строках, выделенных полужирным шрифтом (то есть этот макрос надо запускать после макроса «Форматирование_БДР», рассмотренного нами в предыдущем номере).

 

Рисунок 7. Иллюстрация к задаче 2.

 

Для этого познакомимся с еще одной часто используемой в кодах процедурой FOR

Процедура FOR.

Если Вам надо сделать одинаковые действия известное количество раз подряд (например, отформатировать ячейки в диапазоне со 2 по 5 строку), то можно запустить процедуру FOR.

Синтаксис процедуры IF

For i = A To B

<список команд >

Next i

 

Где i – заданная Вами переменная-счетчик, А и В – диапазоны значений, которые эта переменная будет принимать по очереди. Эта процедура так же допускает вложения внутрь себя других процедур.

По смыслу процедура FOR очень схожа с процедурой While. Однако есть ряд существенных отличий между этими процедурами.

Процедура IF самостоятельно наращивает переменную-счетчик, и в список команд не надо добавлять ее наращивание, как мы это делали для процедуры While. Вторым важным отличием этой процедуры от процедуры While является то, что длина диапазона (значение переменной В) не может быть изменена после начала работы процедуры. Однако есть способ использовать процедуру For, не зная заранее длину диапазона, для которого она будет применена. Об этом – чуть позже.

А пока потренируемся использовать процедуру FOR на легком примере.

Отформатируем в нашем примере блок «Выручка»: увеличим размер шрифта с 10 до 11:

Сначала запишем автоматический макрос-команду изменения шрифта:

· Встаем в ячейку А2.

· Нажимаем кнопку записи макроса, даем понятное ему название и описание.

· Увеличиваем размер шрифта (выбираем на панели в закладке «Главная» размер шрифта «11»).

· Останавливаем макрос (синий квадратик внизу слева)

· Нажимаем Alt+F8 (или на вкладке «Вид» кнопку «Макросы»), находим наш новый макрос, жмем кнопку «Изменить» и попадаем в редактор кода макроса.

· Находим в коде макроса нужную нам строку, остальное можем удалить.

· Обратите внимание: автоматически в код макроса записались все параметры, отраженные на закладке «Шрифт» диалогового окна «Формат ячеек» (вызывается нажатием клавиш ctrl+1). Можете попробовать самостоятельно сопоставить строки макроса с пунктами этой закладки.

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

Sub Шрифт_11()

' Увеличивает размер шрифта до 11

With Selection.Font

.Name = "Times New Roman"

.Size = 11

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ColorIndex = xlAutomatic

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

End Sub

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

Так как нам нужно было изменить только одно свойство объекта Selection.Font, то мы можем отказаться от использования процедуры With и сократить код до 1 строки:

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

Sub Шрифт_11()

' Увеличивает размер шрифта до 11

Selection.Font.Size = 11

End Sub

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

 

Встаньте в ячейку А3 и убедитесь, что после редактирования макрос работает корректно (комбинация клавиш «Alt+F8» или закладка «ВИД», кнопка «Макросы» - далее находим наш новый макрос и нажимаем «Выполнить»).

Теперь добавим в макрос цикл с помощью двух вложенных процедур FOR

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

For Row_count = 2 To 5 ' Перебираем строки

For Col_count = 1 To 3 ' Перебираем столбцы

Cells(Row_count, Col_count).Select ' Выделяем ячейку в строке Row_Cont и столбце Col_count

Selection.Font.Size = 11 ' Форматируем объект Selection (шрифт = 11)

Next Col_count ' Следующий номер столбца

Next Row_count ' Следующий номер строки

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

 

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

Мы рассмотрели процедуру For, где в качестве переменной выступало число. Давайте рассмотрим процедуру For, где в качестве переменных выступают Объекты VBA.

 

Процедура FOR для Объектов.

Как мы уже знаем, объектами в VBA для Excel выступают Ячейки, Листы, Книги. Если Вы хотите использовать процедуру FOR для объектов, Вы должны обязательно «представить» VBA переменную, которую Вы будете использовать как объект.

Если Вы могли заметить, до этого мы не загружали код «представлением» VBA каждой введенной переменной и присвоением ей тип