Як записати макрос в Excel - покроковий посібник

Навіть якщо ви новачок у світі Excel VBA, ви можете легко записати макрос та автоматизувати деякі свої роботи.

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

Якщо ви зацікавлені у вивченні VBA простим способом, ознайомтеся з моїм Онлайн навчання Excel VBA.

Що таке макрос?

Якщо ви новачок у VBA, дозвольте мені спочатку розповісти, що таке макрос - врешті -решт, я буду продовжувати використовувати цей термін у всьому посібнику.

Макрос - це код, написаний у VBA (Visual Basic for Applications), що дозволяє запускати шматок коду під час його виконання.

Часто ви бачите, що люди (включаючи мене) називають код VBA макросом - незалежно від того, чи він створений за допомогою записувача макросів, або написаний вручну.

Коли ви записуєте макрос, Excel уважно стежить за кроками, які ви виконуєте, і записує їх зрозумілою мовою - VBA.

А оскільки Excel - справді хороший помічник, він створює дуже детальний код (як ми побачимо далі в цьому підручнику).

Тепер, коли ви зупиняєте запис, зберігаєте макрос і запускаєте його, Excel просто повертається до коду VBA, який він генерував, і виконує ті ж кроки.

Це означає, що навіть якщо ви нічого не знаєте про VBA, ви можете автоматизувати деякі завдання, просто дозволивши Excel записати ваші кроки один раз, а потім повторно використовувати їх пізніше.

Тепер давайте зануримось і подивимося, як записати макрос в Excel.

Отримання вкладки Розробник на стрічці

Перший крок для запису макросу - перейти на стрічку вкладку Розробник.

Якщо ви вже бачите вкладку розробника на стрічці, перейдіть до наступного розділу, інакше виконайте наведені нижче дії.

• Клацніть правою кнопкою миші на будь-якій із наявних вкладок на стрічці та виберіть опцію «Налаштувати стрічку». Відкриється діалогове вікно Параметри Excel.
• У діалоговому вікні Параметри Excel у вас будуть параметри Налаштування стрічки. Праворуч на панелі "Основні вкладки" перевірте параметр "Розробник".
• Натисніть OK.

Вищезазначені кроки роблять вкладку Розробник доступною в області стрічки.

Запис макросу в Excel

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

Давайте запишемо дуже простий макрос - той, який виділяє клітинку і вводить у неї текст «Excel». Я використовую текст "Excel" під час запису цього макросу, але не соромтеся вводити своє ім'я або будь -який інший текст, який вам подобається.

Ось кроки для запису цього макросу:

1. Перейдіть на вкладку Розробник.
2. У групі Код натисніть кнопку Макрос. Відкриється діалогове вікно "Запис макросу".
3. У діалоговому вікні "Запис макросу" введіть назву свого макросу. Я використовую назву EnterText. Існують деякі умови іменування, яких потрібно дотримуватися при іменуванні макросу. Наприклад, не можна використовувати пробіли між ними. Зазвичай я вважаю за краще зберігати свої імена макросів як одне слово з різними частинами з першого алфавіту з великої літери. Ви також можете використовувати підкреслення, щоб розділити два слова - наприклад, Enter_Text.
4. (Необов’язковий крок) Ви можете призначити комбінацію клавіш, якщо хочете. У цьому випадку ми будемо використовувати комбінацію клавіш Control + Shift + N. Пам’ятайте, що ярлик, який ви призначите тут, замінить усі наявні ярлики у вашій книзі. Наприклад, якщо ви призначите ярлик Control + S, ви не зможете використовувати це для збереження книги (натомість щоразу, коли ви її використовуватимете, вона виконуватиме макрос).
5. У опції "Зберігати макрос у" переконайтеся, що вибрано "Ця книга". Цей крок гарантує, що макрос є частиною книги. Він буде там, коли ви збережете його та відкриєте знову, або навіть якщо поділитесь ним з кимось.
6. (Необов’язковий крок) Введіть опис. Я зазвичай цього не роблю, але якщо ви надзвичайно організовані, ви можете додати те, про що йдеться у макросі.
7. Натисніть OK. Як тільки ви натискаєте ОК, він починає записувати ваші дії в Excel. Ви можете побачити кнопку "Зупинити запис" на вкладці Розробник, яка вказує на те, що запис макросу триває.
8. Виберіть клітинку А2.
9. Введіть текст Excel (або ви можете використати своє ім’я).
10. Натисніть клавішу Enter. Буде вибрано комірку А3.
11. Натисніть кнопку Зупинити запис на вкладці Розробник.

Вітаємо!

Ви щойно записали свій перший макрос в Excel. Ви більше не макро -незаймана.

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

