Учебник для начинающих по написанию макросов VBA в Excel (и почему вы должны учиться)

1. Что такое VBA?
2. Преимущества макросов VBA в Excel
3. Пример макроса VBA в Excel
4. Объявление Sub
5. Объявление переменных
6. Начиная для цикла
7. If-Then-Else Заявления
8. Запись значений ячеек
9. Собираем строительные блоки VBA в Excel

Реклама

Макросы 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 в качестве валюты Dim Sum2 в качестве валюты Dim Sum3 в качестве валюты Dim Sum4 в качестве валюты для каждой ячейки в диапазоне ("C2: C51") Cell.Activate If IsEmpty (Cell), затем выход для If ActiveCell.Offset (0, -1) = 1 Затем Sum1 = Sum1 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 2 Затем Sum2 = Sum2 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 3 Затем Sum3 = Sum3 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 4 Тогда Sum4 = Sum4 + Cell.Value End Если следующий диапазон ячеек ("F2"). Значение = диапазон Sum1 ("F3"). Значение = диапазон Sum2 (" F4 "). Значение = диапазон Sum3 (" F5 "). Значение = 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 Как работают циклы Do-While в компьютерном программировании Как работают циклы Do-While в компьютерном программировании Циклы являются одним из первых типов управления, которые вы изучите в программировании. Вы, вероятно, знаете о циклах while и for, но что делает цикл do-while? Прочитайте больше , В этом примере мы используем цикл For, который также рассматривается в статье.

Вот как выглядит цикл:

Для каждой ячейки в диапазоне ("C2: C51") [куча вещей] Следующая ячейка

Это говорит Excel, чтобы перебирать ячейки в указанном диапазоне. Мы использовали Диапазон объекта Понимание ячеек Excel и функций диапазона в VBA Понимание ячеек Excel и функций диапазона в VBA Использование функций Range и Cells в Excel может привести к путанице. Вот как вы можете использовать их так, как вы, вероятно, никогда не могли себе представить, с помощью VBA Прочитайте больше , который является конкретным типом объекта в VBA. Когда мы используем его таким образом - Range («C2: C51») - это говорит Excel, что мы заинтересованы в этих 50 ячейках.

«Для каждого» говорит Excel, что мы собираемся что-то сделать с каждой ячейкой в ​​диапазоне. «Следующая ячейка» идет после всего, что мы хотим сделать, и говорит Excel начать цикл с начала (начиная со следующей ячейки).

У нас также есть следующее утверждение: «Если IsEmpty (Cell), то Exit For».

Можете ли вы угадать, что он делает?

Примечание: строго говоря, использование цикла While могло бы быть лучшим выбором 4 ошибки, которые вы можете избежать при программировании макросов Excel с помощью VBA 4 ошибки, которые вы можете избежать при программировании макросов Excel с помощью VBA Простой код и макросы являются ключами к сверхспособностям Microsoft Excel. Даже непрограммисты могут легко добавить впечатляющие функциональные возможности в свои электронные таблицы с помощью Virtual Basics for Applications (VBA). Просто избегайте ошибок начинающих программистов! Прочитайте больше , Однако ради обучения я решил использовать цикл For с выходом.

If-Then-Else Заявления

Ядро этого конкретного макроса находится в операторах If-Then-Else. Вот наша последовательность условных операторов:

Если ActiveCell.Offset (0, -1) = 1, то Sum1 = Sum1 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 2, затем Sum2 = Sum2 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 3 Тогда Sum3 = Sum3 + Cell.Value ElseIf ActiveCell.Offset (0, -1) = 4 Тогда Sum4 = Sum4 + Cell.Value End If

По большей части вы, вероятно, можете догадаться, что делают эти утверждения. Возможно, вы не знакомы с ActiveCell.Offset. «ActiveCell.Offset (0, -1)» указывает Excel взглянуть на ячейку, которая находится в одном столбце слева от активной ячейки.

В нашем случае это говорит Excel, чтобы обратиться к столбцу номера магазина. Если Excel находит 1 в этом столбце, он берет содержимое активной ячейки и добавляет его в Sum1. Если он находит 2, он добавляет содержимое активной ячейки в Sum2. И так далее.

Excel проходит все эти утверждения по порядку. Если Условный оператор Как использовать IF-операторы в Microsoft Excel Как использовать IF-операторы в Microsoft Excel Независимо от того, являетесь ли вы опытным экспертом или новичком в работе с электронными таблицами, вы можете обратиться к этому руководству по инструкциям IF в Excel. Прочитайте больше удовлетворен, он завершает утверждение Тогда. Если нет, он переходит к следующему ElseIf. Если он дойдет до конца и ни одно из условий не будет выполнено, он не предпримет никаких действий.

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

Запись значений ячеек

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

Диапазон ("F2"). Значение = диапазон Sum1 ("F3"). Значение = диапазон Sum2 ("F4"). Значение = диапазон Sum3 ("F5"). Значение = Sum4

Используя «.Value» и знак равенства, мы устанавливаем в каждой из этих ячеек значение одной из наших переменных.

И это все! Мы сообщаем Excel, что закончили написание этого Sub с «End Sub», и макрос VBA завершен.

Когда мы запускаем макрос с помощью кнопки « Макросы» на вкладке « Разработчик », мы получаем наши суммы:

Собираем строительные блоки VBA в Excel

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

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

Когда вы застряли, запуск поиска Google - это быстрый способ получить ответы на ваши вопросы VBA. А также Microsoft Excel VBA ссылка может быть полезным, если вы хотите изучить технический ответ.

Как только вы освоитесь с основами, вы можете начать использовать VBA для таких вещей, как отправка писем из Excel , экспорт задач Outlook Как экспортировать задачи Outlook в Excel с VBA Как экспортировать задачи Outlook в Excel с VBA Являетесь ли вы поклонником Microsoft, по крайней мере, одна хорошая вещь, которую можно сказать о продуктах MS Office, это то, насколько легко интегрировать каждый из них друг с другом .... Прочитайте больше , а также отображение информации о вашем ПК Как просмотреть всю информацию о вашем ПК, используя простой скрипт VBA Excel Как просмотреть всю информацию о вашем ПК, используя простой скрипт VBA Excel Excel и 10 минут работы дадут вам более подробную информацию о вашем компьютере, чем вы могли себе представить. Звучит слишком хорошо, чтобы быть правдой? Это Windows, если вы знаете, как ее использовать. Прочитайте больше ,

Узнайте больше о: Уроки по программированию , макрос , Майкрософт Эксель , Microsoft Office 2016 , таблица ,