Excel ускорить работу макроса vba excel
Ускорение работы макроса
Добрый день всем. Прошу помочь с нижеприведенным макросом на предмет ускорения его работы.Расчет.
Ускорение макроса удаления ячеек
Добрый вечер! Очень нужна помощь. Подскажите,как ускорить макрос, который удаляет все неокрашенные.
Ускорение работы макроса (преобразование данных к нужному формату)
Добрый день! в программировании на VBA недавно, а поэтому подозреваю, что подход к написанию кода.
Запуск макроса из макроса ИЛИ повторение одного и того же кода
Excel Есть код в 10 строк (назовем его "блок"), который повторяется 5 раз в модуле (макросе).
Решение
Кроме того, суть в том, чтобы сам по себе алгоритм был во всех смыслах оптимальным. Поменьше обращений к объектам, значение ячейки внутри цикла надо читать из переменной (String, Long, Double, Valiant), а саму переменную инициализировать из объекта до цикла. Обращение к памяти быстрей, чем к объектам. Не забываем не засирать память - освобождать объекты (Set MyObj = Nothing). Если одна ячейка читается или пишется больше 1-2 раз, ее на время заменять переменной (или элементом массива, что почти одно и то же в данном контексте). Поменьше переменных Variant - красивый код требует строгой типизации. Короче много рекомендаций тебе не дашь, так как не вижу ни одной строчки кода и мало знаю о твоих алгоритмах. Да, не злоупотребляй сложной обработкой длинных строк, поиск и сравнение строк заменить если возможно сравнением значений типа Long. Тип Ineger - не айс, меняй везде смело на Long - компьютеры-то уже давно не 16-битные.Решение
1. Используйте оператор With , если устанавливаются несколько свойств или вызываются несколько методов объекта..2. Храните код в надстройках с расширением .XLA . Дело в том, что код в надстройках полностью компилирован и сразу запускается на исполнение. А в обычном файле .XLS код вначале компилируется а лишь затем запускаеся на исполнение. При числе строк кода - 5500шт. выгода будет наверняка..
3.Где возможно, применяйте формулы массива.
4.Где возможно, используйте встроенные функции рабочего листа Эксель.
5.Объявляйте объектные переменные соответствующего типа объекта.. Если таблица Excel содержит данные, не имеет смысла копировать их в массив, а затем сортировать в массиве. Поскольку Excel выполняет сортировку очень просто и быстро даже для больших объемов данных, целесообразно воспользоваться этим до перемещения данных в массив VBA. Прежде чем использовать VBA для сортировки данных, убедитесь в том что , данную задачу нельзя решить встроенными средствами приложения - такой способ почти всегда предпочтительней..
Количество точек в программном коде старайтесь уменьшить всеми доступными способами, т.к. каждое обращение к точке увеличивает время выполнения макроса. Используйте свойство по умолчанию. Например записать в ячейку можно так:
Range("A10").Value = 5
но свойство Value в данном случае является по умолчанию т.е. его можно не писать:
Range("A10") = 5 1. Используйте оператор With , если устанавливаются несколько свойств или вызываются несколько методов объекта..
А это разве не просто упрощение написания? Или он то что в with временно загоняет в память?
2. Храните код в надстройках с расширением .XLA . Дело в том, что код в надстройках полностью компилирован и сразу запускается на исполнение. А в обычном файле .XLS код вначале компилируется а лишь затем запускаеся на исполнение. При числе строк кода - 5500шт. выгода будет наверняка..Не подскажешь как это сделать?
Я вообще не использую формул нигде.
И вот еще вопрос, стоит ли создавать переменные для объектов? Я как понимаю, если используем несколько раз - то лучше создавать? Опять же вопрос с with - если обращаюсь к объекту один раз в with, но внутри with несколько действий, то как лучше?
С таблицей в Excel можно работать средствами SQL. Часто для больших таблиц это дает выигрыш в сравнении с "рукописными" алгоритмами обработки данных на VBA. Используются ODBC-драйверы Excel. К использованию SQL по быстродействию условно можно приравнять скорость встроенной сортировки Excel, как частный случай.
Использование оператора With я понимаю как неявный способ задания объектной переменной, к которой затем идет обращение.
Ещё простейший пример оптимизации быстродействия, который хорошо работает в VBA, поскольку в VBA нет пошагового разбора условий как в языке C. Вместо выражений типа:
Так, если логическое выражение "Выражение1" будет ложным, то "Выражение2" не вычисляется, вторая проверка не производится. В первом примере вычисляются оба выражения и сравниваются, это просто лишняя работа, особенное если в выражениях работаем с объектами.
Написал макрос для excel (на VBA) который обрабатывает данные и заносит их в таблицу (на отдельном листе).
Таблица довольно большая, поэтому макрос работает относительно значительное время. Мне почему-то кажется, что это связано с тем, что после каждой записи в ячейку excel выполняет какие-то действия по перерисовке листа и именно это значительно снижает скорость работы макроса.
Действительно ли это так? И если да, то как лучше всего оптимизировать процесс? Я думал про то, чтобы блокировать лист (перерисовка) до тех пор, пока все данные не будут внести, и лишь после этого разблокировать лист.
Прав ли я? И если да, то как это лучше реализовать?
Очень полезную ссылку дал 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, чтобы посмотреть результаты
Так уж сложилось, что на сегодняшний день много кому приходится работать(писать макросы) на VBA в Excel. Некоторые макросы содержат сотни строк кода, которые приходится выполнять каждый день (неделю, месяц, квартал и так далее) и, при этом, они занимают изрядное количество времени. Вроде бы и и процесс автоматизирован и человеческого вмешательства не нужно, но время, занимаемое выполнением макроса, может охватывать десятки минут, а то и несколько часов. Время, как говориться, — деньги и в этом посте я постараюсь значительно ускорить время выполнения Вашего макроса и, возможно, это положительно скажется на ваших делах, а в итоге и деньгах.
Ускоряем работу макроса
Итак, к сути… Для того что бы реально ускорить работу VBA в Ecxel нужно понимать, что обращение к ячейке на листе — занимает значительно время. Если Вы хотите записать в ячейку одно значение, то это не займет значительного времени, но если Вам потребуется записать(прочитать, обратиться) к тысячам ячеек, то это потребует гораздо большего времени. Что же делать в таких случаях? На помощь приходят массивы. Массивы хранятся в памяти, а операции в памяти VBA выполняет в сотни, а то и в тысячи раз быстрее. Поэтому, если у Вас в данных тысячи, сотни тысяч значений, то время выполнения макроса может занимать от нескольких минут до нескольких часов, а если эти данные перенести в массив, то выполнение макроса может сократиться до нескольких секунд (минут).
Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.
Пример
Предположим, что у нас есть данные на “Лист1” (“Sheet1”). Данные содержаться в 50 колонках (колонки содержат названия) и 10 000 строк. К примеру, нам нужно в последнюю колонку внести значение, которое равно значению во второй колонке, деленное на значение в третьей колонке (начиная со 2-й строки, так как первая содержит заглавие). Потом мы возьмем первые 10 колонок и скопируем их на “Лист2” (“Sheet2”), для дальнейшей обработки (для других потребностей). Пусть пример и банальный, но, как мне кажется, он может отобразить всю суть данного поста.
В данном примере массив заполняется указанным диапазоном. Если у нас будет явно заданный двумерный массив, то скопировать его значение на лист можно таким образом:
Заключение
Большинство операций над данными можно выполнять в массиве, при этом, отображать на лист только результат. Иногда целесообразным бывает показать результат на лист, потом выполнить некоторые действия (например, сортировку) и снова загрузить данные в массив.
Для меня было большой неожиданностью ускорения работы макроса за счет массивов, так как данные на листах, на самом деле, итак представляют собой двумерный массив. Но, оказывается, обращение к памяти происходит гораздо быстрей, чем к ячейкам на листе.
Так уж сложилось, что на сегодняшний день много кому приходится работать(писать макросы) на VBA в Excel. Некоторые макросы содержат сотни строк кода, которые приходится выполнять каждый день (неделю, месяц, квартал и так далее) и, при этом, они занимают изрядное количество времени. Вроде бы и и процесс автоматизирован и человеческого вмешательства не нужно, но время, занимаемое выполнением макроса, может охватывать десятки минут, а то и несколько часов. Время, как говориться, — деньги и в этом посте я постараюсь значительно ускорить время выполнения Вашего макроса и, возможно, это положительно скажется на ваших делах, а в итоге и деньгах.
Ускоряем работу макроса
Итак, к сути… Для того что бы реально ускорить работу VBA в Ecxel нужно понимать, что обращение к ячейке на листе — занимает значительно время. Если Вы хотите записать в ячейку одно значение, то это не займет значительного времени, но если Вам потребуется записать(прочитать, обратиться) к тысячам ячеек, то это потребует гораздо большего времени. Что же делать в таких случаях? На помощь приходят массивы. Массивы хранятся в памяти, а операции в памяти VBA выполняет в сотни, а то и в тысячи раз быстрее. Поэтому, если у Вас в данных тысячи, сотни тысяч значений, то время выполнения макроса может занимать от нескольких минут до нескольких часов, а если эти данные перенести в массив, то выполнение макроса может сократиться до нескольких секунд (минут).
Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.
Пример
Предположим, что у нас есть данные на “Лист1” (“Sheet1”). Данные содержаться в 50 колонках (колонки содержат названия) и 10 000 строк. К примеру, нам нужно в последнюю колонку внести значение, которое равно значению во второй колонке, деленное на значение в третьей колонке (начиная со 2-й строки, так как первая содержит заглавие). Потом мы возьмем первые 10 колонок и скопируем их на “Лист2” (“Sheet2”), для дальнейшей обработки (для других потребностей). Пусть пример и банальный, но, как мне кажется, он может отобразить всю суть данного поста.
В данном примере массив заполняется указанным диапазоном. Если у нас будет явно заданный двумерный массив, то скопировать его значение на лист можно таким образом:
Заключение
Большинство операций над данными можно выполнять в массиве, при этом, отображать на лист только результат. Иногда целесообразным бывает показать результат на лист, потом выполнить некоторые действия (например, сортировку) и снова загрузить данные в массив.
Для меня было большой неожиданностью ускорения работы макроса за счет массивов, так как данные на листах, на самом деле, итак представляют собой двумерный массив. Но, оказывается, обращение к памяти происходит гораздо быстрей, чем к ячейкам на листе.
Читайте также: