Макросы в excel как создавать: примеры и как сделать / Skillbox Media

Макросы Excel — Безопасность — CoderLessons.com

Макросы, которые вы создаете в Excel, будут написаны на языке программирования VBA (Visual Basic для приложений). Вы узнаете о макросе Excel в следующих главах. Как вы знаете, когда есть исполняемый код, существует угроза вирусов. Макросы также подвержены вирусам.

Что такое макро-вирусы?

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

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

Книги Excel с поддержкой макросов

Самая важная функция безопасности макросов Excel – расширения файлов.

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

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

Способы доверять книге с поддержкой макросов

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

• Помещение книг с поддержкой макросов в надежную папку

• Проверка, если макрос имеет цифровую подпись

• Включение предупреждений безопасности перед открытием книг с поддержкой макросов

Помещение книг с поддержкой макросов в надежную папку

Проверка, если макрос имеет цифровую подпись

Включение предупреждений безопасности перед открытием книг с поддержкой макросов

Помещение книг с поддержкой макросов в надежную папку

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

Проверка, если макрос имеет цифровую подпись

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

Включение предупреждений безопасности перед открытием книг с поддержкой макросов

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

Вы можете установить любой из этих трех параметров в центре управления безопасностью в параметрах Excel.

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

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

Настройки безопасности макросов в центре управления безопасностью

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

• Нажмите вкладку ФАЙЛ на ленте.

• Нажмите Опции. Откроется диалоговое окно «Параметры Excel».

• Нажмите Центр управления безопасностью на левой панели.

• Нажмите кнопку Настройки центра управления безопасностью в Центре управления безопасностью Microsoft Excel.

Нажмите вкладку ФАЙЛ на ленте.

Нажмите Опции. Откроется диалоговое окно «Параметры Excel».

Нажмите Центр управления безопасностью на левой панели.

Нажмите кнопку Настройки центра управления безопасностью в Центре управления безопасностью Microsoft Excel.

Откроется диалоговое окно « Центр управления безопасностью ».

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

Настройки макроса

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

В настройках макроса доступны четыре параметра.

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

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

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

• Включить все макросы (не рекомендуется, восприимчивы к макровирусам) – если выбрана эта опция, запускаются все макросы. Этот параметр делает ваш компьютер уязвимым для потенциально вредоносного кода.

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

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

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

Включить все макросы (не рекомендуется, восприимчивы к макровирусам) – если выбрана эта опция, запускаются все макросы. Этот параметр делает ваш компьютер уязвимым для потенциально вредоносного кода.

У вас есть дополнительная опция безопасности в Настройках макроса разработчика с флажком.

• Доверительный доступ к объектной модели проекта VBA.

  • Этот параметр обеспечивает программный доступ к объектной модели Visual Basic для приложений (VBA) из клиента автоматизации.

  • Этот параметр безопасности предназначен для кода, написанного для автоматизации программы Office и управления средой VBA и объектной моделью.

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

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

Доверительный доступ к объектной модели проекта VBA.

Этот параметр обеспечивает программный доступ к объектной модели Visual Basic для приложений (VBA) из клиента автоматизации.

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

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

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

Определение надежного местоположения

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

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

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

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

Вы также можете найти варианты, которые офис не рекомендует, например, места в Интернете.

Макросы с цифровой подписью из надежных источников

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

Вы найдете доверенных издателей в центре доверия.

• Нажмите Надежные издатели в диалоговом окне центра управления безопасностью. Справа появится список сертификатов с подробной информацией – выдано, выдано и срок действия.

• Выберите сертификат и нажмите «Просмотр».

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

Выберите сертификат и нажмите «Просмотр».

Информация о сертификате отображается.

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

Использование предупреждающих сообщений

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

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

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

Включение / отключение предупреждений безопасности на панели сообщений

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

• Нажмите вкладку ФАЙЛ на ленте.
• Нажмите Опции. Откроется диалоговое окно «Параметры Excel».
• Нажмите Траст-центр.
• Нажмите кнопку Настройки центра управления безопасностью.
• Нажмите Панель сообщений.

Появятся настройки панели сообщений для всех приложений Office.

В разделе « Отображение панели сообщений» есть два варианта.

Вариант 1 – Показывать панель сообщений во всех приложениях, когда активный контент, такой как макросы, заблокирован.

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

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

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

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

Вариант 2 – Никогда не показывать информацию о заблокированном контенте.

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

для чего нужны и как создать

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

Макрос в Excel — это записанная последовательность действий, которую можно выполнить автоматически. Кстати, ударение в слове «макрос» ставится на первый слог — мАкрос. По-другому его можно назвать макрокомандой.

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

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

Макрос — то же самое, что и формула?

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

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

Источник: unsplash.comПример формулы «СУММЕСЛИМН» на сайте Microsoft. С помощью этой формулы можно совершить суммирование не всех чисел, а по заданным признакам.

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

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

Кому нужно уметь работать с макросами

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

Научиться работать с Excel можно на курсе переподготовки «Бухгалтер» в онлайн-академии «Сигма».

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

Учитесь маркетингу на курсе «Таргетолог и SMM-специалист».

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

Поделиться:

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

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

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

Что нужно знать о макросах

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

Шаг 1: Добавить вкладку «Разработчик»

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

1. Запустить Excel.
2. В левом нижнем углу перейдите к Опции .
3. В новом окне нажмите Настроить ленту .
4. Теперь перейдите в меню «Основные вкладки» и установите флажок « Разработчик ».
5. Нажмите OK .

Шаг 2: Запись макроса

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

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

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

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

Из ленты Excel

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

1. На вкладке «Разработчик» наведите указатель мыши на раздел Code .
2. Щелкните Макросы .
3. В окне макросов выберите Macro , который вы только что записали ранее, и нажмите Run .

Использование пользовательского сочетания клавиш

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

1. В Excel перейдите на вкладку Developer .
2. Наведите указатель мыши на группу Код и выберите Макросы .
3. В окне макросов выберите имя макроса , которое вы часто используете. Затем нажмите Параметры .
4. На клавише быстрого доступа введите Ключ для ярлыка. Нажмите OK и закройте окно.
5. Теперь, чтобы запустить макрос, нажмите клавиши быстрого доступа.

ПРИМЕЧАНИЕ: Если вы введете строчные буквы в сочетание клавиш, это будет Ctrl + Letter . Но для заглавных букв сочетание клавиш будет Ctrl + Shift + Letter . Excel рекомендует использовать заглавные буквы, чтобы не переопределять сочетания клавиш по умолчанию.

Использование кнопки макроса

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

1. В рабочей книге Excel перейдите на вкладку Developer .
2. В разделе «Управление» нажмите Вставить . Под элементами управления формой выберите кнопку (элемент управления формой) .
3. Когда появится значок плюса, щелкните ячейку. В окне «Назначить макрос» выберите макрос и нажмите OK .
4. Чтобы изменить имя кнопки, щелкните правой кнопкой мыши кнопку и выберите Edit Text . Сотрите текущее имя и введите имя своего макроса.
5. Нажмите кнопку , чтобы запустить макрос.

Как редактировать макрос в Excel?

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

1. Запустите Excel и щелкните значок 9.0010 Разработчик вкладка.
2. Наведите курсор на группу Код и нажмите Макросы .
3. В окне макроса выберите Записанный макрос и нажмите Редактировать .
4. Проверьте код Visual Basic и внесите необходимые изменения.
5. Наконец, запустите макрос.

Как удалить макрос в Excel?

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

1. В Excel перейдите на вкладку Developer .
2. В группе Код щелкните Макросы .
3. В окне макроса выберите макрос , который вы хотите удалить, и нажмите кнопку Удалить .
4. Выберите Да для подтверждения.

Как сохранить книги с поддержкой макросов?

По умолчанию Excel сохраняет все книги в формате файла .xls. Итак, если вы попытаетесь сохранить книгу, содержащую макрос, Excel отправит вам сообщение об ошибке « Следующие функции нельзя сохранить в книгах без макросов. ”В таких случаях вы можете изменить тип Сохранить как на . xlsm (рабочая книга Excel с поддержкой макросов). Следуй этим шагам.

