зміст
Якщо ви ще не знайомі з макросами в Excel, то я вам трохи заздрю. Відчуття всемогутності та усвідомлення того, що ваш Microsoft Excel можна оновлювати майже до безкінечності, яке прийде до вас після знайомства з макросами – приємне відчуття.
Однак ця стаття для тих, хто вже «навчився влади» і почав використовувати макроси (чужі або написані власноруч — не має значення) у повсякденній роботі.
Макрос — це код (кілька рядків) мовою Visual Basic, який змушує Excel робити те, що вам потрібно: обробляти дані, створювати звіт, копіювати та вставляти багато повторюваних таблиць тощо. Питання в тому, де зберігати ці кілька рядків коду? Зрештою, місце зберігання макросу залежатиме від того, де він може (або не може) працювати.
Якщо макрос вирішує невелику локальну проблему в окремо взятому файлі (наприклад, обробляє внесені в конкретний звіт даних особистим способом), то логічно зберігайте цей код усередині цього файлу. Без вопросов.
А якщо макрос має бути відносно універсальним і потрібним у будь-якій книзі Excel – як, наприклад, макрос для перетворення формул у значення? Чому б не скопіювати його код Visual Basic щоразу в кожну книгу? Крім того, рано чи пізно практично будь-який користувач приходить до висновку, що непогано було б помістити всі макроси в одну коробку, тобто мати їх завжди під рукою. А може навіть запустити не вручну, а за допомогою комбінацій клавіш? Ось тут персональна робоча книга макросів може стати великою підмогою.
Як створити особисту книгу макросів
Насправді, Особиста книга макросів (ЛКМ) це звичайний файл Excel у двійковому форматі книги (Personal.xlsb), який автоматично відкривається в прихованому режимі одночасно з Microsoft Excel. Ті. коли ви просто запускаєте Excel або відкриваєте будь-який файл з диска, насправді відкриваються два файли – ваш і Personal.xlsb, але другого ми не бачимо. Таким чином, усі макроси, які зберігаються в ЛКМ, доступні для запуску в будь-який час, поки відкритий Excel.
Якщо ви ніколи не користувалися ЛКМ, то спочатку файл Personal.xlsb не існує. Найпростіший спосіб створити його — записати на диктофон якийсь непотрібний безглуздий макрос, але вказати місцем його зберігання Особисту книгу — тоді Excel буде змушений автоматично створити його для вас. Для цього:
- Натисніть розробник (розробник). Якщо вкладки розробник не видно, то його можна ввімкнути в налаштуваннях через Файл – Параметри – Налаштування стрічки (Домашня сторінка — Параметри — Налаштувати стрічка).
- На вкладці Додатково розробник клацання Запис макросу (Запис макросу). У вікні, що відкриється, виберіть Personal Macro Book (Особистий робочий зошит з макросів) як місце для зберігання написаного коду та преси OK:
- Зупинити запис кнопкою Зупинити запис (Зупинити запис) таб розробник (розробник)
Ви можете перевірити результат, натиснувши на кнопку Візуальний Бейсік прямо там, на вкладці. розробник – у відкритому вікні редактора у верхньому лівому куті на панелі Проект — проект VBA повинен з'явитися наш файл ОСОБИСТИЙ. XLSB. Його гілку якого можна розгорнути знаком плюс зліва, досягнувши Модуль1, де зберігається код безглуздого макросу, який ми щойно записали:
Вітаємо, ви щойно створили свою персональну книгу макросів! Тільки не забудьте натиснути на кнопку збереження з дискетою в лівому верхньому куті панелі інструментів.
Як користуватися персональною книгою макросів
Далі все просто. Будь-який потрібний вам макрос (тобто фрагмент коду, який починається з нижче і закінчення End Sub) можна безпечно скопіювати та вставити в будь-яке з них Модуль1, або в окремому модулі, додавши його попередньо через меню Вставка – модуль. Зберігати всі макроси в одному модулі чи розміщувати їх у різних – лише справа смаку. Це має виглядати приблизно так:
Ви можете запустити доданий макрос у діалоговому вікні, викликаному кнопкою Макрос (Макроси) таб розробник:
У цьому ж вікні, натиснувши кнопку параметри (Параметри), ви можете встановити комбінацію клавіш для швидкого запуску макросу з клавіатури. Будьте обережні: комбінації клавіш для макросів розрізняють розкладку (або англійську) та регістр.
Крім звичайних макропроцедур в Особистій книзі можна також зберігати призначені для користувача функції макросу (UDF = функція, визначена користувачем). На відміну від процедур, код функції починається з оператора функціяor Громадська функція, і закінчуються на End Function:
Код необхідно таким же чином скопіювати в будь-який модуль книги PERSONAL.XLSB, і тоді функцію можна буде викликати звичайним способом, як і будь-яку стандартну функцію Excel, натиснувши кнопку fx у рядку формул і вибір функції у вікні Майстри функцій в категорії Визначений користувачем (Визначений користувачем):
Прикладів таких функцій можна знайти у великій кількості в Інтернеті або тут на сайті (кількість словами, приблизний текстовий пошук, VLOOKUP 2.0, конвертація кирилиці в транслітерацію тощо)
Де зберігається персональна книга макросів?
Якщо ви користуєтеся Особистою книгою макросів, то рано чи пізно у вас виникне бажання:
- поділіться своїми накопиченими макросами з іншими користувачами
- скопіювати та перенести Особисту книгу на інший комп’ютер
- зробити резервну копію
Для цього знадобиться знайти на диску комп'ютера файл PERSONAL.XLSB. За замовчуванням цей файл зберігається в спеціальній папці запуску Excel під назвою XLSTART. Отже, все, що потрібно, це дістатися до цієї папки на нашому ПК. І тут виникає невелика складність, оскільки розташування цієї папки залежить від версії Windows і Office і може відрізнятися. Зазвичай це один із таких варіантів:
- C:Program FilesMicrosoft OfficeOffice12XLSTART
- C:Documents and SettingsComputerApplication DataMicrosoftExcelXLSTART
- C: Користувачіім'я вашого облікового записуAppDataRoamingMicrosoftExcelXLSTART
Крім того, ви можете запитати сам Excel про розташування цієї папки за допомогою VBA. Для цього в редакторі Visual Basic (кнопка Візуальний Бейсік таб розробник) потрібно відкрити вікно Негайний Сполучення клавіш Ctrl + G, введіть команду ? Application.StartupPath і натисніть на Що натомість? Створіть віртуальну версію себе у :
Отриманий шлях можна скопіювати та вставити у верхній рядок вікна Провідника в Windows і клацнути
Що натомість? Створіть віртуальну версію себе у
– і ми побачимо папку з нашим файлом Personal Book of Macros:
PS
І кілька практичних нюансів на шляху:
- при використанні Personal Macro Book Excel працюватиме трохи повільніше, особливо на слабших ПК
- варто періодично очищати Особисту книгу від інформаційного сміття, старих і непотрібних макросів тощо.
- у корпоративних користувачів іноді виникають труднощі з використанням Особистої книги, т.к. це файл у системній прихованій папці
- Що таке макроси і як їх використовувати в роботі
- Корисність для програміста VBA
- Тренінг «Програмування макросів на VBA в Microsoft Excel»