Макросы Excel могут сэкономить массу времени за счет автоматизации часто используемых процессов Excel . Но макросы на самом деле довольно ограничены. С инструментом записи легко ошибиться, а процесс записи неудобен.
Использование VBA для создания макросов дает вам гораздо больше возможностей. Вы можете точно сказать Excel, что делать и как это сделать. Вы также получите доступ к гораздо большему количеству функций и возможностей. Если вы регулярно используете Excel, стоит научиться создавать макросы VBA.
Начнем с основ.
Что такое VBA?
VBA — это Visual Basic для приложений , язык программирования, который вы можете использовать во многих приложениях Microsoft. Visual Basic — это язык программирования, а VBA — его версия для конкретного приложения. (Microsoft прекратила выпуск Visual Basic еще в 2008 году, но VBA продолжает развиваться).
К счастью для непрограммистов, VBA очень прост, и интерфейс, который вы используете для редактирования, предлагает большую помощь. Во многих командах вы будете использовать всплывающие подсказки и автоматические дополнения, которые помогут вам быстро запустить ваш скрипт.
Тем не менее, VBA требует времени, чтобы привыкнуть.
Преимущества макросов VBA в Excel
Если VBA сложнее, чем запись макроса, зачем вам его использовать? Короткий ответ: вы получаете гораздо больше возможностей от макросов VBA.
Вместо того, чтобы щелкать по электронной таблице и записывать эти клики, вы можете получить доступ ко всему спектру функций и возможностей Excel. Вам просто нужно знать, как их использовать.
И как только вы освоитесь с VBA, вы сможете делать все, что можете, в обычном макросе за гораздо меньшее время. Результаты также будут более предсказуемыми, поскольку вы точно указываете Excel , что делать. Там нет никакой двусмысленности вообще.
Создав макрос VBA, его легко сохранить и поделиться им, чтобы каждый мог воспользоваться им. Это особенно полезно, когда вы работаете со многими людьми, которым нужно делать то же самое в Excel.
Давайте посмотрим на простой макрос VBA, чтобы увидеть, как он работает.
Пример макроса VBA в Excel
Давайте посмотрим на простой макрос. Наша электронная таблица содержит имена сотрудников, номер магазина, в котором работают сотрудники, и их квартальные продажи.
Этот макрос добавит квартальные продажи из каждого магазина и запишет эти итоги в ячейки электронной таблицы (если вы не уверены, как получить доступ к диалогу VBA, ознакомьтесь с нашим пошаговым руководством по VBA здесь ):
Sub StoreSales() Dim Sum1 As Currency Dim Sum2 As Currency Dim Sum3 As Currency Dim Sum4 As Currency For Each Cell In Range("C2:C51") Cell.Activate If IsEmpty(Cell) Then Exit For If ActiveCell.Offset(0, -1) = 1 Then Sum1 = Sum1 + Cell.Value ElseIf ActiveCell.Offset(0, -1) = 2 Then Sum2 = Sum2 + Cell.Value ElseIf ActiveCell.Offset(0, -1) = 3 Then Sum3 = Sum3 + Cell.Value ElseIf ActiveCell.Offset(0, -1) = 4 Then Sum4 = Sum4 + Cell.Value End If Next Cell Range("F2").Value = Sum1 Range("F3").Value = Sum2 Range("F4").Value = Sum3 Range("F5").Value = Sum4 End Sub Это может показаться длинным и сложным, но мы разберем его, чтобы вы могли увидеть отдельные элементы и немного узнать об основах VBA.
Объявление Sub
В начале модуля у нас есть «Sub StoreSales ()». Это определяет новую подпрограмму под названием StoreSales.
Вы также можете определять функции — разница в том, что функции могут возвращать значения, а подпрограммы не могут (если вы знакомы с другими языками программирования, подпрограммы эквивалентны методам). В этом случае нам не нужно возвращать значение, поэтому мы используем подпрограмму.
В конце модуля у нас есть «End Sub», который сообщает Excel, что мы закончили с этим макросом VBA.
Объявление переменных
Все первые строки кода в нашем скрипте начинаются с «Dim». Dim — это команда VBA для объявления переменной.
Таким образом, «Dim Sum1» создает новую переменную с именем «Sum1». Однако нам нужно сообщить Excel, что это за переменная. Нам нужно выбрать тип данных. В VBA существует много разных типов данных, полный список которых можно найти в справочных документах Microsoft .
Поскольку наш макрос VBA будет работать с валютами, мы используем тип данных Currency.
Оператор «Dim Sum1 As Currency» указывает Excel создать новую переменную Currency с именем Sum1. Каждая переменная, которую вы объявляете, должна иметь оператор «As», чтобы сообщить Excel ее тип.
Начиная для цикла
Циклы являются одними из самых мощных вещей, которые вы можете создать на любом языке программирования. Если вы не знакомы с циклами, ознакомьтесь с этим объяснением циклов Do-While. циклы В этом примере мы используем цикл For, который также рассматривается в статье.
Вот как выглядит цикл:
For Each Cell in Range("C2:C51") [a bunch of stuff] Next Cell
Это говорит Excel, чтобы перебирать ячейки в указанном диапазоне. Мы использовали объект Range. , который является специфическим типом объектов в VBA. Когда мы используем его таким образом — Range («C2: C51») — это говорит Excel, что мы заинтересованы в этих 50 ячейках.
«Для каждого» говорит Excel, что мы собираемся что-то сделать с каждой ячейкой в диапазоне. «Следующая ячейка» идет после всего, что мы хотим сделать, и говорит Excel начать цикл с начала (начиная со следующей ячейки).
У нас также есть следующее утверждение: «Если IsEmpty (Cell), то Exit For».
Можете ли вы угадать, что он делает?
Примечание. Строго говоря, использование цикла «В то время как» могло бы быть лучшим выбором. Однако ради обучения я решил использовать цикл For с выходом.
If-Then-Else Заявления
Ядро этого конкретного макроса находится в операторах If-Then-Else. Вот наша последовательность условных операторов:
If ActiveCell.Offset(0, -1) = 1 Then Sum1 = Sum1 + Cell.Value ElseIf ActiveCell.Offset(0, -1) = 2 Then Sum2 = Sum2 + Cell.Value ElseIf ActiveCell.Offset(0, -1) = 3 Then Sum3 = Sum3 + Cell.Value ElseIf ActiveCell.Offset(0, -1) = 4 Then Sum4 = Sum4 + Cell.Value End If
По большей части вы, вероятно, можете догадаться, что делают эти утверждения. Возможно, вы не знакомы с ActiveCell.Offset. «ActiveCell.Offset (0, -1)» указывает Excel взглянуть на ячейку, которая находится в одном столбце слева от активной ячейки.
В нашем случае это говорит Excel, чтобы обратиться к столбцу номера магазина. Если Excel находит 1 в этом столбце, он берет содержимое активной ячейки и добавляет его в Sum1. Если он находит 2, он добавляет содержимое активной ячейки в Sum2. И так далее.
Excel проходит все эти утверждения по порядку. Если условный оператор операторы операторы доволен, он завершает утверждение Тогда. Если нет, он переходит к следующему ElseIf. Если он дойдет до конца и ни одно из условий не будет выполнено, он не предпримет никаких действий.
Комбинация цикла и условных выражений — это то, что движет этим макросом. Цикл предписывает Excel пройти через каждую ячейку в выделении, а условные выражения сообщают ему, что делать с этой ячейкой.
Запись значений ячеек
Наконец, мы можем записать результаты наших расчетов в ячейки. Вот строки, которые мы используем для этого:
Range("F2").Value = Sum1 Range("F3").Value = Sum2 Range("F4").Value = Sum3 Range("F5").Value = Sum4
Используя «.Value» и знак равенства, мы устанавливаем в каждой из этих ячеек значение одной из наших переменных.
Вот и все! Мы сообщаем Excel, что закончили написание этого Sub с «End Sub», и макрос VBA завершен.
Когда мы запускаем макрос с помощью кнопки « Макросы» на вкладке « Разработчик », мы получаем наши суммы:
Собираем строительные блоки VBA в Excel
Когда вы впервые посмотрите на макрос VBA выше, он выглядит довольно сложным. Но после разбиения его на составные части логика становится ясной. Как и любой язык сценариев, требуется время, чтобы привыкнуть к синтаксису VBA.
Но с практикой вы увеличите свой словарный запас VBA и сможете писать макросы быстрее, точнее и с гораздо большей мощностью, чем вы могли бы когда-либо записывать.
Когда вы застряли, запуск поиска Google — это быстрый способ получить ответы на ваши вопросы VBA. А справочник Microsoft по VBA для Excel может быть полезен, если вы хотите изучить технический ответ.
Когда вы освоитесь с основами, вы можете начать использовать VBA для таких вещей, как отправка электронных писем из Excel , экспорт задач Outlook. и отображение информации о вашем ПК