Что такое макросы в excel и зачем они нужны

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

Работа с макросами Excel для чайников

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

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

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

Термин «Макрос» слышало множество людей. Нередко при запуске таблицы появляется предупреждение: “Этот документ использует макросы, способные навредить данному компьютеру, поэтому они отключены с целью защиты от вредоносных действий”.

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

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

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

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

Макросы можно запускать по нажатию определенной комбинации клавиш. Например, если нажать Ctrl+J, можно запустить подпрограмму.

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

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

Когда какой тип записи макросов использовать?

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

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

Пример использования макросов №1

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

‘ Подпрограмма для поиска ячеек с адресами A1-A100 текущего активного листа

‘ и поиска ячеек, в которых содержится требуемая строка

Sub Find_String(sFindText As String)

Dim i As Integer ‘ Целочисленная переменная, которая используется в цикле типа «For»

Dim iRowNumber As Integer ‘ Целочисленная переменная, предназначенная для сохранения результата

‘ Цикл через ячейки A1-A100 до тех пор, пока не будет найдена строка ‘sFindText’

If Cells(i, 1).Value = sFindText Then

‘ Совпадение обнаружено для заданной строки

‘ Сохранение текущего номера строки и выход из цикла

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

If iRowNumber = 0 Then

MsgBox «String » & sFindText & » not found»

MsgBox «String » & sFindText & » found in cell A» & iRowNumber

Пример 2

Эта процедура перечисляет все значения числовой последовательности Фибоначчи, вплоть до 1000. В этом примере приводятся следующие возможности макросов Excel:

‘ Подпрограмма для перечисления всех значений последовательности Fibonacci для всех значений ниже тысячи

Dim i As Integer ‘ счетчик для позиции в серии значений

Dim iFib As Integer ‘ сохраняет текущее значение в серии

Dim iFib_Next As Integer ‘ сохраняет следующее значение в серии

Dim iStep As Integer ‘ хранит размер следующего шага

‘ Инициализация переменных variables i и iFib_Next

‘ Цикл Do While, который исполняется до тех пор, пока номер

‘ числа в последовательности Фибоначчи меньше 1000.

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

‘ Подпрограмма, которая хранит значения колонки А текущего листа

Dim iRow As Integer ‘ сохраняется текущий номер строки

Dim dCellValues() As Double ‘ массив, в котором хранятся значения ячеек

ReDim dCellValues(1 To 10)

‘ Цикл Do Until, который извлекает значение каждой ячейки в столбце А

‘ активного листа до тех пор, пока ячейка не окажется пустой

Do Until IsEmpty(Cells(iRow, 1))

‘ Проверка, достаточно ли большой массив dCellValues

‘ Если нет, используется ReDim, чтобы увеличить размер массива на 10 элементов.

If UBound(dCellValues) Следующая процедура «Sub» читает содержимое ячеек из колонки А другого листа, имеющего название «Sheet2» и с этими значениями осуществляет арифметические операции. Результат вычислений пишется в колонке А текущего листа.

Этот пример показывает:

‘ Подпрограмма, запускающая цикл через значения в колонке А текущего листа

‘ «Sheet2», perform arithmetic operations on each value, and write the

‘ result into Column A of the current Active Worksheet («Sheet1»)

Dim dVal As Double

‘ Установить переменную Col в колонку А листа 2

Set Col = Sheets(«Sheet2»).Columns(«A»)

‘ Прохождение цикла через каждую ячейку колонки ‘Col’ до тех пор, пока

‘ не будет обнаружена пустая ячейка

Do Until IsEmpty(Col.Cells(i))

‘ Применение арифметических операций к значению текущей ячейки

dVal = Col.Cells(i).Value * 3 — 1

‘ Команда ниже копирует результат в колонку А

‘ текущего активного листа — без уточнения названия активного листа

Пример 5

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

‘ Код для отображения диалогового окна ячейки B1 текущего листа в случае, если она выбрана

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

‘ Проверка, выбрана ли ячейка B1

If Target.Count = 1 And Target.Row = 1 And Target.Column = 2 Then

‘ При выборе ячейки B1 показать диалоговое окно

MsgBox «You have selected cell B1»