Тепер давайте спробуємо цей макрос.

Щоб перевірити макрос, виконайте наведені нижче дії.

1. Видаліть текст у комірці А2. Це робиться для перевірки, чи вставляє макрос текст у клітинку А2 чи ні.
2. Виберіть будь -яку клітинку - крім A2. Це для перевірки того, чи макрос вибирає комірку A2 чи ні.
3. Перейдіть на вкладку Розробник.
4. У групі Код натисніть кнопку Макроси.
5. У діалоговому вікні «Макрос» натисніть на ім’я макросу - EnterText.
6. Натисніть кнопку Виконати.

Ви помітите, що як тільки ви натискаєте кнопку «Виконати», текст «Excel» вставляється в клітинку A2, а клітинка A3 вибирається.

Тепер це може статися за частку секунди, але насправді макрос - так само, як слухняний ельф - слідував точно тим крокам, які ви показали під час запису макросу.

Отже, макрос спочатку виділяє клітинку A2, потім вводить текст Excel у ньому, а потім виділяє комірку A3.

Примітка: Ви також можете запустити макрос за допомогою комбінації клавіш Control + Shift + N (утримуйте клавіші Control та Shift, а потім натисніть клавішу N). Це та сама комбінація клавіш, яку ми призначили макросу під час запису.

Що робить запис макросу у бекенді

Тепер перейдемо до бекенда Excel - редактора VB - і подивимося, що насправді робить запис макросу.

Ось кроки, щоб відкрити редактор VB в Excel:

1. Перейдіть на вкладку Розробник.
2. У групі Код натисніть кнопку Visual Basic.

Або можна скористатися комбінацією клавіш - ALT + F11 (утримуйте клавішу ALT і натисніть F11) замість двох вищевказаних кроків. Цей ярлик також відкриває той самий редактор VB.

Тепер, якщо ви вперше бачите редактор VB, не переживайте.

Дозвольте мені швидко ознайомити вас з анатомією редактора VB.

• Рядок меню: Тут у вас є всі можливості редактора VB. Вважайте це стрічкою VBA. Він містить команди, які можна використовувати під час роботи з VB Editor.
• Панель інструментів - Це як панель швидкого доступу редактора VB. Він поставляється з деякими корисними опціями, і ви можете додати до нього інші варіанти. Його перевага в тому, що параметр на панелі інструментів знаходиться всього в одному кліку.
• Вікно Провідника проектів - Тут Excel перераховує всі книги та всі об’єкти в кожній книзі. Наприклад, якщо у нас є робоча книга з 3 робочими листами, вона відображатиметься у Провіднику проектів. Тут є деякі додаткові об’єкти, такі як модулі, форми користувача та модулі класів.
• Вікно коду - Тут записується або записується код VBA. Для кожного об’єкта, переліченого в Провіднику проектів, є вікно коду - наприклад, аркуші, робочі книги, модулі тощо. Далі в цьому посібнику ми побачимо, що записаний макрос переходить у вікно коду модуля.
• Вікно властивостей - Ви можете побачити властивості кожного об’єкта у цьому вікні. Я часто використовую це вікно для іменування об’єктів або зміни прихованих властивостей. Ви можете не побачити це вікно, коли відкриєте редактор VB. Щоб показати це, перейдіть на вкладку перегляд і виберіть вікно властивостей.
• Негайне вікно - Я часто використовую безпосереднє вікно під час написання коду. Це корисно, коли ви хочете перевірити деякі оператори або під час налагодження. Можливо, він не відображається за замовчуванням, і ви можете зробити його таким, щоб відкрити його, натиснувши вкладку Перегляд та вибравши опцію Негайне вікно.

Коли ми записували макрос - EnterText, у редакторі VB сталося наступне:

• Був вставлений новий модуль.
• Був записаний макрос з назвою, яку ми вказали - EnterText
• Код був записаний у вікні коду модуля.

Отже, якщо двічі клацнути на модулі (в даному випадку-Модуль 1), з’явиться вікно коду, як показано нижче.

Ось код, який нам надав макрореєстратор:

Sub EnterText () '' EnterText Macro '' Комбінація клавіш: Ctrl+Shift+N 'Діапазон ("A2"). Виберіть ActiveCell.FormulaR1C1 = Діапазон "Excel" ("A3"). Виберіть End Sub

У VBA будь -який рядок, що слідує за ‘ (знак апострофа) не виконується. Це коментар, який розміщено виключно з інформаційною метою. Якщо видалити перші п’ять рядків цього коду, макрос працюватиме належним чином.

Тепер дозвольте мені швидко розповісти про те, що робить кожен рядок коду:

Код починається з Sub, після чого йде ім'я макросу та порожні дужки. Sub - це скорочення від Subruutine. Кожна підпрограма (яка також називається процедурою) у VBA починається з Sub і закінчується End Sub.

• Діапазон (“A2”). Виберіть - Цей рядок вибирає комірку А2.
• ActiveCell.FormulaR1C1 = “Excel” - цей рядок вводить текст Excel в активну клітинку. Оскільки ми вибрали A2 як перший крок, він стає нашою активною клітиною.
• Діапазон (“A3”). Виберіть - Це вибирає комірку А3. Це відбувається, коли ми натискаємо клавішу Enter після введення тексту, результатом чого став вибір клітинки А3.

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

Майте на увазі, що код, написаний макрореєстратором, аж ніяк не є ефективним кодом.

Макрореєстратор іноді додає в код багато пуху, що часом не потрібно. Але це не означає, що це не корисно. Для тих, хто вивчає VBA, макрореєстратор може стати чудовим способом проаналізувати, як все працює у VBA.

Абсолютний проти відносного запису макросів

Ви вже знаєте про абсолютні та відносні посилання в Excel … так?

Якщо ні - спочатку прочитайте цей підручник із посиланнями.

Читати? Йдемо далі.

Далі в цьому розділі ми побачимо, як записувати макроси в абсолютних та відносних посиланнях. Але перед цим дозвольте мені коротко підсумувати різницю між абсолютним і відносним посиланням у VBA (на випадок, якщо ви полінувалися і не прочитали посилання, яке я дав кілька рядків тому):

Якщо ви використовуєте опцію абсолютного посилання для запису макросу, код VBA завжди буде посилатися на ті самі клітинки, які ви використовували. Наприклад, якщо ви вибрали клітинку A2, введіть текст Excel і щоразу натискайте клавішу Enter - незалежно від того, де ви знаходитесь на робочому аркуші, і незалежно від того, яка клітинка вибрана, ваш код спочатку виділить клітинку A2, введіть текст Excel і потім перейдіть до комірки А3.

Якщо ви використовуєте параметр відносного посилання для запису макросу, VBA не буде жорстко кодувати посилання на клітинки. Швидше, це буде зосереджено на русі в порівнянні з активною клітиною. Наприклад, припустимо, що у вас уже вибрана комірка A1, і ви починаєте запис макросу у режимі відносного відліку.

Тепер ви вибираєте комірку А2, вводите текст Excel і натискаєте клавішу введення. Тепер, коли ви запускаєте цей макрос, він не повернеться до клітинки A2, а переміститься відносно активної комірки. Наприклад, якщо вибрано клітинку K3, вона перейде до K4, введе текст Excel, а потім, нарешті, виділить клітинку K5.

Тепер дозвольте мені розповісти вам, як записати макрос у режимі відносних посилань:

1. Виберіть клітинку A1.
2. Перейдіть на вкладку Розробник.
3. У групі «Код» натисніть кнопку «Використовувати відносні посилання». Він стане зеленим, що означає, що він увімкнено.
4. Натисніть кнопку Записати макрос.
5. У діалоговому вікні "Запис макросу" введіть назву свого макросу. Я використовую назву EnterTextRelRef.
6. У опції Зберегти макрос у переконайтеся, що вибрано «Ця книга».
7. Натисніть OK.
8. Виберіть клітинку А2.
9. Введіть текст Excel (або ви можете ввести своє ім’я).
10. Натисніть клавішу Enter. Це призведе до переміщення курсору до комірки A3.
11. Натисніть кнопку Зупинити запис на вкладці Розробник.

Це дозволить записати макрос у режимі відносного посилання.

Тепер зробіть це.

1. Виберіть будь -яку клітинку (крім A1).
2. Перейдіть на вкладку Розробник.
3. У групі Код натисніть кнопку Макроси.
4. У діалоговому вікні «Макрос» натисніть на ім’я макросу - EnterTextRelRef.
5. Натисніть кнопку Виконати.

Що станеться? Чи повернувся курсор до комірки А3.

Не було б - тому що ви записали макрос у режимі відносного відліку. Таким чином, курсор рухатиметься відносно активної комірки. Наприклад, якщо ви зробите це, коли вибрано клітинку K3, вона введе текст Excel - це комірка K4 і в кінцевому підсумку вибере клітинку K5.

Ось код, який записується у бекенд (вікно коду модуля редактора VB):

Sub EnterTextRelRef () '' EnterTextRelRef Macro '' ActiveCell.Offset (1, 0) .Range ("A1"). Виберіть ActiveCell.FormulaR1C1 = "Excel" ActiveCell.Offset (1, 0) .Range ("A1"). Виберіть End Sub

Зауважте, що весь цей код ніде не відноситься до комірок K3 або K4. Натомість він використовує Activecell для посилання на вибрану клітинку та Offset для переміщення щодо активної комірки.

Не турбуйтеся про частину діапазону ("A1"), яку містить код. Це один з тих непотрібних кодів, які додає макрореєстратор, який не служить жодній меті і його можна видалити. Код без нього нормально працюватиме.

Кнопка "Використовувати відносне посилання" на вкладці "Розробник" є кнопкою перемикання. Ви можете вимкнути його (і повернутися до абсолютного посилання), натиснувши його.

Чого не може зробити макрореєстратор

Макрореєстратор чудово стежить за вами в Excel і записує ваші точні кроки, але може вийти з ладу, коли вам потрібно більше.

• Ви не можете виконати код, не вибравши об’єкт. Якщо ви хочете, щоб макрореєстратор перейшов до наступного робочого аркуша та виділив усі заповнені клітинки у стовпці А, не виходячи з поточного робочого аркуша, то він не зможе цього зробити. Це тому, що якщо я попрошу вас це зробити, навіть ви не зможете цього зробити (не виходячи з поточного аркуша). І якщо ви не можете зробити це самостійно, як макрореєстратор фіксує ваші дії. У таких випадках вам потрібно вручну зайти та створити/відредагувати код.
• Ви не можете створити власну функцію за допомогою записувача макросів. За допомогою VBA ви можете створювати власні функції, які можна використовувати на робочому аркуші як звичайні функції. Ви можете створити це, написавши код вручну.
• Ви не можете запускати коди на основі подій: У VBA ви можете використовувати багато подій - наприклад, відкриття книги, додавання робочого аркуша, подвійне клацання клітинки тощо для запуску коду, пов'язаного з цією подією. Для цього можна скористатися макрореєстратором.
• Ви не можете створювати цикли за допомогою записувача макросів. Коли ви вводите код вручну, ви можете використовувати силу циклів у VBA (наприклад, For Next, For Every Next, Do While, Do until). Але ви не можете цього зробити під час запису макросу.
• Ви не можете аналізувати умови: Ви можете перевірити умови в коді за допомогою макрореєстратора. Якщо ви пишете код VBA вручну, ви можете скористатися операторами IF Then Else для аналізу стану та запуску коду, якщо він є істинним (або іншого коду, якщо він не відповідає дійсності).
• Ви не можете передавати аргументи в макропроцедурі: Коли ви записуєте макрос, він ніколи не матиме аргументів. Підпрограма може приймати вхідні аргументи, які можна використовувати в макросі для виконання завдання. Під час запису макросу це неможливо зробити, оскільки записані макроси є незалежними і не підключені до інших існуючих макросів.

Розширення файлів з підтримкою макросу

Коли ви записуєте макрос або вручну пишете код VBA в Excel, вам потрібно зберегти файл із розширенням файлу з підтримкою макросів (.xlsm).

До Excel 2007 існував один єдиний формат файлу, якого вистачало - .xls.

Але з 2007 року .xlsx було введено як стандартне розширення файлу. Файли, збережені як .xlsx, не можуть містити в собі макрос. Отже, якщо у вас є файл із розширенням .xlsx, і ви записуєте/записуєте макрос і зберігаєте його, він попереджає вас зберегти його у форматі з підтримкою макросів і показує діалогове вікно (як показано нижче):

Якщо вибрати Ні, Excel дозволить зберегти його у форматі з підтримкою макросів. Але якщо натиснути Так, Excel автоматично видалить весь код із вашої книги та збереже його як книгу .xlsx.

Отже, якщо у вашій книзі є макрос, вам потрібно зберегти його у форматі .xlsm, щоб зберегти цей макрос.

Різні способи запуску макросу в Excel

Поки що ми бачили лише один спосіб запуску макросу в Excel - це використання діалогового вікна Макрос.

Але є кілька способів запустити макроси.

1. Запустіть макрос зі стрічки (вкладка Розробник)
2. Використання комбінації клавіш (яку потрібно призначити)
3. Призначте макрос фігурі
4. Призначте макрос кнопці
5. Запустіть макрос з редактора VB

Висновок - Запишіть макрос, коли він застряг

Я вже згадував, що макрореєстратор - корисний інструмент для всіх, хто працює з VBA в Excel.

Коли ви кілька разів користуватиметесь макрореєстратором, ви помітите, що він викидає багато непотрібного коду. Однак він все ще корисний і дає вам деякі ідеї, з чого почати. Наприклад, якщо я прошу вас відфільтрувати стовпець клітинок за допомогою VBA, і ви не уявляєте, що таке синтаксис, ви можете швидко записати макрос і перевірити код.

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