Как ускорить работу макроса vba excel
Ускоряем работу VBA в Excel
Предисловие
Так уж сложилось, что на сегодняшний день много кому приходится работать(писать макросы) на VBA в Excel. Некоторые макросы содержат сотни строк кода, которые приходится выполнять каждый день (неделю, месяц, квартал и так далее) и, при этом, они занимают изрядное количество времени. Вроде бы и и процесс автоматизирован и человеческого вмешательства не нужно, но время, занимаемое выполнением макроса, может охватывать десятки минут, а то и несколько часов. Время, как говориться, — деньги и в этом посте я постараюсь значительно ускорить время выполнения Вашего макроса и, возможно, это положительно скажется на ваших делах, а в итоге и деньгах.
Ускоряем работу макроса
Итак, к сути… Для того что бы реально ускорить работу VBA в Ecxel нужно понимать, что обращение к ячейке на листе — занимает значительно время. Если Вы хотите записать в ячейку одно значение, то это не займет значительного времени, но если Вам потребуется записать(прочитать, обратиться) к тысячам ячеек, то это потребует гораздо большего времени. Что же делать в таких случаях? На помощь приходят массивы. Массивы хранятся в памяти, а операции в памяти VBA выполняет в сотни, а то и в тысячи раз быстрее. Поэтому, если у Вас в данных тысячи, сотни тысяч значений, то время выполнения макроса может занимать от нескольких минут до нескольких часов, а если эти данные перенести в массив, то выполнение макроса может сократиться до нескольких секунд (минут).
Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.
Пример
Предположим, что у нас есть данные на “Лист1” (“Sheet1”). Данные содержаться в 50 колонках (колонки содержат названия) и 10 000 строк. К примеру, нам нужно в последнюю колонку внести значение, которое равно значению во второй колонке, деленное на значение в третьей колонке (начиная со 2-й строки, так как первая содержит заглавие). Потом мы возьмем первые 10 колонок и скопируем их на “Лист2” (“Sheet2”), для дальнейшей обработки (для других потребностей). Пусть пример и банальный, но, как мне кажется, он может отобразить всю суть данного поста.
В данном примере массив заполняется указанным диапазоном. Если у нас будет явно заданный двумерный массив, то скопировать его значение на лист можно таким образом:
Заключение
Большинство операций над данными можно выполнять в массиве, при этом, отображать на лист только результат. Иногда целесообразным бывает показать результат на лист, потом выполнить некоторые действия (например, сортировку) и снова загрузить данные в массив.
Для меня было большой неожиданностью ускорения работы макроса за счет массивов, так как данные на листах, на самом деле, итак представляют собой двумерный массив. Но, оказывается, обращение к памяти происходит гораздо быстрей, чем к ячейкам на листе.
Источник: excellentricks.ru
Как ускорить и оптимизировать код VBA
- Если в коде есть много всяких Activate и Select , тем более в циклах – следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate – зачем нужны и нужны ли?
- Обязательно на время выполнения кода отключить:
- автоматический пересчет формул . Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода – это может дико тормозить код, если формул много:
если печать производится внутри кода, то эту строку желательно вставить сразу после строки, выводящей лист на печать(при условии, что печать не происходит в цикле. В этом случае – по завершению цикла печати).
Я советую всегда отключать разбиение на страницы, т.к. это может тормозить весьма значительно, т.к. заставляет при любом изменении на листах обращаться к принтеру и переопределять кол-во и размер печатных страниц. А это порой очень не быстро.
На всякий случай можно отключить отображение информации в строке статуса Excel (в каких случаях там вообще отображается информация и зачем можно узнать в статье: Отобразить процесс выполнения). Хоть это и не сильно поедает ресурсы – иногда может все же ускорить работу кода:
Главное, что следует помнить – все эти свойства необходимо включить обратно после работы кода . Иначе могут быть проблемы с работой внутри 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
Разрывы печатных страниц можно не возвращать – они тормозят работу в любом случае.
Следует избегать циклов, вроде Do While для поиска последней ячейки . Часто такую ошибку совершают начинающие. Куда эффективнее и быстрее вычислять последнюю ячейку на всем листе или в конкретном столбце без этого тормозного цикла Do While. Я обычно использую
другие варианты определения последней ячейки я детально описывал в статье: Как определить последнюю ячейку на листе через 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-битных системах
If s <> s1 Then будет медленнее, чем
If StrComp(s, s1, vbBinaryCompare) = 0
и тем более, если при сравнении необходимо не учитывать регистр:
If LCase(s) <> LCase(s1) Then будет медленнее, чем
If StrComp(s, s1, vbTextCompare) = 0
Dim rRange as Object, wsSh as Object
будет медленнее работать, чем:
Dim rRange as Range, wsSh as Worksheet
Причина в том, что при объявлении As Object мы не даем VBA практически никакой информации о типе данных, кроме того, что это какой-то объект. И VBA приходится “на лету” внутри кода при каждом обращении к такой переменной определять её конкретный тип(Range, Worksheet, Workbook, Chart и т.д.). Что опять же занимает время.
Если работаете с массивами, то можно при объявлении указать это явно:
Такая инициализация происходит быстрее.
А еще лучше будет при этом еще и тип данных сразу присвоить:
Dim arr() as string, arr2() as long
но это только если есть уверенность в том, что в массив будут заноситься строго указанные типы данных
Конечно, это не все приемы и решения для оптимизации. Но на первых парах должно хватить. Плюс, всегда следует исходить из здравого смысла . Например, если код выполняется за 2 секунды, то вероятно нет смысла его дальше оптимизировать. Конечно, если этот код не из тех, которые просто изменяют значение одной-двух ячеек.
Статья помогла? Поделись ссылкой с друзьями!
Источник: www.excel-vba.ru
Ускоряем работу VBA в Excel
Предисловие
Перед началом работы
Перед тем, как перейти прямо к сути, я хотел бы обратить внимание на пост: Несколько советов по работе с VBA в Excel. В частности, в блоке “Ускорение работы макросов” есть полезные примеры кода, которые стоит использовать вместе с моими советами по ускорению работы, для достижения максимального результата.
Ускоряем работу макроса
Итак, к сути… Для того что бы реально ускорить работу VBA в Ecxel нужно понимать, что обращение к ячейке на листе — занимает значительно время. Если Вы хотите записать в ячейку одно значение, то это не займет значительного времени, но если Вам потребуется записать(прочитать, обратиться) к тысячам ячеек, то это потребует гораздо большего времени. Что же делать в таких случаях? На помощь приходят массивы. Массивы хранятся в памяти, а операции в памяти VBA выполняет в сотни, а то и в тысячи раз быстрее. Поэтому, если у Вас в данных тысячи, сотни тысяч значений, то время выполнения макроса может занимать от нескольких минут до нескольких часов, а если эти данные перенести в массив, то выполнение макроса может сократиться до нескольких секунд (минут).
Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.
Пример
Предположим, что у нас есть данные на “Лист1” (“Sheet1”). Данные содержаться в 50 колонках (колонки содержат названия) и 10 000 строк. К примеру, нам нужно в последнюю колонку внести значение, которое равно значению во второй колонке, деленное на значение в третьей колонке (начиная со 2-й строки, так как первая содержит заглавие). Потом мы возьмем первые 10 колонок и скопируем их на “Лист2” (“Sheet2”), для дальнейшей обработки (для других потребностей). Пусть пример и банальный, но, как мне кажется, он может отобразить всю суть данного поста.
В данном примере массив заполняется указанным диапазоном. Если у нас будет явно заданный двумерный массив, то скопировать его значение на лист можно таким образом:
Заключение
Большинство операций над данными можно выполнять в массиве, при этом, отображать на лист только результат. Иногда целесообразным бывает показать результат на лист, потом выполнить некоторые действия (например, сортировку) и снова загрузить данные в массив.
Для меня было большой неожиданностью ускорения работы макроса за счет массивов, так как данные на листах, на самом деле, итак представляют собой двумерный массив. Но, оказывается, обращение к памяти происходит гораздо быстрей, чем к ячейкам на листе.
В дальнейшем я планирую написать советы (примеры) по быстрому поиску данных на листе, но это уже будет другой пост. Если будут вопросы, комментарии, пожалуйста, пишите.
Источник: habr.com
ускорение заполнения ячеек в excel (используя VBA)
Написал макрос для excel (на VBA) который обрабатывает данные и заносит их в таблицу (на отдельном листе).
Таблица довольно большая, поэтому макрос работает относительно значительное время. Мне почему-то кажется, что это связано с тем, что после каждой записи в ячейку excel выполняет какие-то действия по перерисовке листа и именно это значительно снижает скорость работы макроса.
Действительно ли это так? И если да, то как лучше всего оптимизировать процесс? Я думал про то, чтобы блокировать лист (перерисовка) до тех пор, пока все данные не будут внести, и лишь после этого разблокировать лист.
Прав ли я? И если да, то как это лучше реализовать?
1 ответ 1
Очень полезную ссылку дал slippyk, рекомендую ознакомиться обязательно.
К тому, что там написано, от себя добавлю следующее:
- Полезно для производительности не только читать/записывать ячейки с помощью двумерного массива. Более универсальный совет: избегать, по возможности, любых обращений в цикле к объектам библиотек VBA. Worksheet , Range , Borders и др. – это всё COM-объекты, а каждый вызов их методов/свойств – дополнительный оверхед.
Соответственно, если записываете ячейки массивом за один вызов .Range(. ).Value = array , а затем хотите отформатировать таблицу, установив форматы чисел, границы ячеек и др., то обрабатывайте ячейки тоже диапазонами. Если форматирование для разных колонок таблицы должно различаться, обрабатывайте каждую колонку как один диапазон. Но ни в коем случае в больших таблицах не обрабатывайте ячейки по одной.
- Отключив обновление экрана, обязательно гарантируйте восстановление режима работы Excel, чтобы ошибка при выполнении макроса не привела пользователя к пустому окну приложения. Он будет закрывать Excel через диспетчер задач и нехорошо выражаться в адрес программиста 🙂 Используйте операторы “On Error”.
Ниже прилагаю пример кода, демонстрирующий скорость заполнения листа миллионом значений (таблица 10000 строк на 100 столбцов). Чтобы запустить код:
создайте книгу Excel и два листа в ней
откройте окно редактора VBA
переименуйте листы в “Sheet_1” и “Sheet_2”
на листе Sheet_1 можно ввести несколько числовых значений (вводя строку или дату, сможете увидеть впоследствии, как отреагирует макрос на ошибку)
создайте модуль и вставьте в него код макроса
выполните макрос (у меня примерно 1.5 сек.) и перейдите в окно Excel, чтобы посмотреть результаты
Источник: ru.stackoverflow.com
Ускорение макроса
Ускорение работы макроса
Добрый день всем. Прошу помочь с нижеприведенным макросом на предмет ускорения его работы.Расчет.
Ускорение макроса удаления ячеек
Добрый вечер! Очень нужна помощь. Подскажите,как ускорить макрос, который удаляет все неокрашенные.
Ускорение работы макроса (преобразование данных к нужному формату)
Добрый день! в программировании на VBA недавно, а поэтому подозреваю, что подход к написанию кода.
Запуск макроса из макроса ИЛИ повторение одного и того же кода
Excel Есть код в 10 строк (назовем его “блок”), который повторяется 5 раз в модуле (макросе).
