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

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

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

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

• Включение скриптов и макросов
• Как создать макрос в Excel
• Конкретный пример макроса
• Узнать больше о VBA
• Создайте кнопку^(Button) , чтобы начать работу с VBA
• Добавьте код^{(Add Code)} , чтобы придать кнопке функциональность^{(Button Functionality)}
• Это сработало?

Включение скриптов и макросов^{(Enabling Scripts & Macros)}

Прежде чем вы сможете создавать макросы или сценарии VBA в ^(VBA)Excel , вы должны включить вкладку « Разработчик^{(Developer )} » в меню ленты . ^(Ribbon)Вкладка « Разработчик^(Developer) » не включена по умолчанию. Чтобы включить его:

• Откройте рабочий лист Excel.
• Нажмите «Файл»^{(File )} > «Параметры^{( Options )} » > «Настроить ленту».^{( Customize Ribbon.)}

• Поставьте галочку в поле « Разработчик^(Developer) » .

• Нажмите на вкладку « Разработчик^(Developer) » в меню ленты .^(Ribbon)

• Затем нажмите « Безопасность макросов^{(Macro Security)} » и установите флажок « Включить все макросы» (не рекомендуется, может выполняться потенциально опасный код). ^{(Enable all macros (not recommended; potentially dangerous code can run). )}
• Затем нажмите ОК.^{( OK.)}

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

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

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

Создать макрос в Excel^{(Create a Macro in Excel)}

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

• На вкладке «Разработчик» нажмите «Запись макроса»^{(Record Macro)} .

• Введите имя макроса^{(Macro name)} , сочетание клавиш^{(Shortcut key)} и описание. ^{(Description. )}Имена макросов^(Macro) должны начинаться с буквы и не должны содержать пробелов. Горячая клавиша должна быть буквой.

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

• Личная книга макросов^{(Personal Macro Workbook)} : создаст скрытый документ Excel с сохраненными макросами, которые можно использовать с любыми документами Excel .
• Новая рабочая книга^{(New Workbook)} : создаст новый документ Excel для хранения созданных макросов.
• Эта рабочая книга^{(This Workbook)} : это будет применяться только к документу, который вы редактируете в данный момент.

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

• Выполните^(Run) действия, которые вы хотите автоматизировать. Когда вы закончите, нажмите «Остановить запись»^{(Stop Recording)} . 
• Когда вы хотите получить доступ к своему макросу, используйте сочетание клавиш, которое вы ему дали.

Конкретный пример макроса^{(Specific Example Of a Macro)}

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

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

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

Запись макроса^{(Record The Macro)}

• Нажмите «Записать макрос»^{(Record Macro)} . Назовем его Format_Customer_Data и нажмем OK . 
• Чтобы получить желаемое форматирование, мы изменим имя первого столбца на Имя^{(First Name)} . 
• Затем вставьте столбец рядом с A и назовите его Last Name . 
• Выделите^(Highlight) все имена в первом столбце (которые по-прежнему включают имя и фамилию) и нажмите « Данные^(Data) » в навигационной ленте.
• Нажмите «Текст в столбцы^{(Text to Columns)} » .

• Отметьте Разделители^(Delimited) > Далее^{(Next )} > Разделить пробелом^{(Separate by Space)} > Далее^{(Next )} > Готово^(Finish) . Смотрите скриншот ниже и то, как имя и фамилия были разделены описанным выше процессом.

• Чтобы отформатировать поле « Остаток^{(Balance Due)} к оплате », выделите суммы. Нажмите^(Click) « Главная»^{(Home )} > «Условное форматирование^{(Conditional Formatting)} » > «Правила выделения ячеек»^{(Highlight Cell Rules)} > «Больше^{(Greater Than )} > 0 » .

Это выделит ячейки, в которых есть остаток. Мы добавили несколько клиентов без баланса, чтобы проиллюстрировать форматирование.  

• Вернитесь в раздел « Разработчик^{(Developer )} » и нажмите «Остановить запись»^{(Stop Recording)} .

Применить макрос^{(Apply The Macro)}

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

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

Пользователи могут запускать макросы несколькими различными способами. Прочтите Запуск макроса^{(Run a macro)} , чтобы узнать больше.  

Узнать больше о VBA^{(Learn More About VBA)}

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

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

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

Создайте кнопку, чтобы начать работу с VBA^{(Create a Button To Get Started With VBA)}

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

• Чтобы вставить элемент кнопки, перейдите на вкладку  « Разработчик ».^{(Developer )}
• Выберите « Кнопка управления ActiveX^{(ActiveX Command Button )} » в раскрывающемся списке рядом с « Вставить^(Insert) » в разделе « Элементы управления^(Controls) » .

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

• Чтобы прикрепить код, щелкните правой кнопкой мыши на кнопке и выберите « Свойства^(Properties) » . Мы сохраним имя^(Name) как CommandButton и заголовок^{(Caption )} для преобразования^{( Convert )} (это текст кнопки).

Добавьте код, чтобы придать кнопке функциональность^{(Add Code To Give The Button Functionality)}

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

• Перейдите на вкладку « Разработчик^(Developer) » и убедитесь, что режим «Дизайн»^{(Design Mode)} активен.

• Чтобы получить доступ к коду только что созданной кнопки, щелкните ее правой кнопкой мыши и выберите « Просмотр кода»^{(View Code)} .

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

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

ActiveCell.Value = (ActiveCell * 1.28)

Наша цель в этом разделе — конвертировать валюту в нашей электронной таблице. Приведенный выше скрипт отражает обменный курс фунта стерлингов^(GBP) к доллару США^(USD) . Новое значение ячейки будет равно тому, что есть в данный момент, умноженному на 1,28.

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

• Перейдите в « Файл»^{(File )} в верхней панели навигации и нажмите «Закрыть и вернуться в Microsoft Excel»^{(click on Close and Return to Microsoft Excel)} , чтобы вернуться к основному интерфейсу Excel .

Это сработало?^{(Did It Work?)}

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

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

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

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

How To Create a VBA Macro Or Script In Excel

Microsoft Excel enables users to automate features and commands uѕing macros and Viѕual Baѕic for Applications (VBA) ѕcripting. VBA is the programming language Excel uses to create macros. It will also execute automated commands based on specific conditions.

Macros are a series of pre-recorded commands. They run automatically when a specific command is given. If you have tasks in Microsoft Excel that you repeatedly do, such as accounting, project management, or payroll, automating these processes can save a lot of time.

Under the Developer tab on the Ribbon in Excel, users can record mouse clicks and keystrokes (macros).  However, some functions require more in-depth scripting than macros can provide. This is where VBA scripting becomes a huge benefit. It allows users to create more complex scripts.

In this article, we will explain the following:

• Enabling Scripts & Macros
• How to Create a Macro in Excel
• Specific Example of a Macro
• Learn More About VBA
• Create a Button to Get Started with VBA
• Add Code to Give the Button Functionality
• Did it Work?

Enabling Scripts & Macros

Before you can create macros or VBA scripts in Excel, you must enable the Developer tab on the Ribbon menu. The Developer tab is not enabled by default. To enable it:

• Open an Excel worksheet.
• Click on File > Options > Customize Ribbon.

• Put a tick in the box next to Developer.

• Click on the Developer tab from the Ribbon menu.

• Next, click on Macro Security and tick the box next to Enable all macros (not recommended; potentially dangerous code can run). 
• Then click OK.

The reason macros are not turned on by default and come with a warning is that they are computer code that could contain malware.

Make sure the document is from a trusted source if you are working on a shared project in Excel and other Microsoft programs.

When you are done using your scripts and macros, disable all macros to prevent potentially malicious code from infecting other documents.

Create a Macro in Excel

All the actions you take in Excel while recording a macro are added to it. 

• From the Developer tab, click on Record Macro.

• Enter a Macro name, a Shortcut key, and a Description. Macro names must begin with a letter and can’t have any spaces. The shortcut key must be a letter.

Decide where you want to store the macro from the following options:

• Personal Macro Workbook: This will create a hidden Excel document with stored macros to be used with any Excel documents.
• New Workbook: Will create a new Excel document to store the created macros.
• This Workbook: This will only be applied to the document you are currently editing.

When done, click OK. 

• Run through the actions you want to automate. When you are finished, click Stop Recording. 
• When you want to access your macro, use the keyboard shortcut you gave it.

Specific Example Of a Macro

Let’s start with a simple spreadsheet for customers and how much they owe. We will begin by creating a macro to format the worksheet.

Let’s assume that you decide that all spreadsheets should use a different format such as putting first and last name in separate columns. 

You can manually change this. Or you can create a program using a macro to automatically format it correctly for you.

Record The Macro

• Click on Record Macro. Let’s call it Format_Customer_Data and click OK. 
• To get the formatting we want, we will change the first column name to First Name. 
• Then insert a column next to A and call it Last Name. 
• Highlight all the names in the first column (which still include first and last name), and click on Data from the ribbon navigation.
• Click on Text to Columns.

• Tick Delimited > Next > Separate by Space > Next > Finish. See screenshot below and how the first and last names were separated by the process above.

• To format the Balance Due field, highlight the amounts. Click on Home > Conditional Formatting > Highlight Cell Rules > Greater Than > 0.

This will highlight the cells that have a balance due. We added a few customers with no balance due to further illustrate the formatting.  

• Go back to Developer and click Stop Recording.

Apply The Macro

Let’s start with the original spreadsheet before we recorded the macro to format it correctly. Click on Macros, select and Run the macro you just created.

When you run a macro, all the formatting is done for you. This macro we just created is stored in the Visual Basic Editor.

Users can run macros in several different ways. Read Run a macro to learn more.  

Learn More About VBA

To learn about VBA, click on Macro from the Developer tab. Find one you have created and click Edit.

The code you see in the box above is what was created when you recorded your macro. 

It is also what you will run when you want to format other customer payment spreadsheets in the same way.

Create a Button To Get Started With VBA

Using the same spreadsheet above with customers and how much they owe, let’s create a currency converter.

• To insert a button element, navigate to the Developer tab. 
• Select ActiveX Command Button from the dropdown next to Insert in the Controls section.

• Drag the button anywhere on the spreadsheet so you can easily access it and change it later if you want.

• To attach the code, right-click on the button and select Properties. We will keep the Name as CommandButton and the Caption to Convert (this is the button text).

Add Code To Give The Button Functionality

VBA coding doesn’t take place in the Excel interface. It is done in a separate environment.  

• Go to the Developer tab and make sure Design Mode is active.

• To access the code of the button we just created, right-click on it and select View Code.

• Looking at the code in the screenshot below, notice the beginning (Private Sub) and end (End Sub) of the code is already there.

• The code below will drive the currency conversion procedure.

ActiveCell.Value = (ActiveCell * 1.28)

Our purpose in this section is to convert the currency in our spreadsheet. The script above reflects the exchange rate from GBP to USD. The new value of a cell will be what is currently there multiplied by 1.28.

The screenshot below shows you how the code looks in the VBA window after you insert it .

• Go to File in the top navigation and click on Close and Return to Microsoft Excel to return to the main Excel interface.

Did It Work?

Before you can test your code, you must first disable Design Mode (click on it) to avoid further modifications and give the button functionality.

• Type any number into your spreadsheet and then click the Convert button. If the value of your number increases by approximately one-quarter, it worked.

For this example, I put the number 4 into a cell. After clicking Convert, the number changed to 5.12. Since 4 times 1.28 is 5.12, the code was carried out correctly.

Now that you understand how to create a macro or script in Excel, you can use them to automate a multitude of actions in Excel.

Related posts

• Расширенное руководство по VBA для MS Excel

• 5 функций скрипта Google Sheets, которые вам нужно знать

• Как удалить пустые строки в Excel

• Как использовать функцию «Говорить ячейки» в Excel

• Как вставить лист Excel в документ Word

• Используйте имена динамических диапазонов в Excel для гибких раскрывающихся списков

• 4 способа использования галочки в Excel

• Как использовать операторы If и вложенные операторы If в Excel

• Центрируйте данные рабочего листа в Excel для печати

• Как создать простую сводную таблицу в Excel

• Как использовать анализ «что, если» в Excel

• Как переместить столбцы в Excel

• Базовая сортировка данных в один столбец и несколько столбцов в электронных таблицах Excel

• Что такое массив VBA в Excel и как его запрограммировать

• Как удалить повторяющиеся строки в Excel

• Как рассчитать Z-показатель в Excel

• Как искать в Excel

• Почему вы должны использовать именованные диапазоны в Excel

• Google Таблицы против Microsoft Excel — в чем разница?

• Как сортировать по дате в Excel