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

Написание макросов в Excel

Код макроса Excel написанный на языке Visual Basic for Application (VBA), а его выполняет инструмент приложения, к которому он присоединен. Большинство этих инструментов не доступно на уровне окна программы Excel. Как написать макрос.

Теперь продемонстрируем на примере информацию о том, как писать, редактировать и выполнять код макроса.

Чтобы написать макрос:

Примечание. Если в главном меню отсутствует закладка «РАЗРАБОТЧИК», тогда ее необходимо активировать в настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту». В правом списке «Основные вкладки:» активируйте галочкой опцию «Разработчик» и нажмите на кнопку ОК.

Возможности макросов в Excel

Макросы позволяют автоматизировать процессы работы с документами и не только… Они могут практически одновременно выполнить тысячи инструментов за одну операцию (даже по одному клику мышкой). Таким образом расширяются возможности работы с программой.

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

Если бы не было возможности создавать макросы во всех программах, входящих в пакет MS Office. То множество операций в процессе рутинной работы пользователям приходилось бы выполнять вручную (выделять через одну строку кликая по каждому второму заголовку мышкой или копировать вставлять по одному листу). Ручная работа в лучшем случаи приводить к потере огромного количества времени, а в худшем – ведет к ошибкам или даже потере ценных данных.

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

Excel предоставляет большой и изобильный арсенал инструментов для хранения и обработки огромного количества информации с данными. Хотя для самой универсальной аналитической программы Excel – хранение данных само по себе менее интересно, чем возможность их обрабатывать, структурировать и анализировать с презентацией в отчетах. Для этих целей служит сильнейший аналитических инструмент по обработке данных такой как «Сводные таблицы». Но и его можно еще более усовершенствовать с помощью макросов. И тогда возможности сводных таблиц не знают границ.

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

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

Конечно, зная Visual Baisic for Applications, вы можете создавать более сложные и совершенные приложения, однако для большинства пользователей достаточно уметь включить запись макроса, выключить её и знать, как его запустить.

Инструкция для начинающих

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

Теперь попробуем создать макрос. Для это на вкладке «Разработчик» нажмите кнопку «Записать макрос». Откроется диалоговое окно, в котором вам предложат заполнить ряд полей. Нам сейчас важны поля «Имя макроса» и «Сочетание клавиш».

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

Запустить полученный макрос можно несколькими способами. Вы можете нажать присвоенное макросу ранее сочетание клавиш, выполнение макроса начнётся незамедлительно. Ещё вы можете нажать кнопку «Макросы» на вкладке «Разработчик». Перед вами откроется список доступных макросов, выберите нужный по имени и запустите его.

Выполнение макросов можно присваивать элементам управления формы, картинкам и другим объектам. Для этого по объекту следует кликнуть правой кнопкой мыши и выбрать «Назначить макрос».

Макросы могут иметь огромную функциональность и значительно расширять возможности пользователя, но для этого придётся серьёзно потрудиться над их изучением. Однако простейшие макросы вы можете писать уже сейчас.

Многие из Вас наверняка слышали термин «Макрос». Иногда при запуске некоторых документов Word, а чаще Excel, Вам выскакивало предупреждение: «Данный документ содержит макросы, которые могут нанести вред Вашему компьютеру. С целью безопасности макросы были отключены» и т.д. И многих это предупреждение пугает, хотя большая доля правды в этом предупреждении все ж присутствует. Но, это относится к документам, попавшим к Вам из неизвестных источников, а именно из сети, по почте и т.д. Чем же так опасен макрос и для чего они тогда нужны, если их использование небезопасно? Рассмотрим далее, а пока дадим определение макросу.

Что такое «Макрос»?

Макрос это программа, которая содержит набор инструкций выполняющих какие либо действия, как автоматически, так и по требованию пользователя, т.е. выполняются явно и неявно. Макрос от сокращенного «макрокоманда ». Широкое распространение данное понятие получило благодаря корпорации Microsoft, которая реализовала возможность написания макросов в своих продуктах, а именно всеми любимый и известный Office(Word, Excel, Access и т.д.). Используемый язык в макросах - Visual Basic, так же продукт Microsoft, но с некоторыми ограничениями.

Как работают макросы?

Благодаря внедрению в MS Office языка Visual Basic, Microsoft удалось значительно расширить функционал своих продуктов. Макрокоманды, записанные пользователями или написанные программистами, способны автоматизировать большинство процессов и значительно сократить время на обработку данных. Тем самым, оптимизировать рабочее время сотрудников. Для выполнения макрокоманд необходима своя программная среда, в которой эти команды способны работать. Такой средой являются, например Excel, Word, Access. При написании сложных макросов, их запуск и выполнение, как правило, ограничивается рабочей станцией (ПК) на которой он был написан. Сложные, имеется ввиду, подключение дополнительных компонентов и объектов, которые попросту могут отсутствовать на другом ПК. Происходит это потому, что программный код не переводится в исполняемый машинный код и вдобавок к которому прикладываются необходимые модули и библиотеки, а выполняется через интерпретатор . Но, есть и преимущества в таком подходе, а именно, возможность произвести отладку сразу, «на месте», при возникновении каких либо ошибок, просмотреть код и дополнить его новыми инструкциями, расширив функционал макроса. Как правило, макросы написанные для Excel, не работают в Word или Access, только если не используется базовый набор процедур и функций, которые не затрагивают объекты приложения.

