Макрос как создать: Как записать макрос в Excel? Пошаговая инструкция

Как записать макрос в Excel? Пошаговая инструкция

Для начала немного о терминологии. 

Макрос — это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.

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

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

Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.

Отображение вкладки «Разработчик» в ленте меню

Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку «Разработчик». Для этого выполните следующие шаги:

1. Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».
2. В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
3. Нажмите «ОК».

В результате на ленте меню появится вкладка «Разработчик»

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

Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например «Excel».

Вот шаги для записи такого макроса:

1. Перейдите на вкладку «Разработчик».
2. В группе «Код» нажмите кнопку «Запись макроса». Откроется одноименное диалоговое окно.
   
3. В диалоговом окне «Запись макроса» введите имя для своего макроса, например «ВводТекста». Есть несколько условий именования, которые необходимо соблюдать при назначении макроса. Например, вы не можете использовать пробелы между ними. Обычно я предпочитаю сохранять имена макросов как одно слово, с разными частями с заглавным первым алфавитом. Вы также можете использовать подчеркивание для разделения двух слов — например, «Ввод_текста».
   
4. Если вы хотите, то можете задать сочетание клавиш. В этом случае мы будем использовать ярлык Ctrl + Shift + N. Помните, что сочетание, которое вы указываете, будет отменять любые существующие горячие клавиши в вашей книге. Например, если вы назначили сочетание Ctrl + S, вы не сможете использовать это для сохранения рабочей книги (вместо этого, каждый раз, когда вы его используете, он выполняет макрос).
   
5. В поле «Сохранить в» убедитесь, что выбрана опция «Эта книга». Этот шаг гарантирует, что макрос является частью рабочей книги. Он будет там, когда вы сохраните его и снова откроете, или даже если вы поделитесь файлом с кем-то.
   
6. Введите описание при необходимости. Обычно я этого не делаю, но если у вас много макросов, лучше указать, чтобы в будущем не забыть что делает макрос.
7. Нажмите «ОК». Как только вы нажмете OK, Excel начнет записывать ваши действия. Вы можете увидеть кнопку «Остановить запись» на вкладке «Разработчик», которая указывает, что выполняется запить макроса.
8. Выберите ячейку A2.
9. Введите текст «Excel» (или вы можете использовать свое имя).
10. Нажмите клавишу Enter. Вы попадете на ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик».
    

Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel. 

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

1. Удалите текст в ячейке A2. Это нужно, чтобы проверить будет ли макрос вставлять текст в ячейку A2 или нет.
2. Выберите любую ячейку — кроме A2. Это нужно проверить, выбирает ли макрос ячейку A2 или нет.
3. Перейдите на вкладку «Разработчик».
4. В группе «Код» нажмите кнопку «Макросы».
5. В диалоговом окне «Макрос» щелкните макрос «ВводТекста».
6. Нажмите кнопку «Выполнить».
   

Вы увидите, что как только вы нажмете кнопку «Выполнить», текст «Excel» будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.

Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.

Что записывает макрос?

Теперь перейдем к редактору кода и посмотрим что у нас получилось.

Вот шаги по открытию редактора VB в Excel:

1. Перейдите на вкладку «Разработчик».
2. В группе «Код» нажмите кнопку «Visual Basic».
    

Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.

Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.

• Панель меню: содержит команды, которые можно использовать во время работы с редактором VB.
• Панель инструментов — похожа на панель быстрого доступа в Excel. Вы можете добавить к ней дополнительные инструменты, которыми часто пользуетесь.
• Окно проектов (Project Explorer) — здесь Excel перечисляет все книги и все объекты в каждой книге. Например, если у нас есть книга с 3 рабочими листами, она появится в Project Explorer. Здесь есть несколько дополнительных объектов, таких как модули, пользовательские формы и модули классов.
• Окно кода — собственно сам код VBA размещается в этом окне. Для каждого объекта, указанного в проводнике проекта, есть окно кода, например, рабочие листы, книги, модули и т. д. В этом уроке мы увидим, что записанный макрос находится в окне кода модуля.
• Окно свойств — вы можете увидеть свойства каждого объекта в этом окне. Я часто использую это окно для обозначения объектов или изменения их свойств. 
• Immediate Window (окно предпросмотра) — На начальном этапе оно вам не пригодится. Оно полезно, когда вы хотите протестировать шаги или во время отладки. Он по умолчанию не отображается, и вы можете его отобразить, щелкнув вкладку «View» и выбрав опцию «Immediate Window».

Когда мы записали макрос «ВводТекста», в редакторе VB произошли следующие вещи:

• Был добавлен новый модуль.
• Макрос был записан с именем, которое мы указали — «ВводТекста»
• В окне кода добавлена новая процедура.

Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.

 

Вот код, который записан макрорекодером:

Sub ВводТекста()
'
' ВводТекста Макрос
'

'
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Excel"
    Range("A3").Select
End Sub

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

Теперь давайте пробежим по каждой строке кода и опишем что и зачем. 

Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub — сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.

• Range(«A2»).Select — эта строка выбирает ячейку A2.
• ActiveCell.FormulaR1C1 = «Excel» — эта строка вводит текст «Excel» в активной ячейке. Поскольку мы выбрали ячейку A2 в качестве первого шага, она становится нашей активной ячейкой.
• Range(«A3»).Select — выбор ячейки A3. Это происходит, когда мы нажимаем клавишу Enter после ввода текста, результатом которого является выбор ячейки A3.

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

Обращаем внимание, что код, записанный через макрорекордер, как правило, не является эффективным и оптимизированным кодом. Макрорекордер часто добавляет дополнительные ненужные действия. Но это не значит, что не нужно пользоваться макрорекодером. Для тех, кто только изучает VBA , макрорекордер может быть отличным способом проанализировать и понять как все работает в VBA.

Абсолютная и относительная запись макроса

Вы уже знаете про абсолютные и относительные ссылки в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выберете ячейку A2 и введете текст «Excel», то каждый раз — независимо от того, где вы находитесь на листе и независимо от того, какая ячейка выбрана, ваш код будет вводить текст «Excel» в ячейку A2.

Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет «двигаться» относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст «Excel» и затем перейдет к ячейке K5.

Теперь давайте запишем макрос в режиме относительных ссылок:

1. Выберите ячейку A1.
2. Перейдите на вкладку «Разработчик».
3. В группе «Код» нажмите кнопку «Относительные ссылки». Он будет подсвечиваться, указывая, что он включен.
   
4. Нажмите кнопку «Запись макроса».
   
5. В диалоговом окне «Запись макроса» введите имя для своего макроса. Например,  имя «ОтносительныеСсылки».
   
6. В опции «Сохранить в» выберите «Эта книга».
7. Нажмите «ОК».
8. Выберите ячейку A2.
9. Введите текст «Excel» (или другой как вам нравится).
10. Нажмите клавишу Enter. Курсор переместиться в ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик».

Макрос в режиме относительных ссылок будет сохранен.

Теперь сделайте следующее.

1. Выберите любую ячейку (кроме A1).
2. Перейдите на вкладку «Разработчик».
3. В группе «Код» нажмите кнопку «Макросы».
4. В диалоговом окне «Макрос» кликните на сохраненный макрос «ОтносительныеСсылки».
5. Нажмите кнопку «Выполнить».

