Запись Изменить Запустить макрос Excel - Советы по Excel

Это 19-й еженедельный совет по Excel на сайте.com. Многие советы по Excel включают в себя макросы. На этой неделе для пользователей Excel, которые никогда не писали макрос, я предлагаю учебник о том, как записывать, а затем настраивать полезный макрос Excel.

Пример адресных данных

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

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

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

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

Хотя на этом листе 400 записей, я хочу записать крошечный фрагмент макроса, который заботится только о первом адресе. Макрос предполагает, что указатель ячейки находится на первом имени. Он вставит три пустые строки. Он скопирует ячейку справа от исходной ячейки в ячейку под исходной ячейкой. Он скопирует ячейку с городом в ячейку на 2 строки ниже исходной ячейки. Затем он должен переместить указатель ячейки вниз, чтобы он оказался на следующем имени.

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

Итак, поместите указатель ячейки в ячейку A1. Перейдите в меню и выберите Инструменты> Макрос> Записать новый макрос. В диалоговом окне «Запись макроса» предлагается имя «Макрос1». Это нормально, так что нажмите ОК.

У регистратора макросов Excel есть одна очень глупая настройка по умолчанию, которую вы обязательно должны изменить, чтобы этот макрос работал. В Excel 95 выберите «Инструменты»> «Макрос»> «Использовать относительные ссылки». В Excel 97–2003 щелкните второй значок на панели инструментов «Остановить запись». Значок выглядит как крошечный лист. Красная ячейка в C3 указывает на другую красную ячейку в A3. Значок называется «Относительная ссылка». Когда этот значок включен, его окружает какой-то цвет. Значок запоминает последнюю настройку из текущего сеанса Excel, поэтому вам, возможно, придется щелкнуть его пару раз, чтобы выяснить, какой метод включен или нет. В Excel 2007 используйте Вид - Макросы - Использовать относительные ссылки.

Хорошо, мы готовы к работе. Следуй этим шагам:

  • Нажмите стрелку вниз один раз, чтобы перейти к ячейке B1.
  • Удерживая нажатой клавишу Shift, дважды нажмите стрелку вниз, чтобы выбрать строки 2, 3 и 4.
  • В меню выберите «Вставить», затем выберите «Строки», чтобы вставить три пустые строки.
  • Нажмите стрелку вверх, а затем стрелку вправо, чтобы перейти в ячейку B2.
  • Нажмите Ctrl X, чтобы вырезать ячейку B2.
  • Нажмите стрелку вниз, стрелку влево, затем Ctrl V, чтобы вставить в ячейку A2.
  • Нажмите стрелку вверх, стрелку вправо, стрелку вправо, Ctrl X, стрелку влево, стрелку влево, стрелку вниз, стрелку вниз, Ctrl V, чтобы переместить C1 в A3.
  • Дважды нажмите стрелку вниз, чтобы указатель ячейки оказался на следующем имени в строке A5.
  • Щелкните значок «Остановить запись» на панели инструментов, чтобы остановить запись макроса.

Итак, вы записали свой первый макрос. Давайте взглянем. Перейдите в Инструменты> Макрос> Макросы. В списке выделите Macro1 и нажмите кнопку Edit. Вы должны увидеть что-то вроде этого.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End Sub

Эй, если вы не программист, это, вероятно, выглядит довольно устрашающе. Не позволяй этому быть. Если вы чего-то не понимаете, вам помогут. Щелкните курсором где-нибудь в ключевом слове Offset и нажмите F1. Если вы установили файл справки VBA, вы увидите раздел справки для ключевого слова Offset. В справке сообщается синтаксис оператора. Там написано, что это Offset (RowOffset, ColumnOffset). Все еще не очень понятно? Найдите зеленое подчеркнутое слово «пример» в верхней части справки. Примеры Excel VBA позволят вам узнать, что происходит. В примере смещения говорится, что для активации ячейки двумя строками ниже и тремя строками справа от текущей ячейки вы должны использовать:

ActiveCell.Offset(3, 2).Activate

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

Вернитесь к листу Excel. Поместите указатель ячейки в ячейку A5. Выберите Инструменты> Макрос> Макросы> Макрос1> Выполнить. Макрос запустится, и ваш второй адрес будет отформатирован.

Вы можете сказать, что выбрать всю эту длинную большую строку команд сложнее, чем просто форматировать вручную. ОК, затем выберите Инструменты> Макрос> Макросы> Параметры. В поле быстрого доступа скажем, Ctrl + w - это сочетание клавиш для этого макроса. Нажмите «ОК», затем закройте диалоговое окно «Макрос», нажав «Отмена». Теперь, когда вы нажмете Ctrl w, макрос запустится. Вы можете отформатировать адрес одним нажатием клавиши.

Вы готовы к большому успеху? Сколько адресов у вас осталось? Я нажал Ctrl wa несколько раз, так что осталось 395. Вернитесь к своему макросу. Мы собираемся поместить весь код макроса в цикл. Вставьте новую строку с надписью «Do until activecell.value =" "" перед первой строкой кода макроса. Вставьте строку с надписью «Loop» перед строкой End Sub. Цикл Do выполнит все, что находится между строками Do и Loop, пока не перейдет в пустую строку. Теперь макрос выглядит так:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End Sub

Вернитесь к листу Excel. Поместите указатель ячейки на следующее имя. Нажмите Ctrl w, и макрос отформатирует все ваши записи за несколько секунд.

Авторы книг по Excel говорят, что ничего полезного, записывая макрос, нельзя. Не правда! Этот макрос очень полезен для человека, которому нужно было скопировать и вставить 800 раз. На запись и настройку ушло несколько минут. Да, профессиональные программисты отметят, что код ужасно неэффективен. Excel помещает туда целую кучу вещей, которые ему не нужно вставлять. Да, если бы вы знали, что делаете, вы могли бы выполнить ту же задачу с половиной строк, которые будут выполняться за 1,2 секунды вместо 3 секунд. И ЧТО? 3 секунды намного быстрее, чем 30 минут на выполнение задачи.

Еще несколько советов для начинающих записывающих макросов:

  • Апостроф используется для обозначения комментария. Все, что находится после апострофа, игнорируется VBA
  • Это объектно-ориентированное программирование. Базовый синтаксис - object.action. Если бы объектно-ориентированный компилятор играл в футбол, он бы сказал «ball.kick», чтобы пнуть мяч. Итак, «Selection.Cut» говорит, что нужно «редактировать> вырезать» для текущего выделения.
  • В приведенном выше примере модификаторы диапазона относятся к активной ячейке. Если активная ячейка находится в B2 и вы говорите «ActiveCell.Range (« A1: C3 »). Select», то вы выбираете область 3 строки на 3 столбца, начиная с ячейки B2. Другими словами, вы выбираете B2: D4. Произнесение «ActiveCell.Range (« A1 »)» означает выбор диапазона ячеек 1 x 1, начиная с активной ячейки. Это невероятно избыточно. Это эквивалентно высказыванию «ActiveCell.Select».
  • Сохраните книгу перед первым запуском макроса. Таким образом, если возникнет ошибка и произойдет что-то неожиданное, вы можете закрыть без сохранения и вернуться к сохраненной версии.

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

Интересные статьи...