Ускоренный код в VBA — Excel
Видеокурсы по Excel
В сотрудничестве с SEDUO я создал несколько видеокурсов:
- Как работать с Excel – научитесь эффективно использовать Excel – самый популярный ( рейтинг 97 %, более 13 000 студентов, + 750 положительных отзывов ) – Получить курс
- MaxiCourse Excel – получите лучшую работу, больше денег, станьте незаменимым ( более 6 часов просмотра, 160 уроков ) – Получить курс
- G-таблицы. — если не нравится Excel, воспользуйтесь курсом на Google-таблицах
Как ускорить выполнение кода VBA в приложениях Excel
Ъпо воде
После создания приложения в VBA и заполнения его реальными данными вы обнаружите, что работа с расчетом занимает много времени. По сравнению с тестовым пакетом (когда у вас не было столько данных). Хотя это не имеет значения. Несколько минут, в течение которых требуемые действия выполняются автоматически, по сравнению с несколькими днями, если бы расчет производился вручную, не проблема. Если вы все же хотите ускориться, я рассмотрю несколько вариантов в статье.
Application.ScreenUpdating
Выполнение расчетов в фоновом режиме без обновления экрана.
Демонстрация скорости записи ряда чисел на листе с именем 1 . Я позаимствовал идею с http://www.vbforums.com/. Я не занимаюсь внутренней оптимизацией кода.
Для i = от 1 до 1000 рабочих листов («1»). Диапазон («A1»). Смещение (i, 0). Значение = i рабочих листов («1»). Диапазон («A1»). Смещение (i , 0).Активировать следующий i
Примечание. Этот код показан только для демонстрации скорости выполнения, когда (не) используется Application.ScreenUpdating.
Где дополнено Application.ScreenUpdating:
Скорость выполнения будет заметно выше 😉
Приложение.Расчет
Если нет необходимости спрашивать, сделайте это в конце
Если изменение ячейки не влияет на расчет или если вам не нужно постоянно менятьвыполнить расчет в листе, автоматический расчет можно отключить.
В конце макроса (VBA) не забудьте снова включить его.
Приложение.DisplayAlerts
Не отображать сообщения об ошибках.
Отключаем отображение диалогов и окон подтверждения, которые могут появиться при работе макроса (например, подтверждение при сохранении файла, удалении листа и т.п.). Если вы хорошо справились с этим в программе, вам не нужно отображать эти сообщения без необходимости. Выключение делается просто:
Перед выходом, даже в тех местах, где он отображается, снова включите его:
Заполненная ячейка
Эффективно ссылайтесь.
В одной книге я прочитал учебник, чем меньше «точек» проходит через VBA, тем быстрее он работает. Во-первых, он запомнил этот урок. Используя эту идею, я пытаюсь оптимизировать результат. В чем дело. Вы ссылаетесь на данную ячейку на листе следующим образом:
Куд содержит две точки. Теперь доделаем так, чтобы в первых 99 строках был написан текст: медленный код . Выполним медленное решение в цикле как неподходящее:
Для i = от 1 до 99 Workbooks(«muj-sesit.xlsm»).Sheets(«Sheet22»).Range(«A1»).offset(i-1) = «медленный код» Далее
Куд содержит три пункта. Для 99 строки это 297 точек. Так что успокойся. Если мы отредактируем область так, чтобы поместить область в переменную.
Set MojePromena = Workbooks(«my-sesit.xlsm»).Sheets(«Sheet22»).Range(«A1») Для i = от 1 до 99 MojePromena.offset(i-1) = «быстрый код» Далее
У нас уже есть 101 точка и, следовательно, более быстрый код.
Всего
Я буду постоянно добавлять в статью дополнительные параметры ускорения VBA. Если у вас есть где лучше продемонстрировать скорость, буду рад, если добавите в комментарии.
Статья обновлена: 19.09.2020 11:07
Если
Помог ли этот веб-сайт сэкономить вам время и деньги? Помог ли он решить проблему? Вы готовы предоставить символическое вознаграждениеДальнейшее развитие? Выберите форму вознаграждения, которая вам подходит.
Павел Ласбк — автор сайта
Microsoft Office (Word, Excel, Google Sheets , PowerPoint) Я использую с 2000 года (с 2004 года в этом домене) — В 2017 году я получил престижную награду MVP от Microsoft (пока я единственный из IR в категории Excel). Я также делюсь своими знаниями и опытом онлайн в видеороликах для SEDUO . В компаниях преподаю и консультирую , работаю в МУНИ. Я создаю этот сайт более 15 лет. Существует более 1000 бесплатных инструкций, советов и рекомендаций, включая более 250 различных шаблонов и рабочих книг.
Комментарии
Мартин
Добавлено: 24.07.13 07:31
обновление экрана мне очень помогло. Поскольку я только начинаю, мы пока сделали примитивный макрос примерно на 90 листов. Время все равно было рассчитано, скажем, 10 секунд, что ничего, но с выключенным обновлением экрана оно рассчитывается в течение 2 секунд.
Павел Ласбак
Добавлено: 24.07.13 11:11
Мартину: Я помню чувство, когда впервые использовал и ускорил макрос. Я рад, что помог.
Джордж
Добавлено: 20.08.13 12:58
Заполненная ячейка: можно ускорить выполнение, используя with: With Workbooks(«muj-sesit.xlsm»).Sheets(«List22»).Range(«A1») For i = 1 to 99 .offset (i-1) = «быстрый код» Next i End With Который быстрее, даже если мы избегаем переменных типа Variant, которые всегда есть, если мы не определяем их размер перед заголовком функции, например: Sub VyplnBunky() Dim i как целое число. куда Конец подписки
Джордж
Добавлено: 20.08.13 13:16
Извините, я попробую пример еще раз (см. предыдущий комментарий), используя код, который это сделает: With Workbooks(«muj-sesit.xlsm»).Sheets(«List22»).Range(«A1 «) Для i = от 1 до 99 .offset(i-1) = «быстрый код» Next i End With
Радек
Добавлено: 20.04.1422:31
не всем повезло иметь MS Office, работающий прямо на их компьютере. На работе я захожу в офис через какой-то сервер и скорость линии вполне схожа с приложением, что дало мне возможность заметить некоторое ускорение. 1) Реальное ускорение: Если вам не нужно взаимодействовать с пользователем напрямую и расчеты в книге можно отложить на потом, выполняйте их в событии книги Private Sub Workbook_Open() Не знаю почему, но когда документ открывается, сотни тысяч операций выполняются за один раз, а затем выполняются мгновенно. Подходит для больших файлов БД 2) виртуальное ускорение если не нужно взаимодействовать с пользователем напрямую, а нужно что-то пришить прочесать перед закрытием, поставить в событии листа Private Sub Workbook_BeforeClose(Cancel As Boolean) Расчет операции порядка десятков тысяч вас не будут беспокоить. Курсор какое-то время мелькает над приложением Excel, но пользователь уже сохранил его и может сосредоточиться на других вещах. Я вообще не замечаю этого в замедленной съемке. 3) виртуальное ускорение необходимо для реакции на поведение пользователя и т.д. откройте другие книги и скопируйте большой объем данных, скопируйте их куда-нибудь. Такую долгую операцию лучше запускать непосредственно пользователем через commandButton и если это не занимает огромное количество времени отключить Application.ScreenUpdating = True Пользователь в порядке. У него такое ощущение, что он нажал на что-то большое, потому что теперь перед его глазами открываются новые тетради, удаляются ненужные столбцы, копирование, расчеты, раскрашивание, . Ну а когда он привыкнет, вводишь Application.ScreenUpdating=False и снова возвращаешься.Кому-то виртуальные ускорения неважны, а о пользователе надо постоянно думать. Приложение работает настолько быстро, насколько оно есть на самом деле
Владимин
Добавлено: 28.10.14 16:26
У меня выключен экран с помощью Application.DisplayAlerts = False, но здесьПри сохранении или открытии большого файла главный экран исчезает и остается белым. После завершения макроса экран снова запустится правильно. Я не могу от этого избавиться. Есть ли у вас какие-либо рекомендации? Спасибо Владимиру
Йирка Ви
Добавлено: 11.12.14 08:15
Когда я обрабатываю данные с действительно БОЛЬШОЙ таблицей (около 200 тысяч строк), я сначала преувеличиваю ее размер, а затем использую только индексы. Это сумасшедшая скорость! Например DIM dtab как Variant prime = Cells(987654, 1).End(xlUp).Row dtab = Range(«a1:z» & prime) . а потом везде вместо Range(«a1») используем dtab(1,1).. и т.д. world mr.
Джон
Добавлено: 26.11.15 15:27
Большое спасибо за эту статью. Это сократило время обработки до 1/3.