Как вы заметите, макрос записал текст «Excel» не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel — ячейка B4 и в конечном итоге выберет ячейку B5.

Вот код, который записал макрорекодер:

Sub ОтносительныеСсылки()
'
' ОтносительныеСсылки Макрос
'

'
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "Excel"
    ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.

Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.

Что нельзя сделать с помощью макрорекодера?

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

• Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
• Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
• Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода. 
• Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).

Расширение файлов Excel, которые содержат макросы

Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).

До Excel 2007 был достаточен один формат файла — .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:

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

Краткое руководство: создание макроса — Служба поддержки Office

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

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

Процедура

Перед записью макроса     Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки «Разработчик».

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

Подробное рассмотрение макроса     Чтобы ознакомиться с языком программирования Visual Basic, можно изменить макрос. Чтобы изменить макрос, в группе код на вкладке разработчик нажмите кнопку макросы, выберите имя макроса, а затем нажмите кнопку изменить. Откроется редактор Visual Basic. Посмотрите, как записанные действия выводятся в виде кода. Часть кода, скорее всего, будет понятно вам, а некоторые из них могут быть немного мистериаус. Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос. На этот раз посмотрите, что происходит, если что-то не так.

Дальнейшие действия

Процедура

Перед записью макроса     Убедитесь, что на ленте отображается вкладка разработчик . По умолчанию вкладка разработчик не отображается, поэтому выполните указанные ниже действия. Перейдите в раздел настройки > Excel…Панель инструментов & > ленты. В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

Запись макроса     На вкладке Разработчик нажмите кнопку Запись макроса. При необходимости введите имя макроса в поле имя макроса , введите сочетание клавиш в поле сочетание клавиш и описание в поле Описание , а затем нажмите кнопку ОК , чтобы начать запись. Выполните действия, которые вы хотите автоматизировать (например, введите Стандартный текст или заполните столбец данных). На вкладке Разработчик в группе Код нажмите кнопку Остановить запись.

Подробное рассмотрение макроса     Чтобы ознакомиться с языком программирования Visual Basic, можно изменить макрос. Чтобы изменить макрос, на вкладке разработчик нажмите кнопку макросы, выберите имя макроса, а затем нажмите кнопку изменить. Откроется редактор Visual Basic. Посмотрите, как записанные действия выводятся в виде кода. Часть кода, скорее всего, будет понятно вам, а некоторые из них могут быть немного мистериаус. Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос. На этот раз посмотрите, что происходит, если что-то не так.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

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

Способы записи макросов в Excel

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

Вариант 1: Автоматическая запись макросов

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

Подробнее: Включение и отключение макросов в Microsoft Excel

Когда все готово, приступаем к записи.

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



2. Открывается окно настройки записи макроса. Тут можно указать любое имя для него, если установленное по умолчанию вас не устраивает. Главное, чтобы имя это начиналось с буквы, а не с цифры, а также в названии не должно быть пробелов. Мы оставили название по умолчанию – «Макрос1».
3. Тут же при желании можно установить сочетание клавиш, при нажатии на которые макрос будет запускаться. Первой клавишей обязательно должна быть Ctrl, а вторую пользователь устанавливает самостоятельно. Мы в качестве примера установили клавишу М.
4. Далее следует определить, где будет храниться макрос. По умолчанию он расположен в этой же книге (файле), но при желании можно установить хранение в новой книге или в отдельной книге макросов. Мы оставим значение по умолчанию.
5. В самом нижнем поле можно оставить любое подходящее по контексту описание макроса, но это делать не обязательно. Когда все настройки выполнены, жмем на кнопку «OK».



6. После этого все ваши действия в данной книге (файле) Excel будут записываться в макрос до тех пор, пока вы сами не остановите запись.
7. Для примера запишем простейшее арифметическое действие: сложение содержимого трех ячеек (=C4+C5+C6).



8. Когда алгоритм был выполнен, щелкаем на кнопку «Остановить запись». Эта кнопка преобразовалась из кнопки «Запись макроса» после включения записи.

Запуск макроса

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

1. Кликаем в том же блоке инструментов «Код» по кнопке «Макросы» или жмем сочетание клавиш Alt + F8.



2. После этого открывается окно со списком записанных макросов. Ищем макрос, который мы записали, выделяем его и кликаем на кнопку «Выполнить».



3. Можно поступить еще проще и не вызывать даже окно выбора макросов, так как на первом этапе мы задали сочетание клавиш для быстрого вызова макроса. В нашем случае это Ctrl + М. Жмем данную комбинацию на клавиатуре, после чего он запускается.
4. Как видим, он выполнил в точности все те действия, которые были записаны ранее.

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

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

1. Снова щелкаем на кнопку «Макросы». В открывшемся окне выбираем нужный и кликаем по кнопке «Изменить».



2. Открывается «Microsoft Visual Basic» (VBE) – среда, где происходит их редактирование.



3. Запись каждого макроса начинается с команды Sub, а заканчивается командой End Sub. Сразу же после Sub указывается имя макроса. Оператор Range("…").Select указывает выбор ячейки. Например, при команде «Range(«C4»).Select» выбирается ячейка «C4». Оператор ActiveCell.FormulaR1C1 используется для записи действий в формулах и других расчетов.
4. Попытаемся немного изменить макрос, дописав выражение:

   Range("C3").Select
ActiveCell.FormulaR1C1 = "11"

5. Выражение ActiveCell.FormulaR1C1 = "=R[-3]C+R[-2]C+R[-1]C" заменим на ActiveCell.FormulaR1C1 = "= R[-4]C+R[-3]C+R[-2]C+R[-1]C".



6. Закрываем редактор и запускаем макрос. Как видим, вследствие введенных нами изменений была добавлена дополнительная ячейка с данными. Она также была включена в расчет общей суммы.
7. В случае если макрос слишком большой, его выполнение может занять значительное время, но внесением ручного изменения в код мы можем ускорить процесс. Добавляем команду Application.ScreenUpdating = False. Она позволит сохранить вычислительные мощности, а значит, ускорить работу. Это достигается путем отказа от обновления экрана во время выполнения вычислительных действий. Чтобы возобновить обновление после выполнения макроса, в его конце пишем команду Application.ScreenUpdating = True.
8. Добавим также команду Application.Calculation = xlCalculationManual в начало кода, а в его конец дописываем Application.Calculation = xlCalculationAutomatic. Этим мы сначала отключаем автоматический пересчет результата после каждого изменения ячеек, а в конце макроса – включаем. Таким образом, Excel подсчитает результат только один раз, а не будет его постоянно пересчитывать, чем сэкономит время.



Вариант 2: Написание кода макроса с нуля

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

1. Чтобы приступить к этому, нужно нажать на кнопку «Visual Basic», которая расположена в самом начале ленты разработчика.



2. Откроется окно редактора VBE, которое уже быдло продемонстрировано в предыдущем варианте.



3. Программист пишет там код макроса вручную.

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

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.


Помогла ли вам эта статья?

ДА НЕТ

Макросы в Excel: создание, изменение, выполнение

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

Создание макросов

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

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

Метод 1: Записываем макрос в автоматическом режиме

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

Узнать, как это можно сделать, можно в нашей статье – “Как включать и отключать макросы в Excel”.

После того, как макросы включены, можно перейти к нашей основной задаче.

