зміст
Навіть якщо ви не вмієте програмувати, є багато місць (книг, сайтів, форумів), де ви можете знайти готовий макрокод VBA для величезної кількості типових завдань в Excel. З мого досвіду більшість користувачів рано чи пізно збирають свою особисту колекцію макросів для автоматизації рутинних процесів, будь то переклад формул у значення, відображення сум словами або підсумовування клітинок кольором. І тут виникає проблема – макрокод у Visual Basic потрібно десь зберігати, щоб потім використовувати в роботі.
Найпростіший варіант - зберегти код макросу безпосередньо в робочому файлі, зайшовши в редактор Visual Basic за допомогою комбінації клавіш інший+F11 і додавання нового порожнього модуля через меню Вставка – модуль:
Однак у цього методу є кілька недоліків:
- Якщо робочих файлів багато, і скрізь потрібен макрос, наприклад макрос для перетворення формул в значення, то доведеться скопіювати код в кожній книзі.
- Не можна забувати зберегти файл у форматі з підтримкою макросів (xlsm) або у двійковому форматі книги (xlsb).
- При відкритті такого файлу макрозахист щоразу буде видавати попередження, яке потрібно підтвердити (ну або повністю відключити захист, що не завжди може бути бажаним).
Більш елегантним рішенням буде створити ваша власна надбудова (надбудова Excel) – окремий файл спеціального формату (xlam), що містить усі ваші «улюблені» макроси. Переваги цього підходу:
- Цього буде достатньо підключіть надбудову один раз в Excel – і ви можете використовувати його процедури та функції VBA в будь-якому файлі на цьому комп’ютері. Перезбереження ваших робочих файлів у форматах xlsm- і xlsb, таким чином, не потрібно, т.к. вихідний код буде зберігатися не в них, а у файлі надбудови.
- захист вас також не турбуватимуть макроси. Додатки, за визначенням, є надійними джерелами.
- Можна зробити окрема вкладка на стрічці Excel із гарними кнопками для запуску макросів надбудови.
- Надбудова є окремим файлом. Його легко носити з собою з комп’ютера на комп’ютер, поділіться з колегами або навіть продайте 😉
Давайте крок за кроком пройдемо весь процес створення власної надбудови Microsoft Excel.
Крок 1. Створіть файл надбудови
Відкрийте Microsoft Excel з порожньою робочою книгою та збережіть її під будь-яким відповідним іменем (наприклад MyExcelAddin) у форматі надбудови за допомогою команди Файл – Зберегти як або ключі F12, вказавши тип файлу Додаток Excel:
Зверніть увагу, що за замовчуванням Excel зберігає надбудови в папці C:UsersYour_nameAppDataRoamingMicrosoftAddIns, але, в принципі, ви можете вказати будь-яку іншу зручну для вас папку.
Крок 2. Підключаємо створену надбудову
Тепер надбудова, яку ми створили на останньому кроці MyExcelAddin необхідно підключити до Excel. Для цього зайдіть в меню Файл – Параметри – Додатки (Файл — Параметри — Надбудови), натисніть на кнопку МЕНЮ (іти) у нижній частині вікна. У вікні, що відкриється, натисніть кнопку Розгляд (Огляд) і вкажіть розташування нашого файлу надбудови.
Якщо ви все зробили правильно, то наші MyExcelAddin має з’явитися в списку доступних доповнень:
Крок 3. Додайте макроси до надбудови
Наша надбудова підключена до Excel і успішно працює, але в ній ще немає жодного макросу. Давайте заповнимо його. Для цього відкрийте редактор Visual Basic за допомогою комбінації клавіш інший+F11 або кнопкою Візуальний Бейсік таб розробник (розробник). Якщо вкладки розробник не видно, його можна відобразити через Файл – Параметри – Налаштування стрічки (Файл — Параметри — Налаштувати стрічку).
У верхньому лівому куті редактора повинно бути вікно Проекти (якщо не видно, то включити через меню Перегляд — Провідник проекту):
У цьому вікні відображаються всі відкриті робочі книги та запущені надбудови Microsoft Excel, включаючи нашу. VBAProject (MyExcelAddin.xlam) Виберіть його мишкою та додайте до нього новий модуль через меню Вставка – модуль. У цьому модулі ми будемо зберігати код VBA наших макросів надбудови.
Ви можете або набрати код з нуля (якщо ви вмієте програмувати), або скопіювати його звідкись уже готового (що набагато простіше). Давайте, для перевірки, введемо код простого, але корисного макросу в доданий порожній модуль:
Після введення коду не забудьте натиснути на кнопку збереження (дискета) у верхньому лівому куті.
Наш макрос FormulasToValues, як ви можете легко собі уявити, перетворює формули на значення в попередньо вибраному діапазоні. Іноді ці макроси також викликаються Процедури. Для його запуску необхідно виділити клітинки з формулами і відкрити спеціальне діалогове вікно Макрос із вкладки розробник (Розробник — Макроси) або комбінацію клавіш інший+F8. Зазвичай у цьому вікні відображаються доступні макроси з усіх відкритих робочих книг, але додаткові макроси тут не відображаються. Незважаючи на це, ми можемо ввести назву нашої процедури в поле ім'я макросу (Назва макросу)а потім натисніть кнопку прогін (бігти) – і наш макрос буде працювати:
| |
Тут же можна призначити комбінацію клавіш для швидкого запуску макросу – за це відповідає кнопка параметри (Параметри) у попередньому вікні Macro:
Призначаючи клавіші, майте на увазі, що вони чутливі до регістру та розкладки клавіатури. Отже, якщо ви призначаєте комбінацію, подібну до Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Shiftотримати велику букву.
Для зручності ми також можемо додати кнопку для нашого макросу на панель швидкого доступу у верхньому лівому куті вікна. Для цього виберіть Файл – Параметри – Панель швидкого доступу (Файл — Параметри — Налаштувати панель швидкого доступу), а потім у розкривному списку у верхній частині вікна параметр Макрос. Після цього наш макрос FormulasToValues можна розмістити на панелі за допомогою кнопки додавати (Додати) і виберіть для нього піктограму кнопкою Редагувати (Edit):
Крок 4. Додайте функції до надбудови
але макропроцедури, тут є також функціональні макроси або як їх ще називають UDF (Функція, визначена користувачем = функція, визначена користувачем). Давайте створимо окремий модуль в нашому доповненні (команда меню Вставка – модуль) і вставте туди код такої функції:
Легко побачити, що ця функція потрібна для вилучення ПДВ із суми з ПДВ. Звичайно, не біном Ньютона, але він підійде нам як приклад, щоб показати основні принципи.
Зауважте, що синтаксис функції відрізняється від процедури:
- використовується конструкція Функція …. Кінцева функція замість Sub … End Sub
- після назви функції в дужках вказуються її аргументи
- в тілі функції виконуються необхідні обчислення, а потім результат присвоюється змінній з іменем функції
Також зауважте, що ця функція не потрібна, і її неможливо запустити через діалогове вікно, як попередню процедуру макросу Макрос і кнопку прогін. Таку макрофункцію слід використовувати як стандартну функцію аркуша (SUM, IF, VLOOKUP…), тобто просто ввести в будь-яку клітинку, вказавши значення суми з ПДВ як аргумент:
… або увійдіть через стандартне діалогове вікно для вставки функції (кнопка fx у рядку формул), вибравши категорію Визначений користувачем (Визначений користувачем):
Єдиний неприємний момент тут - відсутність звичного опису функції внизу вікна. Щоб додати його, вам потрібно зробити наступне:
- Відкрийте редактор Visual Basic за допомогою комбінації клавіш інший+F11
- Виберіть надбудову на панелі «Проект» і натисніть клавішу F2щоб відкрити вікно браузера об’єктів
- Виберіть проект надбудови зі спадного списку у верхній частині вікна
- Клацніть правою кнопкою миші функцію, яка з’явиться, і виберіть команду властивості.
- Введіть опис функції у вікні Опис
- Збережіть файл надбудови і перезапустіть Excel.
Після перезапуску функція має відобразити введений опис:
Крок 5. Створіть вкладку надбудови в інтерфейсі
Останнім, хоч і не обов'язковим, але приємним штрихом стане створення окремої вкладки з кнопкою для запуску нашого макросу, яка з'явиться в інтерфейсі Excel після підключення нашої надбудови.
Інформація про вкладки, які відображаються за замовчуванням, міститься в книзі та має бути відформатована в спеціальному коді XML. Найпростіше писати і редагувати такий код за допомогою спеціальних програм – редакторів XML. Однією з найзручніших (і безкоштовних) є програма Максима Новікова Редактор XML стрічки.
Алгоритм роботи з ним такий:
- Закрийте всі вікна Excel, щоб не виникало конфлікту файлів під час редагування XML-коду надбудови.
- Запустіть програму Ribbon XML Editor і відкрийте в ній наш файл MyExcelAddin.xlam
- З кнопкою Вкладки у верхньому лівому куті додайте фрагмент коду для нової вкладки:
- Потрібно взяти порожні лапки id нашу вкладку та групу (будь-які унікальні ідентифікатори) і в етикетка – назви нашої вкладки та групи кнопок на ній:
- З кнопкою button на лівій панелі додайте порожній код для кнопки та додайте до неї теги:
— етикетка це текст на кнопці
— imageMso — це умовна назва зображення на кнопці. Я використовував значок червоної кнопки під назвою AnimationCustomAddExitDialog. Назви всіх доступних кнопок (а їх кілька сотень!) можна знайти на величезній кількості сайтів в Інтернеті за ключовими словами «imageMso». Для початку ви можете зайти сюди.
- onAction – це назва процедури зворотного виклику – спеціального короткого макросу, який буде виконувати наш основний макрос FormulasToValues. Ви можете називати цю процедуру як завгодно. Ми додамо його трохи пізніше.
- Перевірити правильність всього зробленого можна за допомогою кнопки із зеленою галочкою у верхній частині панелі інструментів. Там же натисніть на кнопку з дискетою, щоб зберегти всі зміни.
- Закрийте редактор XML стрічки
- Відкрийте Excel, перейдіть до редактора Visual Basic і додайте процедуру зворотного виклику до нашого макросу KillFormulasщоб він запускав наш головний макрос для заміни формул значеннями.
- Зберігаємо зміни і, повернувшись в Excel, перевіряємо результат:
Ось і все – надбудова готова до використання. Наповніть його власними процедурами і функціями, додайте красивих кнопок – і використовувати макроси в роботі стане набагато легше.
- Що таке макроси, як їх використовувати в роботі, де взяти макрокод у Visual Basic.
- Як зробити заставку при відкритті книги в Excel
- Що таке Personal Macro Book і як нею користуватися