1. После нажатия Ctrl + S выберите Нет в окне ошибки.
2. В окне «Сохранить как» выберите Книга Excel с поддержкой макросов (*.xlsm) для параметра «Тип файла» и нажмите «Сохранить ».

Как записывать и запускать макросы в Excel: пошаговое руководство

CPE

9 ноября 2022 г.

Уэйн Уинстон, доктор философии

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

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

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

Добавление вкладки «Разработчик»

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

1. На вкладке «Файл» нажмите «Параметры».
2. На левой панели диалогового окна «Параметры Excel» щелкните «Настроить ленту».
3. Установите флажок Разработчик на панели справа (см. рис. 1). Затем нажмите ОК.

Рисунок 1: Добавление вкладки «Разработчик» на ленту

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

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

1. После перехода на вкладку «Разработчик» вы увидите параметры, показанные на рисунке 2.

Рис. 2. Вкладка «Разработчик»

2. В группе «Код» на вкладке «Разработчик» нажмите кнопку «Запись макроса». Либо щелкните значок «Готово» в левом нижнем углу экрана. Откроется диалоговое окно «Запись макроса» (см. рис. 3).

Рис. 3. Диалоговое окно «Запись макроса»

3. В поле «Имя макроса» замените имя по умолчанию на более описательное.
4. Если вы хотите использовать горячую клавишу для запуска макроса, введите нужную клавишу в поле Быстрая клавиша. Горячая клавиша — это последовательность нажатий клавиш, которые можно использовать для запуска макроса. Например, если вы нажмете Shift+F, то нажатие Ctrl+Shift+F запустит ваш макрос. При назначении сочетания клавиш убедитесь, что вы не выбрали ту, которая уже используется для выполнения задачи. Например, поскольку Ctrl+B применяет полужирный шрифт, вы не можете назначить эту комбинацию клавиш своему макросу.
5. По умолчанию Excel сохраняет макрос в текущей книге, что делает его доступным для запуска при каждом открытии книги. Если вы хотите, чтобы ваш макрос всегда был доступен, независимо от того, какую книгу вы используете, откройте «Сохранить макрос» в раскрывающемся списке и выберите «Личная книга макросов».
6. При необходимости введите описание макроса в поле Описание. Затем нажмите ОК.
7. Выполните шаги, которые вы хотите повторить при запуске макроса.
8. В группе Код на вкладке Разработчик нажмите кнопку Остановить запись. Либо щелкните маленький серый прямоугольник, который появляется в нижнем левом углу экрана под вкладками рабочего листа.

Запись и запуск вашего первого макроса

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

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

1. В группе Код на вкладке Разработчик нажмите Записать макрос.
2. Заполните диалоговое окно «Запись макроса», как показано на рис. 4. Затем нажмите «ОК».

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

3. Нажмите Ctrl+1, чтобы открыть диалоговое окно «Формат ячеек».
4. Перейдите на вкладку «Шрифт», выберите «Жирный» в списке «Стиль шрифта» и выберите оттенок красного в разделе «Цвет». Затем нажмите ОК.
5. Щелкните Остановить запись.
6. Поскольку книга содержит макрос, сохраните файл книги как книгу Excel с поддержкой макросов. Имя рабочей книги теперь Redbold. xlsm. Суффикс .xlsm указывает, что файл является книгой с поддержкой макросов.
7. Протестируйте макрос, введя данные в ячейки J5:J7. После выбора J5:J7 и нажатия Ctrl+Shift+R ячейки J5:J7 будут отформатированы красным шрифтом и выделены жирным шрифтом.

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

Вы можете более удобно запускать макросы, создав кнопку. Вот как: 

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

2. Щелкните место на листе, где вы хотите создать кнопку, а затем перетащите ее, пока кнопка не станет нужного размера и формы.

