Макрос Excel для ускорения работы vba

Обновлено: 21.11.2024

В этом руководстве рассматриваются способы ускорения макросов VBA и другие передовые методы работы с VBA.

Настройки для ускорения кода VBA

Ниже вы найдете несколько советов по ускорению кода VBA. Советы произвольно упорядочены по важности.

Самый простой способ повысить скорость кода VBA — отключить обновление экрана и автоматические вычисления. Эти настройки должны быть отключены во всех больших процедурах.

Отключить обновление экрана

По умолчанию Excel отображает изменения в книгах в режиме реального времени по мере выполнения кода VBA. Это вызывает значительное замедление скорости обработки, поскольку Excel чаще всего интерпретирует и отображает изменения для каждой строки кода.

Чтобы отключить обновление экрана:

В конце макроса вы должны снова включить обновление экрана:

Во время работы вашего кода может потребоваться «обновить» экран. Команды «обновить» нет. Вместо этого вам нужно снова включить обновление экрана и снова отключить его.

Установить расчеты вручную

Всякий раз, когда значение ячейки изменяется, Excel должен следовать «дереву вычислений», чтобы пересчитать все зависимые ячейки. Кроме того, всякий раз, когда формула изменяется, Excel необходимо будет обновить «дерево вычислений» в дополнение к пересчету всех зависимых ячеек. В зависимости от размера вашей книги эти пересчеты могут привести к тому, что ваши макросы будут работать неоправданно медленно.

Чтобы установить расчеты вручную:

Чтобы вручную пересчитать всю книгу:

Обратите внимание, что вы также можете рассчитать только лист, диапазон или отдельную ячейку, если это необходимо для повышения скорости.

Чтобы восстановить автоматические расчеты (в конце процедуры):

Важно! Это настройка Excel. Если вы не перенастроите расчеты на автоматический режим, ваша рабочая книга не будет пересчитываться до тех пор, пока вы не скажете об этом.

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

Отключить события

События – это триггеры, запускающие специальные процедуры обработки событий. Примеры включают: изменение любой ячейки на листе, активацию листа, открытие книги, перед сохранением книги и т. д.

Отключение событий может привести к незначительному повышению скорости при выполнении любых макросов, но повышение скорости может быть намного больше, если в вашей книге используются события. А в некоторых случаях необходимо отключить события, чтобы избежать создания бесконечных циклов.

Это предотвратит мерцание и обновление экрана во время выполнения или запуска макросов, а также значительно ускорит ваш код.

Совет. Используйте Application.ScreenUpdating = False в начале кода и Application.ScreenUpdating = True перед окончанием кода, чтобы контролировать, когда останавливать и запускать обновление экрана.

2. Отключить «Автоматические расчеты»

Это предотвращает вычисления во время выполнения или запуска макроса, поэтому вы можете дождаться завершения набора действий и указать, что вычисления должны обновляться в конце один раз, а не после каждого обновления.

Совет: используйте приложение. Calculation = xlCalculationManual в начале вашего кода и приложения. Calculation = xlCalculationAutomatic перед окончанием кода.

3. Отключить события

Отключение событий Excel поможет вам предотвратить или остановить бесконечные циклы при выполнении или запуске макросов, особенно если у вас есть события рабочего листа или рабочей книги.

Совет: используйте приложение. EnableEvents = False в начале вашего кода и приложения. EnableEvents = True перед окончанием кода.

4. Используйте оператор «С»

Используйте оператор 'WITH' при работе с объектами в макросе. Если вы используете несколько операторов с одним и тем же объектом, используйте «WITH», а не ссылайтесь на них по отдельности.

5. Редактировать записанные макросы

Хотя записанный макрос может дать полезную информацию о синтаксисе и ссылках Excel VBA, всегда лучше избегать использования всего кода из записанного макроса. Это может отрицательно сказаться на производительности, поэтому всегда проверяйте макрос и редактируйте код, чтобы убедиться, что остались только основные исполняемые строки.
Пример. Измените цвет ячейки («C2») на желтый и полужирный шрифт.

Записанный макрос
Если вы записываете макрос, код может выглядеть так:

Записанный макрос:
Записанный макрос также может быть записан следующим образом:

6. Используйте vbNullString вместо «»

VbNullString – это константа. Он обозначает нулевую строку. Он занимает меньше памяти, чем строка нулевой длины (обозначается ""), и быстрее обрабатывается и назначается.

немного эффективнее

7. Уменьшите количество строк, используя запятую (,) или двоеточие (:)

Некоторые операторы VBA могут быть записаны в одной строке исполняемого кода, а не в нескольких строках.

Пример: мы можем объявлять переменные в одной строке, разделяя их запятой:

Пример: используйте двоеточие (:), чтобы написать несколько операторов в одной строке, например, чтобы присвоить значения переменной:

Вместо следующего примера:

8. Объявить переменные с наименьшим допустимым размером типа данных

Всегда убедитесь, что вы объявляете свои переменные и используете наименьший возможный тип данных, особенно для чисел.

Проверьте диапазон данных на предмет емкости значений, которые необходимо сохранить:

< tr> < td data-th=" Размер хранилища">8 байт

Тип данных	Размер хранилища	Диапазон
Байт	1 байт	0–255
Boolean	2 байта	True или False
Целое число	2 байта	-32 768 до 32 767
Long (длинное целое число)	4 байта	-2 147 483 648 до 2 147 483 647
Одиночный ( с плавающей запятой одинарной точности)	4 байта	-3,402823E38 до -1,401298E-45 для отрицательных значений ; от 1.401298E-45 до 3.402823E38 для положительных значений
Double (двойная точность с плавающей запятой)< /td>	8 байт	-1,79769313486231E308 до -4,94065645841247E-324 для отрицательных значений; от 4,94065645841247E-324 до 1,79769313486232E308 для положительных значений
Валюта (масштабированное целое число)	-922 337 203 685 477,5808 до 922 337 203 685 477,5807
Десятичный	14 байт	+/-79 228 162 514 264 337 593 543 950 335 без десятичной точки; +/-7,9228162514264337593543950335 с 28 знаками справа от запятой
Дата	8 байт	1 января 100 г. по 31 декабря 9999 г.
Объект	4 байта	Любая ссылка на объект
String (переменной длины)	10 байт + длина строки	от 0 до приблизительно 2 миллиардов
String (фиксированной длины)	Длина строки	от 1 до приблизительно 65 400
Вариант < br />(с числами)	16 байт	Любое числовое значение до диапазона Double
Вариант (с символами)	22 байта + длина строки (24 байта в 64-битных системах)	Тот же диапазон, что и для строки переменной длины< /td>
Определяемый пользователем (используя Тип)	Количество, требуемое elements	Диапазон каждого элемента совпадает с диапазоном его типа данных.

Избегайте вариантов. Это простая вещь, но ее часто упускают из виду. Все переменные, параметры и функции должны иметь определенный тип данных. Если данные представляют собой строку, то тип данных должен быть определен как строка. Если вы не указываете тип данных, вы используете вариант. Вариантный тип данных имеет свое применение, но не в обработке строк. Вариант означает потерю производительности в большинстве случаев.

Поэтому добавьте операторы Option Explicit в каждый модуль и затемните все переменные с подходящим типом данных. Проверьте свои функции и убедитесь, что они определяют тип возвращаемых данных.

Следующие функции далеко не идеальны, если вы используете их для строк, поскольку они применяются к вариантам и возвращают варианты. Эти функции подходят для обработки вариантов, но бесполезны при работе со строками.

Если вы имеете дело со строками текста, забудьте о вариантах. Вместо этого используйте строковые версии:

9. Используйте лучший подход к копированию и вставке

Существуют разные подходы к копированию данных в VBA. Наиболее эффективным является прямое копирование без использования буфера обмена Windows.
Пример:

