10 профессиональных советов по созданию макросов Excel

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

1. Имена макросов

Держите имена макросов короткими (но описательными), особенно если вы записываете много макросов, чтобы их можно было легко идентифицировать в диалоговом окне макроса. Система также предоставляет поле для описания, хотя не все его используют.

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

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

Абсолютный означает, что точные местоположения ячеек записываются в макрос — жестко заданные адреса ячеек, такие как A6 или B12. Абсолютные ссылки ограничивают возможность макроса работать, если что-либо изменяется, добавляются/удаляются новые данные или список становится длиннее. Относительный означает, что записанные в макросе нажатия клавиш относятся к положению начальной ячейки.

По умолчанию в Excel установлено Абсолютное значение, но вы можете изменить это значение на Относительное на панели инструментов Остановить запись:

Щелкните Разработчик > Макрос записи .

  1. В Макрос записи введите имя макроса и горячую клавишу (если применимо). Выберите Персональная книга макросов из раскрывающегося списка вариантов Сохранить макрос в , введите описание (при желании) и нажмите ОК. .
  2. Диалоговое окно исчезнет, ​​а кнопка Запись макроса изменится на кнопку Остановить запись . Нажмите кнопку Относительная ссылка рядом — она ​​станет темно-зеленой, чтобы указать, что она активна.
  3. Введите нажатия клавиш, формулы и т. Д., Затем нажмите Остановить запись и запустите макрос.

JD Sartain/IDG

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

3. Всегда начинайте с домашней страницы

Всегда начинайте с начальной позиции (ячейка A1) — с курсора и ваших данных. Если вы сохранили свой макрос в личной книге макросов (рекомендуется), вы можете повторно использовать этот макрос на других листах с аналогичными данными.

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

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

  1. Создайте новую книгу в Excel (например, апрельский отчет). Ваш курсор автоматически окажется в исходной позиции (A1). Не перемещайте его для размещения заголовков, заголовков полей или чего-либо еще. Он должен располагаться в ячейке A1.
  2. Откройте рабочую книгу первой ветви и скопируйте данные всей электронной таблицы (без заголовков столбцов) в ячейку A1 в таблице апрельского отчета. Данные начинаются с A1 и заканчиваются G55.
  3. Откройте рабочую книгу второй ветви и скопируйте все данные электронной таблицы (без заголовков столбцов) в ячейку A56 в таблице апрельского отчета. Продолжайте этот процесс, пока данные из всех книг не будут скопированы в электронную таблицу отчета за апрель.
  4. Если данные других электронных таблиц содержат формулы, выберите Вставить > Специальные > Значения для параметров вставки. Не копируйте формулы или форматы.
  5. На этом этапе не беспокойтесь о заголовке листа, заголовках столбцов или форматировании (например, о ширине столбцов, шрифтах и ​​т. Д.). Просто скопируйте данные и запустите макрос.
  6. После вычисления данных вы можете использовать «Вставить строки» для добавления заголовков столбцов, заголовков таблиц и другой информации. Затем вы можете отформатировать ширину столбцов, изменить шрифты, добавить атрибуты и разместить границы или поля, если хотите. (Вы также можете создать дополнительный макрос для выполнения задач форматирования.)

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

Используйте клавиши направления (End-Down, Ctrl + Up и т. Д.) Для размещения курсора, чтобы вы могли добавлять, изменять или удалять данные внутри электронной таблицы по мере необходимости. Если вы жестко запрограммируете адреса ячеек, любые изменения или правки в электронной таблице могут сделать недействительными функции макроса. Например, если вы закодируете макрос для суммирования от A1 до A20, а затем добавите больше строк (например, от A1 до A35), формула не будет включать дополнительные ячейки, и общая сумма будет неправильной.

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

5. Макросы должны быть небольшими и конкретными

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

JD Sartain/ IDG

Выполните макрос Excel с помощью F8.

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

Если макрос не работает, вы можете устранить его, пройдя по одной строке за раз. Это похоже на замедленное движение, только вы контролируете каждый раз, когда происходит следующее событие, с помощью клавиши F8. Щелкните Разработчик > Макрос , выберите макрос из списка, затем нажмите кнопку Шаг с заходом . Уменьшите размер окна Код и поместите его в правом нижнем углу экрана, чтобы вы могли видеть свою электронную таблицу за ним. Нажимайте F8 каждый раз, когда хотите, чтобы макрос переходил к следующему шагу. Макрос останавливается, когда достигает ошибки. Затем вы можете исправить ошибку или записать новый макрос.

6. Заранее введите статические данные

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

7. Сохранение электронной таблицы

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

JD Sartain/IDG

Сохранение электронной таблицы как книги с поддержкой макросов

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

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

1. Выберите Файл > Параметры > Панель быстрого доступа .

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

3. В диалоговом окне ниже Excel отображает список всех макросов в этой книге.

4. Выберите макрос SumTotals , затем нажмите кнопку Добавить , чтобы переместить макрос в диалоговое окно Настроить панель быстрого доступа .

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

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

7. Затем введите описательное имя для вашего макроса в поле Отображаемое имя .

8. И наконец, дважды нажмите кнопку ОК .

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

JD Sartain/IDG

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

9. Добавьте кнопку макроса в настраиваемую группу в меню ленты

1. Выберите Файл > Параметры > Настроить ленту .

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

3. Выберите вкладку для своих настраиваемых макросов или выберите/выделите группу Надстройки на вкладке Разработчик и нажмите Новая группа .

4. Когда Новая группа уже выбрана (выделена), нажмите кнопку Переименовать , которая открывает Диалоговое окно «Переименовать «.

5. Поле Отображаемое имя выбрано: введите имя для этой группы, например «Пользовательские макросы».

6. Затем просмотрите диалоговое окно Символы и выберите значок, который лучше всего идентифицирует этот макрос (мы выбрали песочные часы), затем нажмите ОК .

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

8. В диалоговом окне ниже Excel отображает список всех макросов в этой книге.

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

10. При желании снова нажмите кнопку Переименовать и выберите значок в диалоговом окне «Символы» (мы снова выбрали калькулятор, такой же, как кнопка на панели быстрого доступа.

11. Дважды нажмите OK , чтобы закрыть экран Настроить .

JD Sartain/IDG

Добавить кнопку макроса в настраиваемую группу в меню ленты.

10. Предупреждение о безопасности макросов

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

«Предупреждение безопасности: макросы были отключен », за которым следует кнопка с надписью Включить контент .

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

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

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

1. Выберите Файл> Параметры> Центр управления безопасностью.

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

3. Выберите в меню Настройки макроса .

4. Установите флажок Включить все макросы , затем нажмите ОК .

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

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

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

JD Sartain/IDG

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

Панель сообщений

5. Выберите Файл> Параметры> Центр управления безопасностью.

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

7. Выберите в меню Сообщение B ar.

8. Установите флажок Никогда не отображать информацию о заблокированном содержимом , затем нажмите ОК .

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

Примечание. Когда вы покупаете что-то после перехода по ссылкам в наших статьях, мы можем получить небольшую комиссию. Подробнее читайте в нашей политике в отношении партнерских ссылок.
Оцените статью
Botgadget.ru
Добавить комментарий