Пример 6

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

‘ Подпрограмма, для для установки определенных значений

‘ в ячейках A1 и B1 документа «Data.xls» на диске C:\

Sub Set_Values(Val1 As Double, Val2 As Double)

Dim DataWorkbook As Workbook

On Error GoTo ErrorHandling

‘ Открытие документа с данными

Set DataWorkbook = Workbooks.Open(«C:\Documents and Settings\Data»)

‘ Выбрать переменные Val1 and Val2 с данных в книге Excel

Val1 = Sheets(«Sheet1»).Cells(1, 1)

Val2 = Sheets(«Sheet1»).Cells(1, 2)

‘ Если файл не найден, предложить пользователю найти правильную директорию

‘ после чего продолжить выполнение подпрограммы

MsgBox «Data Workbook not found;» & _

«Please add the workbook to C:\Documents and Settings and click OK»

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

Рекомендации по использованию макросов

Есть несколько рекомендаций, позволяющих значительно увеличить эффективность использования макросов в электронных таблицах:

Выводы

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

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

Источник

Краткое руководство: создание макроса

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

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

Процедура

Перед записью макроса

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

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

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

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

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

Подробнее о макросах

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

Чтобы изменить макрос, в группе Код на вкладке Разработчик нажмите кнопку Макрос, выберите имя макроса и нажмите кнопку Изменить. При этом Visual Basic редактора.

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

Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос. На этот раз посмотрите, не произойдет ли что-то другое!

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

Дополнительные информацию о создании макроса см. в теме Создание и удаление макроса.

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

Процедура

Перед записью макроса

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

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

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

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

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

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

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

Подробнее о макросах

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

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

Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос. На этот раз посмотрите, не произойдет ли что-то другое!

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Что такое макрос в Excel и для чего он предназначен?

Доброго времени, друзья. В сегодняшнем выпуске расскажу об интереснейшей функции в самом ходовом офисном пакете программ от Microsoft Office. Тем, кто постоянно работает с таблицами Excel, знает сколько рутинных операций приходится иногда делать с содержимым. Если документов много — приходится одни и те же действия повторять многократно. От выделения мышкой и копирования до более сложных — таких как вычисления, заполнение, очистка отдельных граф, создание таблиц, макетов и так далее.

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

Лично для меня знакомство с макросами в свое время превратилось в изучение программирования. Дело в том, что Microsoft Office является еще и легкой и понятной средой для этого. В нем есть свой встроенный язык — Visual Basic for Application или сокращенно VBA. И когда записываете макросы, Вы вы как бы становитесь программистом. При этом не обязательно на первых этапах знать язык. Программный код можно посмотреть уже потом, в редакторе и изучать. Как смотреть — об этом так же расскажу.

VBA позволяет создавать для себя мощные инструменты при работе с табличными документами для решения многих задач в виде отдельных окон, кнопок, списков, флажков — все визуально. А опытные программисты знают, что макросы можно использовать для того чтобы не набирать команды руками, а достать уже готовый код из только что записанного макроса. Но обо всем по порядку. Тем, кто после прочтения статьи заинтересуется и захочет изучать язык VBA самостоятельно в помощь книга. А сейчас разберемся подробно, что за «макросы», как их включать.

Что означает макросы и как с ними работать?

Итак, макрос — это некий программный код, который описывает ПОСЛЕДОВАТЕЛЬНОСТЬ ваших действий в документе. Записывается даже перемещение вашего курсора по ячейкам, нажатие клавиш. Сначала Вы записываете макрос. Когда понадобится, Вы в Excel запускаете этот код на выполнение и все записанные ранее действия выполняются автоматически. Например, у Вас есть одна и та же таблица, макет которой Вы каждую неделю создаете по новый, меняете оформление, или очищаете графы. Вы нажимаете кнопку, и вот, то на что вы тратили 5-10 минут заняло у вас всего одну секунду.

Сначала настроим «Ленту»; нужно включить «Режим разработчика»:

Если Вы собираетесь серьезно работать с макросами — нужно включить еще одну настройку. Здесь же переходим в «Центр управления безопасностью» и настраиваем «Параметры центра управления безопасностью»:

Нужно отключить все ограничения на запуск макросов. Это позволит Вам не только работать со своими макросами но и запускать уже готовые кем — то созданные.

Так же нужно доверять доступ к объектной модели проектов VBA. Ведь в Интернете или у коллег можно найти много готовых и интересных проектов VBA написанных для Excel, Word и пользоваться.

Что такое макрос в Excel 2007?

Мы пока что научимся записывать несложные макросы. Сделать это очень просто. В «Ленте» у нас теперь появились соответствующие значки «Разработчик»:

В этом режиме нам будут доступны и остальные функции — «режим конструктора». Можно самостоятельно создавать окна, кнопки. А в редакторе Visual Basic можно редактировать созданные макросы. Итак, покажу, как все это работает. При нажатии кнопки «Запись макроса» сначала нужно будет заполнить его наименование(без пробелов), горячие клавиши вызова (не обязательно) и место где он будет сохранен:

В зависимости от поставленной задачи Вы можете сохранять макросы в отдельном документе либо в «личной книге». В первом случае он записывается в пределах документа и будет выполняться только в документе. Во втором случае его можно вызвать перед созданием документа. Лучше продемонстрировать наглядно, для чего бывают нужны макросы. Задача: мне нужно создать: документ Excel, в документе создать таблицу определенного формата и вычислениями внутри. Смотрим видео, что получилось:

Вы сами увидели, что процесс создания одной простенькой демо — таблицы занимает от пяти и более минут. А макрокомандой мы таблицу создали за 2 секунды. А если речь идет о сложных проектах? Ответ очевиден, создав один раз процедуру, вы сэкономите время и увеличите производительность своей работы.

После окончания записи при закрытии Excel программа обязательно попросит сохранить изменения в «Личной книге» макросов. Не забываем согласиться; в противном случае записанный макрос пропадет и всё придется делать заново.

Важно! «Личная книга» макросов хранится в папке пользователя по пути C:\Users\%Пользователь компьютера%\AppData\Roaming\Microsoft\Excel\XLSTART. Этой книгой можно делится с другими или удалять, если что то не получилось.

Наш макрос теперь доступен по кнопке «Макросы», оттуда его можно запускать.

Что значит макрос в Excel 2016 и для чего он предназначен?

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

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

Для начала не забудем сделать настройки MSoffice 2016. Так же идем в «Параметры», но далее идем в «Настроить ленту»

После «Разработчик» станет видимым на «Ленте». Переходим туда. Наша личная книга макросов по прежнему на месте, откроем ее:

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

Как включить макросы в Excel 2016 видео

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

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

Далее, программа предложит способ создания макроса. Если Вы уже продвинутый VBA- програмиист, можно руками написать код, нажав «Создать». Но мы сегодня будем использовать уже знакомый нам способ — запись наших действий:

Запись начата! Мы собираемся очистить выбранные графы от содержимого, поэтому одновременно выделим нужные диапазоны в графах «Количество» и «Цена». Для этого сначала выделим графу «Количество», затем зажимаем клавишу Ctrl и не отпуская ее выделяем нужные ячейки в графе «Цена», после отпускаем клавишу:

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

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

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

Я изменил текст на «Очистить». Цвет, размер текста шрифт в кнопках так же можно менять по вкусу. Заглянем для этого в «Формат объекта»:

Там все примерно так, как при форматировании ячейки. А изменять размеры самой кнопки и ее местоположение можно растягивая ее за края или перетаскивать:

Другой способ — можно щелкнуть правой кнопкой мыши по кнопке и выбрать «Назначить макрос»; результат будет аналогичный. Открылся редактор VBA, в котором можно делать много чего интересного и полезного. Мы пока смотрим, что мы там напрограммировали:

Простой и понятный пример кода; после комментариев (выделенных зеленым) идет сам код «Выбрать диапазон ячеек сначала один, затем другой, затем удаляем содержимое». Закрыть редактор можно как обычное окошко — нажав на крестик. Надеюсь, что такое макросы — Вы разобрались. Заодно узнали, как можно стать программистом. Удачи в освоении программы!

Источник

• ← Что такое макросъемка в фотоаппарате
• Что такое макросы в excel и как с ними работать →