Приведенный выше пример гораздо более эффективен, чем описанный ниже метод «буфер обмена», который отправляет скопированные элементы в буфер обмена для последующей вставки:

10.Добавляйте дополнительные справочные библиотеки только при необходимости

Если вы используете объекты в других приложениях как часть своего приложения Visual Basic, вы можете установить ссылку на библиотеки объектов этих приложений. Прежде чем вы сможете это сделать, вы должны проверить, предоставляет ли приложение библиотеку объектов.

Чтобы узнать, предоставляет ли приложение библиотеку объектов

В меню "Инструменты" выберите "Ссылки", чтобы открыть диалоговое окно "Ссылки".
В диалоговом окне "Ссылки" отображаются все библиотеки объектов, зарегистрированные в операционной системе. Прокрутите список до приложения, на библиотеку объектов которого вы хотите сослаться.
Если приложения нет в списке, вы можете использовать кнопку Обзор для поиска библиотек объектов (*.olb и *.tlb) или исполняемых файлов (*.exe и *.dll в Windows).
Ссылки, для которых установлены флажки, используются вашим проектом; те, которые не отмечены флажком, не используются, но могут быть добавлены.

Рассмотрите возможность позднего связывания вместо раннего связывания в качестве альтернативы.

Проверьте наш календарь запланированных учебных курсов VBA в Глазго и Эдинбурге или свяжитесь с нами, чтобы более подробно обсудить ваши конкретные требования к обучению

У меня есть файл excel с кодом VBA (не написанным мной). Как работает этот код, пользователь вводит 6-значное число в пользовательскую форму, затем VBA проверяет другой лист, и если это 6-значное число присутствует на рабочий лист. Если это так, он меняет этап, но если нет, он добавляет это 6-значное число на лист

Раньше он работал отлично, но теперь, поскольку количество строк в файле excel выросло почти до 6000 строк, этот код стал очень медленным, для обновления листа требуется до 20 секунд

Может ли кто-нибудь помочь мне ускорить этот код или предложить другой способ его достижения

Код ниже

Заранее спасибо

Значит, в PSDataStageCals всего 6000 строк? Что такое PSDUDateRow? Одно число или целая строка, состоящая из нескольких столбцов? Есть ли в файле формулы, ссылающиеся на PSDataStageCals? (Если это так, эти формулы будут пересчитаны, потому что вы добавляете новую ListRow, и если они требуют больших вычислительных ресурсов или находятся в начале длинных цепочек вычислений, это может объяснить большую часть вашей задержки). Есть ли в вашем файле изменчивые функции, такие как OFFSET, NOW, TODAY или INDIRECT?

5 ответов 5

Вы можете отключить обновление экрана, автоматические вычисления и т. п.

В общем, есть два способа ускорить код VBA:

Напишите хороший код, который не использует Select , Activate , ActiveCell , Selection и т. д. Как избежать использования Select в Excel VBA

Обратитесь к этим процедурам в начале и в конце кода:

Я думаю, что Calculation всегда должен быть установлен на xlAutomatic , поскольку, если вам нужно xlCalculationManual для ускорения, было бы неплохо провести рефакторинг кода. Кроме того, ручной расчет слишком рискован.
То же самое относится и к Date1904 — для него всегда установлено значение False .

Помимо настроек, предложенных Storax, ваш код работает медленно, потому что вы передаете данные ячейка за ячейкой, а не разделяете Excel/VBA.

Кроме того, вы можете значительно ускорить работу функции ПОИСКПОЗ, используя ее двоичную версию. Прочтите http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/, а также постарайтесь свести к минимуму количество отдельных переводов, которые вы делаете через Разделение Excel/VBA выполняется либо путем выполнения поиска полностью на листе Excel (используя VBA для записи формулы на листе и выполнения ее там), либо путем переноса всех данных в VBA за один раз с использованием массивов вариантов, выполнения вашей логики и затем сбросив его обратно за один раз. Google «Эффективный способ передачи данных между Excel и VBA» или что-то подобное. Также ознакомьтесь со статьями Чарльза Уильямса на эту тему.

При обработке больших фрагментов данных в Excel, требующих частых ссылок на ячейки, всегда гораздо быстрее скопировать данные в массив (при необходимости скопировать весь рабочий лист), обработать данные в массиве, а затем записать обратно. на рабочий лист, если это необходимо. Копирование данных из рабочего листа в массив — это однострочная команда, которая выполняется очень и очень быстро. То же самое с массивом на лист. Условно говоря, обращение к ячейкам занимает очень много времени по сравнению с обращением к элементам массива.

Не тот ответ, который вы ищете? Просмотрите другие вопросы с тегом vba excel или задайте свой вопрос.

Связано

Связанные

Горячие вопросы о сети

Чтобы подписаться на этот RSS-канал, скопируйте и вставьте этот URL-адрес в программу для чтения RSS.

В вашем коде может быть процедура, выполнение которой занимает много времени — она может постоянно обращаться к листу или выполнять вычисления, которые, кажется, переводят ваш курсор в состояние вечных песочных часов.Это две основные настройки, которые мы можем добавить к нашему коду, чтобы ускорить код, обе из которых являются частью лучших практик VBA при написании кода.

Отключить обновление экрана

По умолчанию Excel отображает изменения в книгах в режиме реального времени по мере выполнения кода VBA. Это означает, что экран будет обновляться по мере выполнения макроса. Это может значительно замедлить работу вашего кода, особенно если выполняется длинная процедура.

span class = "crayon-i" > Application / span > span class = "crayon-sy" > . /span > span class = "crayon-i" > ScreenUpdating / span > span class = "crayon-h" > / span > = span class = "crayon-h" > / span > span class = "crayon-t" > Ложь/диапазон >

Поэтому ваш макрос должен выглядеть в этом примере ниже:

Отключить автоматический расчет

По мере запуска макроса и изменения значений ячеек Excel пересчитывает все ячейки, зависящие от ячейки, значение которой изменилось. Кроме того, всякий раз, когда формула изменяется, расчеты выполняются снова. Это может сильно замедлить работу вашего макроса.

Чтобы отключить автоматический расчет, добавьте эту строку кода в начало макроса.

span class = "crayon-i" > Application / span > span class = "crayon-sy" > . /span > span class = "crayon-i" > Расчет / span > = span class = "crayon-i" > xlManual / span >

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

span class = "crayon-i" > Application / span > span class = "crayon-sy" > . /span > span class = "crayon-i" > Расчет / span > = span class = "crayon-h" > xlAutomatic

Риски, связанные с изменением этих настроек по умолчанию

По умолчанию в Excel для параметра «Обновление экрана» установлено значение «ИСТИНА», а для параметра «Вычисления» установлено значение «Автоматически». Использование кода для отключения этих настроек в приложении значительно ускорит ваш код. Однако, если мы забудем снова включить эти настройки или если код перестанет работать до того, как дойдет до строк кода, которые сбрасывают эти настройки, вы можете получить не отвечающий экран Excel (поскольку экран не будет обновляться) или расчеты который не будет вычисляться, если вы не сделаете это вручную.

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

В приведенном выше примере мы создали обработчик ошибок под названием EH. Если в коде что-то пойдет не так, макрос перейдет к EH и запустит последние 2 строки кода. Поскольку мы НЕ поместили Exit Sub в макрос над обработчиком ошибок, если макрос работает нормально, он доберется до 2 строк кода и сбросит эти настройки обратно к значениям по умолчанию.

Программирование VBA стало проще

Прекратите искать код VBA в Интернете. Узнайте больше об AutoMacro — конструкторе кода VBA, который позволяет новичкам создавать процедуры с нуля с минимальными знаниями в области кодирования и множеством функций, позволяющих сэкономить время для всех пользователей!

Читайте также: