Макросы в Excel — Инструкция по использованию. VBA Excel: примеры программ. Макросы в Excel

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

Что такое VBA

Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.

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

Недостатком программы являются проблемы, связанные с совместимостью различных версий. Они обусловлены тем, что код программы VBA обращается к функциональным возможностям, которые присутствуют в новой версии продукта, но отсутствуют в старой. Также к минусам относят и чрезмерно высокую открытость кода для изменения посторонним лицом. Тем не менее Microsoft Office, а также IBM Lotus Symphony позволяют пользователю применять шифрование начального кода и установку пароля для его просмотра.

Объекты, коллекции, свойства и методы

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

Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.

Что касается понятия "коллекция", то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.

Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в "Эксель" используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами

Вместе с ней нередко используется Selection.ClearContents. Ее выполнение означает очистку содержимого выбранной ячейки.

Как начать

Затем необходимо перейти в приложение VB, для чего достаточно воспользоваться комбинацией клавиш «Alt» и «F11». Далее:

• в строке меню, расположенном в верхней части окна, нажимают на иконку рядом с иконкой Excel;
• выбирают команду Mudule;
• сохраняют, нажав на иконку с изображением ;
• пишут, скажем так, набросок кода.

Он выглядит следующим образом:

Sub program ()

"Наш код

Обратите внимание, что строка «"Наш код» будет выделена другим цветом (зеленым). Причина в апострофе, поставленном в начале строки, который обозначает, что далее следует комментарий.

Теперь вы можете написать любой код и создать для себя новый инструмент в VBA Excel (примеры программ см. далее). Конечно, тем, кто знаком с азами Visual Basic, будет намного проще. Однако даже те, кто их не имеет, при желании смогут освоиться достаточно быстро.

Макросы в Excel

За таким названием скрываются программы, написанные на языке Visual Basic for Application. Таким образом, программирование в Excel — это создание макросов с нужным кодом. Благодаря этой возможности табличный процессор Microsoft саморазвивается, подстраиваясь под требования конкретного пользователя. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению конкретных примеров программ VBA Excel. Лучше всего начать с самых элементарных кодов.

Пример 1

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

Для этого:

• открывают вкладку «Вид»;
• переходят на пиктограмму «Макросы»;
• жмут на «Запись макроса»;
• заполняют открывшуюся форму.

Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.

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

• вновь переходят на строку «Макросы»;
• в списке выбирают «Макрос 1»;
• нажимают «Выполнить» (то же действие запускается начатием сочетания клавиш «Ctrl+hh»).

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

Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.

Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.

Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.

Пример 2

Циклы VBA помогают создавать различные макросы в Excel.

Циклы VBA помогают создавать различные макросы. Предположим, что имеется функция y=x + x 2 + 3x 3 - cos(x). Требуется создать макрос для получения ее графика. Сделать это можно только, используя циклы VBA.

За начальное и конечное значение аргумента функции берут x1=0 и x2=10. Кроме того, необходимо ввести константу — значение для шага изменения аргумента и начальное значение для счетчика.

Все примеры макросов VBA Excel создаются по той же процедуре, которая представлена выше. В данном конкретном случае код выглядит, как:

Sub programm()

shag = 0.1

Do While x1 < x2 (цикл будет выполняться пока верно выражение x1 < x2)

y=x1 + x1^2 + 3*x1^3 - Cos(x1)

Cells(i, 1).Value = x1 (значение x1 записывается в ячейку с координатами (i,1))

Cells(i, 2).Value = y (значение y записывается в ячейку с координатами (i,2))

i = i + 1 (действует счетчик);

x1 = x1 + shag (аргумент изменяется на величину шага);

End Sub.

В результате запуска данного макроса в "Эксель" получаем два столбца, в первом из которых записаны значения для x, а во втором — для y.

Затем по ним строится график способом, стандартным для "Эксель".

Пример 3

Для реализации циклов в VBA Excel 2010, как и в других версиях, наряду с уже приведенной конструкцией Do While используется For.

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

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

For i = 1 to 10 Next

Команда переводится на «человеческий» язык, как «Повторять от 1 до 10 с шагом один».

Если ставится задача получить столбец с квадратами, например, всех нечетных чисел из диапазона от 1 до 11, то пишем:

For i = 1 to 10 step 1 Next.

Здесь step — шаг. В данном случае он равен двум. По умолчанию отсутствие этого слова в цикле означает, что шаг единичный.

Полученные результаты нужно сохранять в ячейки с номером (i,1). Тогда при каждом запуске цикла с увеличением i на величину шага автоматически будет расти и номер у строки. Таким образом, произойдет оптимизация кода.

В целом код будет выглядеть, как:

Sub program()

For i = 1 To 10 Step 1 (можно записать просто For i = 1 To 10)

Cells(i, 1).Value = i ^ 2 (т.е. в ячейку (i,1) записывается значение квадрата i)

Next (в некотором смысле играет роль счетчика и означает еще один запуск цикла)

End Sub.

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

Пример 4

В повседневной жизни сплошь и рядом возникает необходимость принять то или иное решение в зависимости от какого-то условия. Не обойтись без них и в VBA Excel. Примеры программ, где дальнейший ход выполнения алгоритма выбирается, а не предопределен изначально, чаще всего используют конструкцию If …Then (для сложных случаев) If …Then …END If.

Рассмотрим конкретный случай. Предположим, необходимо создать макрос для "Эксель", чтобы в ячейку с координатами (1,1) было записано:

1, если аргумент положительный;

0, если аргумент нулевой;

-1, если аргумент отрицательный.

Создание такого макроса для "Эксель" начинается стандартным способом, через использование «горячих» клавиш Alt и F11. Далее записывается следующий код:

Sub program()

x= Cells(1, 1).Value (эта команда присваивает x значение содержимого ячейки с координатами (1, 1))

If x>0 Then Cells(1, 1).Value = 1

If x=0 Then Cells(1, 1).Value = 0

If x<0 Then Cells(1, 1).Value = -1

End Sub.

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

Функции VBA

Как вы уже могли заметить, программировать в самом известном табличном процессоре Microsoft не так уж сложно. Особенно, если научиться применять функции VBA. Всего в этом языке программирования, созданном специально для написания приложений в "Эксель" и Word, около 160 функций. Их можно разделить на несколько больших групп. Это:

• Математические функции. Применив их к аргументу, получают значение косинуса, натурального логарифма, целой части и пр.
• Финансовые функции. Благодаря их наличию и используя программирование в Excel, можно получать эффективные инструменты для ведения бухгалтерского учета и осуществления финансовых расчетов.
• Функции обработки массивов. К ним относятся Array, IsArray; LBound; UBound.
• Функции VBA Excel для строки. Это достаточно многочисленная группа. В нее входят, например, функции Space для создания строки с числом пробелов, равных целочисленному аргументу, или Asc для перевода символов в код ANSI. Все они имеют широкое применение и позволяют работать со строками в "Эксель", создавая приложения, значительно облегчающие работу с этими таблицами.
• Функции преобразования типа данных. Например, CVar возвращает значение аргумента Expression, преобразовав его в тип данных Variant.
• Функции работы с датами. Они значительно расширяют стандартные Так, функция WeekdayName возвращает название (полное или частичное) дня недели по его номеру. Еще более полезной является Timer. Он выдает число секунд, которые прошли с полуночи до конкретного момента дня.
• Функции для преобразования числового аргумента в разные системы счисления. Например, Oct выдает в восьмеричное представление числа.
• Функции форматирования. Важнейшей из них является Format. Она возвращает значение типа Variant с выражением, отформатированным согласно инструкциям, которые заданы в описании формата.
• и пр.

Изучение свойств этих функций и их применение позволит значительно расширить сферу применения "Эксель".

Пример 5

Попробуем перейти к решению более сложных задач. Например:

Дан бумажный документ отчета фактического уровня издержек предприятия. Требуется:

• разработать его шаблонную часть посредством табличного процессора "Эксель";
• составить программу VBA, которая будет запрашивать исходные данные для ее заполнения, осуществлять необходимые расчеты и заполнять ими соответствующие ячейки шаблона.

Рассмотрим один из вариантов решения.

Создание шаблона

Все действия осуществляются на стандартном листе в Excel. Резервируются свободные ячейки для внесения данных по названию компании-потребителя, сумме издержек, их уровня, товарооборота. Так как количество компаний (обществ), относительно которых составляется отчет, не зафиксировано, ячейки для внесения значений по итогам и ФИО специалиста заранее не резервируют. Рабочему листу присваивается новое название. Например, "Օтчет".

Переменные

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

• NN- номер текущей строки таблицы;
• TP и TF - планируемый и фактический товарооборот;
• SF и SP - фактическая и планируемая сумма издержек;
• IP и IF - планируемый и фактически уровень издержек.

Обозначим теми же буквами, но с «приставкой» Itog накопление итога по данному столбцу. Например, ItogTP - касается столбца таблицы, озаглавленного, как «планируемый товарооборот».

Решение задачи с использованием программирования на VBA

Используя введенные обозначения, получаем формулы для отклонений. Если требуется осуществить расчет в % имеем (F - P) / P * 100, а в сумме — (F - P).

Результаты этих вычислений можно лучше всего сразу внести в соответствующие ячейки таблицы "Эксель".

Для итогов по факту и прогнозу получают по формулам ItogP=ItogP + P и ItogF=ItogF+ F.

Для отклонений используют = (ItogF - ItogP) / ItogP * 100, если расчет ведется в процентах, а в случае суммарной величины — (ItogF - ItogP).

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

Перед запуском созданной программы, требуется сохранить рабочую книгу, например, под названием "Отчет1.xls".

Клавишу «Создать отчетную таблицу» требуется нажать всего 1 раз после ввода заголовочной информации. Следует знать и другие правила. В частности, кнопка «Добавить строку» должна нажиматься каждый раз после ввода в таблицу значений по каждому виду деятельности. После занесения всех данных требуется нажать кнопку «Закончить» и затем переключиться в окно "Эксель".

Теперь вы знаете, как решать задачи для Excel с помощью макросов. Умение применять vba excel (примеры программ см. выше) может понадобиться и для работы в среде самого популярного на данный момент текстового редактора "Ворд". В частности, можно путем записи, как показано в самом начале статьи, или через написание кода создавать кнопки меню, благодаря которым многие операции над текстом можно будет осуществлять нажатием дежурных клавиш или через вкладку "Вид" и пиктограмму "Макросы".

Макрос для выделения на каждом листе в активной книге ячейки А1. При этом также происходит перемещение экрана.

Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Select ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Select Next Sheets(1).Select Application.ScreenUpdating = True End Sub

Макрос для копирования текущего листа заданное количество раз. Полезен для тестирования каких то макросов — внесли правки, проверили на копии данных. Закончились копии — запустили макрос еще раз

Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Введите число копий текущего листа") Application.ScreenUpdating = False For j = 1 To i ActiveSheet.Copy after:=Sheets(Sheets.Count) ActiveSheet.Name = "Копия " & j Next j Application.ScreenUpdating = True End Sub

Создание листов с названиями из указанного диапазона на листе

Sub CreateFromList() Dim cell As Range For Each cell In Selection Sheets.Add after:=Sheets(Sheets.Count) ActiveSheet.Name = cell.Value Next cell End Sub

Маркрос отправки письма с задержкой. Доработанный макрос из книги Джона Уокенбаха Профессиональное программирование на VBA

Sub ОтправкаПисьма() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = "[email protected]" .Subject = "Отчет по продажам" .Attachments.Add "C:\Test.txt" .Body = "Текст письма" .DeferredDeliveryTime = Replace(Date, ".", "/") & " 11:00:00" .send ".Display для формирования письма и его открытия End With On Error GoTo 0 Set OutMail = Nothing cleanup: Set OutApp = Nothing End Sub

Немного доработанный макрос оглавления от Николая Павлова .
Если в книге уже существует лист «Оглавление» — макрос предлагает его удалить. Если нет — создает лист «Оглавление» и вставляет ссылки с названиями листов

Sub TableOfContent() Dim sheet As Worksheet Dim cell As Range Dim Answer As Integer Application.ScreenUpdating = False With ActiveWorkbook For Each Worksheet In ActiveWorkbook.Worksheets If Worksheet.Name = "Оглавление" Then Answer = MsgBox("В книге есть лист с именем Оглавление. Удалить его?", vbYesNo) If Answer = vbNo Then Exit Sub If Answer = vbYes Then Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array(1)).Select Sheets.Add Sheets(1).Name = "Оглавление" With ActiveWorkbook For Each sheet In ActiveWorkbook.Worksheets If sheet.Name <> "Оглавление" Then Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:=""" & sheet.Name & """ & "!A1" cell.Formula = sheet.Name End If Next sheet End With Rows("1:1").Delete Application.ScreenUpdating = True End Sub

Сортировка листов от мастеров VBA . Макрос сортирует в том числе и скрытые листы. Не сработает, если у книги защищена структура

Sub СОРТИРОВАТЬ_ВСЕ_ЛИСТЫ() Application.ScreenUpdating = False: Application.EnableEvents = False Dim iSht As Worksheet, oDict As Object, i%, j% Set oDict = CreateObject("Scripting.Dictionary") " запомнить состояние видимости каждого из листов и сделать все видимыми For Each iSht In ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " сортировка видимых листов For i = 1 To .Sheets.Count - 1 For j = i + 1 To .Sheets.Count If UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Then .Sheets(j).Move Before:=.Sheets(i) Next j Next i End With " восстановить исходное состояние видимости каждого из листов For Each iSht In ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Next Application.EnableEvents = True: Application.ScreenUpdating = True End Sub

Импорт столбцов «Поле1» и «Поле2» из листа «Лист1» файла Excel «C:\Manager.xls» через ADODB подключение и вставка содержимого начиная с ячейки A1 текущего листа

VBA считается стандартным языком написания сценариев для приложений Microsoft, и в настоящее время он входит в состав всех приложений Office и даже приложений других компаний. Следовательно, овладев VBA для Excel, вы сможете сразу перейти к созданию макросов для других программных продуктов Microsoft. Более того, вы сможете создавать полноценные программные продукты, одновременно использующие функции самых разных приложений.

Как включить макросы в Excel

По умолчанию, вкладка, отвечающая за управление и навигацию макросов в Excel, скрыта. Чтобы активировать данную опцию перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры Excel, перейдите по вкладке Настройка ленты, в правом поле со списком ставим маркер напротив вкладки Разработчик. Данные действия актуальны для версий Excel 2010 и старше.

На ленте появиться новая вкладка Разработчик с элементами управления автоматизации Excel.

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

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

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

Теперь вы можете посмотреть список всех созданных макросов, нажав на кнопку Макрос, находящуюся в группе Код. В появившемся диалоговом окне вы можете дать более описательные имена своим кодам или задать сочетания клавиш, которые бы запускали тот или иной макрос. Альтернативным вариантом запуска данного окна является нажатие клавиш Alt + F8.

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

Поздравляю! Вы написали свой первый макрос. Логичным будет проверить теперь, какой же код сгенерировал нам Excel. Сгенерированный код написан на языке VBA (Visual Basic for Applications). Чтобы увидеть его, нужно открыть Редактор VB (VBE), который запускается нажатием клавиш Alt + F11 или кнопкой Visual Basic на вкладке Разработчик.

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

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

Для просмотра кода, щелкните по ветке Modules в окне проектов и дважды щелкните по появившейся ветке Module 1 . Редактор откроет окно с кодом, как изображено на картинке.

Здесь можно редактировать сгенерированный код, который был записан при работе в Excel. К примеру, вам требуется заполнить определенный столбец значениями от 1 до 10. У вас уже есть первые три шага, которые вводят значения 1, 2 и 3 в первые три ячейки столбца А. Нам необходимо дописать оставшиеся семь шагов.

Если вы посмотрите на приведенный выше код, вы увидите, что макрос определенным образом структурирован. Сначала приложение перемещает курсор на ячейку с помощью команды Range("A1").Select, затем редактирует его содержимое с помощью ActiveCell.FormulaR1C1 = "1". Таким образом, для оставшихся шагов мы можем повторить эти действия, меняя адрес ячейки и значение, которое вы хотите записать в эту ячейку. Например, чтобы задать ячейке A4 значение 4, вы должны написать:

И повторить аналогичные шаги для оставшихся значений.

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

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

Увеличение скорости выполнения макросов Excel

Пока все хорошо. Давайте разберем пару хитростей, которые помогут ускорить выполнение макроса. Рассмотрим в качестве примера фрагмент кода, приведенный выше. Современные компьютеры отработают рассматриваемый код так быстро, что вы даже не заметите этого. Но что если вам необходимо выполнить операцию 50000 раз. Это займет какое-то время. Если макрос, написанный вами, исчисляется сотнями строк, ускорить выполнение кода можно путем обрезки части процессов, который не используется во время исполнения макроса.

Использование команды Application.ScreenUpdating

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

Команда Application.ScreenUpdating говорит Excel, чтобы он прекратил выводить пересчитанные данные на экран, а выдал готовые значения в конце выполнения кода.

Использование команды Application. Calculation

Вторая хитрость заключается в отключении автоматических вычислений. Давайте я поясню. Каждый раз, когда пользователь или процесс обновляет ячейку, Excel пытается пересчитать все зависимые от нее ячейки. Так скажем, если ячейка, которую пытается обновить макрос, влияет на 10000 остальных ячеек, Excel будет пытаться пересчитать их все до того, как закончится выполнение кода. Соответственно, если существует целый ряд влияющих ячеек, пересчет может значительно замедлить выполнение кода. Чтобы этого не происходило, вы можете установить команду Application. Calculation в начале кода, которая переключит пересчет формул в ручной режим, а затем вернуть автоматическое вычисление в конце макроса.

Будьте внимательны, не забудьте переключить данную опцию снова в автоматический режим в конце макроса. В противном случае, вам необходимо будет это сделать в самом Excel, перейдя по вкладке Формулы в группу Вычисление и выбрать Параметры вычислений –> Автоматический.

Избежание выбора ячеек и диапазонов

В режиме автоматической записи макросов, вы можете заметить, что Excel очень часто использует команду выбора ячеек, например, Range(«A1»).Select. В нашем примере, мы использовали данную команду несколько раз, чтобы выбрать ячейку и изменить ее значение. Вы можете избежать этого просто указав адрес ячейки и задав ей необходимое значение (Макрос записал движение курсора от одной ячейки к другой, следовательно, вставил эти шаги. Однако они не являются необходимыми). Так что, более эффективный код будет выглядеть следующим образом.

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

Примеры макросов Excel

Ниже приведены несколько примеров кодов VBA, которые помогут автоматизировать наиболее часто повторяющиеся задачи.

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

. .

В предыдущих статьях вы видели, как Excel может управлять другими приложениями Microsoft, такими как Word и Outlook. Но одновременно с этим другие приложения также могут быть использованы для управления Excel. Например, вы написали макрос для Word или Access, который создает таблицу Excel, заполняет ее данными, а затем сохраняет ее. Excel не обязательно должен при этом […]

. .

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

. .

Этот метод может оказаться очень полезным, например, если у вас есть стандартный документ с таблицами, заполненными данными макросами из таблиц Excel. Вы можете запустить макрос, и данные будут перенесены в таблицы в документе Word. Недавно у меня была задача по написанию программы по заполнению отчета по SLA (Service Level Agreement — уровень сервисных соглашений). Отчет […]

. .

Для автоматизации работы приложений часто используются макросы. Любой макрос — это последовательность действий, записанная под определенным именем. Если при работе с Microsoft Excel возникает необходимость несколько раз выполнить одну и ту же последовательность операций (например, сложное форматирование текущей ячейки или добавление новой строки с заполнением некоторых ее ячеек формулами), то можно записать эти действия, а […]

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

Рассмотрим два примера:

1. Ищем и используем команду

Например, нам необходима команда, которая бы вставляла в выделенные ячейки цифру "1". Запускаем поисковик, набираем поисковую фразу, получаем результаты, начинаем просматривать, находим код примерно в таком виде:

Sub Вставка1() Dim q As Object On Error Resume Next Set q = Cells For Each q In Selection q = 1 Next q End Sub

Выделяем данный код (без нумерации строк, начиная со слова Sub ) и нажимаем Ctrl+C . Переходим в свою рабочую книгу MS Excel и нажимаем сочетание клавиш Alt+F11 , у вас откроется окно редактора VBA:

В левом окне "Project - VBA Project" выбираем (щелкаем мышкой) нашу рабочую книгу, в которую необходимо вставить макрос, например, "VBAProject (Книга2)":

В пункте меню "Insert" выбираем "Module":

В левом окне "Project - VBA Project" у вас должна появиться новая папка "Modules" и в ней новый объект "Module1":

Переходим курсором в правое "Большое" поле для ввода и нажимаем Ctrl+V , скопированный макрос вставиться в модуль:

Макросы в MS Excel можно вставлять в следующие места:

• В Модуль, обычно вставляют код макросов, запуск которых будет производится по нажатию кнопки пользователем (как, например, в нашем случае) или код функций (формул);
• В Рабочий лист, обычно вставляют код макросов, запуск которых должен происходить автоматически в зависимости от действий пользователя или изменения данных в листе (поменялись данный, макрос выполнился);
• В Рабочую книгу, обычно вставляют код макросов, запуск которых должен происходить автоматически в зависимости от действий, производимых над книгой (файлом). Например, макрос, который запускается при открытии или закрытии книги, или при ее сохранении;
• Так же макросы могут быть частью пользовательской формы.

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

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

Для вставки кода в Книгу, выберите "ЭтаКнига":

Давайте потренируемся. Вставьте код опубликованный ниже в "Лист1".

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo a If Target = 2 Then VBA.MsgBox ("Ячейка " & Target.Address & " = 2") End If a: Exit Sub End Sub

Данный макрос выводит информационное сообщение если вы введете в любую ячейку листа "2".

Вернитесь в рабочую книгу, перейдите в "Лист1" и введите в ячейку "А1" цифру "2" и нажмите Enter , после чего у вас должно появиться следующее сообщение:

Если вы видите это сообщение, то вы все сделали правильно. Если нет, то вы вставили код куда-то не туда, повторите попытку.

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

2. Ищем и используем функцию

Находим в интернете VBA код функции, которая подсчитывает, например, количество слов в ячейке:

Public Function КолСловВЯчейке(Ячейка As Range) Dim q As Variant Application.Volatile q = VBA.Split(Application.WorksheetFunction.Trim(Ячейка.Value), " ") КолСловВЯчейке = UBound(q) + 1 End Function

Копируем код, нажимаем сочетание клавиш Alt+F11 , откроется редактор VBA:

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

Закрываем редактор VBA и переходим в свою рабочую книгу. Создаем новый лист (необязательно) в ячейку A1 вводим текст "мама мыла раму". Встаем в ячейку, в которой хотим получить результат (количество слов), в меню "Формулы" нажимаем кнопку "Вставить функцию":

В открывшемся окне "Вставка функции" в поле "Категория" выбираем "Определенные пользователем"

В списке доступных функций выбираем "КолСловВЯчейке", нажимаем "ОК":

Вводим необходимые аргументы и нажимаем "ОК":

Получаем результат:

Важно:

Если вы не сохраните книгу, в которую вставили макрос как "Книгу с поддержкой макросов", все модули с макросами удаляться и вам придется, потом, повторно проделывать всю эту работу.

Если при вставке макроса в модуль у вас вместо некоторого текста стоят красные знаки "???????? "

Это значит, что у вас проблема с кодировкой, которая наблюдается с копированием Кириллического текста из некоторых браузеров. Чтобы победить эту проблему, попробуйте вставить скопированный код в пустой лист MS Excel, как "Текст в кодировке Unicode". Для этого перейдите в книгу MS Excel, выберите или создайте пустой лист, встаньте в ячейку "A1" и нажмите сочетания клавишCtrl+Alt+V . Должно будет появиться меню "Специальной вставки", выберите пункт "Текст в кодировке Unicode" и нажмите "OK".

Код должен будет вставиться в рабочий лист без знаков вопроса:

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

Если вы не видите в редакторе VBA окна "Project - VBA Project", перейдите во вкладку меню "View" и в списке выберите пункт "Project Explorer" или нажмите сочетание клавиш Ctrl+R :