Це мої нотатки до шоу про мою появу в епізоді 33 заклику про допомогу на TechTV Canada.
VBA Macro Recorder
Кожна копія Excel, що продається з 1995 року, включає неймовірно потужний Visual Basic for Applications (VBA), що ховається за клітинками. Якщо у вас є Excel, у вас є VBA.
VBA дозволяє автоматизувати все, що можна зробити в Excel, а також робити більше речей, які в Excel загалом неможливі.
На щастя для нас, Microsoft включила макрофон з програмою Excel. Це дозволяє писати код VBA, не будучи програмістом. На жаль, Макрореєстратор має недоліки. Він не створить код, який працюватиме щоразу. Макрореєстратор наблизить вас, але в багатьох випадках вам потрібно трохи виправити код, щоб він надійно працював. В інших випадках вам потрібно знати, коли використовувати відносний запис, щоб макрос працював.
Ми з Трейсі Сирстад написали VBA та Macros Microsoft Excel 2016, щоб допомогти вам зрозуміти обмеження записаного коду та навчити, як виправити записаний код у щось, що буде працювати щоразу.
На шоу я продемонстрував процес запису макросу, який буде чудово працювати, якщо ви знаєте, як використовувати кнопку відносного посилання. Хтось дав мені аркуш Excel із сотнями імен та адрес. Вони хотіли поштові етикетки. Якщо ви коли-небудь використовували функцію злиття пошти Microsoft Word, ви знаєте, що вам потрібно кожне поле в новому стовпці на аркуші. Натомість на цьому конкретному аркуші дані спускалися вниз у стовпець А.
Процес виправлення однієї етикетки досить виснажливий.
- Почніть з покажчика комірки на ім'я у стовпці А.
- Натисніть стрілку вниз, щоб перейти до адреси
- Ctrl + x, щоб вирізати
- Стрілка вгору, стрілка вправо, щоб перейти до стовпця В поруч із назвою
- Ctrl + v, щоб вставити
- Двічі стрілка вниз, один раз стрілка ліворуч, щоб рухатися до міста
- Ctrl + x, щоб вирізати
- Стрілка вгору двічі, стрілка вправо тричі
- Ctrl + v, щоб вставити
- Стрілка вліво двічі, стрілка вниз один раз, щоб перейти до порожнього рядка.
- Утримуючи клавішу Shift, натискаючи стрілку вниз двічі
- Alt + edr для видалення порожніх рядків
- Стрілка вгору та стрілка вниз, щоб знову вибрати лише назву.
Ось анімація, що показує ці кроки:
Налаштування Excel для дозволу макросів
Хоча кожна копія Excel містить VBA, за замовчуванням Microsoft вимикає можливість запуску макросів. Вам потрібно зробити одноразову настройку, щоб дозволити макросам працювати. Відкрийте Excel. У меню виберіть Інструменти> Макрос> Безпека. Якщо рівень захисту макросів наразі встановлений як високий, змініть його на Середній.
Підготовка до запису макросу
Подумайте про кроки, необхідні для досягнення поставленого завдання. Ви хочете переконатися, що ви починаєте з вказівника клітинки на ім’я і закінчуєте ним на наступному імені. Це дозволить багаторазово запускати макрос багато разів. Коли кроки готові до роботи, увімкніть макрореєстратор. У меню виберіть Інструменти> Макрос> Записати новий макрос.
Якщо ви будете запускати цей макрос протягом багатьох днів, вам слід дати макросу корисну назву. Якщо це одноразовий макрос для автоматизації одноразового завдання, то, мабуть, залишати назву макросу як Macro1 цілком нормально. Тут важливим полем є поле клавіші швидкого доступу. Якщо ви створюєте макрос для одноразового використання, призначте макрос комбінації клавіш, наприклад, Ctrl + a - досить легко натиснути Ctrl + a кілька разів, оскільки клавіші розташовані близько одна від одної. Якщо ви збираєтеся створити макрос, який ви будете використовувати щодня, тоді ви хочете призначити макрос ключу, який ще не є важливою комбінацією клавіш швидкого доступу. Ctrl + j або Ctrl + k - хороший вибір.
Макроси одноразового використання слід зберігати у цій книзі. Якщо вам потрібно неодноразово використовувати макрос у багатьох різних книгах, ви можете зберегти макрос у Особистій книзі макросів.
Тепер вам представлена найменша панель інструментів у всьому Excel; панель інструментів «Зупинити запис». Він має лише дві піктограми і виглядає так:
Якщо ця панель інструментів вам заважає, не натискайте X, щоб закрити її. Натомість візьміть синю смужку та перетягніть панель інструментів у нове місце. Акт переміщення панелі інструментів не фіксується в макросі. Якщо ви випадково закрили панель інструментів, поверніть її за допомогою меню Перегляд> Панелі інструментів> Зупинити запис. Однак ця дія буде записана.
Першою кнопкою на панелі інструментів є кнопка "Зупинити запис". Це само собою пояснюється. Закінчивши запис макросу, натисніть панель інструментів зупинки запису.
Найважливішою (і рідко зрозумілою) кнопкою є кнопка "Відносне посилання".
У нашому поточному прикладі перед початком роботи потрібно натиснути кнопку відносного посилання. Якщо ви записуєте макрос із увімкненими відносними посиланнями, Excel запише такі дії:
- Перемістіть одну клітинку вниз
- Вирізати поточну комірку
- Переміщення на одну клітинку вгору
- Перемістіть одну клітинку вправо
- Вставити
- тощо
Якщо ви замість цього записали б макрос без відносних посилань, макрос записав би такі кроки:
- Перейти до комірки A4
- Виріжте комірку А4
- Перейти до комірки A3
- Перейти до комірки B3
- Вставте в комірку B3
- тощо
Це було б жахливо неправильно - нам потрібен макрос для роботи з клітинками, які є 1 і 2 клітинками від початкової точки макросу. Під час запуску макросу знову і знову початковою точкою буде рядок 4, рядок 5, рядок 6 і т. Д. Запис макросу без увімкнених відносних посилань макрос завжди запускався б у рядку 3 як початкова точка.
Виконайте такі дії:
- Виберіть кнопку відносного посилання на панелі інструментів «Зупинити запис»
- Покажчик комірки вже повинен мати ім'я у стовпці А.
- Натисніть стрілку вниз, щоб перейти до адреси
- Ctrl + x, щоб вирізати
- Стрілка вгору, стрілка вправо, щоб перейти до стовпця В поруч із назвою
- Ctrl + v, щоб вставити
- Двічі стрілка вниз, один раз стрілка ліворуч, щоб рухатися до міста
- Ctrl + x, щоб вирізати
- Стрілка вгору двічі, стрілка вправо тричі
- Ctrl + v, щоб вставити
- Стрілка вліво двічі, стрілка вниз один раз, щоб перейти до порожнього рядка.
- Утримуючи клавішу Shift, натискаючи стрілку вниз двічі
- Alt + edr для видалення порожніх рядків
- Стрілка вгору та стрілка вниз, щоб вибрати наступну назву у списку.
- Натисніть на панель інструментів "Зупинити запис"
- Збережіть книгу
- Перевірте макрос, натиснувши ярлик, призначений вище. Це повинно чудово зафіксувати наступне ім’я у списку
Як тільки ви побачите, що макрос працює за бажанням, ви можете утримувати Ctrl + a, щоб швидко зафіксувати кілька імен на секунду. Весь список із 500 імен можна виправити за хвилину чи дві.
Бонусна порада - Не в шоу
Ви можете легко обернути макрос у простий цикл, щоб він зафіксував усі 500 імен без необхідності натискати Ctrl + кілька сотень разів.
Натисніть клавіші Alt + F11, щоб відкрити редактор макросів.
Якщо ви не бачите панелі Project - VBAProject, натисніть Ctrl + r.
Клацніть правою кнопкою миші Module1 і виберіть View Code. Ви побачите код, який виглядає так:
Тепер вам не потрібно розуміти, що робить цей код, але ви знаєте, що ми хочемо запускати все в цьому макросі один раз для кожного імені, яке ми маємо. Якщо ви знаєте, що існує 462 імена, тоді ви можете додати 2 рядки для запуску коду 462 рази. Угорі макросу вставте новий рядок зі словами " For i = 1 to 462". Внизу макросу вставте рядок із написом " Next i". Це говорить VBA запускати код всередині 462 рази.
Висновок
Після цього простого макросу я показав приклад на шоу дуже складного макросу. Цей макрос створив зведені таблиці та діаграми для 46 підрозділів компанії. Цей звіт займав 40 годин щомісяця. Макрос VBA тепер працює і створює всі 46 звітів менш ніж за 2 хвилини.
Книга VBA та макроси Microsoft Excel 2016 підведе вас до кривої навчання, щоб ви могли перейти від запису простих макросів, як цей, до створення дуже складних звітів, які можуть заощадити сотні годин на рік. Звичайно, якщо ви не хочете вивчати VBA, найміть консультанта Excel, який складе для вас звіт.
