Запис Змінити Запустити макрос Excel - Поради Excel

Це 19-й щотижневий підказка Excel на сайті.com. Багато підказок Excel передбачають певний макро-фокус. Цього тижня для користувачів Excel, які ніколи не писали макрос, я пропоную підручник з того, як записати, а потім налаштувати корисний макрос Excel.

Зразок даних адреси

Скажімо, у вас є 400 рядків адресних даних, як показано на верхньому малюнку зліва. Поле імені знаходиться у стовпці A, адреса вулиці - у стовпці B, а місто - у стовпці C.

Ваша мета - перетворити дані в один стовпець, як показано на другому малюнку.

Ця проста проблема буде використана для ілюстрації того, як записати, змінити, а потім запустити простий макрос.

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

Хоча на цьому аркуші є 400 записів, я хочу записати крихітний біт макросу, який піклується лише про першу адресу. Макрос вважатиме, що покажчик комірки має перше ім’я. Буде вставлено три порожні рядки. Він скопіює комірку праворуч від вихідної комірки в клітинку під вихідною коміркою. Він скопіює міську комірку в клітинку 2 рядки під вихідною коміркою. Потім він повинен перемістити покажчик комірки вниз, щоб він знаходився на наступному імені.

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

Отже, помістіть покажчик комірки в комірку A1. Зайдіть в меню та виберіть Інструменти> Макрос> Записати новий макрос. Діалогове вікно «Запис макросу» пропонує назву Macro1. Це добре, тож натисніть ОК.

У програмі запису макросів Excel є одна дуже дурна настройка за замовчуванням, яку ви обов’язково повинні змінити, щоб цей макрос працював. У програмі Excel 95 перейдіть до Інструменти> Макрос> Використовувати відносні посилання. У програмі Excel 97-2003 клацніть другий значок на панелі зупинки запису. Значок виглядає як крихітний аркуш. Червона клітина в С3 вказує на іншу червону клітину в А3. Піктограма називається відносним посиланням. Коли ця піктограма увімкнена, навколо піктограми є якийсь колір. Значок запам'ятовує останнє налаштування з поточного сеансу 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. Довідка повідомляє вам синтаксис висловлювання. Там сказано, що це зміщення (RowOffset, ColumnOffset). Все ще не дуже зрозуміло? Шукайте зелене підкреслене слово "приклад" у верхній частині довідки. Приклади VBA в Excel дозволять вам дізнатися, що відбувається. У прикладі Offset сказано, що для активації клітинки на два рядки нижче та на три рядки праворуч від поточної комірки потрібно використовувати:

ActiveCell.Offset(3, 2).Activate

Добре, так що це підказка. Функція зсуву - це спосіб переміщення по таблиці Excel. Враховуючи цей шматочок інформації, ви можете якось побачити, що робить макрос. Перший зсув (1, 0) - це місце, де ми перемістили покажчик комірки вниз до А2. Наступний зсув - це місце, де ми перемістилися вгору на один рядок (-1 рядок) і на 1 стовпець. Ви можете нічого іншого не зрозуміти в макросі, але це все одно корисно.

Поверніться до аркуша Excel. Помістіть покажчик комірки в клітинку A5. Виберіть Інструменти> Макрос> Макроси> Макрос1> Виконати. Макрос запускається, і ваша друга адреса відформатована.

Можливо, ви кажете, що вибір цілого довгого великого рядка команд складніше, ніж просто форматування вручну. Добре, тоді виконайте Інструменти> Макрос> Макроси> Параметри. Скажімо, у вікні сполучень клавіш Ctrl + w - це ярлик для цього макросу. Клацніть OK, а потім закрийте діалогове вікно Macro із кнопкою Cancel. Тепер, коли ви натиснете Ctrl w, макрос запуститься. Ви можете відформатувати адресу одним натисканням клавіші.

Ви готові до великого часу? Скільки адрес у вас залишилось? Я натиснув Ctrl wa кілька разів, тож у мене залишилося 395. Поверніться до свого макросу. Ми збираємося розмістити весь цикл макрокоманд у циклі. Вставте новий рядок із написом "Do Do Activecell.value =" "" перед першим рядком коду макросу. Вставте рядок із написом "Loop" перед кінцевим підрядком. Цикл 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 "). Виберіть", тоді ви вибираєте область 3 рядки на 3, починаючи з комірки B2. Іншими словами, ви вибираєте B2: D4. Вимовляючи "ActiveCell.Range (" A1 ")", потрібно вибрати діапазон комірок 1 x 1, починаючи з активної комірки. Це неймовірно зайве. Це еквівалентно вимові "ActiveCell.Select".
Збережіть свою книгу перед першим запуском макросу. Таким чином, якщо у ньому є помилка та щось несподіване, ви можете закрити без збереження та повернутися до збереженої версії.

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