1. Переключаемся во вкладку “Разработчик”. В группе инструментов “Код” щелкаем по значку “Записать макрос”.
2. На экране появится окошко, в котором мы можем выполнить настройки записи. Здесь указывается:
   • имя макроса – любое, но должно начинаться с буквы (не с цифры), не допускаются пробелы.
   • комбинация клавиш, которая будет использоваться для запуска макроса. Обязательной является Ctrl, а вторую мы можем назначить в специальном поле. Допустим, пусть это будет клавиша “r”. Если нужно использовать заглавную букву, нужно зажать клавишу Shift, которая будет добавлена в сочетание клавиш.
   • место хранения макроса. Стандартно – это будет текущая книга, но, если требуется, можно выбрать новую книгу или личную книгу макросов. В нашем случае оставляем стандартный вариант.
   • При желании можно добавить описание к макросу.
   • По готовности кликаем OK.
3. Теперь в созданный макрос будут записываться все действия, которые мы будем выполнить в книге. И так до тех пор, пока мы не остановим запись.
4. Допустим, давайте давайте в ячейке D2 выполним умножение двух ячеек (B2 и B3): =B2*B3.
5. После того, как выражение готово, нажимаем клавишу Enter, чтобы получить результат. Затем останавливаем запись макроса, нажав на значок “Стоп” (появилась вместо кнопки “Записать макрос”) в группе инструментов “Код”.

Запускаем выполнение макроса

Чтобы проверить работу записанного макроса, нужно сделать следующее:

1. В той же вкладке (“Разработчик”) и группе “Код” нажимаем кнопку “Макросы” (также можно воспользоваться горячими клавишами Alt+F8).
2. В отобразившемся окошке выбираем наш макрос и жмем по команде “Выполнить”.Примечание: Есть более простой вариант запустить выполнение макроса – воспользоваться сочетанием клавиш, которое мы задали при создании макроса.
3. Результатом проверки будет повторение ранее выполненных (записанных) действий.

Корректируем макрос

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

1. Нажимаем кнопку “Макросы” (или комбинацию Ctrl+F8).
2. В появившемся окошке выбираем наш макрос и щелкаем “Изменить”.
3. На экране отобразится окно редактора “Microsoft Visual Basic”, в котором мы можем внести правки. Структура каждого макроса следующая:
   • открывается с команды “Sub”, закрывается – “End Sub”;
   • после “Sub” отображается имя макроса;
   • далее указано описание (если оно есть) и назначенная комбинация клавиш;
   • команда “Range(“…”).Select” возвращает номер ячейки. К примеру, “Range(“B2″).Select” отбирает ячейку B2.
   • В строке “ActiveCell.FormulaR1C1” указывается значение ячейки или действие в формуле.
4. Давайте попробуем скорректировать макрос, а именно, добавить в него ячейку B4 со значением 3. В код макроса нужно добавить следующие строки:
   Range("B4").Select
ActiveCell.FormulaR1C1 = "3"
5. Для результирующей ячейки D2, соответственно, тоже нужно изменить начальное выражение на следующее:
   ActiveCell.FormulaR1C1 = "=RC[-2]*R[1]C[-2]*R[2]C[-2]".Примечание: Обратите внимание, что адреса ячеек в данной строке (ActiveCell.FormulaR1C1) пишутся в стиле R1C1.
6. Когда все готово, редактор можно закрывать (просто щелкаем на крестик в правом верхнем углу окна).
7. Запускаем выполнение измененного макроса, после чего можем заметить, что в таблице появилась новая заполненная ячейка (B4 со значением “3”), а также, пересчитан результат с учетом измененной формулы.
8. Если мы имеем дело с большим макросом, на выполнение которого может потребоваться немало времени, ручное редактирование изменений поможет быстрее справиться с задачей.
   • Добавив в конце команду Application.ScreenUpdating = False мы можем ускорить работу, так как во время выполнения макроса, изменения на экране отображаться не будут.
   • Если потребуется снова вернуть отображение на экране, пишем команду: Application.ScreenUpdating = True.
9. Чтобы не нагружать программу пересчетом после каждого внесенного изменения, в самом начале пишем команду Application.Calculation = xlCalculationManual, а в конце – Application.Calculation = xlCalculationAutomatic. Теперь вычисление будет выполняться только один раз.

Метод 2: создание макроса вручную

Опытные пользователи Эксель в некоторых случаях предпочитают создавать макросы от начала до конца полностью вручную . План действий следующий:

1. Во вкладке “Разработчик” нажимаем на значком “Visual Basic” (группа инструментов “Код”).
2. В результате, на экране появится уже знакомое окно, которое мы рассмотрели выше.
3. Здесь создается макрос и пишется ее код – полностью вручную.

Заключение

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

Автоматизация задач с помощью средства записи макросов — Excel

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

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

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки «Разработчик».

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

Перед записью макросов полезно знать следующее:

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

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

• В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

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

1. На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.

   -ИЛИ-

   Нажмите ALT+T+M+R.

   

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

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

3. Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции «Отменить» в данном экземпляре Excel.

4. В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

   Как правило, макрос сохраняется в указанном расположении книги , но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите пункт Личная книга макросов . Если выбрать команду Личная книга макросов, Excel создаст скрытую личную книгу макросов (личное. xlsb), если она еще не существует, и сохранит макрос в этой книге.

5. В поле Описание при необходимости введите краткое описание действий макроса.

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

6. Чтобы начать запись макроса, нажмите кнопку ОК.

7. Выполните действия, которые нужно записать.

8. На вкладке разработчик в группе код нажмите кнопку остановить запись .

   -ИЛИ-

   Нажмите ALT+T+M+R.

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Задача	Описание
Изменение параметров безопасности макросов в Excel	Сведения о параметрах безопасности макросов и их значении.
Запуск макроса	Макросы можно запускать различными способами, например с помощью сочетания клавиш, графического объекта, панели быстрого доступа, кнопки или даже при открытии книги.
Изменение макроса	С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.
Копирование модуля макроса в другую книгу	Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.
Назначение макроса объекту, фигуре или графическому элементу	На листе щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому нужно назначить существующий макрос, а затем выберите команду назначить макрос. В поле Назначить макроса выберите макрос, который вы хотите назначить.
Назначение макроса кнопке	Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.
Назначение макроса для элемента управления на листе	Вы можете назначать макросы формам и элементам ActiveX на листе.
Включение и отключение макросов в файлах Office	Узнайте, как включать и отключать макросы в файлах Office.
Открытие редактора Visual Basic	Нажмите клавиши ALT+F11.
Поиск справки по использованию редактора Visual Basic	Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.

Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.

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

Перед записью макросов полезно знать следующее:

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

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

• В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

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

1. Перейдите в раздел настройки > Excel…Панель инструментов & > ленты.

2. В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

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

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

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

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

3. В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

   Как правило, макрос сохраняется в указанном расположении книги , но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите пункт Личная книга макросов. Если выбрать команду Личная книга макросов, в Excel будет создана скрытая личная книга макросов (личное. XLSB), если он еще не существует, и сохранение макроса в этой книге. Книги в этой папке открываются автоматически при запуске Excel, а код, хранящийся в личной книге макросов, будет указан в диалоговом окне Макрос, которое описано в следующем разделе.

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

5. В поле Описание при необходимости введите краткое описание действий макроса.

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

6. Чтобы начать запись макроса, нажмите кнопку ОК.

7. Выполните действия, которые нужно записать.

8. На вкладке Разработчик щелкните Остановить запись.

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. При этом откроется диалоговое окно Макрос.

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Задача	Описание
Включение и отключение макросов	Узнайте, как включать и отключать макросы в Excel для Mac.
Копирование модуля макроса в другую книгу	Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.
Назначение макроса объекту, фигуре или графическому элементу	На листе щелкните правой кнопкой мыши объект, рисунок, фигуру или элемент, которому нужно назначить существующий макрос, а затем выберите команду назначить макрос. В поле Назначить макроса выберите макрос, который вы хотите назначить.
Назначение макроса кнопке	Вы можете назначить макрос значку и добавить его на панель быстрого доступа или ленту.
Назначение макроса для элемента управления на листе	Вы можете назначать макросы формам и элементам ActiveX на листе.
Открытие редактора Visual Basic	На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic.
Поиск справки по использованию редактора Visual Basic	Узнайте, как найти справку по элементам Visual Basic.

Создание макросов и пользовательских функций на VBA

Введение

Всем нам приходится — кому реже, кому чаще — повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую «рутинную составляющую» — одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

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

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP…), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно — редактор программ на VBA, встроенный в Microsoft Excel.

• В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис — Макрос — Редактор Visual Basic (Toos — Macro — Visual Basic Editor).
• В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer). Выбираем Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer). Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic (Visual Basic Editor)
  
  :

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

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

• Обычные модули — используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert — Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:

  

• Модуль Эта книга — также виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.):

  

• Модуль листа — доступен через Project Explorer и через контекстное меню листа, т.е. правой кнопкой мыши по ярлычку листа — команда Исходный текст (View Source). Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (изменение данных в ячейках, пересчет листа, копирование или удаление листа и т.д.)

  

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

Давайте разберем приведенный выше в качестве примера макрос Zamena:

• Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.
• Любой макрос должен заканчиваться оператором End Sub.
• Все, что находится между Sub и End Sub — тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

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

Способ 2. Запись макросов макрорекордером

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

• Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу — запись останавливается.
• Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
• Если во время записи макроса макрорекордером вы ошиблись — ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) — во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

• в Excel 2003 и старше — выбрать в меню Сервис — Макрос — Начать запись (Tools — Macro — Record New Macro)
• в Excel 2007 и новее — нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

• Имя макроса — подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
• Сочетание клавиш — будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис — Макрос — Макросы — Выполнить (Tools — Macro — Macros — Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
• Сохранить в… — здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
• Эта книга — макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
• Новая книга — макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
• Личная книга макросов — это специальная книга Excel  с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.

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

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или — в старых версиях Excel — через меню Сервис — Макрос — Макросы (Tools — Macro — Macros):

• Любой выделенный в списке макрос можно запустить кнопкой Выполнить (Run).
• Кнопка Параметры (Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
• Кнопка Изменить (Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.

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

Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис — Настройка (Tools — Customize) и перейдите на вкладку Команды (Commands). В категории Макросы легко найти веселый желтый «колобок» — Настраиваемую кнопку (Custom button):

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar):

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:

• В Excel 2003 и старше — откройте панель инструментов Формы через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms)
• В Excel 2007 и новее — откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer) 

Выберите объект Кнопка (Button):

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

Создание пользовательских функций на VBA

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция — только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert — Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка — Функция) в категории Определенные пользователем (User Defined):

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

 

 

 

Создание и запуск макроса — Word

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

1. В меню Вид последовательно выберите пункты Макросы и Записать макрос.

   

2. Введите имя макроса.

   

3. Чтобы использовать этот макрос во всех новых документах, проверьте, что в поле Сохранить изменения в указано значение Всех документов (Normal.dotm).

   

4. Чтобы запускать макрос нажатием кнопки, выберите пункт Кнопка.

   

5. Щелкните новый макрос (у него будет имя примерно следующего вида: Normal.NewMacros.<имя вашего макроса>), а затем нажмите кнопку Добавить.

   

6. Нажмите кнопку Изменить.

   

7. Выберите изображение для кнопки, введите нужное имя и дважды нажмите OK.

   

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

   Примечание: Во время записи макроса для выделения текста используйте клавиатуру. Макрос не записывает выделения, сделанные с помощью мыши.

9. Чтобы остановить запись, в меню Вид последовательно выберите пункты Макросы и Остановить запись.

   

На панели быстрого доступа появится кнопка для вашего макроса.

Чтобы запустить макрос, нажмите эту кнопку.

1. В меню Вид последовательно выберите пункты Макросы и Записать макрос.

   

2. Введите имя макроса.

   

3. Чтобы использовать этот макрос во всех новых документах, проверьте, что в поле Сохранить изменения в указано значение Всех документов (Normal.dotm).

   

4. Чтобы запускать макрос с помощью сочетания клавиш, выберите пункт Клавиатура.

   

5. Введите сочетание клавиш в поле Новое сочетание клавиш.

6. Проверьте, не назначено ли такое же сочетание клавиш какой-либо другой команде. Если такое сочетание клавиш назначено какой-либо другой команде, то попробуйте использовать другое сочетание клавиш.

7. Чтобы использовать этот макрос во всех новых документах, проверьте, что в поле Сохранить в указано значение Normal.dotm.

8. Нажмите кнопку Назначить.

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

   Примечание: Во время записи макроса для выделения текста используйте клавиатуру. Макрос не записывает выделения, сделанные с помощью мыши.

10. Чтобы остановить запись, в меню Вид последовательно выберите пункты Макросы и Остановить запись.

    

Чтобы запустить макрос, нажмите сочетание клавиш.

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

1. В меню Вид последовательно выберите пункты Макросы и Просмотр макросов.

   

2. В пункте Имя макроса выберите в списке макрос, который вы хотите запустить.

3. Нажмите кнопку Запустить.

Чтобы сделать макрос в одном документе доступным во всех новых документах, добавьте его в шаблон Normal.dotm.

1. Откройте документ, в котором содержится макрос.

2. В меню Вид последовательно выберите пункты Макросы и Просмотр макросов.

   

3. Нажмите кнопку Организатор.

   

4. Щелкните макрос, который вы хотите добавить в шаблон Normal.dotm, а затем нажмите кнопку Копировать.

1. В меню Файл последовательно выберите пункты Параметры и Настроить ленту.

2. В пункте Выбрать команды из выберите пункт Макросы.

3. Выберите нужный макрос.

4. В пункте Настройка ленты выберите вкладку и настраиваемую группу, в которую вы хотите добавить макрос.

Если у вас нет настраиваемой группы, то нажмите кнопку Новая группа. Затем нажмите кнопку Переименовать и введите имя настраиваемой группы.

1. Нажмите кнопку Добавить.

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

3. Дважды нажмите кнопку OK.

1. На вкладке Разработчик в группе Код нажмите кнопку Макросы.

2. В поле Имя макроса введите имя нового макроса.

   Примечание: Если новому макросу присвоить то же самое имя, что и макросу, встроенному в Word, новые макрокоманды будут выполняться вместо встроенных. Чтобы просмотреть список встроенных макросов, выберите пункт Команды Word в списке Макросы из.

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

   Чтобы макрос был доступен во всех документах, выберите Normal.dotm.

4. Нажмите кнопку Создать, чтобы вызвать редактор Visual Basic.

После запуска редактора Visual Basic могут потребоваться дополнительные сведения о работе с языком Visual Basic для приложений. Для получения сведений выберите в меню Help (Справка) пункт Microsoft Visual Basic Help (Справка по Microsoft Visual Basic) или нажмите клавишу F1.

Вы можете записать последовательность действий или создать макрос с нуля, введя код на языке Visual Basic для приложений в редактор Visual Basic.

Примечание: Чтобы работать с макросами в приложении Office Word 2007, необходимо отобразить вкладку Разработчик.

1. Нажмите кнопку Microsoft Office , а затем — Параметры Word.

2. Выберите категорию Основные.

3. В группе Основные параметры работы с Word установите флажок Показывать вкладку «Разработчик» на ленте.

   Примечание: Лента входит в Пользовательский интерфейс Microsoft Office Fluent.

1. На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.

   

2. В поле Имя макроса введите имя нового макроса.

   Примечание: Если новому макросу присвоить то же самое имя, что и макросу, встроенному в Office Word 2007, новые макрокоманды будут выполняться вместо встроенных. Чтобы просмотреть список встроенных макросов, на вкладке Разработчик в группе Код нажмите кнопку Макросы. В списке Макросы из выберите вариант Команды Word.

3. В списке Макрос доступен для щелкните шаблон или документ, в котором нужно сохранить макрос.

   Важно: Чтобы макрос был доступен во всех документах, выберите Normal.dotm.

4. Введите описание макроса в поле Описание.

5. Выполните одно из указанных ниже действий.

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

   • Создайте кнопку.    Чтобы связать макрос с кнопкой на панели быстрого доступа, сделайте следующее:

     1. Щелкните кнопке.

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

        Важно: Чтобы макрос был доступен во всех документах, выберите Normal.dotm.

     3. В диалоговом окне Выбрать команды из выберите макрос, который требуется записать, и нажмите кнопку Добавить.

     4. Чтобы настроить кнопку, нажмите кнопку Изменить.

     5. В поле Символ выберите нужный символ для кнопки.

     6. В поле Отображаемое имя введите имя макроса, которое должно отображаться.

     7. Чтобы начать запись макроса, нажмите кнопку ОК два раза.

        На панели быстрого доступа будет показан выбранный символ. Заданное имя отображается при наведении указателя на символ.

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

     1. Выберите пункт Клавиатура.

     2. В списке Команды выберите макрос, который требуется записать.

     3. В поле Новое сочетание клавиш введите любую последовательность клавиш и нажмите кнопку Назначить.

     4. Чтобы начать запись макроса, нажмите кнопку Закрыть.

6. Выполните действия, которые следует включить в макрос.

   Примечание: При записи макроса вы можете с помощью мыши щелкать команды и параметры, но не выделять текст. Для выделения текста необходимо использовать клавиатуру. Дополнительные сведения о выделении текста с помощью клавиатуры см. в статье Выделение текста.

7. Чтобы остановить запись макрокоманд, выберите команду Остановить запись в группе Код.

Изменение сочетания клавиш для макроса

1. Нажмите кнопку Microsoft Office , а затем — Параметры Word.

2. Выберите команду Настройка .

3. Рядом с полем Сочетания клавиш нажмите Настройка.

4. В списке Категории выберите пункт Макросы.

5. В списке Макросы выберите макрос, который нужно изменить.

6. Щелкните поле Новое сочетание клавиш и нажмите клавиши, которые следует назначить.

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

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

   Важно: Чтобы макрос был доступен во всех документах, выберите Normal.dotm.

9. Нажмите кнопку Закрыть.

1. На вкладке Разработчик в группе Код выберите пункт Макросы.

   

2. В списке Имя макроса выберите макрос, который необходимо запустить.

3. Нажмите кнопку Выполнить.

1. На вкладке Разработчик в группе Код нажмите кнопку Макросы.

   

2. В поле Имя макроса введите имя нового макроса.

   Примечание: Если новому макросу присвоить то же самое имя, что и макросу, встроенному в Office Word 2007, новые макрокоманды будут выполняться вместо встроенных. Чтобы просмотреть список встроенных макросов, выберите пункт Команды Word в списке Макросы из.

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

   Чтобы макрос был доступен во всех документах, выберите Normal.dotm.

4. Нажмите кнопку Создать, чтобы вызвать редактор Visual Basic.

После запуска редактора Visual Basic могут потребоваться дополнительные сведения о работе с языком Visual Basic для приложений. Для получения сведений выберите в меню Help (Справка) пункт Microsoft Visual Basic Help (Справка по Microsoft Visual Basic) или нажмите клавишу F1.

Чтобы быстро выполнять часто повторяющиеся задачи, в классической версии Word вы можете объединить шаги в макросы, а затем запускать их одним щелчком мыши. В Веб-приложение Word такие задачи придется выполнять вручную.

Если у вас есть Word, нажмите кнопку Открыть в Word, чтобы открыть документ в Word.

Затем следуйте инструкциям для классической версии Word.

Если вы запустите макрос в Word и сохраните документ, вы увидите результаты работы макроса при повторном открытии документа в Веб-приложение Word.

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

Вкладка разработчика | Командная кнопка | Назначить макрос | Редактор Visual Basic

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

Вкладка разработчика

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

1.Щелкните правой кнопкой мыши в любом месте ленты и выберите команду «Настроить ленту».

2. В разделе «Настройка ленты» в правой части диалогового окна выберите «Основные вкладки» (при необходимости).

3. Установите флажок Разработчик.

4. Щелкните OK.

5. Вы можете найти вкладку «Разработчик» рядом с вкладкой «Просмотр».

Командная кнопка

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

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

2. В группе ActiveX Controls нажмите кнопку Command.

3. Перетащите командную кнопку на рабочий лист.

Назначить макрос

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

1. Щелкните правой кнопкой мыши CommandButton1 (убедитесь, что выбран режим «Дизайн»).

2. Щелкните Просмотреть код.

Откроется редактор Visual Basic.

3.Поместите курсор между Private Sub CommandButton1_Click () и End Sub.

4. Добавьте строку кода, показанную ниже.

Примечание: окно слева с именами Sheet1 (Sheet1) и ThisWorkbook называется Project Explorer. Если проводник проекта не отображается, щелкните «Просмотр», «Обозреватель проекта». Если окно кода для Sheet1 не отображается, щелкните Sheet1 (Sheet1). Вы можете пока игнорировать оператор Option Explicit.

5. Закройте редактор Visual Basic.

6. Щелкните командную кнопку на листе (убедитесь, что режим «Дизайн» не выбран).

Результат:

Поздравления. Вы только что создали макрос в Excel!

Редактор Visual Basic

Чтобы открыть редактор Visual Basic, на вкладке Разработчик щелкните Visual Basic.

Откроется редактор Visual Basic.

.

Как записывать и создавать собственные макросы Excel

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

Вы, вероятно, уже используете такие функции, как = sum (A1: A5) , простые фрагменты текста, которые складывают, усредняют и вычисляют ваши значения.Именно они делают электронные таблицы мощным инструментом для обработки чисел и текста. Следующий шаг — макросы: это инструменты, автоматизирующие простые задачи и помогающие сделать больше за меньшее время. Вот как разблокировать эту новую часть вашего набора навыков Excel, создав собственные макросы в Excel.

