Разное

Как работать с макросами: примеры и как сделать / Skillbox Media

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

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

Вам даже не нужно быть программистом и знать язык программирования «VBA» чтобы создавать свои макро-программы с помощью инструмента записи макросов.

Сначала надо включить панель разработчика. Для этого в меню «Файл» открываем группу опций «Параметры». В появившемся окне «Параметры Excel» открываем группу «Настройка ленты». Обратите внимание на правую колонку настроек под аналогичным названием «Настройка ленты». В ней следует отметить галочкой опцию «Разработчик» как показано ниже на рисунке:

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

Применение VBA и макросов в Microsoft Excel

Макросы – это внутренние приложения, которые берут на себя всю рутинную работу, облегчая жизнь пользователю. Каждый пользователь может создать макрос без знания языков программирования. Для этого существует макрорекодер, который запускается с помощью кнопки «Запись макроса».

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

Как записать готовый макрос в Excel? Очень просто:

  1. На вкладке «Разработчик» нажимаем кнопку «Запись макроса».
  2. В появившимся диалоговом окне заполняем параметры макроса. И нажимаем «ОК».
  3. После завершения нажимаем на кнопку «Остановить запись», после чего макрос будет автоматически сохранен.
  4. Для выполнения или редактирования записанного макроса нажимаем на кнопку «Макросы» (или комбинацию клавиш ALT+F8). Появится окно со списком записанных макросов и кнопками для управления ими.

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



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

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

1 Правильные имена в макросах.

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

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

2 Используйте относительные (не абсолютные) адреса ячеек

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

По умолчанию в Excel включен режим «Абсолют», но вы можете изменить его, включив кнопку «Относительные ссылки» расположенную ниже под кнопкой «Запись макроса» на панели инструментов вкладки «Разработчик»:

3 Всегда начинайте запись с курсором в A1

Абсолютный отсчет ячеек, всегда ведется с исходного положения (адрес ячейки А1) – до адреса курсора с вашими данными. Если вы сохранили ваш макрос в книге личных макросов (рекомендуется так и делать), то вы можете использовать свою программу на других листах с аналогичными данными. Независимо от того, где ваш курсор позиционируется, когда вы начинаете запись макроса! Даже если он уже находится в ячейке A1, ваш первый макрос лучше записывать после нажатия клавиш должны быть Ctrl + Home.

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

4 Всегда перемещаться с клавиш направления в момент записи макроса

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

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

5 Создавайте макросы для конкретных небольших задач

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

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

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

Макросы

Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel. Пройдитесь по полному списку или сначала выберите раздел слева и — вперед!

Сортировка: дата создания дата изменения просмотры комментарии

Риск безопасности и блокировка макросов

Как разблокировать макросы при появлении сообщения РИСК БЕЗОПАСНОСТИ, когда файл была скачана из интернета.

Копировать сумму выделенных ячеек в Буфер обмена

Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)

Моделирование лотереи в Excel

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

Ад Условного Форматирования

Что такое «Ад Условного Форматирования», когда и почему он возникает? Как с ним бороться вручную и при помощи специального макроса?

Пишем игру «Жизнь» (Life) на VBA в Excel

Пишем за 15 минут «с нуля» игру «Жизнь» (Life) Джона Конвея на VBA в Microsoft Excel.

Как открыть новый Excel в отдельном окне

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

Дубликаты внутри ячейки

Как бороться с повторами в тексте внутри ячейки: обнаруживать их, выделять цветом или удалять. С помощью формул, макросов или запросов Power Query.

Сборка данных со всех листов книги в одну таблицу

Как быстро собрать данные со всех (или только с нужных) листов книги в одну большую таблицу — готовым макросом или с помощью Power Query.

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

Как запускать нужные вам макросы в определенное время, с определенной частотой или по расписанию? Например, автоматически обновлять тяжелый и медленный отчет каждое утро в 5:00?

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

Подробный пошаговый разбор создания своей собственной надстройки для Microsoft Excel.

