Програмування макросів в Excel: повний покроковий посібник

Інформатек Діджитал » програмування » Програмування макросів в Excel: повний покроковий посібник

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

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

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

Макрос – це список інструкцій, які Excel виконує у визначеному порядку . Це може бути як просте застосування стилю відсотків, так і складне, як підготовка звіту зі зведеними таблицями , його форматування, створення робочих аркушів і надсилання електронного листа в Outlook. Макроси зберігаються у вашій книзі та можуть бути запущені за потреби.

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

Навіщо використовувати макроси? Реальні переваги

Головна мета макросу — уникнути повторюваних завдань і мінімізувати помилки . Якщо ви готуєте один і той самий звіт щомісяця, макрос позбавить вас від механічних кроків і дозволить зосередитися на важливому.

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

Увімкнення вкладки «Розробник» (Windows та Mac)

Вкладка «Розробник» за замовчуванням прихована. У Windows клацніть правою кнопкою миші на стрічці та виберіть «Налаштувати стрічку». Поставте позначку «Розробник» і натисніть кнопку OK. Після цього ви побачите вкладку «Розробник» поруч із пунктом «Вид».

На Mac перейдіть до меню Excel > Параметри > Панелі інструментів і стрічка. У розділі «Основні вкладки» виберіть «Розробник» і збережіть зміни. Це надасть вам доступ до диктофона, VBA та елементів керування . Якщо ви новачок в Excel, перегляньте базовий посібник з Excel , щоб ознайомитися зі стрічкою та її параметрами.

Як створювати макроси за допомогою Диктофона

Диктофон ідеально підходить для початку роботи. У розділі «Розробник» > «Запис макросу» (або Alt+T+M+R у Windows) призначте описову назву. Пам’ятайте: назва має починатися з літери, без пробілів та символів.

Виберіть місце для збереження: «Ця книга» працюватиме у більшості випадків. Якщо ви хочете, щоб вона завжди була доступною, виберіть «Особиста книга макросів», і Excel створить/збереже її у файлі Personal.xlsb , який відкривається приховано разом з Excel.

За бажанням, додайте комбінацію клавіш. Щоб уникнути конфліктів (наприклад, втрати Ctrl+Z), рекомендується використовувати комбінації клавіш із Ctrl+Shift. Ви також можете написати короткий, чіткий опис (дуже корисно, коли у вас багато макросів).

Натисніть кнопку «ОК» і виконайте дії, які потрібно записати. Наприклад: виберіть A1 і застосуйте формат «Відсоток». Уникайте зайвих кліків або змін вибору, які не є частиною запланованого процесу, оскільки диктофон записує майже все.

Після завершення зупиніть запис. Щоб запустити його, перейдіть до розділу Розробник > Макроси (або Alt+F8), виберіть макрос і натисніть кнопку Виконати. Ви побачите, що Excel повторює кроки в тому ж порядку.

Запуск, редагування та керування макросами

З Розробник > Макроси (Alt+F8) можна запускати, редагувати або видаляти макроси. Якщо вибрати Редагувати, відкриється редактор Visual Basic із записаним кодом, який можна очистити та оптимізувати (він часто містить зайві кроки).

Призначення макросу кнопці або фігурі дуже зручне: вставте фігуру, клацніть правою кнопкою миші > Призначити макрос, виберіть макрос, і все готово. Після клацання фігури він запуститься миттєво.

Щоб вона була легкодоступною на панелі швидкого доступу, відкрийте меню панелі інструментів (стрілка вниз) > Інші команди > у розділі «Команди, доступні в», виберіть «Макроси», додайте свої та змініть значок, якщо хочете. Тепер у вас буде постійна кнопка, навіть коли ви не в режимі розробника.

Якщо вам потрібно повторно використати макрос в іншій книзі, ви можете скопіювати модуль з редактора Visual Basic. Відкрийте VBE (Alt+F11), перетягніть модуль до іншої відкритої книги або скористайтеся командою «Експорт/імпорт BAS-файлу».

Програмування макросів в Excel за допомогою редактора Visual Basic (VBA)

Щоб вийти за рамки диктофона, відкрийте Редактор за допомогою Alt+F11. Вставте модуль (Вставка > Модуль) та створіть свою першу підпрограму. Основний шаблон: Sub Name() … End Sub.

Наприклад, щоб відобразити повідомлення та записати значення в клітинку A1, можна почати з чогось простого. Таким чином, ви зрозумієте структуру та те, як викликати макрос з Excel (Alt+F8) або призначити його кнопці, використовуючи лише кілька рядків коду :

Sub Primera_Macro()
    Range("A1").Value = "Esto de las macros mola."
    MsgBox "Hola, querido amigo."
End Sub

Ще один швидкий приклад застосування стилю відсотків до поточного виділення. Цей макрос повторює те, що ви робили з диктофоном , але ввели безпосередньо:

Sub FormatoPorcentaje()
    Selection.Style = "Percent"
End Sub

Ідеальна комбінація: спочатку записати, щоб пришвидшити роботу, а потім відкрити VBE, щоб видалити непотрібні кроки, додати змінні, умови або цикли. Таким чином, ви перетворюєте "літеральний" запис на надійний та багаторазовий макрос.

Абсолютні та відносні посилання

За замовчуванням диктофон створює макроси в абсолютному режимі: він завжди діє на ті самі комірки (наприклад, B3) незалежно від того, де ви знаходитесь під час його запуску. Якщо дані змінюють позицію у вашому процесі, це може бути не те, що вам потрібно.

Якщо вибрано опцію «Використовувати відносні посилання», макрос працює відносно активної комірки на початку виконання. Наприклад, якщо ви почали запис у комірці B3 і ввели «Ім’я», «Прізвище» тощо, рухаючись праворуч, то під час запуску з комірки C4 він введе ту саму інформацію відносно комірок C4, D4, E4 тощо.

Типовий приклад: додавання заголовків до таблиці, яка іноді починається з комірки B3, а іноді з комірки C5. В абсолютному режимі запис завжди виконуватиметься в комірці B3. У відносному режимі запис буде адаптуватися до вибраної початкової комірки.

Типові помилки під час запису процесів

Поширеною помилкою є запис формули, яку потрібно "перетягнути", скажімо, для трьох рядків, і думка, що вона працюватиме для довших таблиць. Реєстратор зареєструє щось еквівалентне " заповнити три комірки вниз ", а не "до кінця таблиці".

Рішення полягає у зміні підходу: використовуйте динамічні діапазони (наприклад, визначення останнього рядка), цикли або метод CurrentRegion, або налаштуйте макрос у VBA після його запису, щоб він масштабувався до будь-якого розміру.

Корисні практики під час запису

Перед записом перевірте процес вручну кілька разів, щоб удосконалити його. Робіть короткі, конкретні записи замість одного величезного; це полегшить їх підтримку та поєднання.

Уникайте зайвих кліків, прокручування стрічки та вибору елементів, які не мають жодного значення. Зберігайте назву та опис макросу чіткими, щоб ви або будь-який колега міг одразу визначити його призначення.

Запускайте макроси різними способами

Окрім діалогового вікна «Макроси» (Alt+F8), макроси можна запускати за допомогою фігури, кнопки на панелі швидкого доступу або відкривши книгу. Ви навіть можете налаштувати Excel на запуск макросу з інших програм Office, що дозволить автоматизувати завдання між програмами (наприклад, оновлення таблиці та надсилання електронного листа через Outlook).

Ретельно обміркуйте комбінації клавіш: якщо ви призначите макросу Ctrl+Z, ви втратите можливість скасувати дії, поки книга відкрита. Тому рекомендується використовувати комбінації Ctrl+Shift , щоб уникнути конфліктів.

Збереження робочих книг за допомогою макросів

Якщо файл містить код VBA, збережіть його як файл .xlsm, щоб код зберігся та його можна було запустити. Збереження як файл .xlsx знищить проект VBA . Якщо вам потрібні глобальні макроси, використовуйте Особисту книгу макросів (Personal.xlsb).

Безпека та активація макросів

Макроси можуть бути заблоковані залежно від ваших налаштувань безпеки. Перевірте Центр безпеки, щоб увімкнути їх вибірково, і ніколи не вмикайте макроси з невідомих джерел. Розуміння вашого рівня безпеки допоможе вам збалансувати захист і продуктивність.

Призначення макросів кнопкам, фігурам та елементам керування

Щоб користувачі могли автоматично запускати макроси, призначте макрос кнопці, фігурі або елементу керування. Клацніть об’єкт правою кнопкою миші > Призначити макрос > вибрати. Ви також можете розмістити піктограму на стрічці або панелі швидкого доступу та зв’язати її з макросом.

Якщо ви використовуєте форми або елементи керування ActiveX, ви можете призначити їм макроси або процедури подій (наприклад, при клацанні). Це забезпечує зручніші інтерфейси та керовані процеси.

Корисні приклади макросів (VBA)

Ці приклади ілюструють поширені завдання. Ви можете вставити їх у модуль (Alt+F11 > Вставити > Модуль) та адаптувати. Не забудьте підписати робочі книги або налаштувати параметри безпеки, якщо цього вимагає ваша організація, щоб ви могли запускати їх без попереджень.

Скопіювати вибраний вміст в інше місце на тому ж аркуші: Selection.Copy Destination

Sub CopiarSeleccion()
    If TypeName(Selection) = "Range" Then
        Selection.Copy Destination:=Selection.Offset(0, 2)
    Else
        MsgBox "Selecciona un rango de celdas primero."
    End If
End Sub

Друк активного аркуша: ActiveSheet.PrintOut

Sub ImprimirHojaActual()
    ActiveSheet.PrintOut
End Sub

Збережіть файл як робочу книгу з підтримкою макросів (.xlsm): ActiveWorkbook.SaveAs

Sub GuardarComoXlsm()
    Dim ruta As String
    ruta = ThisWorkbook.Path & Application.PathSeparator & "LibroConMacros.xlsm"
    ActiveWorkbook.SaveAs Filename:=ruta, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

Застосуйте форматування до діапазону (налаштуйте діапазон відповідно до своїх потреб): За допомогою Діапазон(…)

Sub FormatoRango()
    With Range("A1:D10")
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250)
        .Borders.LineStyle = xlContinuous
    End With
End Sub

Знайти значення в діапазоні та відобразити перший збіг: Range(…).Find

Sub BuscarValor()
    Dim celda As Range
    Dim valor As String
    valor = InputBox("¿Qué valor quieres buscar?")
    If valor = "" Then Exit Sub
    Set celda = Range("A1:D1000").Find(What:=valor, LookIn:=xlValues, LookAt:=xlPart)
    If Not celda Is Nothing Then
        MsgBox "Encontrado en: " & celda.Address
        celda.Select
    Else
        MsgBox "No se encontró el valor especificado."
    End If
End Sub

Практичний випадок: форматування у відсотках

Запис: Розпочніть запис, дайте йому назву, виберіть місце для збереження та, за потреби, призначте ярлик і опис. Виберіть комірки та застосуйте «Відсоток» на вкладці «Головна». Зупиніть запис, а потім знову запустіть його за допомогою Alt+F8. Якщо ви хочете відтворити його з мінімалістичним кодом, використовуйте Selection.Style = «Відсоток» у VBA.

Порада: Під час запису уникайте непотрібного перемикання комірок. Ці рухи туди-сюди записуються та можуть уповільнити та пошкодити макрос.

Автоматизуйте довгі завдання: дрібні макроси краще

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

Наприклад, один макрос для підготовки даних, інший для форматування, ще один для створення роздруківки та ще один для експорту в PDF або надсилання електронною поштою . Ви можете викликати їх по одному або всі одночасно.

Редагування та очищення записаного коду

Рекордер фіксує «все»: виділення, кліки та зміни табуляції. Коли VBE відкривається, він видаляє непотрібні виділення (Вибір/Виділення), замінюючи їх прямими посиланнями (наприклад, Діапазон("A1").Значення = ... ), та групує формати в блоки За/Кінець За.

Крім того, він додає змінні та базову обробку помилок. Кілька операторів if та простих перевірок запобігають помилкам, коли діапазон порожній або вибір не відповідає очікуванням.

Часті запитання

Як створити форму користувача в Excel? Відкрийте редактор Visual Basic (Alt+F11), перейдіть до меню Вставка > Форма користувача та додайте елементи керування (текстові поля, кнопки тощо). Ви можете відобразити її за допомогою макросу.

Sub MostrarFormulario()
    UserForm1.Show
End Sub

Звідти призначте процедури подіям елементів керування (наприклад, натисканню кнопки), щоб виконати потрібну вам логіку.

Як захистити/зняти захист аркуша за допомогою макросів? Використовуйте ці процедури, змінивши пароль за потреби. Вони дозволяють активувати або деактивувати захист одним клацанням миші.

Sub ProtegerHoja()
    ActiveSheet.Protect Password:="segura", AllowFiltering:=True
End Sub

Sub DesprotegerHoja()
    ActiveSheet.Unprotect Password:="segura"
End Sub

Як видалити макрос? Перейдіть до розділу Розробник > Макроси, виберіть макрос і натисніть клавішу Видалити. Якщо потрібно видалити весь код з модуля, відкрийте VBE (Alt+F11), знайдіть модуль у проекті, клацніть правою кнопкою миші та виберіть Видалити модуль (спочатку його можна експортувати, якщо потрібно зберегти).

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