Как создать макрос или скрипт VBA в Excel

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

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

На вкладке «Разработчик» на ленте в Excel пользователи могут записывать щелчки мыши и нажатия клавиш (макросы). Однако некоторые функции требуют более глубокого написания сценариев, чем могут обеспечить макросы. Здесь сценарии VBA становятся огромным преимуществом. Это позволяет пользователям создавать более сложные сценарии.

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

В этой статье мы объясним следующее:

Включение скриптов и макросов
Как создать макрос в Excel
Конкретный пример макроса
Узнайте больше о VBA
Создать кнопку для начала работы с VBA
Добавить код для придания кнопкам функциональности
Это сработало?

Включение скриптов и макросов

Прежде чем вы сможете создавать макросы или сценарии VBA в Excel, необходимо включить вкладку «Разработчик» в меню ленты. Вкладка «Разработчик» не включена по умолчанию. Чтобы включить это:

Откройте лист Excel.
Нажмите Файл> Параметры> Настроить ленту.

Поставьте галочку в поле рядом с Developer.

Нажмите на вкладку Разработчик в меню ленты.

Затем нажмите «Безопасность макросов» и установите флажок «Включить все макросы» (не рекомендуется; может выполняться потенциально опасный код).
Затем нажмите ОК.

Макросы по умолчанию не включены и сопровождаются предупреждением, потому что они представляют собой компьютерный код, который может содержать вредоносное ПО.

Убедитесь, что документ из надежного источника, если вы работаете над общим проектом в Excel и других программах Microsoft.

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

Создать макрос в Excel

Все действия, которые вы выполняете в Excel во время записи макроса, добавляются к нему.

На вкладке «Разработчик» нажмите «Запись макроса».

Введите имя макроса, сочетание клавиш и описание. Имена макросов должны начинаться с буквы и не должны содержать пробелов. Сочетание клавиш должно быть буквой.

Решите, где вы хотите сохранить макрос из следующих опций:

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

Когда закончите, нажмите ОК.

Выполните действия, которые вы хотите автоматизировать. Когда вы закончите, нажмите «Остановить запись».
Если вы хотите получить доступ к вашему макросу, используйте сочетание клавиш, которое вы ему дали.

Конкретный пример макроса

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

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

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

Запись макроса

Нажмите на запись макроса. Давайте назовем его Format_Customer_Data и нажмите ОК.
Чтобы получить желаемое форматирование, мы изменим имя первого столбца на Имя.
Затем вставьте столбец рядом с A и назовите его Фамилия.
Выделите все имена в первом столбце (которые по-прежнему включают имя и фамилию) и нажмите «Данные» на ленте навигации.
Нажмите на текст в столбцы.

Отметьте разделителем> Далее> Разделить пробелом> Далее> Готово. Смотрите скриншот ниже и как имена и фамилии были разделены вышеописанным процессом.

Чтобы отформатировать поле «Сальдо», выделите суммы. Нажмите Главная> Условное форматирование> Выделить правила ячейки> Больше, чем> 0.

Это будет выделять клетки, которые имеют баланс из-за. Мы добавили несколько клиентов без остатка из-за дальнейшей иллюстрации форматирования.

Вернитесь к разработчику и нажмите «Остановить запись».

Применить макрос

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

Когда вы запускаете макрос, все форматирование выполняется за вас. Этот макрос, который мы только что создали, хранится в редакторе Visual Basic.

Пользователи могут запускать макросы несколькими различными способами. Читать Запустить макрос Узнать больше.

Узнайте больше о VBA

Чтобы узнать о VBA, нажмите Макрос на вкладке Разработчик. Найдите созданный вами файл и нажмите «Изменить».

Код, который вы видите в поле выше, создан при записи макроса.

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

Создать кнопку, чтобы начать работу с VBA

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

Чтобы вставить элемент кнопки, перейдите на вкладку «Разработчик».
Выберите ActiveX Command Button в раскрывающемся списке рядом с «Вставить» в разделе «Элементы управления».

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

Чтобы прикрепить код, щелкните правой кнопкой мыши на кнопке и выберите «Свойства». Мы будем сохранять имя как CommandButton и заголовок для преобразования (это текст кнопки).

Добавить код для придания кнопке функциональности

Кодирование VBA не выполняется в интерфейсе Excel. Это делается в отдельной среде.

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

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

Глядя на код на скриншоте ниже, обратите внимание, что начало (Private Sub) и конец (End Sub) кода уже есть.

Код ниже будет управлять процедурой конвертации валюты.

ActiveCell.Value = (ActiveCell * 1.28)

Наша цель в этом разделе — конвертировать валюту в нашей таблице. Сценарий выше отражает обменный курс от GBP к USD. Новое значение ячейки будет то, что в настоящее время там умножено на 1,28.

На приведенном ниже снимке экрана показано, как выглядит код в окне VBA после его вставки.

Перейдите в «Файл» в верхней части навигации и нажмите «Закрыть» и «Вернуться в Microsoft Excel», чтобы вернуться в основной интерфейс Excel.

Это сработало?

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

Введите любое число в таблицу и нажмите кнопку «Преобразовать». Если ценность вашего номера увеличивается примерно на четверть, это сработало.

Для этого примера я положил число 4 в клетку. После нажатия кнопки «Преобразовать» число изменилось на 5.12. Так как 4 раза 1,28 является 5,12, код был выполнен правильно.