Простые макросы в excel примеры с описанием. Краткое руководство: создание макроса

Следующие простые примеры макросов Excel иллюстрируют некоторые возможности и приёмы, описанные в самоучителе по Excel VBA .

Макрос Excel: пример 1

Первоначально эта процедура Sub была приведена, как пример использования комментариев в коде VBA. Однако, здесь также можно увидеть, как объявляются переменные, как работают ссылки на ячейки Excel, использование цикла For , условного оператора If и вывод на экран окна сообщения.

"Процедура Sub выполняет поиск ячейки, содержащей заданную строку "в диапазоне ячеек A1:A100 активного листа Sub Find_String(sFindText As String) Dim i As Integer "Целое число типа Integer, используется в цикле For Dim iRowNumber As Integer "Целое число типа Integer для хранения результата iRowNumber = 0 "Просматривает одну за другой ячейки A1:A100 до тех пор, пока не будет найдена строка sFindText For i = 1 To 100 If Cells(i, 1).Value = sFindText Then "Если совпадение с заданной строкой найдено "сохраняем номер текущей строки и выходим из цикла For iRowNumber = i Exit For End If Next i "Сообщаем пользователю во всплывающем окне найдена ли искомая строка "Если заданная строка найдена, указываем в какой ячейке найдено совпадение If iRowNumber = 0 Then MsgBox "Строка " & sFindText & " не найдена" Else MsgBox "Строка " & sFindText & " найдена в ячейке A" & iRowNumber End If End Sub

Макрос Excel: пример 2

Следующая процедура Sub – пример использования цикла Do While . Здесь также можно увидеть, как объявляются переменные, работу со ссылками на ячейки Excel и применение условного оператора If .

"Процедура Sub выводит числа Фибоначчи, не превышающие 1000 Sub Fibonacci() Dim i As Integer "Счётчик для обозначения позиции элемента в последовательности Dim iFib As Integer "Хранит текущее значение последовательности Dim iFib_Next As Integer "Хранит следующее значение последовательности Dim iStep As Integer "Хранит размер следующего приращения "Инициализируем переменные i и iFib_Next i = 1 iFib_Next = 0 "Цикл Do While будет выполняться до тех пор, пока значение "текущего числа Фибоначчи не превысит 1000 Do While iFib_Next < 1000 If i = 1 Then "Особый случай для первого элемента последовательности iStep = 1 iFib = 0 Else "Сохраняем размер следующего приращения перед тем, как перезаписать "текущее значение последовательности iStep = iFib iFib = iFib_Next End If "Выводим текущее число Фибоначчи в столбце A активного рабочего листа "в строке с индексом i Cells(i, 1).Value = iFib "Вычисляем следующее число Фибоначчи и увеличиваем индекс позиции элемента на 1 iFib_Next = iFib + iStep i = i + 1 Loop End Sub

Макрос Excel: пример 3

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

"Процедура Sub сохраняет значения ячеек столбца A активного листа в массиве Sub GetCellValues() Dim iRow As Integer "Хранит номер текущей строки Dim dCellValues() As Double "Массив для хранения значений ячеек iRow = 1 ReDim dCellValues(1 To 10) "Цикл Do Until перебирает последовательно ячейки столбца A активного листа "и извлекает их значения в массив до тех пор, пока не встретится пустая ячейка Do Until IsEmpty(Cells(iRow, 1)) "Проверяем, что массив dCellValues имеет достаточный размер "Если нет – увеличиваем размер массива на 10 при помощи ReDim If UBound(dCellValues) < iRow Then ReDim Preserve dCellValues(1 To iRow + 9) End If "Сохраняем значение текущей ячейки в массиве dCellValues dCellValues(iRow) = Cells(iRow, 1).Value iRow = iRow + 1 Loop End Sub

Макрос Excel: пример 4

В этом примере процедура Sub считывает значения из столбца A рабочего листа Лист2 и выполняет с ними арифметические операции. Результаты заносятся в ячейки столбца A на активном рабочем листе. В этом макросе продемонстрировано использование объектов Excel. В частности, производится обращение процедурой Sub к объекту Columns , и показано, как доступ к этому объекту осуществляется через объект Worksheet . Показано так же, что при обращении к ячейке или диапазону ячеек на активном листе, имя этого листа при записи ссылки указывать не обязательно.

"Процедура Sub при помощи цикла считывает значения в столбце A рабочего листа Лист2, "выполняет с каждым значением арифметические операции и записывает результат в "столбец A активного рабочего листа (Лист1) Sub Transfer_ColA() Dim i As Integer Dim Col As Range Dim dVal As Double "Присваиваем переменной Col столбец A рабочего листа Лист 2 Set Col = Sheets("Лист2").Columns("A") i = 1 "При помощи цикла считываем значения ячеек столбца Col до тех пор, "пока не встретится пустая ячейка Do Until IsEmpty(Col.Cells(i)) "Выполняем арифметические операции над значением текущей ячейки dVal = Col.Cells(i).Value * 3 - 1 "Следующая команда записывает полученный результат в столбец A активного рабочего листа "Имя листа в ссылке указывать нет необходимости, так как это активный лист. Cells(i, 1) = dVal i = i + 1 Loop End Sub

Макрос Excel: пример 5

Данный макрос показывает пример кода VBA, отслеживающего событие Excel. Событие, к которому привязан макрос, происходит каждый раз при выделении ячейки или диапазона ячеек на рабочем листе. В нашем случае при выделении ячейки B1 , на экран выводится окно с сообщением.

"Данный код показывает окно с сообщением, если на текущем рабочем листе "выбрана ячейка B1 Private Sub Worksheet_SelectionChange(ByVal Target As Range) "Проверяем выбрана ли ячейка B1 If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then "Если ячейка B1 выбрана, выполняем необходимое действие MsgBox "Вы выбрали ячейку B1" End If End Sub

Макрос Excel: пример 6

На примере этой процедуры показано использование операторов On Error и Resume для обработки ошибок. В данном коде также показан пример открытия и чтения данных из файла.

"Процедура Sub присваивает аргументам Val1 и Val2 значения ячеек A1 и B1 "из рабочей книги Data.xlsx, находящейся в папке C:\Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling "Открываем рабочую книгу с данными Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") "Присваиваем переменным Val1 и Val2 значения из заданной рабочей книги Val1 = Sheets("Лист1").Cells(1, 1) Val2 = Sheets("Лист1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: "Если файл не найден, пользователю будет предложено поместить искомый файл "в нужную папку и после этого продолжить выполнение макроса MsgBox "Файл Data.xlsx не найден! " & _ "Пожалуйста добавьте рабочую книгу в папку C:\Documents and Settings и нажмите OK" Resume End Sub

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

Например, самостоятельно написанный макрос можно привязать к иконке и вывести на Ленту меню. Либо Вы можете создать пользовательскую функцию (UDF) и использовать ее точно так же, как и остальные встроенные функции Excel.

Макрос - это компьютерный код, написанный для Excel на языке программирования Visual Basic for Applications (VBA). Базовые понятия языка программирования VBA рассматриваются на нашем сайте в Учебнике по VBA. Однако прежде чем приступить к написанию кода VBA, рекомендуем познакомиться с уроками, в которых рассматривается безопасность макросов Excel и редактор Visual Basic .

Настройка разрешения для использования макросов в Excel

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

Редактор Visual Basic

Запись макросов

Инструментарий Excel для записи макросов – это отличный способ эффективно выполнять простые повторяющиеся задачи. Также его можно использовать, как вспомогательное средство при написании более сложных макросов.

Учебник Excel VBA

Для тех, кто только начинает осваивать язык программирования Excel VBA, предлагаем небольшой вводный курс по Visual Basic for Applications.

Давайте рассмотрим способы создания макросов в Excel. Первым делом Вам необходимо проверить настройку безопасности для того, что бы макросы были включены, иначе ничего не получится. Перейдите главное меню «Сервис-Макрос-Безопасность »

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

В Excel есть два способа создания макроса:

Записать с помощью соответствующего пункта меню
Создать вручную

Первый способ легкий и не требует никаких знаний в программировании. Достаточно в главном меню выбрать Сервис- >Макрос->Начать запись…

В открывшемся окне записи макроса необходимо указать его имя, которое будет выводиться в списке доступных макросов, можно добавить описание (для чего макрос, автор и т.д.), присвоить клавишу для быстрого запуска и указать в какую книгу сохранить макрос. После нажатия «OK » начнется запись

Теперь, все что Вы будете делать в рабочей книге (добавлять, изменять, удалять, создавать сводные и т.д.) все будет записываться. Для примера напишите в ячейке B3=45, B4 = 5, а в В5 формулу «=В3+B4*10». Для остановки записи необходимо нажать соответствующую кнопку:

После завершения записи наш макрос появится в списке Сервис->Макрос->Макросы ( Alt+ F8)

Остается его только выбрать и нажать «Выполнить ».

Все действия, которые мы произвели во время записи, с точностью повторятся. Для проверки очистите лист и выполните макрос. Но такой способ не удобен и практически в дальнейшем применить запись невозможно т.к. отсутствует универсальность. Плюс в том, что мы записывая какие либо действия получаем готовый код, который в умелых руках становится универсальным и затачивается под необходимые задачи. Давайте рассмотрим, какой код был записан. Для этого нажмите кнопку «Изменить» в меню Сервис->Макрос->Макросы .

Откроется следующий код:

Sub Макрос1()
Range("B3").Select
ActiveCell.FormulaR1C1 = "45"
Range("B4").Select
ActiveCell.FormulaR1C1 = "5"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C*10"
Range("B6").Select
End Sub

Sub … End Sub – все макросы запускаемые через меню Сервис->Макрос->Макросы начинаются с ключевого слова Sub (процедура). Далее следует название процедуры «Макрос1», оно же имя нашего макроса которое указывается в момент начала записи. Пустые скобки обязательны! Следует учесть, что «запускаемая» процедура не должна содержать никаких параметров, иначе макрос исчезнет из списка. Все процедуры в VB завершаются командой End Sub . Sub имеет дополнительные ключевые слова Private и Public , определяющие зону видимости процедуры. Об этом будет рассказано в следующих статьях.

Range(" B3"). Select – эта и последующие команды были записаны когда мы выделяли ячейки B3, B4, B5.

ActiveCell.FormulaR1C1 – команда записывающая значение или формулу в выделенную ячейку после знака равенства. Данная запись присвоения ячейке значения и формулы не очень удобна. На следующих уроках мы будем использовать свойство Cells объекта рабочего листа Worksheet.

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

Второй способ, запись кода VBA вручную. Данный способ будет рассмотрен на следующем уроке и на всех последующих, будем работать только вторым способом.

Ну и напоследок, видео-демонстрация записи макроса.

Видео: Запись макроса в Excel

Сегодня я снова представляю вашему вниманию статью Чернякова Михаила Константинович , который расскажет о работе с макросами.

Поработав с документами Ехсеl 2010, вы можете обнаружить, что часто выполняете одни и те же задачи. Некоторые из них (такие как сохранение и закрытие файлов) выполняются достаточно бы-стро, другие включают последовательность шагов, что требует определенного времени и усилий. Вместо того чтобы каждый раз выполнять одни и те же действия вручную, можно создать МАКРО-СЫ , которые представляют собой программы, автоматизирующие выполнение заданной последовательности действий.

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

Создание макросов

1. Введите имя макроса, например, Таблица Умножения в предназначенное для этого поле.

2. Добавьте в сочетание клавиш букву «у».

3. В описание добавьте текст «Таблица умножения до 100 »

4. Щелк-ните на кнопке ОК .

5. Теперь можно выполнить действия, которые нужно записать в виде макроса, например, создайте таблицу умножения на 10.

6. В ячейку А2 введите 1, выделите эту ячейку и протяните на 9 ячеек вниз. При необходимости укажите в параметрах автозаполнения флажок Заполнить .

7. В ячейку В1 введите 1, выделите эту ячейку и протяните на 9 ячеек вправо. При необходимости укажите в параметрах автозаполнения флажок Заполнить .

8. Введите в ячейку В2 формулу: =$A2*B$1. Выделите ее и протяните сначала на 9 ячеек вниз, а затем выделенный столбец на 9 колонок вправо. Закончив, щелкните на стрелке кнопки Макросы на вкладке Вид Остановить запись

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

Удалить в разделе Ячейки на вкладке Вид , а затем щелкните на команде За-пись макроса .

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

3. Выделите диапазон ячеек А1:К11 .

Макросы в разделе Макросы на вкладке Главная , а затем щелкните на команде Удалить ячейки - Удалить со сдвигом вверх .

5. Щелкните на стрелке кнопки Макросы на вкладке Вид , а затем щелк-ните на команде Остановить запись .

Запуск, изменение и удаление макроса

Щелк-ните на стрелке кнопки Макросы в разделе Макросы на вкладке Вид , а затем щелкните на команде Макросы . Появится диалоговое окно Макрос .

1. Чтобы запустить Макрос и щелкнуть на кнопке Выполнить.

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

3. Если же требуется незначительное измене-ние, можно открыть макрос в редакторе VBA и внести изменения в код макроса. Для этого достаточно выде-лить его в диалоговом окне Макрос и щелкнуть на кнопке Изменить.

4. Чтобы удалить макрос, достаточно выде-лить его в диалоговом окне Макрос и щелкнуть на кнопке Удалить.

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

Редактирование макросов осуществляется средствами Microsoft Visual Basic for Applications (VBA ) . Редактор можно запустить командой Visual Basic в группе Код вкладки Разработчик или нажатием клавиш Alt + F 11 .

Добавление кнопок макросов на вкладки

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

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

2. Выделить вкладку, например, Разработчик , и нажать кнопку Добавить группу .

3. Нажать кнопку Переименовать и ввести в поле Отображаемое имя название новой группы, например, Макросы ОК .

4. Из списка Выбрать команды щелкнуть Макросы .

5. Найти макрос ТаблицаУмножения и кнопкой Добавить Макросы.

6. Нажать кнопку Переименовать и ввести в поле Отображаемое имя ТаблицаУмножения , выбрать символ и нажать кнопку ОК .

7. Найти макрос Очистить и кнопкой Добавить включить его в созданную группу Макросы.

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

9. После нажатия кнопки ОК диалогового окна Параметры Excel на вкладке Разработчик появиться группа Макросы с командами Таблица умножения и Очистить .

Рис. 4. Добавление кнопок макросов на вкладку Разработчик

Добавление кнопки макросов на панель быстрого доступа

Новый пользовательский интерфейс Ехсеl 2010 позволяет быстро находить встроенные команды, однако потребуется несколько се-кунд, чтобы запустить макрос с помощью диалогового окна Мак-рос . В Ехсеl 2010 имеется несколько способов сде-лать макросы более доступными.

Можно упростить доступ к диалоговому окну Мак-рос , добавив кнопку Макросы на панель быстрого доступа.

1. Для этого правой кнопкой щелк-ните на стрелке кнопки Макросы в разделе Макросы на вкладке Вид .

2. В контекстном меню щелкните на команде Добавить на панель быстрого доступа.

3. На панели быстрого доступа появится кнопка Макросы , щелчок на которой открывает диалоговое окно Макрос .

Рис. 5. Добавление кнопки макросов на панель быстрого доступа

Если вы предпочитаете выполнять макрос, не открывая диало-говое окно Макрос , можно добавить кнопку, запускающую макрос, на панель быстрого доступа. Это особенно удобно, если мак-рос автоматизирует задачу, которая часто выполняется. Чтобы до-бавить кнопку для макроса на панель быстрого доступа:

1. Щелкните на кнопке Настройка напели быстрого доступа в конце панели быстрого доступа.

2. Щелкните на Другие команды , чтобы отобразить страницу Настройка диалогового окна Параметры Ехсе l .

3. Щелкните на стрелке поля Выбрать команды из .

5. Щелкните на макросе, для которого нужно создать кнопку.

6. Щелкните на кнопке Добавить .

7. Щелкните на кнопке ОК .

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

Щелкните на команде Очистить группы Макросы вкладки Разработчик правой кнопкой мыши и из контекстного меню выберите Добавить на панель быстрого доступа.

Создание объектов для выполнения макросов

Назначение макросов фигурам позволяет создавать «кнопки» более сложной формы, чем те, которые отображаются на панели быстрого доступа. При желании можно даже разрабо-тать собственные макеты кнопок для различных объектов. Чтобы назначить макрос фигуре, щелкните на ней правой кнопкой мыши, а затем щелкните на команде Назначить макрос в контекстном меню. В диалоговом окне, которое появится, щелкни-те на нужном макросе, а затем щелкните на кнопке ОК .

При назначении макросов фигурам не следует из-менять имя макроса, которое отображается в диалоговом окне Назна-чить макрос объекту , поскольку оно содержит ссылку на объект. Изменение имени макроса разрывает эту связь и препятству-ет выполнению макроса.

Для запуска макроса ТаблицаУмножения можно создать фигуру в виде прямоугольника, а для Очистить - в виде элипса:

1. Щелк-ните на стрелке кнопки Фигуры в разделе Иллюстрации на вкладке Вставка , а затем щелкните на команде Скругленный прямоугольник .

2. Впишите фигуру Скругленный прямоугольник в диапазон ячеек М2:Р4 . Введите текст «Таблица умножения ». Установите размер шрифта введенного текста 18.

3. Щелкните на прямоугольнике правой кнопкой мыши, а затем щелкните на команде Назначить макрос ТаблицаУмножения , а затем щелкните на кнопке ОК .

4. Щелк-ните на стрелке кнопки Фигуры в разделе Иллюстрации на вкладке Вставка , а затем щелкните на команде Овал .

5. Впишите фигуру Овал в диапазон ячеек М7:Р10 . Введите текст «Очистка ». Установите размер шрифта введенного текста 18. Выровняйте надпись по центру. Измените цвет заливки фигуры на красный.

6. Щелкните на овале правой кнопкой мыши, а затем щелкните на команде Назначить макрос в контекстном меню. В диалоговом окне, которое появится, щелкни-те на макросе Очистить , а затем щелкните на кнопке ОК .

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

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

2. Макросы можно запускать, изменять, редактировать и удалять по мере необходимости. Для редактирования макросов необходимы знания алгоритмического языка Basic, оптимально Visual Basic for Applications. Последний позволяет управлять диалоговыми окнами и решать нестандартные задачи.

3. Кнопки запуска макросов можно располагать на вкладках, панели быстрого запуска или фигурах любых типов.

В Excel и Word макросы отлично помогают, когда надо срочно посмотреть конкретный кусок объектной модели, а под рукой нет интернета, например. Правда, чаще всего придется внимательно почистить полученный код от лишних "следов" перемещения по ячейкам и других необязательных действий. Но это того стоит. Запись и просмотр макроса займет минуты 2. А вот поиск некоторых видов информации (особенно, связанной с графиками) на MSDN в контексте объектной модели может длиться часами и не факт, что окажется результативным.

Ну, а для пользователей, не знакомых с VBA, макросы – вещь безусловно незаменимая и полезная.

Самый простой способ начать изучение макросов Excel состоит в их использовании для записи последовательности действий. Затем вы можете воспроизводить эту последовательность, то есть выполнять макрос.

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

Создание макроса

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

Активизируйте пустую ячейку.
Нажмите кнопку Запись макроса в левой части строки состояния. Появится окно Запись макроса .
Введите новое имя для макроса, состоящее из одного слова, чтобы заменить имя по умолчанию Макрос1 . Укажем МоеИмя .
Назначьте макросу сочетание клавиш Ctrl+Shift+N , введя букву N в верхнем регистре в поле для редактирования, помеченное как Сочетание клавиш .
Нажмите ОК , чтобы закрыть диалоговое окно Запись макроса и начать запись ваших действий.
Введите свое имя в выбранную ячейку и нажмите клавишу Enter .
Макрос завершен, поэтому нажмите кнопку Остановить запись в строке состояния.

Этот макрос очень простой. В большинстве случаев вы будете записывать больше действий, чем указано в этом примере.

Просмотр кода макроса

Макрос был записан в новый модуль VBA под названием Module1 . Если вам интересно, можете посмотреть на инструкции, которые были записаны. Для просмотра кода этого модуля вы должны открыть Visual Basic Editor (VBE). Нажмите Alt+F11 для переключения между окнами VBE и Excel.

В VBE окно Project отображает список всех открытых книг и надстройки. Этот список показан в виде древовидной диаграммы, которую можно сворачивать и разворачивать. Код, который вы записали ранее, хранится в Module1 текущей книги. Если вы дважды щелкнете на Modulel, то код модуля появится в окне Code (рис. 204.1).

Обратите внимание, что Excel вставил комментарии в верхней части процедуры. Это некоторая информация, которая появилась в окне Запись макроса . Строки с комментариями (которые начинаются с апострофа) необязательны, а их удаление не влияет на работу макроса.

Тестирование макроса

Перед тем как записать этот макрос, вы назначили ему сочетание клавиш Ctrl+Shift+N . Чтобы протестировать макрос, вернитесь в Excel, нажав Alt+F11 . Когда Excel активен, активизируйте лист (он может быть в книге, содержащей модуль VBA, или в любой другой книге). Выберите ячейку или диапазон ячеек и нажмите Ctrl+Shift+N . Макрос немедленно введет ваше имя в ячейку.

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