Робота з макросами Excel VBA
Макроси в Excel зберігаються як код VBA, і іноді вам захочеться редагувати цей код безпосередньо. У цьому посібнику буде розглянуто, як переглядати та редагувати макроси, описувати деякі методи налагодження макросів та наводити деякі загальні приклади редагування.
Перегляд макросів
Список макросів можна показати у діалоговому вікні Макроси. Щоб переглянути це діалогове вікно, виберіть вкладку Розробник на стрічці та натисніть кнопку Макроси.
Якщо відкрито кілька книг, у списку відображатимуться макроси з усіх книг. Макроси в активній книзі відображатимуться лише за назвою, тоді як макроси в інших книгах матимуть префікс назви книги та оклику (тобто «Book2! OtherMacro»).
Відкрийте макрос для редагування
Ви можете використовувати діалогове вікно «Макрос», щоб відкрити код макросу, вибравши назву макросу та натиснувши кнопку «Редагувати». Це відкриє макрос у редакторі VB.
Крім того, ви можете відкрити редактор VB безпосередньо, натиснувши кнопку Visual Basic на вкладці Розробник або натиснувши комбінацію клавіш ALT+F11.
За допомогою цього методу вам потрібно буде перейти до потрібного макросу (також званий «процедура»). Ми розглянемо макет редактора VBA:
Огляд редактора VB
Редактор VB має кілька вікон; у цьому посібнику ми розглянемо вікно проекту, вікно властивостей та вікно коду.
Вікно проекту
У вікні проекту кожен файл Excel відображається як власний проект із усіма об’єктами в цьому проекті за категоріями. Записані макроси відображатимуться в категорії "Модулі", зазвичай в об’єкті “Модуль1”. (Якщо у вашому проекті є кілька модулів, і ви не впевнені, де зберігається ваш макрос, просто відкрийте його у вищезгаданому діалоговому вікні Макроси.)
Вікно властивостей
У вікні властивостей відображаються властивості та пов’язані значення об’єкта - наприклад, натискання на об’єкт аркуша у вікні проекту покаже список властивостей аркуша. Імена властивостей знаходяться зліва, а значення властивостей - справа.
Вибір модуля у вікні Project покаже, що він має лише одну властивість, “(Name)”. Ви можете змінити назву модуля, двічі клацнувши значення властивості, ввівши нову назву та натиснувши Enter. Зміна назви модуля перейменує його у вікно проекту, що стане в нагоді, якщо у вас багато модулів.
Вікна коду
Вікна коду - це спеціальні текстові редактори, в яких можна редагувати код VBA вашого макросу. Якщо ви хочете побачити код для макросу, розташований у модулі 1, двічі клацніть «Модуль1» у вікні проекту.
Запуск макросів у редакторі VB
Макроси можна запускати безпосередньо з редактора VB, що корисно для тестування та налагодження.
Запуск макросу
- У вікні проекту двічі клацніть модуль, що містить макрос, який потрібно перевірити (щоб відкрити вікно коду)
- У вікні коду встановіть курсор у будь -якому місці коду макросу між “Sub” та “End Sub”
- Натисніть на Біжи на панелі інструментів або натисніть комбінацію клавіш F5
“Покроковий” макрос
Замість того, щоб виконувати макрос одночасно, ви можете запускати макрос по одному рядку за допомогою комбінації клавіш, щоб «пройти» код. Макрос призупиняється в кожному рядку, дозволяючи вам переконатися, що кожен рядок коду виконує те, що ви очікуєте в Excel. Ви також можете будь -коли зупинити продовження макросу за допомогою цього методу.
Щоб "перейти" через макрос:
- У вікні проекту двічі клацніть модуль, що містить макрос, який потрібно перевірити (щоб відкрити вікно коду)
- У вікні коду встановіть курсор у будь -якому місці коду макросу
- Натисніть комбінацію клавіш F8, щоб розпочати процес «крок за кроком»
- Натискайте клавішу F8, щоб прискорити виконання коду, що позначено жовтим виділенням у вікні Код
- Щоб зупинити продовження макросу, натисніть кнопку Скинути кнопку
Навіщо редагувати макроси VBA?
Макрореєстратор - хоча він ефективний - також дуже обмежений. У деяких випадках він створює повільні макроси, записує дії, які ви не збиралися повторювати, або записує те, що ви не думали, що робите. Навчання редагуванню макросів допоможе їм працювати швидше, ефективніше і передбачуваніше.
Окрім вирішення цих проблем, ви також отримаєте значне збільшення продуктивності, коли використаєте всю потужність макросів. Макроси не просто повинні бути записами завдань - макроси можуть містити логіку, тому вони виконують завдання лише за певних умов. Всього за пару хвилин ви можете кодувати цикли, які повторюють завдання сотні чи тисячі разів за один раз!
Нижче ви знайдете кілька корисних порад, які допоможуть оптимізувати код макросу, а також інструменти, які допоможуть вашим макросам працювати важче та розумніше.
Загальні приклади редагування макросів
Прискорення макросів
Якщо у вас є макрос, на виконання якого потрібно багато часу, може бути кілька причин, чому він працює повільно.
По -перше: під час запуску макросу Excel покаже все так, як це відбувається в режимі реального часу - поки це можливо подивіться насправді швидко до васпоказ твір є значним хітом у виконанні. Один із способів прискорити роботу Excel - це повідомити його припинити оновлення екрана:
'Disable Screen Updating Application.ScreenUpdating = False' Увімкнути оновлення програми Screen.ScreenUpdating = True
Рядок "Application.ScreenUpdating = False" означає, що ви не побачите, що макрос працює, але він буде працювати набагато швидше. Зауважте, що завжди потрібно встановлювати ScreenUpdating на True в кінці вашого макросу, інакше Excel може не діяти так, як ви очікуєте пізніше!
Ще один спосіб прискорити макроси:вимкнути автоматичний розрахунок у макросі. Якщо ви працювали зі складними електронними таблицями, ви знатимете, що невеликі зміни можуть викликати тисячі обчислень, на виконання яких потрібен час, тому багато людей вимикають автоматичний розрахунок у параметрах Excel. Ви також можете переключити це за допомогою коду VBA, щоб ваш макрос все ще швидко працював на інших комп'ютерах. Це допомагає у випадках, коли ви вставляєте копію багато комірок формул або викликаєте багато обчислень, коли ви вставляєте дані в діапазон:
"Вимкнути програму автоматичного обчислення. Розрахунок = xlCalculationManual" Увімкнути програму автоматичного обчислення. Розрахунок = xlВирахування Автоматично
Додати цикли та логіку (оператори If)
Макрореєстратор зберігає всі ваші дії як код мовою VBA. VBA - це більше, ніж просто спосіб запису дій у Excel - це мова програмування, що означає, що він може містити код для прийняття рішень про те, які дії виконувати, або повторювати дії до виконання умови.
Цикл
Скажімо, ви хотіли створити макрос, який би підготував звіт, і в рамках цього макросу вам потрібно було додати до робочої книги дев’ятнадцять аркушів, загалом двадцять. Ви можете записати себе, натискаючи кнопку (+) знову і знову, або написати цикл, який повторює дію, наприклад:
Sub ReportPrep () Dim i As Long For i = 1 To 19 Sheets. Додати Next i End Sub
У цьому прикладі ми використовуємо a Цикл for, який є своєрідним циклом, який перебирає ряд елементів. Тут наш діапазон - це числа від 1 до 19, використовуючи змінну з назвою "i", щоб цикл міг відстежувати. Усередині нашого циклу між ними повторюється лише одна дія за танаступний рядків (додавання аркуша), але ви можете додати стільки коду всередину циклу, скільки захочете, наприклад, відформатувати аркуш або скопіювати та вставити дані на кожен аркуш - що б ви не хотіли повторити.
Якщо Заяви
Ан Якщо заява використовується для вирішення того, чи виконується якийсь код, за допомогою логічного тесту для прийняття рішення. Ось простий приклад:
Sub ClearIfSmall () If Selection.Value <100 Тоді Selection.Clear End Якщо End Sub
Цей простий приклад показує, як працює оператор If - ви перевіряєте якусь умову, яка є True або False (значення вибраної комірки менше 100?), і якщо тест повертає True, код всередині запускається.
Недоліком цього коду є те, що він тестує лише одну клітинку за раз (і не спрацює, якщо вибрано кілька клітинок). Це було б корисніше, якби ви могли… прокрутити кожну вибрану клітинку та перевірити кожну…
Sub ClearIfSmall () Dim c Як діапазон для кожного c In Selection. Клітинки If c.Value <100 Тоді c.Clear End If Next c End Sub
У цьому прикладі є дещо інший цикл For - цей не перебирає діапазон чисел, а замість цього перебирає всі клітинки у виділенні, використовуючи для відстеження змінну під назвою «c». Усередині циклу значення ‘c’ використовується для визначення того, чи потрібно очистити клітинку чи ні.
Цикли та оператори If можна об'єднати будь -яким способом - ви можете помістити цикли всередину циклів, або один If всередину іншого, або скористатися If, щоб вирішити, чи повинен цикл взагалі працювати.
<<>>
Видаліть ефекти прокрутки
Поширеною причиною редагування коду макросу є видалення прокрутки екрана. Під час запису макросу вам може знадобитися прокрутити інші області аркуша, але макроси не потрібно прокручувати, щоб отримати доступ до даних.
Прокрутка може захарастити ваш код сотнями або навіть тисячами рядків непотрібного коду. Ось приклад коду, який записується при натисканні та перетягуванні на смузі прокрутки:
Такий код абсолютно непотрібний і його можна видалити, не впливаючи на інші функції. Навіть якби ви хотіли зберегти прокрутку, цей код все одно можна було б ущільнити у цикл.
Видаліть зайвий код
Записані макроси, як правило, додають багато зайвого коду, який не обов'язково відображає те, що ви хочете, щоб макрос робив. Візьмемо, наприклад, наступний записаний код, який записує зміну назви шрифту в комірці:
Незважаючи на те, що була змінена лише назва шрифту, було записано одинадцять (11) змін шрифту, таких як розмір шрифту, текстові ефекти тощо. записаний макрос не працюватиме!
Цей макрос можна змінити так, щоб він міняв лише назву шрифту:
Цей макрос не тільки працюватиме належним чином зараз, але й його набагато легше читати.
Видалити рухи курсора
Інша річ, яка записується в макроси, - це виділення аркуша та комірки. Це проблема, оскільки користувач може легко втратити слід над тим, над чим тільки працював, якщо курсор переміститься в інше положення після запуску макросу.
Як і при прокручуванні, ти може знадобитися перемістити курсор і вибрати різні клітинки для виконання завдання, але макроси не повинні використовувати курсор для доступу до даних. Розглянемо наступний код, який копіює діапазон, а потім вставляє його на три інші аркуші:
З цим кодом є кілька проблем:
- Користувач втратить попереднє місце у книзі
- Макрос не визначає, який аркуш ми копіюємовід - це може стати проблемою, якщо макрос був запущений на неправильному аркуші
Крім того, код важко читається і марнотратний. Ці проблеми можна вирішити досить легко:
У цьому коді зрозуміло, що ми копіюємо з Sheet1, і ні активний аркуш, ні вибраний діапазон не потрібно змінювати, щоб вставити дані. (Однією з істотних змін є використання “PasteSpecial” замість “Paste” - об’єкти Range, наприклад “Range (“ C4 ″) ”, мають доступ лише до команди PasteSpecial.)
Кожен раз, коли код переповнюється посиланнями на ".Select" та "Selection", це означає, що є можливість оптимізувати цей код та зробити його більш ефективним.
