End With

Пример использования инструкции:

В примере форматируются ячейки A1:C10 листа Лист1 (шрифт – полужирный, цвет фона – желтый). Как видим, ссылка на необходимый диапазон указывается один раз, хотя изменяются значения двух свойств объекта.

Макросы MS Excel

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

Одним из простейших способов создания программы на VBA является запись последовательности действий с помощью специальной функции записи макросов Excel. Чтобы записать макрос:

1. Выполните команду Разработчик/ Код / Запись макроса.

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

3. Выполните необходимые действия. Они будут записываться в виде программы на языке VBA.

4. Для остановки записи макроса достаточно щелкнуть на голубом квадратике, который находится в строке состояния Excel. Можно также выполнить команду Разработчик/ Код/ Остановить запись.

Для просмотра текста макроса перейдите в редактор и откройте нужный модуль. Если в проекте до записи макроса программных модулей не было, новый макрос будет находиться в модуле Module1. Можно выполнить команду Разработчик/ Код/ Макросы, в открывшемся окне выбрать имя нужного макроса и нажать на кнопку Изменить.

Задать или изменить комбинацию клавиш, которая будет использоваться для вызова макроса, или его описание можно в момент создания макроса или позднее, выполнив команду Разработчик/ Код/ Макросы. В открывшемся окне выбрать имя нужного макроса и нажать на кнопку Параметры. При назначении макросу комбинации клавиш, которая уже используется в Excel, данная команда имеет преимущество перед зарезервированной операцией.

Для выполнения макроса можно использовать несколько способов:

1. команда Run/ Run Sub/User Form, или инструмент , или клавиша F5 в редакторе VBE (курсор должен находиться внутри макроса);

2. в диалоговом окне Макрос (команда Разработчик/ Код/ Макросы) выбрать имя макроса и нажать кнопку Выполнить;

3. с помощью комбинации клавиш, назначенной макросу.

С точки зрения VBA макрос представляет собой процедуру Sub без параметров, написанную в программном модуле, которую совсем не обязательно создавать с помощью функции записи макросов. Любая процедура, удовлетворяющая приведенным выше условиям, будет макросом (будет вызываться из окна Макрос). Функция записи макросов очень удобна для изучения VBA, однако программы, созданные таким образом, часто содержат лишний код.

Пример 3.1. Создайте новую книгу в Excel. Измените размер окна Excel так, чтобы оно занимало примерно половину экрана. Откройте окно редактора VBE и измените его размер, чтобы оно занимало оставшуюся часть экрана. Перейдите в окно Exсel и выполните команду Разработчик/ Код / Записать макрос. В появившемся окне нажмите OK. Перейдите в окно VBE. Откройте окно модуля Module1. Можно закрыть окна Project и Properties для экономии места. Если понадобится, эти окна можно вывести по команде View/ Project Explorer или View/ Properties Windows. В результате этих действий, выполняя команды Excel, можно видеть, какой код генерируется в макросе.

Вернитесь в ячейку A1. Установите следующие параметры форматирования: шрифт – Arial, размер шрифта – 14, цвет шрифта – красный. Остановите запись макроса. В этот момент экран будет выглядеть приблизительно так:

Как видим, автоматически была создана довольно длинная процедура, в которой большинство инструкций не нужны для решения нашей задачи. Напишем в этом же модуле процедуру Макрос2 (можно использовать команду Insert/ Procedure), в которую скопируем из первой только строки, приведенные ниже. Созданный таким образом второй макрос можно вызвать так же, как первый, и результат их работы одинаковый.

В автоматически созданных макросах часто сначала выделяется диапазон ячеек, а затем уже у объекта Selection используется нужное свойство или метод. В этом нет необходимости. Можно сразу применять те же действия к диапазону, заданному с помощью Range, Cells, ActiveCell и других свойств. Если убрать из нашего макроса инструкцию Range("A1").Select, то он станет более универсальным, так как форматирование будет выполняться для любого выделенного диапазона. Если при этом Selection заменить, например, на ActiveCell, то будет форматироваться активная ячейка.

Обратите внимание, как задается цвет. Использованная числовая константа соответствует некоторому оттенку красного цвета. Запомнить такую константу невозможно, да и не нужно. Всегда можно записать макрос и потом воспользоваться автоматически созданным значением. Однако в языке есть и другие способы задания цветов. Во-первых, для основных цветов существуют именованные константы (для красного это vbRed), поэтому можно было бы эту инструкцию записать в виде

(посмотрите в справке, какие еще есть константы цвета, для этого установите курсор на имени константы и нажмите клавишу F1). Во-вторых, можно использовать функцию RGB (название от red-green-blue). У этой функции 3 аргумента, каждый из которых может иметь значение от 0 до 255 и задает долю красного, зеленого и синего компонента в цвете. RGB(255, 255, 255) соответствует белому цвету, RGB(0, 0, 0) – черному. Если аргументы имеют одинаковые значения, то получим оттенок серого. Самому яркому красному цвету соответствует значение RGB(255, 0, 0).

У созданных макросов задайте описание и комбинацию клавиш для вызова. Для этого выведите окно Макрос (Разработчик/ Код/ Макросы), выберите имя макроса и нажмите кнопку Параметры. Введите необходимые значения в соответствующие поля. Попробуйте вызвать макрос на рабочем листе, используя указанное сочетание клавиш.

Пример 3.2. Рассмотрим, как при автоматическом создании макроса записывается вставка формулы в ячейку. В качестве примера запишите макрос, который вставляет в ячейку C7 формулу "=ОКРУГЛ(A7+$A$1*B7;2)" (функция округляет значение первого аргумента до двух знаков после запятой). В тексте макроса появится инструкция:

Как видим, строка с формулой присваивается свойству FormulaR1C1, но выглядит формула иначе. Во-первых, используется другое имя функции. На самом деле в формуле использовано имя нашей функции в англоязычной версии. Во-вторых, ссылки на ячейки выглядят странно. Дело в том, что для ссылок на ячейки в Excel используются два стиля: A1 и R1C1. Обычно вы работаете со ссылками в стиле A1: ссылка состоит из имени столбца (обозначаются латинскими буквами) и номера строки. Признаком абсолютной ссылки является знак доллара перед именем строки или столбца. Макросы используют стиль R1C1. В этом случае после буквы R указывается номер строки ячейки, после буквы C – номер столбца. Для задания относительной ссылки в стиле R1C1 указывается смещение по отношению к активной ячейке (в квадратных скобках). Знак определяет направление смещения. Например, RC[-1] (относительная ссылка на ячейку, расположен­ную в той же строке в предыдущем столбце), R[3]C[2] (относительная ссылка на ячейку, расположенную на три строки ниже и на два столбца правее), R2C2 (абсолютная ссылка на ячейку, расположенную во второй строке во втором столбце), R[-1] (относительная ссылка на предыдущую строку), R (абсолютная ссылка на текущую строку).

При автоматическом создании макросов формулы всегда записываются в таком виде, но если вы сами пишете текст макроса, то вместо свойства FormulaR1C1 можно использовать свойство FormulaLocal. В этом случае вы можете использовать в формуле формат A1 и привычные (на языке пользователя) названия функций:

Задания для самостоятельной работы:

3.1. Попробуйте выполнять различные действия в Excel при включенной функции записи макросов. Проанализируйте полученные тексты программ. Научитесь пользоваться справочной системой.

3.2. Создайте макрос, который изменяет формат первой буквы текста активной ячейки: цвет – зеленый, шрифт – Monotype Corsiva, размер увеличивает на 2 (Указание: воспользуйтесь контекстной справкой для свойства Font (вариант Font(object)), в разделе Remarks есть гиперссылка для свойства Characters, перейдите по ней и прочитайте, как можно форматировать часть строки).