Как использовать Личную Книгу Макросов

Личная Книга Макросов (Personal Macro Workbook) — что это такое, как ее создать и использовать в качестве личной библиотеки для хранения и запуска самых нужных вам в повседневной работе макросов и функций.

Генерация дубликатов строк

Как размножить некоторые строки в таблице, создав для них заданное количество дубликатов. Разбор двух способов: «в лоб» макросом и красиво — через Power Query.

Полезности для VBA программиста

Обзор полезных программ, утилит и надстроек для программиста на Visual Basic. Если вы хотя бы иногда создаете макросы на VBA в Excel, то что-то из этого списка вам точно пригодится.

Заставка при открытии книги Excel

Как создать окно-заставку, которая будет автоматически отображаться заданное время (3-5 сек) при открытии книги Excel.

Сохранение листов книги как отдельных файлов

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

Подробнее…

Подсветка незащищенных ячеек

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

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

Что такое макросы. Как создавать макросы для автоматизации повторяющихся действий и расширения функционала Microsoft Excel. Как добавить свою пользовательскую функцию (UDF) к стандартному набору функций Excel. Макросы и безопасность.

Координатное выделение

Автоматическая координатная подсветка текущей строки и столбца «крестом» — крайне удобно при просмотре больших таблиц!

Создание резервных копий ценных файлов

Как восстановить потерянную работу, если вы вышли из Excel без сохранения изменений? Или нужно вернуться в работе к состоянию, которое было 2 часа назад? Как научить Excel автоматически сохранять резервную копию ценного файла в заданную папку и спасать потерянные данные?

Ячейка с накоплением (нарастающим итогом)

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

Подробнее. ..

Транслит

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


Видео: Работа с макросами — Служба поддержки Майкрософт

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

Лучший способ сделать это? Запишите макрос.

Средство записи макросов — это самый простой способ создать новый макрос в Excel.

Небольшое примечание: макросы недоступны в Office на ПК с Windows RT.

Чтобы узнать, какую версию Office 2013 вы используете, щелкните ФАЙЛ , а затем щелкните Учетная запись 9.0012 .

Если вы используете ПК с Windows RT, вы увидите здесь Microsoft Office 2013 RT.

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

Здесь находятся все макрокоманды.

Я нажимаю вкладку ФАЙЛ > Параметры > Настроить ленту , и здесь я выбираю Разработчик , а затем нажимаю OK .

Теперь я вижу ПРОЯВИТЕЛЬ , а здесь, в группе кода , находится кнопка Record Macro и некоторые другие кнопки, о которых я расскажу позже.

Итак, приступим.

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

Когда я перехожу сюда, Подсказки показывают, что будет в каждой ячейке до субботы.

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

Вот что мы хотим записать. Итак, я очищу эти ячейки и выберу A1.

Я повторю эти шаги, но на этот раз включу запись макросов. Я нажимаю кнопку Запись макроса .

Назовем этот макрос «FillDays». Имена макросов не могут содержать пробелы. Я нажимаю OK , чтобы запустить рекордер.

Посмотрите, как кнопка Запись макроса превратилась в Остановить запись . Когда я закончу, я нажму на это.

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

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

Я нажму кнопку Macros , чтобы открыть диалоговое окно Macro , и нажму Run .

Пока отлично.

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

Смотри, что происходит.

Макрос заполнен от A1 до G1. Другими словами, это не удалось.

Итак, почему это произошло?

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

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

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

Ну, я знаю, что мне нужно записать новый макрос.

Итак, я очищу эти ячейки и включу Использовать относительные ссылки .

Чтобы немного изменить ситуацию, давайте начнем с ячейки B5.

Итак, я нажимаю Record Macro , даю ему новое имя «FillDaysRelative» и нажимаю OK , чтобы начать запись.

Я наберу воскресенье, сделаю автозаполнение и Остановить запись .

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

На этот раз я выбираю «FillDaysRelative» и Выполнить , и теперь все работает отлично.

