Это мои заметки о моем появлении в 33-й серии Call for Help на TechTV Canada.
Регистратор макросов VBA
Каждая копия Excel, проданная с 1995 года, включает в себя невероятно мощный Visual Basic для приложений (VBA), скрывающийся за ячейками. Если у вас есть Excel, у вас есть VBA.
VBA позволяет автоматизировать все, что вы можете делать в Excel, а также делать больше вещей, которые обычно невозможны в Excel.
К счастью для нас, Microsoft включила в Excel средство записи макросов. Это позволяет писать код VBA, не будучи программистом. К сожалению, в Macro Recorder есть недостатки. Это не приведет к созданию кода, который будет работать каждый раз. Регистратор макросов поможет вам приблизиться, но во многих случаях вам нужно немного поправить код, чтобы он работал надежно. В других случаях вам нужно знать, когда использовать относительную запись, чтобы макрос работал.
Мы с Трейси Сирстад написали VBA и макросы в Microsoft Excel 2016, чтобы помочь вам понять ограничения записанного кода и научить вас, как исправить записанный код во что-то, что будет работать каждый раз.
На шоу я продемонстрировал процесс записи макроса, который будет отлично работать, если вы знаете, как использовать кнопку «Относительная ссылка». Кто-то дал мне лист Excel с сотнями имен и адресов. Им нужны были почтовые марки. Если вы когда-либо использовали функцию слияния почты Microsoft Word, вы знаете, что вам нужно каждое поле в новом столбце на листе. Вместо этого на этом конкретном листе данные идут вниз по столбцу A.
Процесс исправления одной метки довольно утомительный.
- Начните с указателя ячейки на имени в столбце A.
- Нажмите стрелку вниз, чтобы перейти к адресу
- Ctrl + x, чтобы вырезать
- Стрелка вверх, стрелка вправо для перехода в столбец B рядом с именем
- Ctrl + v, чтобы вставить
- Стрелка вниз дважды, стрелка влево один раз, чтобы перейти в город
- Ctrl + x, чтобы вырезать
- Стрелка вверх дважды, стрелка вправо трижды
- Ctrl + v, чтобы вставить
- Стрелка влево дважды, стрелка вниз один раз, чтобы перейти к теперь пустой строке.
- Удерживая нажатой клавишу Shift, дважды нажмите стрелку вниз
- Alt + edr, чтобы удалить пустые строки
- Стрелка вверх и стрелка вниз, чтобы снова выбрать только имя.
Вот анимация, показывающая эти шаги:
Настройка Excel для разрешения макросов
Хотя каждая копия Excel содержит VBA, по умолчанию Microsoft отключает возможность запуска макросов. Вам нужно сделать одноразовую настройку, чтобы разрешить запуск макросов. Откройте Excel. В меню выберите Инструменты> Макрос> Безопасность. Если ваш уровень безопасности макросов в настоящее время установлен на высокий, измените его на средний.
Подготовка к записи макроса
Подумайте о шагах, необходимых для выполнения задачи. Вы хотите убедиться, что вы начинаете с указателя ячейки на имени и заканчиваете им на следующем имени. Это позволит вам многократно запускать макрос много раз. Когда у вас есть готовые шаги, включите регистратор макросов. В меню выберите Инструменты> Макрос> Записать новый макрос.
Если вы будете запускать этот макрос в течение многих дней, вам следует дать макросу удобное имя. Если это одноразовый макрос для автоматизации одноразовой задачи, то, вероятно, можно оставить имя макроса как Macro1. Важным полем здесь является поле сочетания клавиш. Если вы создаете макрос для одноразового использования, назначьте макрос сочетанию клавиш, например Ctrl + a - довольно легко нажимать Ctrl + a несколько раз, поскольку клавиши расположены близко друг к другу. Если вы собираетесь создать макрос, который будете использовать каждый день, то вы хотите назначить макрос для клавиши, которая еще не является важной комбинацией горячих клавиш. Ctrl + j или Ctrl + k - хороший выбор.
Одноразовые макросы следует хранить в ThisWorkbook. Если вам нужно многократно использовать макрос во многих разных книгах, вы можете сохранить макрос в личной книге макросов.
Теперь перед вами самая маленькая панель инструментов во всем Excel; Панель инструментов остановки записи. Он имеет всего две иконки и выглядит так:
Если эта панель инструментов мешает вам, не нажимайте X, чтобы закрыть ее. Вместо этого возьмите синюю полосу и перетащите панель инструментов в новое место. Действие перемещения панели инструментов не записывается в макросе. Если вы случайно закроете панель инструментов, верните ее, выбрав «Просмотр»> «Панели инструментов»> «Остановить запись». Однако это действие будет записано.
Первая кнопка на панели инструментов - это кнопка «Остановить запись». Это говорит само за себя. Когда вы закончите запись макроса, нажмите панель инструментов остановки записи.
Более важная (и редко понимаемая) кнопка - это кнопка «Относительная ссылка».
В нашем текущем примере вы должны нажать кнопку «Относительная ссылка», прежде чем начать. Если вы записываете макрос с включенными относительными ссылками, Excel будет записывать следующие шаги:
- Переместить на одну ячейку вниз
- Вырезать текущую ячейку
- Переместить на одну ячейку вверх
- Переместить на одну ячейку вправо
- Вставить
- и т.п.
Если вместо этого вы запишете макрос без относительных ссылок, макрос запишет следующие шаги:
- Перейти в ячейку A4
- Вырезать ячейку А4
- Перейти в ячейку A3
- Перейти в ячейку B3
- Вставить в ячейку B3
- и т.п.
Это было бы ужасно неправильно - нам нужен макрос для работы с ячейками, находящимися на 1 и 2 ячейки от начальной точки макроса. Когда вы запускаете макрос снова и снова, начальной точкой будет строка 4, строка 5, строка 6 и т. Д. При записи макроса без включения относительных ссылок макрос всегда будет запускаться в строке 3 в качестве отправной точки.
Следуй этим шагам:
- Нажмите кнопку относительной ссылки на панели инструментов остановки записи.
- Указатель ячейки уже должен находиться на имени в столбце A.
- Нажмите стрелку вниз, чтобы перейти к адресу
- Ctrl + x, чтобы вырезать
- Стрелка вверх, стрелка вправо для перехода в столбец B рядом с именем
- Ctrl + v, чтобы вставить
- Стрелка вниз дважды, стрелка влево один раз, чтобы перейти в город
- Ctrl + x, чтобы вырезать
- Стрелка вверх дважды, стрелка вправо трижды
- Ctrl + v, чтобы вставить
- Стрелка влево дважды, стрелка вниз один раз, чтобы перейти к теперь пустой строке.
- Удерживая нажатой клавишу Shift, дважды нажмите стрелку вниз
- Alt + edr, чтобы удалить пустые строки
- Стрелка вверх и стрелка вниз для выбора следующего имени в списке.
- Нажмите на панель инструментов "Остановить запись"
- Сохраните книгу
- Проверьте макрос, нажав назначенную выше комбинацию клавиш. Он должен идеально исправить следующее имя в списке
Как только вы увидите, что макрос работает должным образом, вы можете удерживать Ctrl + a, чтобы быстро исправить несколько имен в секунду. Весь список из 500 имен можно исправить за минуту-две.
Бонусный совет - не на шоу
Вы можете легко заключить макрос в простой цикл, чтобы он исправил все 500 имен без необходимости нажимать Ctrl + a несколько сотен раз.
Нажмите Alt + F11, чтобы открыть редактор макросов.
Если вы не видите панель Project - VBAProject, нажмите Ctrl + r.
Щелкните правой кнопкой мыши Module1 и выберите Просмотреть код. Вы увидите код, который выглядит так:
Теперь вам не нужно понимать, что делает этот код, но вы знаете, что мы хотим запускать все в этом макросе один раз для каждого имени, которое у нас есть. Если вы знаете, что существует 462 имени, то вы можете добавить 2 строки, чтобы выполнить код 462 раза. Вверху макроса вставьте новую строку со словами « For i = 1 to 462». Внизу макроса вставьте строку с надписью « Next i». Это говорит VBA выполнить код внутри 462 раза.
Заключение
После этого простого макроса я показал пример очень сложного макроса. Этот макрос создал сводные таблицы и диаграммы для 46 отделов компании. Раньше этот отчет занимал 40 часов каждый месяц. Теперь макрос VBA запускается и создает все 46 отчетов менее чем за 2 минуты.
Книга VBA и макросы Microsoft Excel 2016 проведет вас по кривой обучения, чтобы вы могли перейти от записи простых макросов, подобных этому, к созданию очень сложных отчетов, которые могут сэкономить вам сотни часов в год. Конечно, если вы не хотите изучать VBA, наймите консультанта по Excel, который разработает для вас отчет.