Впервые использовали электронные таблицы? Начните сначала с нашего руководства по электронной таблице 101 — оно знакомит вас с основными функциями электронных таблиц, которые помогут вам начать работу с любым приложением для работы с электронными таблицами: Google Таблицы, Excel или любой другой инструмент для работы с электронными таблицами.

Этот пост был первоначально опубликован в августе 2016 года, но был дополнен дополнительными советами

---

---

Что такое макросы Excel?

Макросы

— это код, который автоматизирует работу в программе — они позволяют вам добавлять свои собственные крошечные функции и улучшения, которые помогут вам выполнить именно то, что вам нужно, быстро, всего лишь одним щелчком кнопки. В таком инструменте работы с электронными таблицами, как Excel, макросы могут быть особенно мощными. Скрытые за обычным пользовательским интерфейсом, они более мощные, чем стандартные функции, которые вы вводите в ячейку (например,грамм. = ЕСЛИ (A2 <100,100; A2) ).

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

Для создания этих макросов вы можете просто записать свои действия в Excel, чтобы сохранить их как повторяемые шаги, или вы можете использовать Visual Basic для приложений (VBA), простой язык программирования, встроенный в Microsoft Office.Мы покажем вам, как использовать и то, и другое ниже, а также поделимся примерами макросов Excel, которые помогут вам начать работу.

Совет: Это руководство и все примеры написаны в Excel 2016 для Windows, но принципы применимы к Excel 2007 и новее как для Mac, так и для ПК.

Зачем нужны макросы Excel?

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

Если бы был только способ нажать одну кнопку и позволить Excel сделать это за вас в одно мгновение ... Ну, вы можете догадаться, что я собираюсь сказать дальше?

Есть!

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

Как создать свой первый макрос Excel

Вы уже знакомы с Excel и знакомы с его сеткой ячеек, в которую вы вводите текст и функции. Однако для создания макросов Excel вам понадобится дополнительный инструмент, встроенный в Excel: редактор Visual Basic.

Перед тем, как продолжить: Не забудьте загрузить файл нашего проекта - он понадобится вам позже, чтобы следовать нашему руководству по макросам Excel.

Встречайте редактор VBA

Excel имеет встроенный инструмент для написания макросов, называемый редактором Visual Basic, или для краткости редактором VBA.Чтобы открыть это, откройте электронную таблицу и используйте ярлык Alt + F11 (для Mac: Fn + Shift + F11 ).

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

Ваши макросы будут состоять из «модулей» или файлов с вашим кодом VBA.Вы добавите новый модуль или откроете существующий в редакторе VBA, а затем введите нужный код. Чтобы вставить модуль, нажмите «Вставить», а затем «Модуль». После этого справа вы увидите пустое место для написания кода.

Как записать макрос Excel

Есть два способа создать макрос: закодировать или записать. Основное внимание в этой статье уделяется первому, но запись макроса настолько проста и удобна, что ее тоже стоит изучить. Запись макроса - хороший способ познакомиться с основами VBA.Позже он служит удобным хранилищем для кода, который вам не нужно запоминать.

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

У этого есть ограничения, поэтому вы не можете автоматизировать каждую задачу или стать экспертом в автоматизации, только записывая. Иногда вам все равно придется вводить или редактировать код вручную.Но это все еще удобный способ начать работу. Вот как это сделать:
1. Перейдите на вкладку «Просмотр» на ленте и щелкните маленькую стрелку под кнопкой «Макросы».
2. Затем нажмите «Записать макрос».
3. Введите имя макроса и нажмите «ОК», чтобы начать запись.
4. Выполните действия в электронной таблице, которые вы хотите превратить в макрос.
5. Когда вы закончите, перейдите на вкладку «Просмотр», снова щелкните маленькую стрелку под кнопкой «Записать макрос» и выберите «Остановить запись».

Теперь используйте ярлык Alt + F11 (для Mac: Fn + Shift + F11 ), чтобы открыть редактор VBA, и дважды щелкните «Модуль 1» в проводнике проекта.

Это ваш первый код! Удивительно, правда? Возможно, вы не написали его сами, но он все равно создан вашими действиями.

Ваш, вероятно, выглядит иначе, чем мой. Вы можете догадаться, что делает мой код?
– Sub Makebold - это просто текст Sub , за которым следует имя, которое я ввел, когда начал запись.
- Зеленая линия на самом деле ничего не делает - это комментарий, в который вы можете добавить объяснение того, что делает макрос.
- Выбор.Font.Bold = True делает значения в выбранных ячейках жирным шрифтом .
- End sub просто сообщает Excel, что макрос здесь останавливается.

Что произойдет, если я изменю часть третьей строки True на False ? Затем макрос удалит любое выделение жирным шрифтом из выделения вместо того, чтобы сделать его жирным.

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

Как кодировать собственные макросы Excel

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

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

Добавьте кнопку для запуска макроса

Вы можете использовать различные объекты Excel в качестве кнопок для запуска макросов, но я предпочитаю использовать фигуру из вкладки «Вставка».Когда вы вставили фигуру, щелкните ее правой кнопкой мыши и выберите «Назначить макрос…». Затем выберите макрос, который вы хотите запускать при щелчке фигуры - возможно, тот, который вы только что создали с записью, и сохраните его, нажав «ОК».

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

Прежде чем мы начнем, следует отметить еще одну вещь: сохранение вашей электронной таблицы с помощью макросов. По умолчанию электронные таблицы Excel имеют расширение .Расширение xlsx не может включать макросы. Вместо этого, когда вы сохраняете электронную таблицу, выберите формат «Excel Macro-Enabled Workbook (* .xlsm)» и добавьте свое имя файла как обычно.

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

---

А теперь приступим к написанию кода!

Копирование и вставка - это самый простой способ перемещения данных, но он по-прежнему утомителен. Что, если бы ваша таблица могла сделать это за вас? С макросом это возможно.Давайте посмотрим, как написать макрос, который будет копировать данные и перемещать их в электронной таблице.

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

Давайте попробуем скопировать все данные из столбцов с A по C в столбцы D по F с помощью VBA. Для начала посмотрим на нужный нам код:

Копирование ячеек с помощью VBA

Копирование в VBA довольно просто.Просто вставьте этот код в редактор VBA: Range («Вставьте здесь диапазон»). Скопируйте . Вот несколько примеров:
– Диапазон («A: C»). Копировать ← копирует столбцы от A до C
– Диапазон («A1: C100»). Копировать ← копирует диапазон A1: C100

Помните, когда вы записывали макрос раньше? Макрос имел Sub Nameofmacro () и End sub в верхней и нижней строке кода. Эти строки всегда должны быть включены. Excel также упрощает это: когда вы вводите «Sub», а затем имя макроса в начале кода, End sub автоматически вставляется в нижнюю строку.

Совет: Не забудьте ввести эти строки вручную, когда вы не используете средство записи макросов.

Склеивание ячеек с помощью VBA

Наклеивание можно выполнить по-разному в зависимости от того, что вы хотите вставить. В 99% случаев вам понадобится одна из этих двух строк кода:
– Диапазон («Ячейка / область, в которую вы хотите вставить»). Вставьте специальные ← вставки как обычно (формулы и форматирование)
- Диапазон («Ячейка / область, в которую нужно вставить»). Pastespecial xlPasteValues ​​ ← вставляет только значения

Ячейки для резки с VBA

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

Вот код: Диапазон («Вставьте здесь диапазон»). Вырезать

При вырезании нельзя использовать команду «Специальная вставка». Это означает, что нельзя вставлять только значения или только форматирование. Следовательно, вам нужны эти строки, чтобы вставить ячейки с помощью VBA:
Range («Вставьте то место, где вы хотите вставить»). Выберите
ActiveSheet.Paste

Например, вот код, который вам нужно вырезать из диапазона A: C и вставить в D1 :
– Range («A: C»).Вырезать
– Диапазон («D1»). Выберите
– ActiveSheet. Вставить

.

---

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

Добавление циклов в VBA

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

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

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

Давайте сделаем петлю, которая исправит это за вас. Введите этот код в модуль и посмотрите пояснения под картинкой:

1. Эта строка гарантирует, что цикл начинается в верхней левой ячейке листа и случайно не испортит данные, начавшись где-то еще.
2. Строка For i = от 1 до 500 означает, что количество запусков цикла (представленное i ) является возрастающим числом, которое начинается с 1 и заканчивается на 500.Это означает, что цикл будет выполнен 500 раз. Количество запусков цикла зависит от того, какие действия вы хотите выполнить. Используйте здесь свой здравый смысл. 500 раз - это слишком много для нашего образца набора данных, но идеально подошло бы, если бы в базе данных было 1500 строк данных.
3. Эта строка распознает активную ячейку и указывает Excel переместиться на 3 строки вниз и выбрать эту ячейку, которая затем станет новой активной ячейкой. Если бы в наших данных была потеряна каждая четвертая строка, а не каждая третья, мы могли бы просто заменить 3 на 4 в этой строке.
4. Эта строка сообщает Excel, что делать с этой вновь выбранной ячейкой. В этом случае мы хотим удалить ячейку таким образом, чтобы ячейки справа от ячейки сдвинулись влево. Это достигается с помощью этой линии. Если бы мы хотели сделать что-то еще с неуместными строками, то это самое подходящее место. Если бы мы хотели полностью удалить каждую третью строку, то должна была бы быть строка: Selection.Entirerow.delete .
5. Эта строка сообщает Excel, что в цикле больше нет действий.В этом случае 2 и 5 - это кадр цикла, а 3 и 4 - действия внутри цикла.

Когда мы запустим этот макрос, это приведет к аккуратному набору данных без каких-либо неуместных строк.

Добавление логики в VBA

Logic - это то, что оживляет фрагмент кода, делая его больше, чем просто машину, которая может выполнять простые действия и повторяться. Логика - это то, что делает Excel-лист почти человеком - он позволяет ему самостоятельно принимать разумные решения. Давайте использовать это для автоматизации!

Этот раздел посвящен операторам IF, которые включают логику «если-то-то-то», как и функция ЕСЛИ в Excel.

Допустим, экспорт с CMS нашего сайта оказался еще более ошибочным, чем ожидалось. Каждая третья строка по-прежнему неуместна, но теперь некоторые из неуместных строк размещаются на 2 столбца справа, а не на 1 столбец справа. Взгляните на лист «IF-оператор» в файле проекта, чтобы увидеть, как он выглядит.

Как это учесть в нашем макросе? Добавляем в цикл IF-оператор!

Давайте сформулируем, что мы хотим, чтобы Excel делал:

Начинаем с ячейки A1.Затем мы переходим на три строки вниз (до ячейки A4, A7, A10 и т. Д.), Пока не кончатся данные. Каждый раз, когда мы опускаемся на три строки вниз, мы проверяем эту строку, чтобы увидеть, не смещены ли данные на 1 или 2 столбца. Затем переместите данные в строке на 1 или 2 столбца влево.

Теперь давайте переведем это в код VBA. Начнем с простого цикла, как и раньше:

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

Это последний код, который нужно скопировать в редактор модуля, каждый шаг описан ниже:

1. Это первая часть IF-оператора. В нем говорится, что , если ячейка справа от активной ячейки (или Activecell.Offset (0,1) в коде VBA) пуста (представлена ​​ = "" ) , тогда что-то сделайте. Это то же самое действие, что и при создании цикла: удаление активной ячейки и перемещение активной строки на одну ячейку влево (выполняется с помощью Selection.Удалить Shift: = код xlToLeft ). На этот раз мы делаем это два раза вместо одного, потому что в левой части строки есть две пустые ячейки.
2. Если указанное выше неверно, и ячейка справа от активной ячейки не пуста, то активная ячейка пуста. Следовательно, нам нужно только удалить активную ячейку и переместить активную строку на одну ячейку влево один раз.

IF-оператор всегда должен заканчиваться End If , чтобы сообщить Excel о завершении работы. После IF-оператора цикл может выполняться снова и снова, каждый раз повторяя IF-оператор

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

Автоматизация Excel без макросов

У макросов

Excel есть только одна проблема: они привязаны к вашему компьютеру и не могут работать в Excel Web App или на вашем мобильном устройстве. И они лучше всего работают с данными, уже находящимися в вашей электронной таблице, что затрудняет перенос новых данных из других ваших приложений в вашу электронную таблицу.

Инструмент интеграции приложений Zapier может помочь. Он соединяет версию Excel Office 365 для бизнеса с сотнями других приложений - Stripe, Salesforce, Slack и др. - так что вы можете автоматически записывать данные в электронную таблицу или запускать задачи в других приложениях прямо из Excel.

Вот как это работает. Скажем, вы хотите сохранить записи формы Typeform в электронную таблицу Excel. Просто создайте учетную запись Zapier и нажмите кнопку Make a Zap в правом верхнем углу. Затем выберите Typeform в средстве выбора приложения и настройте его для отслеживания вашей формы на предмет новых записей.

Zapier может следить за вашей формой Typeform на предмет новых записей

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

Zapier позволяет добавлять, обновлять или находить строки в электронной таблице Excel

Теперь выберите электронную таблицу и рабочий лист, затем щелкните значок + справа от каждой строки электронной таблицы, чтобы выбрать правильное поле формы для сохранения в этой строке электронной таблицы. Сохраните и протестируйте интеграцию с Zapier, а затем включите ее. Затем каждый раз, когда ваша форма Typeform заполняется, Zapier будет сохранять эти данные в вашу электронную таблицу Excel.

Zapier может добавлять данные формы прямо в нужную строку электронной таблицы

Вот несколько отличных способов начать автоматизацию Excel с помощью Zapier за несколько щелчков мышью или создать собственную интеграцию с Excel для подключения ваших электронных таблиц к любимым приложениям.

Управление данными электронных таблиц

Сохранение записей формы в таблице Excel

Журнал данных в электронную таблицу Excel

Работайте с таблицей

---

Go Создайте свои собственные макросы!

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

Чтобы узнать больше, вот несколько дополнительных ресурсов, которые помогут вам максимально эффективно использовать макросы Excel:
- Узнайте больше о том, как повторять код VBA с циклами
- Узнайте глубже, что вы можете сделать с if операторами и другой логикой прыжки в Excel Macros
- Изучите справочник Microsoft Excel VBA, чтобы изучить все, что вы можете делать с кодом и макросами VBA.
- Использовать Google Таблицы вместо Excel? Ознакомьтесь с нашим руководством по скрипту Google Apps, чтобы узнать, как автоматизировать Google Sheets

. .

Как создать макрос с нуля в Excel

«Секрет успеха в том, чтобы начать» - Марк Твен.

В этом посте рассказывается, как создать макрос в Excel. Даже если вы никогда не создавали его раньше, вам будет легко следить за этим постом.

Давайте начнем с быстрого взгляда, что такое макросы?

Что такое макросы Excel?

Термин «макросы Excel» вызывает большую путаницу. Вкратце, макросы Excel относятся к языку программирования VBA, который встроен в Excel.

Когда используются термины Excel Macros или VBA , они относятся к одному и тому же. Вообще говоря, кто-то, кто плохо знаком с VBA, будет использовать термин Macros , а как только они станут знакомыми, они будут использовать термин VBA .

Что такое VBA?

VBA - это язык программирования Visual Basic для приложений. Этот язык предустановлен в Excel. Это очень мощный инструмент, который позволяет расширить функциональные возможности Excel.

VBA также входит в комплект всех других приложений Microsoft Office, таких как Microsoft Word, Access и Outlook.

В следующих разделах мы собираемся создать макрос с нуля. Под макросом мы подразумеваем Sub .

Sub (сокращение от Subroutine) - это просто набор строк кода VBA, который выполняет определенные задачи. Любое приложение VBA, которое вы создаете, по сути представляет собой набор Subs.

Итак, теперь мы понимаем фон, давайте создадим макрос.

Шаг 1. Сделайте вкладку разработчика видимой

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

1. Щелкните ленту правой кнопкой мыши. В появившемся списке выберите «Настроить ленту»

.

2. В появившемся диалоговом окне вы увидите список вкладок с правой стороны. Убедитесь, что установлен флажок «Разработчик».

Шаг 2 Проверьте настройки безопасности

Следующим шагом является проверка правильности настроек макроса. На ленте разработчика нажмите кнопку «Безопасность макросов» в разделе «Код».

Ваши настройки безопасности должны совпадать с настройками на скриншоте ниже

Шаг 3 Сохранить как правильный тип файла

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

Выберите File-> Save As и выберите «Macro Enabled Workbook» в типе файла. Затем нажмите «Сохранить».

Шаг 4 Войдите в редактор Visual Basic

Чтобы войти в редактор Visual Basic, щелкните значок Visual Basic на вкладке «Разработчик» на ленте. В качестве альтернативы вы можете нажать Alt + F11 на клавиатуре

Шаг 5 Создайте модуль

Макросы хранятся в модулях, поэтому создайте модуль.

Посмотрите в окне проекта VBA. Если окно не отображается, выберите в меню «Просмотр» -> «Проводник проекта» (сочетание клавиш Ctrl и R).

Выберите книгу, в которую вы хотите поместить код. Рабочие книги будут заключены в скобки рядом с «VBAProject». Каждая открытая книга будет иметь такую ​​запись в окне проекта VBA

.

Щелкните правой кнопкой мыши книгу, в которую вы хотите поместить код.Выберите «Вставить», а затем «Модуль».

появится новый модуль:

Когда вы дважды щелкаете модуль в окне проекта VBA, код этого модуля отображается в главном окне кода. Когда создается новый модуль, он обычно не содержит никакого кода (обратите внимание, что он может содержать "Option Explicit", если у вас включена эта опция).

Шаг 6 Создайте макрос

Введите «Sub MyFirstMacro» в главном окне кода и нажмите клавишу возврата.У вас должны быть следующие

Шаг 7 Добавьте код в свой макрос

Используйте MsgBox «Hello World» . Это отобразит диалоговое окно с «Hello World» для пользователя

.

Шаг 8 Запустите макрос

Для запуска макроса:

1. Щелкните в любом месте макроса
2. Выберите Run-> Run Sub / UserForm

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

Вы должны увидеть это диалоговое окно

Заключение

В этом посте показано, как начать работу с VBA. Он прошел через шаги для правильной настройки и закончился запуском макроса.

Теперь вы готовы начать работу с VBA / макросами. Возможно, вы захотите ознакомиться с The Ultimate VBA Tutorial, предназначенным для начинающих.

Что дальше?

Бесплатное руководство по VBA Если вы новичок в VBA или хотите отточить свои существующие навыки VBA, почему бы не попробовать The Ultimate VBA Tutorial.

Связанное обучение: Получите полный доступ к обучающим веб-семинарам по Excel VBA и всем учебным материалам.

( ПРИМЕЧАНИЕ: Планируете создание приложения VBA или управление им? Узнайте, как создать 10 приложений Excel VBA с нуля.)

Получите бесплатную электронную книгу

Пожалуйста, не стесняйтесь подписаться на мой информационный бюллетень и получать эксклюзивный контент VBA, который вы не можете найти здесь, в блоге, а также бесплатный доступ к моей электронной книге How to Ace the 21 Most Common Question in VBA , которая полна примеров, которые вы можно использовать в собственном коде.

.

Step by Step Учебник для начинающих

• Home

• Testing

  • • Back
    • Agile Testing
    • BugZilla
    • Cucumber
    • Database Testing
    9000 J5000
    • 9000 J5000 Тестирование базы данных
    • JUnit
    • LoadRunner
    • Ручное тестирование
    • Мобильное тестирование
    • Mantis
    • Почтальон
    • QTP
    • Назад
    • Центр качества (ALM)
    • Управление тестированием
    • TestLink

• SAP

  • • Назад
    • ABAP
    • APO
    • Начинающий
    • Basis
    • BODS
    • BI
    • BPC
    • CO
    • Назад
    • CRM
    • Crystal Reports
    • MM5000
    • Crystal Reports
    • Заработная плата
    • Назад
    • PI / PO
    • PP
    • SD
    • SAPUI5
    • Безопасность
    • Менеджер решений
    • Successfactors
    • SAP Tutorials
  • 8 SAP Tutorials
• 8
• Apache
• AngularJS
• ASP.Net
• C
• C #
• C ++
• CodeIgniter
• СУБД
• JavaScript

• Назад
• Java
• JSP
• Kotlin
• Linux
• Linux
• Kotlin
• Linux
js
• Perl

• Назад
• PHP
• PL / SQL
• PostgreSQL
• Python
• ReactJS
• Ruby & Rails
• Scala
• SQL
0000004 SQL
• UML
• VB.Net
• VBScript
• Веб-службы
• WPF

Обязательно учите!

• • Назад
  • Бухгалтерский учет
  • Алгоритмы
  • Android
  • Блокчейн
  • Business Analyst
  • Веб-сайт сборки
  • CCNA
  • Облачные вычисления
  0005
  • COBOL 9000 Compiler
    0005
    9000 Встроенный COBOL 9000 Дизайн 9000
    • Ethical Hacking
    • Учебные пособия по Excel
    • Программирование на Go
    • IoT
    • ITIL
    • Jenkins
    • MIS
    • Сетевые подключения
    • Операционная система
    • Назад
    Управление проектами Обзоры
    • Salesforce
    • SEO
    • Разработка программного обеспечения
    • VBA

  • Большие данные

    .