Итак, вы видите, что настройка относительных ссылок может быть очень важной.

В следующем видео мы перепишем макрос «FillDaysRelative» вручную в инструменте Excel под названием Visual Basic Editor , чтобы он делал что-то немного другое.

Запуск макроса — служба поддержки Microsoft

Существует несколько способов запуска макроса в Microsoft Excel. Макрос — это действие или набор действий, которые можно использовать для автоматизации задач. Макросы записываются на языке программирования Visual Basic для приложений. Вы всегда можете запустить макрос, нажав кнопку 9Команда 0011 Macros на вкладке Developer на ленте. В зависимости от того, как макрос назначен для запуска, вы также можете запустить его, нажав комбинацию клавиш быстрого доступа, нажав кнопку на панели быстрого доступа или в пользовательской группе на ленте, или щелкнув объект, графика или контроль. Кроме того, вы можете автоматически запускать макрос всякий раз, когда открываете книгу.

Перед запуском макросов

Перед началом работы с макросами необходимо включить Разработчик вкладка.

  • Для Windows перейдите к Файл > Параметры > Настроить ленту .

  • Для Mac перейдите к Excel > Настройки… > Лента и панель инструментов .

  • Затем в Настройка ленты в разделе Основные вкладки установите флажок Разработчик и нажмите OK.

  1. Откройте книгу, содержащую макрос.

  2. На вкладке Developer в группе Code нажмите Macros .

  3. У вас также есть другие варианты:

    • Параметры — Добавить сочетание клавиш или описание макроса.

    • Шаг . Откроется редактор Visual Basic для первой строки макроса. Нажатие F8 позволит вам пройти код макроса по одной строке за раз.

    • Редактировать — откроется редактор Visual Basic и вы сможете отредактировать код макроса по мере необходимости.

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

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

  1. На вкладке Developer в группе Code нажмите Macros .

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

  3. Щелкните Опции .

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

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

    Примечания:

    • На Windows , сочетание клавиш для строчных букв Ctrl+буква . Для прописных букв это Ctrl+Shift+Letter .

    • Для Mac сочетание клавиш для строчных букв — Option+Command+буква , но Ctrl+буква также будет работать. Для прописных букв это Ctrl+Shift+Letter .

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

    • Нажмите OK , чтобы сохранить изменения, а затем нажмите Отмена , чтобы закрыть диалоговое окно Macro .

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

Вы можете создать пользовательскую группу, которая появится на вкладке ленты, а затем назначить макрос кнопке в этой группе. Например, вы можете добавить пользовательскую группу с именем «Мои макросы» на вкладку «Разработчик», а затем добавить макрос (который отображается в виде кнопки) в новую группу. Чтобы сделать это, см. Назначение макроса кнопке.

Запустить макрос, щелкнув область графического объекта

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

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

  2. Выполните одно из следующих действий:

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

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

      Совет:   Вы также можете нажать Остановить запись в левой части строки состояния.

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

  3. Откройте книгу, в которую вы хотите добавить макрос, или создайте новую книгу.

  4. На вкладке Developer в группе Code щелкните Visual Basic .

  5. В окне Project Explorer щелкните правой кнопкой мыши объект ThisWorkbook и выберите View Code .

    Совет:  Если окно Project Explorer не отображается, в меню View выберите Project Explorer .

  6. В списке Объект над окном кода выберите Рабочая тетрадь .

    Это автоматически создает пустую процедуру для события Open , например:

    Private Sub Workbook_Open()

    End Sub

  7. Переключитесь на Excel и сохраните книгу как книгу с поддержкой макросов (.xlsm).

  8. Закройте и снова откройте книгу. Когда вы снова открываете книгу, Excel запускает процедуру Workbook_Open , которая отображает сегодняшнюю дату в окне сообщения.

  9. Щелкните OK в окне сообщения.

    Примечание.  Ячейка A1 на листе Sheet1 также содержит дату, полученную в результате выполнения процедуры Workbook_Open.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *