В этом руководстве будет рассказано, как ускорить макросы VBA и другие передовые практики VBA.
Настройки для ускорения кода VBA
Ниже вы найдете несколько советов по ускорению кода VBA. Подсказки слабо организованы по важности.
Самый простой способ повысить скорость вашего кода VBA - отключить ScreenUpdating и отключить автоматические вычисления. Эти настройки должны быть отключены во всех больших процедурах.
Отключить обновление экрана
По умолчанию Excel отображает изменения в книгах в реальном времени по мере выполнения кода VBA. Это приводит к значительному снижению скорости обработки, поскольку Excel чаще всего интерпретирует и отображает изменения для каждой строки кода.
Чтобы отключить обновление экрана:
| 1 | Application.ScreenUpdating = False |
В конце макроса вы должны снова включить обновление экрана:
| 1 | Application.ScreenUpdating = True |
Пока ваш код работает, вам может потребоваться «обновить» экран. Команды «обновить» нет. Вместо этого вам нужно будет снова включить обновление экрана и снова отключить его.
Установите для расчетов значение "Вручную"
При изменении значения ячейки Excel должен следовать «дереву вычислений» для пересчета всех зависимых ячеек. Кроме того, при изменении формулы Excel потребуется обновить «дерево вычислений» в дополнение к повторному вычислению всех зависимых ячеек. В зависимости от размера вашей книги эти перерасчеты могут привести к неоправданно медленной работе макросов.
Чтобы установить для расчетов ручной режим:
| 1 | Application.Calculation = xlManual |
Чтобы вручную пересчитать всю книгу:
| 1 | Рассчитать |
Обратите внимание, что вы также можете рассчитать только лист, диапазон или отдельную ячейку, если это необходимо для повышения скорости.
Чтобы восстановить автоматические расчеты (в конце процедуры):
| 1 | Application.Calculation = xlAutomatic |
Важный! Это настройка Excel. Если вы не переустанавливаете вычисления на автоматический, ваша книга не будет пересчитывать, пока вы не скажете.
Вы увидите самые большие улучшения по сравнению с указанными выше настройками, но есть несколько других настроек, которые могут иметь значение:
Отключить события
События - это «триггеры», которые вызывают особые процедуры проведения мероприятий бежать. Примеры включают: когда любая ячейка на листе изменяется, когда лист активирован, когда книга открыта, перед сохранением книги и т. Д.
Отключение событий может привести к незначительному повышению скорости при запуске любых макросов, но улучшение скорости может быть намного больше, если ваша книга использует события. А в некоторых случаях отключение событий необходимо, чтобы избежать бесконечных циклов.
Чтобы отключить события:
| 1 | Application.EnableEvents = False |
Чтобы снова включить события:
| 1 | Application.EnableEvents = True |
Отключить PageBreaks
Отключение PageBreaks может помочь в определенных ситуациях:
- Вы ранее установили свойство PageSetup для соответствующего листа а также ваша процедура VBA изменяет свойства многих строк или столбцов
- ИЛИ Ваша процедура VBA заставляет Excel вычислять разрывы страниц (отображение предварительного просмотра или изменение любых свойств PageSetup).
Чтобы отключить PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = False |
Чтобы снова включить PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Лучшие практики для повышения скорости VBA
Избегайте активации и выбора
Когда вы записываете макрос, вы увидите множество методов Activate и Select:
| 12345678 | Sub Slow_Example ()Таблицы ("Лист2"). ВыберитеДиапазон ("D9"). ВыберитеActiveCell.FormulaR1C1 = "пример"Диапазон ("D12"). ВыбратьActiveCell.FormulaR1C1 = "демо"Диапазон ("D13"). ВыбратьКонец подписки |
Активация и выбор объектов обычно не нужны, они добавляют беспорядок в ваш код и отнимают очень много времени. По возможности следует избегать использования этих методов.
Улучшенный пример:
| 1234 | Sub Fast_Example ()Листы ("Sheet2"). Диапазон ("D9"). FormulaR1C1 = "example"Листы ("Sheet2"). Range ("D12"). FormulaR1C1 = "demo"Конец подписки |
Избегайте копирования и вставки
Копирование требует значительного объема памяти. К сожалению, вы не можете указать VBA очистить внутреннюю память. Вместо этого Excel будет очищать внутреннюю память через (по-видимому) определенные интервалы. Поэтому, если вы выполняете много операций копирования и вставки, вы рискуете перегрузить слишком много памяти, что может резко замедлить ваш код или даже привести к сбою Excel.
Вместо копирования и вставки рассмотрите возможность установки свойств значений ячеек.
| 123456789 | Sub CopyPaste ()'ПомедленнееДиапазон («a1: a1000»). Копировать диапазон («b1: b1000»)'БыстрееДиапазон ("b1: b1000"). Значение = Диапазон ("a1: a1000"). ЗначениеКонец подписки |
Используйте циклы For Each вместо циклов For
При просмотре объектов цикл For Each быстрее, чем цикл For Loop. Пример:
Это цикл для:
| 123456 | Sub Loop1 ()dim i as RangeДля i = от 1 до 100Ячейки (i, 1). Значение = 1Далее яКонец подписки |
| 123456 | Sub Loop2 ()Тусклая ячейка как диапазонДля каждой ячейки в диапазоне ("a1: a100")cell.Value = 1Следующая ячейкаКонец подписки |
Объявление переменных / использование явного параметра
VBA не требует объявления переменных, если только вы не добавите Option Explicit в верхнюю часть модуля:| 1 | Вариант Явный |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlКонец подписки |
Использовать с - конец с утверждениями
Если вы ссылаетесь на одни и те же объекты несколько раз (например, диапазоны, рабочие листы, рабочие книги), рассмотрите возможность использования оператора With. Он быстрее обрабатывается, может упростить чтение и упростить код.С примером утверждения:| 12345678 | Sub Faster_Example ()С листами ("Лист2").Range ("D9"). FormulaR1C1 = "example".Range ("D12"). FormulaR1C1 = "demo".Range ("D9"). Font.Bold = True.Range ("D12"). Font.Bold = TrueКонец сКонец подписки |
| 123456 | Sub Slow_Example ()Листы ("Sheet2"). Диапазон ("D9"). FormulaR1C1 = "example"Листы ("Sheet2"). Range ("D12"). FormulaR1C1 = "demo"Листы ("Лист2"). Диапазон ("D9"). Полужирный шрифт = TrueЛисты ("Лист2"). Диапазон ("D12"). Полужирный шрифт = TrueКонец подписки |
Советы передовой передовой практики
Защитить UserInterfaceOnly
Рекомендуется защищать листы от редактирования незащищенных ячеек, чтобы конечный пользователь (или вы!) Случайно не повредил книгу. Однако это также защитит лист (ы) от разрешения VBA вносить изменения. Таким образом, вы должны снять защиту и повторно защитить рабочие листы, что отнимает очень много времени, когда выполняется на многих листах.
| 12345 | Sub UnProtectSheet ()Таблицы («sheet1»). Снять защиту с «пароля»'Редактировать Sheet1Таблицы («лист1»). Защитить «пароль»Конец подписки |
Вместо этого вы можете защитить листы, установив UserInterfaceOnly: = True. Это позволяет VBA вносить изменения в листы, при этом защищая их от пользователя.
| 1 | Таблицы («sheet1»). Защитить пароль: = «пароль», UserInterFaceOnly: = True |
Важный! UserInterFaceOnly сбрасывается в значение False при каждом открытии книги. Итак, чтобы использовать эту замечательную функцию, вам нужно будет использовать события Workbook_Open или Auto_Open, чтобы устанавливать параметр каждый раз при открытии книги.
Поместите этот код в модуль Thisworkbook:
| 123456 | Частная вспомогательная книга_Open ()Dim ws как рабочий листДля каждого ws в листахws.Protect Password: = "пароль", UserInterFaceOnly: = TrueСледующий wsКонец подписки |
или этот код в любом штатном модуле:
| 123456 | Частный Sub Auto_Open ()Dim ws как рабочий листДля каждого ws в листахws.Protect Password: = "пароль", UserInterFaceOnly: = TrueСледующий wsКонец подписки |
Использование массивов для редактирования больших диапазонов
Обработка большого количества ячеек (например, 100 000+) может занять очень много времени. Вместо того, чтобы перебирать диапазоны ячеек, манипулируя каждой ячейкой, вы можете загрузить ячейки в массив, обработать каждый элемент в массиве, а затем вывести массив обратно в их исходные ячейки. Загрузка ячеек в массивы для манипуляций может быть намного быстрее.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Тусклая ячейка как диапазонDim tStart как двойнойtStart = ТаймерДля каждой ячейки в диапазоне ("A1: A100000")cell.Value = cell.Value * 100Следующая ячейкаDebug.Print (Таймер - tStart) & "секунды"Конец подпискиSub LoopArray ()Dim arr как вариантТусклый элемент как вариантDim tStart как двойнойtStart = Таймерarr = Диапазон ("A1: A100000"). ЗначениеДля каждого элемента в обр.item = элемент * 100Следующий элементДиапазон ("A1: A100000"). Значение = прибл.Debug.Print (Таймер - tStart) & "секунды"Конец подписки |
