У цьому підручнику буде обговорено, як прискорити макроси VBA та інші найкращі практики VBA.
Налаштування для прискорення коду VBA
Нижче ви знайдете кілька порад щодо прискорення коду VBA. Поради вільно організовані за важливістю.
Найпростіший спосіб покращити швидкість коду VBA - це відключити оновлення екрана та вимкнути автоматичні розрахунки. Ці параметри слід вимикати у всіх великих процедурах.
Вимкнути оновлення екрана
За замовчуванням Excel відображатиме зміни у робочих книгах у режимі реального часу під час виконання коду VBA. Це спричиняє значне уповільнення швидкості обробки, оскільки Excel більшість інтерпретує та відображає зміни для кожного рядка коду.
Щоб вимкнути оновлення екрана:
| 1 | Application.ScreenUpdating = Неправда |
Наприкінці макросу слід знову увімкнути оновлення екрана:
| 1 | Application.ScreenUpdating = Істина |
Під час запуску коду вам може знадобитися «оновити» екран. Команди «оновити» немає. Натомість вам потрібно буде знову ввімкнути оновлення екрана та вимкнути його знову.
Встановіть Розрахунки на Ручний
Щоразу, коли змінюється значення комірки, Excel повинен слідувати "дереву обчислень", щоб перерахувати всі залежні клітинки. Крім того, щоразу, коли формулу змінюють, Excel буде потрібно оновлювати "дерево обчислень" на додаток до повторного обчислення всіх залежних клітинок. Залежно від розміру вашої книги, ці перерахунки можуть призвести до того, що ваші макроси будуть працювати надмірно повільно.
Щоб встановити Розрахунки на Ручний:
| 1 | Application.Calculation = xlManual |
Щоб повторно обчислити всю книгу вручну:
| 1 | Обчисліть |
Зверніть увагу, що ви також можете обчислити лише аркуш, діапазон або окрему клітинку, якщо це необхідно для поліпшення швидкості.
Щоб відновити автоматичні розрахунки (наприкінці процедури):
| 1 | Розрахунок = xlАвтоматичний |
Важливо! Це налаштування Excel. Якщо ви не налаштуєте обчислення на автоматичні, ваша книга не буде повторно обчислюватися, доки ви не скажете це.
Ви побачите найбільші поліпшення з наведених вище налаштувань, але є кілька інших налаштувань, які можуть змінити ситуацію:
Вимкнути події
Події - це «тригери», які викликають особливі події процедури проведення заходів бігти. Приклади включають: коли змінюється будь -яка клітинка на аркуші, коли активується аркуш, коли відкривається книга, перед збереженням книги тощо.
Вимкнення подій може спричинити незначне поліпшення швидкості під час виконання будь -яких макросів, але поліпшення швидкості може бути значно більшим, якщо у вашій книзі використовуються події. А в деяких випадках відключення подій необхідно, щоб уникнути створення нескінченних циклів.
Щоб вимкнути події:
| 1 | Application.EnableEvents = Неправда |
Щоб знову ввімкнути події:
| 1 | Application.EnableEvents = Істина |
Вимкніть розрив сторінки
Вимкнення розриву сторінки може допомогти у певних ситуаціях:
- Ви раніше встановили властивість PageSetup для відповідного аркуша та ваша процедура VBA змінює властивості багатьох рядків або стовпців
- АБО Ваша процедура VBA змушує Excel обчислювати розриви сторінок (відображення попереднього перегляду друку або зміна будь -яких властивостей PageSetup).
Щоб вимкнути переривання сторінок:
| 1 | ActiveSheet.DisplayPageBreaks = Неправда |
Щоб знову ввімкнути розрив сторінки:
| 1 | ActiveSheet.DisplayPageBreaks = Правда |
Найкращі практики підвищення швидкості VBA
Уникайте активації та вибору
Під час запису макросу ви побачите багато методів активації та вибору:
| 12345678 | Sub Slow_Example ()Аркуші ("Аркуш2"). ВиберітьДіапазон ("D9"). ВиберітьActiveCell.FormulaR1C1 = "приклад"Діапазон ("D12"). ВиберітьActiveCell.FormulaR1C1 = "демонстрація"Діапазон ("D13"). ВиберітьEnd Sub |
Активувати та вибирати об’єкти зазвичай не потрібно, вони додають безлад у ваш код, і вони займають багато часу. По можливості слід уникати цих методів.
Покращений приклад:
| 1234 | Sub Fast_Example ()Аркуші ("Аркуш2"). Діапазон ("D9"). Формула R1C1 = "приклад"Аркуші ("Аркуш2"). Діапазон ("D12"). Формула R1C1 = "демонстрація"End Sub |
Уникайте копіювання та вставки
Копіювання вимагає значної пам’яті. На жаль, ви не можете сказати VBA очистити внутрішню пам’ять. Натомість Excel очищатиме свою внутрішню пам’ять через (здавалося б) певні проміжки часу. Тому, якщо ви виконуєте багато операцій копіювання та вставлення, ви ризикуєте затримати занадто багато пам’яті, що може різко уповільнити ваш код або навіть вивести з ладу Excel.
Замість копіювання та вставлення, подумайте про встановлення властивостей значення клітинок.
| 123456789 | Sub CopyPaste ()'ПовільнішеДіапазон ("a1: a1000"). Діапазон копіювання ("b1: b1000")'ШвидшеДіапазон ("b1: b1000"). Значення = Діапазон ("a1: a1000"). ЗначенняEnd Sub |
Використовуйте цикли For Each замість For Loops
При циклічному переході через об'єкти цикл "Для кожного" швидше, ніж цикл "Для кожного". Приклад:
Це для циклу:
| 123456 | Sub Loop1 ()dim i як ДіапазонДля i = 1 до 100Клітинки (i, 1). Значення = 1Далі iEnd Sub |
| 123456 | Sub Loop2 ()Яскрава клітинка як діапазонДля кожної клітинки в діапазоні ("a1: a100")комірка. Значення = 1Наступна клітинаEnd Sub |
Заявляйте змінні / Використовуйте опцію явно
VBA не вимагає оголошення ваших змінних, якщо ви не додасте Option Explicit у верхній частині вашого модуля:| 1 | Опція явна |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlEnd Sub |
Використовувати з - Завершити з операторами
Якщо ви посилаєтесь на одні і ті ж об’єкти кілька разів (наприклад, діапазони, робочі аркуші, робочі книги), подумайте про використання оператора With. Він швидше обробляється, полегшує читання коду та спрощує його.З прикладом заяви:| 12345678 | Підшвидший_приклад ()З аркушами ("Аркуш2").Range ("D9"). Формула R1C1 = "приклад".Range ("D12"). Формула R1C1 = "демонстрація".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueЗакінчити зEnd Sub |
| 123456 | Sub Slow_Example ()Аркуші ("Аркуш2"). Діапазон ("D9"). Формула R1C1 = "приклад"Аркуші ("Аркуш2"). Діапазон ("D12"). Формула R1C1 = "демонстрація"Аркуші ("Sheet2"). Діапазон ("D9"). Font.Bold = ПравдаАркуші ("Аркуш2"). Діапазон ("D12"). Font.Bold = ПравдаEnd Sub |
Поради щодо вдосконаленої практики
Захистіть лише UserInterface
Гарна практика-захищати свої робочі листи від редагування незахищених клітинок, щоб запобігти випадковому пошкодженню книгою кінцевим користувачем (або вами!). Однак це також захистить аркуші (аркуші) від дозволу VBA вносити зміни. Тому вам потрібно зняти захист та знову захистити аркуші, що дуже багато часу, коли це робиться на багатьох аркушах.
| 12345 | Sub UnProtectSheet ()Аркуші ("аркуш1"). Зняти захист "пароль"'Редагувати аркуш1Аркуші ("аркуш1"). Захистити "пароль"End Sub |
Натомість ви можете захистити аркуші, встановивши UserInterfaceOnly: = True. Це дозволяє VBA вносити зміни до аркушів, захищаючи їх від користувача.
| 1 | Аркуші ("аркуш1"). Захист пароля: = "пароль", UserInterFaceOnly: = Істина |
Важливо! UserInterFaceOnly скидає значення False щоразу, коли відкривається книга. Тож, щоб скористатися цією чудовою функцією, вам потрібно буде використовувати події Workbook_Open або Auto_Open, щоб встановити налаштування кожного разу, коли відкривається книга.
Розмістіть цей код у модулі цієї робочої книги:
| 123456 | Private Sub Workbook_Open ()Затемнити як робочий аркушДля кожного ws у робочих аркушахws.Protect Password: = "пароль", UserInterFaceOnly: = ПравдаНаступний wsEnd Sub |
або цей код у будь -якому звичайному модулі:
| 123456 | Приватний Sub Auto_Open ()Затемнити як робочий аркушДля кожного ws у робочих аркушахws.Protect Password: = "пароль", UserInterFaceOnly: = ПравдаНаступний wsEnd Sub |
Використовуйте масиви для редагування великих діапазонів
Маніпулювання великими діапазонами клітин (наприклад, 100 000+) може зайняти багато часу. Замість того, щоб прокручувати діапазони клітинок, маніпулювати кожною клітиною, ви можете завантажити клітинки в масив, обробити кожен елемент у масиві, а потім вивести масив назад у вихідні осередки. Завантаження клітинок у масиви для маніпуляцій може бути набагато швидшим.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Яскрава клітинка як діапазонDim tStart As DoubletStart = ТаймерДля кожної клітинки в діапазоні ("A1: A100000")cell.Value = cell.Value * 100Наступна клітинаDebug.Print (Таймер - tStart) & "секунди"End SubSub LoopArray ()Dim arr як варіантЗатемнити елемент як варіантDim tStart As DoubletStart = Таймерarr = Діапазон ("A1: A100000"). ЗначенняДля кожного товару У обрitem = item * 100Наступний пунктДіапазон ("A1: A100000"). Значення = обрDebug.Print (Таймер - tStart) & "секунди"End Sub |
