Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Как ускорить и оптимизировать код VBA


Рано или поздно у пишущих на Visual Basic for Applications возникает проблема - код хоть и облегчает жизнь и делает все автоматически, но очень долго. В этой статье я решил собрать несколько простых рекомендаций, которые помогут ускорить работу кода VBA, при этом в некоторых случаях весьма внушительно - в десятки, а то и больше, раз. Основной упор в статье сделан на начинающих, поэтому в начале статьи приводятся самые простые методы оптимизации. Более "глубокие" решения по оптимизации кода приведены в конце статьи, т.к. для применения данных решений необходим достаточный опыт работы в VB и сходу такие методы оптимизации кому-то могут быть непонятны.

  1. Если в коде есть много всяких Activate и Select, тем более в циклах - следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate - зачем нужны и нужны ли?
  2. Обязательно на время выполнения кода отключить:
    • автоматический пересчет формул. Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода - это может дико тормозить код, если формул много:
      Application.Calculation = xlCalculationManual

      Если во время кода все же нужно пересчитывать какие-то диапазоны, то можно пересчитывать только их:

      Range("A1:C60").Calculate
    • обновление экрана, чтобы действия по изменению значений ячеек и пр. не мелькали. Зачем это надо? Т.к. все эти действия обращаются к графическому процессору и заставляют его трудиться для перерисовки экрана - это может значительно тормозить код:
      Application.ScreenUpdating = False
    • На всякий случай отключаем отслеживание событий. Нужно для того, чтобы Excel не выполнял никаких событийных процедур, которые могут быть в листе, в котором производятся изменения. Как правило это события изменения ячеек, активации листов и пр.:
      Application.EnableEvents = False
    • Если книга выводилась на печать или выводится на печать в процессе выполнения кода, то лучше убрать разбиение на печатные страницы.
      ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

      если печать производится внутри кода, то эту строку желательно вставить сразу после строки, выводящей лист на печать(при условии, что печать не происходит в цикле. В этом случае - по завершению цикла печати).
      Я советую всегда отключать разбиение на страницы, т.к. это может тормозить весьма значительно, т.к. заставляет при любом изменении на листах обращаться к принтеру и переопределять кол-во и размер печатных страниц. А это порой очень не быстро.

    • На всякий случай можно отключить отображение информации в строке статуса Excel(в каких случаях там вообще отображается информация и зачем можно узнать в статье: Отобразить процесс выполнения). Хоть это и не сильно поедает ресурсы - иногда может все же ускорить работу кода:
      Application.StatusBar = False

    Главное, что следует помнить - все эти свойства необходимо включить обратно после работы кода. Иначе могут быть проблемы с работой внутри Excel. Например, если забыть включить автопересчет формул - большинство формул будут пересчитывать исключительно принудительным методом - после нажатия сочетания клавиш Shift+F9. А если забыть отключить обновление экрана - то есть шанс заблокировать себе возможность работы на листах и книгах. Хотя по умолчанию свойство ScreenUpdating и должно возвращаться в True, если было отключено внутри процедуры - лучше не надеяться на это и привыкать возвращать все свойства на свои места принудительно. По сути все это сведется к нескольким строкам:

    'Возвращаем обновление экрана
    Application.ScreenUpdating = True
    'Возвращаем автопересчет формул
    Application.Calculation = xlCalculationAutomatic
    'Включаем отслеживание событий
    Application.EnableEvents = True

    Как такой код выглядит на практике. Предположим, надо записать в цикле в 10 000 строк значения:

    Sub TestOptimize()
    'отключаем обновление экрана
    Application.ScreenUpdating = False
    'Отключаем автопересчет формул
    Application.Calculation = xlCalculationManual
    'Отключаем отслеживание событий
    Application.EnableEvents = False
    'Отключаем разбиение на печатные страницы
    ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False
     
    'Непосредственно код заполнения ячеек
    Dim lr As Long
    For lr = 1 To 10000
        Cells(lr, 1).Value = lr 'для примера просто пронумеруем строки
    Next
     
    'Возвращаем обновление экрана
    Application.ScreenUpdating = True
    'Возвращаем автопересчет формул
    Application.Calculation = xlCalculationAutomatic
    'Включаем отслеживание событий
    Application.EnableEvents = True
    End Sub

    Разрывы печатных страниц можно не возвращать - они тормозят работу в любом случае.

  3. Следует избегать циклов, вроде Do While для поиска последней ячейки. Часто такую ошибку совершают начинающие. Куда эффективнее и быстрее вычислять последнюю ячейку на всем листе или в конкретном столбце без этого тормозного цикла Do While. Я обычно использую
    lLastRow = Cells(Rows.Count,1).End(xlUp).Row

    другие варианты определения последней ячейки я детально описывал в статье: Как определить последнюю ячейку на листе через VBA?

