ВходИспользование макросов в excel. Что такое макрос и где его искать? Объекты, коллекции, свойства и методы
Статья предназначена для людей, которые хотят научиться писать программы на встроенном в Excel Visual Basic (VBA), но абсолютно не знают что это такое.
Для начала - несколько слов о том, зачем это нужно. Средство VBA в MS Excel, представляет нам универсальный инструмент для быстрого и точного решения любых индивидуальных пользовательских задач в MS Excel
. Можно конечно использовать и встроенные в MS Excel функции которых великое множество, однако они далеко не всегда решают поставленную задачу.
Итак, создадим для примера простейшую программу. Будем использовать MS Excel 2007. Откройте MS Excel, нажмите "сохранить как" и сохраните файл Вашей программы нажав "Книга ексель с поддержкой макросов".
Далее необходимо включить вкладку "Разработчик". Для этого нажимаем "Параметры Excel" 
Ставим галочку на "Показывать вкладку "Разработчик" на ленте"
После этого на ленте, в верху листа Excel, появится вкладка "Разработчик", которая содержит в себе инструменты для создания VBA макросов.
Представим себе небольшую задачу - допустим мы имеем 2 числа, нам необходимо их сложить и по полученной сумме получить значение из нашей таблицы.
Поставим в ячейки Листа1 следующие значения:
на Листе2 заполним ячейки, создав таблицу из 2 столбцов
Далее перейдем на Лист1, нажмем на вкладку "Разработчик", "Вставить", на ней выберем кнопку 
и нарисуем кнопку на Листе1, после чего сразу появится окно "Назначить макрос объекту", в котором выбираем "Создать" 
После этого откроется редактор Visual Basic, и автоматически напишется наименование процедуры, которая будет выполняться при нажатии кнопки. Под названием процедуры впишем следующий код:
Код выполнит следующие действия:
- MsgBox ("Это мой первый Макрос!") - сообщение
- Переменной q присваивается значение ячейки на Листе1, с координатами 2 строка, 2 столбец
- Переменной w присваивается значение ячейки на Листе1, с координатами 3 строка, 2 столбец
- В ячейку на Листе1, с координатами 4 строка, 2 столбец, записывается сумма q+w
Далее получим значение столбца В из Листа2, которое расположено на той же строке где значение нашей суммы совпадает с значением столбца А.
Введем следующий код:
и получим при нажатии на кнопку следующий результат:
из результата видно что макрос подобрал число из таблицы на Листе2 в соответствии с нашей суммой.
Не буду вдаваться в подробности этого хитрого кода, так как цель данной статьи - начать писать макросы. Для VBA в интернете есть масса ресурсов, с примерами и разъяснениями, хотя для автоматизации расчетов вполне хватит объема информации в справке.
Таким образом с помощью VBA возможно автоматизировать расчет любой сложности и последовательности. Справочные таблицы можно копировать из различной литературы на отдельные листы Excel и писать последовательный расчет с кнопками.
Макросы позволяют существенно расширить возможности в программе Excel. Они автоматизируют рабочие процессы и берут большую часть рутинной работы пользователя на себя. Просто нужно научится пользоваться макросами и производительность труда возрастет в десятки раз!
Вам даже не нужно быть программистом и знать язык программирования «VBA» чтобы создавать свои макро-программы с помощью инструмента записи макросов.
Сначала надо включить панель разработчика. Для этого в меню «Файл» открываем группу опций «Параметры». В появившемся окне «Параметры Excel» открываем группу «Настройка ленты». Обратите внимание на правую колонку настроек под аналогичным названием «Настройка ленты». В ней следует отметить галочкой опцию «Разработчик» как показано ниже на рисунке:
Теперь нам доступна на ленте новая закладка «Разработчик» со всеми своими инструментами для автоматизации работы в Excel и создания макросов.
Применение VBA и макросов в Microsoft Excel
Макросы – это внутренние приложения, которые берут на себя всю рутинную работу, облегчая жизнь пользователю. Каждый пользователь может создать макрос без знания языков программирования. Для этого существует макрорекодер, который запускается с помощью кнопки «Запись макроса».
В этом режиме все действия пользователя макрорекодер в Excel записывает, переводя на язык программирования VBA-код в автоматическом режиме. После завершения записи мы получаем готовую программу, которая сама выполняет те действия, которые выполнял пользователь при записи.
Как записать готовый макрос в Excel? Очень просто:
С помощью макропрограмм можно увеличить производительность труда пользователя в десятки раз. Но чтобы использовать запись пользовательских макросов на все 100% следует соблюдать простые правила, которые существенно влияют на их качество в момент записи и эффективность при выполнении.
Как работать с макросами в Excel
5 простых советов, которые помогут в создании макросов без программирования. Воспользуйтесь этими простыми советами, которые позволяют быстро и просто создавать качественные макропрограммы автоматически:
1 Правильные имена в макросах.
Присваивайте макросам короткие, но содержательные имена. Когда вы войдете вкус, со временем вам придется создавать много макросов. При выборе в диалоговом окне их легче найти с короткими и содержательными названиями. Система VBA предоставляет вам возможность указать описание к имени. Обязательно используйте ее.
Имя макроса обязательно должно начинаться с букв и не может содержать пробелы, символы или знаки препинания. После первого символа, вы можете использовать больше букв, цифр или нижнее подчеркивание, но максимальная длина имени составляет 80 символов.
2 Используйте относительные (не абсолютные) адреса ячеек
Абсолютный адрес ячейки – это точное местонахождение курсора, когда информация о его размещении записывается в макро-адреса с жесткой привязкой к конкретной ячейке в момент записи. Абсолютные адреса ограничивают возможности макроса, если будут добавляться / удаляться данные на листе Excel или список данных будет становиться больше. Относительные средства не привязывают курсор к конкретному адресу ячейки.
По умолчанию в Excel включен режим «Абсолют», но вы можете изменить его, включив кнопку «Относительные ссылки» расположенную ниже под кнопкой «Запись макроса» на панели инструментов вкладки «Разработчик»:
3 Всегда начинайте запись с курсором в A1
Абсолютный отсчет ячеек, всегда ведется с исходного положения (адрес ячейки А1) – до адреса курсора с вашими данными. Если вы сохранили ваш макрос в книге личных макросов (рекомендуется так и делать), то вы можете использовать свою программу на других листах с аналогичными данными. Независимо от того, где ваш курсор позиционируется, когда вы начинаете запись макроса! Даже если он уже находится в ячейке A1, ваш первый макрос лучше записывать после нажатия клавиш должны быть Ctrl + Home.
Пример: Представьте себе, что каждый месяц вы получаете десятки таблиц из всех филиалов. От вас требуется организовать данные и рассчитать показатели, чтобы произвести еще один ежемесячный отчет. Вы можете записать макрос для выполнения всех этих функций, в том числе открытие и соединение всех листов в одну комбинированную таблицу.
4 Всегда перемещаться с клавиш направления в момент записи макроса
Используйте кнопки со стрелками для управления курсором (Ctrl + Up, и т.п.). Позиционируйте курсор, так чтобы вы могли добавить, изменить или удалить данные внутри таблицы по мере необходимости.
Использование мыши для навигации является более сложным и не так надежным в момент записи. Когда дело доходит до макросов, использовать мышь лучше только для вызова меню.
5 Создавайте макросы для конкретных небольших задач
Держите ваши макросы для небольших специфичных задач. Чем больше программный код в макросе, тем медленнее он работает, особенно если это требуется для выполнения многих функций или рассчитать много формул в большой электронной таблице.
Если вы запустите каждый процесс отдельно, вы можете быстро просмотреть результаты для проверки точности их выполнения.
Если нельзя разбить длинный макрос на короткие приложения, а требуется проверить его функциональность пошагово («отладкой»). Нажмите клавишу F8 каждый раз, когда вы хотите перейти к следующему шагу выполнения задачи. Процесс выполнения программы останавливается, когда он видит ошибку. Вы можете исправить ошибку, которую легко найти с помощью «отладки» или записать по-новому.
Также как и в других приложениях Microsoft Office , есть такая интересная штука как макрос. Если в двух словах, то макрос – это программируемая последовательность действий. Создать его можно двумя способами:
1. С помощью соответствующего пункта меню;
2. Вручную.
Первый способ очень простой и не требует специальных навыков программирования. Но прежде чем приступать к созданию макроса, необходимо поковырять настройки безопасности. Заходим в Файл/Параметры/Центр управления безопасностью/Параметры центра управления безопасностью/Параметры макросов . Здесь необходимо выбрать Включить все макросы .
Теперь все готово, и мы переходим непосредственно к созданию макроса. Открываем вкладку Разработчик и нажимаем .
Появится окно, в котором нужно указать имя макроса, назначить клавишу для быстрого запуска, а также выбрать книгу, в которую необходимо сохранить макрос.
После нажатия кнопки ОК начинается запись макроса. Все что вы будете делать, будет записываться. К примеру, можно создать таблицу и отредактировать ее – задать нужное количество строк, столбцов, их ширину. Затем, чтобы остановить запись, нужно нажать соответствующую кнопку.
Теперь, чтобы запустить наш макрос, нужно на вкладке Разработчик нажать Макросы .
В появившемся окне находим созданный нами макрос и нажимаем Выполнить . Также можно использовать комбинацию клавиш, которую мы вводили при создании макроса.
Преимуществ у данного способа создания макросов не очень много. В большинстве случаев такой макрос можно применить только в текущей книге и для какой-то конкретной ситуации. Гораздо эффективнее использовать макрос, написанный при помощи языка программирования Visual Basic . Однако создание такого макроса требует определенных навыков. Пример создания макроса на VBA рассмотрен .
Е сли в программе MS Excel вы часто выполняете сходные действия, то облегчить вам жизнь помогут макросы.
Макрос в Excel — это работающая внутри Excel программа, зачастую созданная самим пользователем, которая помогает автоматизировать выполнение частых действий. Огромный плюс макросов в Excel — для их создания не требуется знание языков программирования.
Конечно, зная Visual Baisic for Applications, вы можете создавать более сложные и совершенные приложения, однако для большинства пользователей достаточно уметь включить запись макроса, выключить её и знать, как его запустить.
Инструкция для начинающих
Для удобства работы с макросами надо включить вкладку «Разработчик» на ленте. Для это зайдите в «Параметры» программы», там выберите вкладку «Настройка ленты» и в разделе «Основные вкладки» добавьте вкладку «Разработчик».
Добавление панели «Разработчик»
Теперь попробуем создать макрос. Для это на вкладке «Разработчик» нажмите кнопку «Записать макрос». Откроется диалоговое окно, в котором вам предложат заполнить ряд полей. Нам сейчас важны поля «Имя макроса» и «Сочетание клавиш».
Дайте макросу имя и присвойте сочетание клавиш для вызова. После нажатия кнопки «OK» начнётся запись макроса. Проделайте набор различных действий (например, введите текст в ячейку). После этого нажмите «Остановить запись». Макрос записан.
Запустить полученный макрос можно несколькими способами. Вы можете нажать присвоенное макросу ранее сочетание клавиш, выполнение макроса начнётся незамедлительно. Ещё вы можете нажать кнопку «Макросы» на вкладке «Разработчик». Перед вами откроется список доступных макросов, выберите нужный по имени и запустите его.
Выполнение макросов можно присваивать элементам управления формы, картинкам и другим объектам. Для этого по объекту следует кликнуть правой кнопкой мыши и выбрать «Назначить макрос».
Макросы могут иметь огромную функциональность и значительно расширять возможности пользователя, но для этого придётся серьёзно потрудиться над их изучением. Однако простейшие макросы вы можете писать уже сейчас.
Следующие простые примеры макросов 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