Как макросы помогают в работе?

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

Это один из многих вариантов применения. Другой пример, на личном опыте. При работе с ежедневными накопительными данными, мне приходилось заполнять поле «Контрагент» (КА) соответствующим именем, имеющимся в базе. Название КА должно обязательно быть идентичным. Создание списка очень неудобно т.к. список состоит не из одной сотни КА. Я написал макрос, который запускается на исполнение, при нажатии в соответствующей колонке, правой кнопкой мыши. Открывается форма, со списком КА в которой также имеется «горячий поиск» в списке т.е. достаточно начать вводить сочетание символов содержащихся в названии и список тут же отфильтрует и оставит подходящих, далее остается выбрать из двух трех КА нужного. На все про все, не больше 5 сек. Вот как вариант. И примеров можно приводить кучу, в зависимости от специфики работы.

В чем заключается опасность макросов?

Как я уже говорил выше, макросы могут быть как полезны, так и опасны. Все зависит от того с какой целью и кем он был написан. В чем заключается опасность? Так как в написании макросов используется язык Visual Basic, то располагая всем его функционалом, злоумышленник способен написать на этом языке вирус – «макровирус». Макровирус способен повредить все данные, способен работать как программа шпион и т.д. Время жизни такого вируса - пока запущено приложение(Excel, Word и т.д.). После внедрения такого вируса, в дальнейшем, его запуск будет осуществляться в скрытом от пользователя режиме. Макросы так же способны выступать в роли посредника для внедрения более серьезных угроз всей системе. Например, активировав макрос, произойдет копирование или генерирование троянской программы, которая работает под управлением операционной системы, с последующим ее запуском. А это уже угроза работе всей системе.

Варианты защиты от таких макросов:

• Повысить уровень безопасности Excel (способ не удобен т.к. и полезные макросы не будут работать);
• Не открывать файлы из неизвестных источников и не игнорировать предупреждения безопасности при открытии таких файлов;
• Если установлена операционная система Windows 7, не допускать запуск приложений с подписью «неизвестный источник».

На этом я завершаю вводную статью по макросам. В дальнейшем, я расскажу и покажу способы записи макросов в Excel. Постараюсь подбирать интересные и полезные примеры, а так же рассмотрим, каким потенциалом обладает Excel, если посмотреть на него глазами программиста.

P. S. Используя смекалку и фантазию на VBA можно написать простейшие и интересные игры. Если Вас заинтересовал такой вариант применения, то можете ознакомиться с играми, написанными для Excel ().

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

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

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье — Выгрузка данных из Access в шаблон Word и Excel . Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

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

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

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

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные .

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон .

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»

Свои поля я назвал следующим образом:

• ФИО – fio;
• № — number;
• Должность – dolgn;
• Адрес проживания – addres;
• Тел. № сотрудника – phone;
• Комментарий – comment.

Код макроса на VBA Excel

Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

После у Вас откроется окно редактора кода, где собственно мы и будем писать свой код VBA. Ниже представлен код, я его как обычно подробно прокомментировал:

Sub Карточка() "Книга NewBook = "" " Путь, где будут храниться наши карточки " Т.е. в той папке, откуда запустился файл с макросом Path = ThisWorkbook.Path " Выбираем лист с данными Sheets("Данные").Select " Запускаем цикл, скажем на 100000 итераций " Начиная со второй строки, не учитывая заголовок For i = 2 To 100000 " Выйдем из него, когда фамилии закончатся, т.е. строки If Cells(i, 1).Value = "" Then i = 100000 Exit For End If " Имя файла карточки, назовем по фамилии Name_file = Path & "\" & Sheets("Данные").Cells(i, 1).Value & ".xls" ‘Выбираем лист с шаблоном Sheets("Шаблон").Select " Присваиваем значения нашим ячейкам, по именам которые мы задавали Range("fio").Value = Sheets("Данные").Cells(i, 1).Value & " " & _ Sheets("Данные").Cells(i, 2).Value & " " & Sheets("Данные").Cells(i, 3).Value Range("number").Value = Sheets("Данные").Cells(i, 4).Value Range("addres").Value = Sheets("Данные").Cells(i, 5).Value Range("dolgn").Value = Sheets("Данные").Cells(i, 6).Value Range("phone").Value = Sheets("Данные").Cells(i, 7).Value Range("comment").Value = Sheets("Данные").Cells(i, 8).Value " Копируем все Cells.Select Selection.Copy " Создаем новую книгу или делаем ее активной If NewBook = "" Then Workbooks.Add NewBook = ActiveWorkbook.Name Else Workbooks(NewBook).Activate Cells(1, 1).Select End If " Вставляем данные в эту книгу Application.DisplayAlerts = False ActiveSheet.Paste Application.CutCopyMode = False " Сохраняем с нашим новым названием ActiveWorkbook.SaveAs Filename:= _ Name_file, FileFormat:=xlExcel8, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False NewBook = ActiveWorkbook.Name Application.DisplayAlerts = True " Снова активируем файл с макросом и выбираем лист Workbooks("Макрос.xls").Activate Sheets("Данные").Select " Переходим к следующей строке Next i " Закроем книгу Workbooks(NewBook).Close " Выведем сообщение об окончании MsgBox ("Выполнено!") End Sub

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

Следующие простые примеры макросов 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