3. ","469777815":"hybridMultilevel"}» data-listid=»2″ role=»listitem»>

   Когда вы отпустите кнопку мыши, откроется диалоговое окно «Назначить макрос» (см. рис. 5). В поле «Имя макроса» выберите ранее созданный макрос, выделенный красным жирным шрифтом, и нажмите «ОК».

4. Чтобы протестировать кнопку, введите некоторые данные, выберите их и нажмите кнопку. Новые данные должны быть отформатированы полужирным красным шрифтом.

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

Как просмотреть код макроса VBA

Когда вы записываете макрос в Excel, вы можете позже просмотреть его как код VBA. Вот как просмотреть код VBA вашего макроса:

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

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

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

Обратите внимание на следующее: 

• Макрос должен начинаться с оператора, содержащего «Sub», за которым следует имя макроса и (). Например, Sub redbold().

• Макрос должен заканчиваться оператором End Sub.

• Любые операторы, начинающиеся с ‘, являются просто комментариями.

• Все операторы, содержащие слово False, избыточны и не нужны. Другие операторы, такие как .Fontstyle=»Bold», предписывают Excel изменить формат выбранного диапазона.

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

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

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

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

Как создавать макросы с использованием как абсолютных, так и относительных ссылок 

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

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

Рис. 7. Начальные данные о продажах  

Вот как создать макрос с абсолютными и относительными ссылками:  

1. Выберите любую ячейку в книге.

2. ","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

   Убедитесь, что в разделе «Группа кода» на вкладке «Разработчик» не выбран параметр «Использовать относительную ссылку».

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

4. » data-list-defn-props=»{"335551671":1,"335552541":0,"335559683":0,"335559684":-1,"335559685":720,"335559991":360,"469769242":[65533,0,46],"469777803":"left","469777804":"%1.","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

   В диалоговом окне «Запись макроса» введите «changerel» в поле «Имя макроса». Введите Ctrl+Shift+R в качестве сочетания клавиш. Выберите сохранение макроса в текущей книге и нажмите кнопку ОК.

5. ","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

   Щелкните ячейку D1 и введите «изменить». Это будет заголовок для столбца, содержащего изменение продаж.

6. Щелкните ячейку D2, введите формулу «=C2-B2» и нажмите Ctrl+Enter, чтобы оставить выбранной текущую ячейку.  

7. Нажмите Ctrl+C, чтобы скопировать формулу в ячейку D2.

8. ","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

   Выберите ячейку C2.

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

10. » data-list-defn-props=»{"335551671":1,"335552541":0,"335559683":0,"335559684":-1,"335559685":720,"335559991":360,"469769242":[65533,0,46],"469777803":"left","469777804":"%1.","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

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

11. Переместите одну ячейку вправо и нажмите Ctrl+shift+стрелку вверх, чтобы выбрать каждую заполненную ячейку в столбце D, кроме ячейки D1.

12. Нажмите Ctrl+V, чтобы вставить формулу из ячейки D2 в каждую ячейку столбца D. Таким образом, даже если вы измените количество товаров, на этом шаге формула из ячейки D2 будет скопирована в последнюю строку данных о товарах.

13. ","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

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

14. Нажмите Ctrl+*, чтобы выбрать все ваши данные (в настоящее время ячейки A1:D4).

15. » data-list-defn-props=»{"335551671":1,"335552541":0,"335559683":0,"335559684":-1,"335559685":720,"335559991":360,"469769242":[65533,0,46],"469777803":"left","469777804":"%1.","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

    Перейдите на вкладку «Главная». Затем группа «Шрифт». Нажмите кнопку «Граница» и выберите «Дополнительные параметры».

16. ","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

    Откроется диалоговое окно «Формат ячеек» с отображаемой вкладкой «Граница». Выберите настройки, показанные на рис. 8, и нажмите OK. Эти настройки создадут жирную красную рамку вокруг выбранных ячеек.

Рис. 8. Параметры границ для макроса

17. ","469777815":"hybridMultilevel"}» data-listid=»7″ role=»listitem»>

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

18. Чтобы протестировать макрос, удалите содержимое D1:D4. Затем выберите ячейку и нажмите Ctrl+Shift+R, чтобы запустить макрос.