Для более опытных пользователей VBA я приведу несколько решений по оптимизации кодов в различных ситуациях:

  • Самая хорошая оптимизация кода, если приходится работать с ячейками листа напрямую, обрабатывать их и, возможно, изменять значения, то быстрее все обработки делать в массиве и разом выгружать на листе. Например, код выше по заполнению ячеек номерами будет в этом случае выглядеть так:
    Sub TestOptimize_Array()
    'Непосредственно код заполнения ячеек
    Dim arr, lr As Long
    'запоминаем в массив одним махом все значения 10000 строк первого столбца
    arr = Cells(1, 1).Resize(10000).Value
    'если нужно заполнение для двух и более столбцов
    'arr = Cells(1, 1).Resize(10000, 2).Value
    'или 
    'arr = Range(Cells(1, 1),Cells(10000, 2)).Value
    'или автоматически вычисляем последнюю ячейку и заносим в массив данные, начиная с ячейки А3
    'llastr = Cells(Rows.Count, 1).End(xlUp).Row 'последняя ячейка столбца А
    'arr = Range(Cells(3, 1),Cells(llastr, 2)).Value
    For lr = 1 To 10000
        arr(lr,1) = lr 'заполняем массив порядковыми номерами
    Next
    'Выгружаем обработанный массив обратно на лист в те же ячейки
    Cells(1, 1).Resize(10000).Value = arr
    End Sub

    Но здесь следует учитывать и тот момент, что большие массивы могут просто вызвать переполнение памяти. Наиболее актуально это для 32-битных систем, где на VBA и Excel выделяется памяти меньше, чем в 64-битных системах

  • Если используете быстрый ЕСЛИ - IIF, то замените его на IF ... Then ... Else
  • Так же лучше вместо Switch() и Choose() применить тот же IF ... Then ... Else
  • В большинстве случаев проверять строку на "не пусто" лучше через Len(), чем прямое сравнение с пустотой: Len(s)=0 вместо s = "". Связано с тем, что работа со строками значительно медленнее, чем с числовыми данными и Len по сути не подсчитывает длину переменной, а берет это число непосредственно уже готовое из памяти. При сравнении же текста с пустой строкой(""), VBA сначала создает в памяти переменную нулевой длинны, а уже потом сравнивает с ней наш текст. Поэтому в некоторых случаях так же ускоряет сравнение и в таком виде: s = vbNullString
  • Не применять объединение строк без необходимости. Например, s = "АВ", будет быстрее, чем: s ="А" & "В"
  • Не применять сравнение текстовых величин напрямую. Лучше применить встроенную функцию StrComp:
    If s <> s1 Then будет медленнее, чем
    If StrComp(s, s1, vbBinaryCompare) <> 0
    и тем более, если при сравнении необходимо не учитывать регистр:
    If LCase(s) <> LCase(s1) Then будет медленнее, чем
    If StrComp(s, s1, vbTextCompare) <> 0
  • Циклы ForNext в большинстве случаев работает быстрее, чем цикл Do ... Lоор
  • Избегать присвоения переменным типа Variant. Хоть соблазн и велик - этот тип забирает много памяти и в дальнейшем замедляет работу кода. Так же для объектных переменных следует избегать по возможности безликого глобального типа Object и применять конкретный тип:
    Dim rRange as Object, wsSh as Object

    будет медленнее работать, чем:

    Dim rRange as Range, wsSh as Worksheet

    Причина в том, что при объявлении As Object мы не даем VBA практически никакой информации о типе данных, кроме того, что это какой-то объект. И VBA приходится "на лету" внутри кода при каждом обращении к такой переменной определять её конкретный тип(Range, Worksheet, Workbook, Chart и т.д.). Что опять же занимает время.

  • Если работаете с массивами, то можно при объявлении указать это явно:
    Dim arr()

    вместо

    Dim arr

    Такая инициализация происходит быстрее.
    А еще лучше будет при этом еще и тип данных сразу присвоить:

    Dim arr() as string, arr2() as long

    но это только если есть уверенность в том, что в массив будут заноситься строго указанные типы данных

Конечно, это не все приемы и решения для оптимизации. Но на первых парах должно хватить. Плюс, всегда следует исходить из здравого смысла. Например, если код выполняется за 2 секунды, то вероятно нет смысла его дальше оптимизировать. Конечно, если этот код не из тех, которые просто изменяют значение одной-двух ячеек.


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 4 комментария
  1. Сергей:

    Спасибо за статью, очень помогла.
    Хотел обратить внимание, что вот в этом месте примера:
    'Отключаем разбиение на печатные страницы
    Application.StatusBar = False
    ошибка.

  2. Апострофф:

    Здравствуйте! Спасибо за полезный ресурс.
    Опечатка с слове Choose -
    "Так же лучше вместо Switch() и Shoose() применить тот же IF ... Then ... Else"

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти