Ускорение работы макроса vba

Ускорение работы макроса vba

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

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

Действительно ли это так? И если да, то как лучше всего оптимизировать процесс? Я думал про то, чтобы блокировать лист (перерисовка) до тех пор, пока все данные не будут внести, и лишь после этого разблокировать лист.

Прав ли я? И если да, то как это лучше реализовать?

1 ответ 1

Очень полезную ссылку дал slippyk, рекомендую ознакомиться обязательно.

К тому, что там написано, от себя добавлю следующее:

  1. Полезно для производительности не только читать/записывать ячейки с помощью двумерного массива. Более универсальный совет: избегать, по возможности, любых обращений в цикле к объектам библиотек VBA. Worksheet , Range , Borders и др. — это всё COM-объекты, а каждый вызов их методов/свойств — дополнительный оверхед.

Соответственно, если записываете ячейки массивом за один вызов .Range(. ).Value = array , а затем хотите отформатировать таблицу, установив форматы чисел, границы ячеек и др., то обрабатывайте ячейки тоже диапазонами. Если форматирование для разных колонок таблицы должно различаться, обрабатывайте каждую колонку как один диапазон. Но ни в коем случае в больших таблицах не обрабатывайте ячейки по одной.

  1. Отключив обновление экрана, обязательно гарантируйте восстановление режима работы Excel, чтобы ошибка при выполнении макроса не привела пользователя к пустому окну приложения. Он будет закрывать Excel через диспетчер задач и нехорошо выражаться в адрес программиста 🙂 Используйте операторы "On Error".

Ниже прилагаю пример кода, демонстрирующий скорость заполнения листа миллионом значений (таблица 10000 строк на 100 столбцов). Чтобы запустить код:

создайте книгу Excel и два листа в ней

откройте окно редактора VBA

переименуйте листы в "Sheet_1" и "Sheet_2"

на листе Sheet_1 можно ввести несколько числовых значений (вводя строку или дату, сможете увидеть впоследствии, как отреагирует макрос на ошибку)

создайте модуль и вставьте в него код макроса

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

Бывает, что выполнение макросов происходит очень долго, при сложных и долгих математических расчётах или составлении каких-то отчётов и табилц. В ходе выполнения макроса на мониторе происходит мелькание различных окон, открытие и закрытие книг, и прочая светомузыка. Для того чтобы этого не происходило, и чтобы время выполнение нашего макроса сократить раз в 100, можно воспользоваться специальнымикомандами

Просмотр содержимого документа
«Ускоряем работу макроса в Excel»

Ускоряем работу макроса в Excel

Чем больше познаём мы макросы, тем интересней выгледят наши программы. А бывает, что их выполнение происходит очень долго, при сложных и долгих математических расчётах или составлении каких-то отчётов и табилц. В ходе выполнения макроса на мониторе происходит мелькание различных окон, открытие и закрытие книг, и прочая светомузыка. Для того чтобы этого не происходило, и чтобы время выполнение нашего макроса сократить раз в 100, можно воспользоваться командами описанные ниже.

Application.ScreenUpdating — отвечает за обновление экрана и может принимать два значения — False (обновление экрана отключено) и True (обновление экрана включено). В коде это обычно прописывается в том месете, где происходит мелькание различных окон или видно как производится расчёт и происходит заполнение таблицы. Ниже показан пример заполнения ячеек, и данную команду вставили в начало и конец макроса, т.е. сначала отключаем обновление экрана, а потом включаем обновление экрана. При такой записи мы не увидим процесс заполнения ячеек. А вот если убрать эти команды, то мы сможем наблюдать за процессом заполнения этих ячеек.

For a = 1 To 100

For b = 1 To 100

Cells(a, b) = "Пример"

Application.Calculation — отвечает за автоматический расчёт в книге Excel и может принимать два значения — xlCalculationManual(ручной расчёт) и xlCalculationAutomatic (автоматический расчёт — по умолчанию установлен в Excel). Но тут есть одна осторожность, если вы перевели Excel в ручной расчёт, и в макросе произошла ошибка и он так и не выполнился до конца — т.е. не включился автоматический расчёт формул, то все ваши вычисления в дальнейшем будут в пустую. Так как формулы не будут автоматически пересчитываться, Excel превратиться в обычную таблицу. Но данная команда играет одну из основных ролей в быстроте выполнеия макроса. Вообщем лучше сделать код, который исключает ошибки, чтобы макрос полюбому выполнился и Excel перевёлся в автоматический расчёт. Если у Вас имеется большая таблица с многочисленными формулами, и часть вычислений вы производите при помощи макросов, то для быстроты выполнения расчётов разумно в начало и конец кода поместить команду Application.Calculation.

Application.EnableEvents — команда отвечающая за выполнение сторонних событий. Эту команду мы уже затрагивали в этом уроке. И она также может принимать два значения — это False (отключить собтие) и True (включить выполнение промежуточных событий). Но теперь ещё известно, что она играет значительную роль в скорости выполнения некоторых кодов макроса. Пример можно взять из Урока №23.

Читайте также:  Как красиво замазать лицо на фото

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.DisplayPageBreaks — отображение границ листа. Может принимать два значения — False (отключить отображение границ) и True (включить отображение границ). Не знаю как это помогает на скорости выполнения макроса, лично я этого не ущутил, но некоторые говорят, что помогает. Я вообще не люблю когда отображаются границы листа, мне кажется, что это нужно только при распечатке. Помещать этот код можно в начало и конец макроса.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.DisplayStatusBar — строка состояния. Может принимать два значения — False (отключить строку состояния) и True (включить строку состояния). При выполнении макросов в строке состояния отображаются все происходяще события. Для того чтобы не тратить время на просчёт событий и прорисовку их в статусбаре, отключаем её на время выполнения макроса, и включаем её когда макрос закончил выполняться.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.DisplayAlerts — команда отвечающая за события в Excel. Может принимать два значения — False (отключаем запросы Excel) и True (включаем события Excel). Это чень интересная и полезная команда при помощи которой можно отключить запросы Excel, например, чтобы он не спрашивал нужно ли сохранить изменения в книге, или отключить запрос на совместимость версий Excel. Ниже приведён пример, в котором книга закрывает сама себя, при этом независимо от того внесли вы изменения или нет в книгу, при закрытии книги вам не поступит запрос "Сохранить изменения", а книга просто закроется без сохранения и уведомления пользователя.

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

Я решил выделить отдельный урок, в котором будут описаны относительные команды скрытых перемещений по книге. Например каким образом возможно выполнить перемещение курсора на 3 столбца влево и на 6 строк вниз относительно активной ячейки. Это бывает очень удобно когда вы работаете с одними и теми же данными, с одинаковыми таблицами, из которых Вам надо выделить только определённые значения, скопировать их или наоборот оставить только их, а всё остальное удалить. При этом у Вас всё время выполняется одна и таже операция, которая уже надоела. Почему бы не написать готовый макрос, который будет работать и опираться на определённую ячейку таблицы — активную ячейку. Именно это я и решил собрать всё в одном уроке — Скрытые перемещения.

Рассмотрим команду относительного перемещения, которая называется:

RowOffset — это на сколько строк вверх или вниз необходимо сделать перемещение. Может принимать как положительные значения (при перемещении вниз), так и отрицательные значения (при перемещении вверх);
ColumnOffset — это на сколько столбцов влево или вправо необходимо сделать перемещение. Может принимать как положительные значения (при перемещении вправо), так и отрицательные значения (при перемещении влево);

ActiveCell.Offset(0, 2).Select ‘1

ActiveCell.Offset(4, 0).Select ‘2

ActiveCell.Offset(-2, 0).Select ‘3

ActiveCell.Offset(0, -2).Select ‘4

В первом случае курсор выделит ячеку находящуюся на 2 ячейки вправо от активной ячейки. Во втором случае выделится ячейка находящаяся на 4 строки вниз от предыдущей — активной ячейки. В третьем случае выделится ячейка находящаяся на две строки выше от предыдущей — активной ячейки. И в последнем случае выделится ячейка находящаяся на два столбца влево от предыдущей — активной ячейки.

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

Перемещаемся по листам не зная их имён

Иногда возникает необходимость выполнить перемещение по листам, но при этом их имена постоянно меняются или Вы делаете такую программу, в которой Вы заранее не знаете имена листов, но перемещаться в ней по этим листам как-то надо. Для этого можно воспользоваться записью относительной:

Sheets — это лист;
(X) — это номер листа;

Ниже на примере показано как можно выделить лист №2, и не важно какое у него имя, второй по счёту лист будет выделен:

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

Возможно кто-то уже задавался вопросом: "Как определить край таблицы или последний заполненный столбец, строку?" Вот тут я и покажу как определить последнюю заполненную ячейку. Зная эту ячейку можно определить конечную координату таблицы, которую в последствии можно успешно обработать. Ниже показан пример выделения конечных ячеек относительно текущей.

1 — это выделение крайней правой яейки;
2 — это выделение самой верхней ячейки;
3 — это выделение крайней левой ячейки;
4 — это выделение нижней заполненной ячейки;

Читайте также:  Как заменить жесткий диск на ноутбуке lenovo

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

Выделения относительно активной ячейки

Выделить вниз до первой или последней заполненной ячейки (равносильно нажатию Ctrl+Shift+Down)

Выделить вверх до первой или последней заполненной ячейки (равносильно нажатию Ctrl+Shift+Up)

Выделить вправо до первой или последней заполненной ячейки (равносильно нажатию Ctrl+Shift+Right)

Выделить влево до первой или последней заполненной ячейки (равносильно нажатию Ctrl+Shift+Left)

Выделить текущую область (выделяется диапазон неразрывно заполненных ячеек — равносильно нажатию кнопок Ctrl+Shift+*)

Выделить активную область (происходит выделение всего заполненного диапазона Ctrl+Shift+Home, End, Home)

Выделить смежные (заполненные прилегающие к активной ячейке) ячейки в столбце с активной ячейкой

Dim TopCell As Range

Dim BottomCell As Range

If IsEmpty(ActiveCell) Then Exit Sub

On Error Resume Next

If IsEmpty(ActiveCell.Offset(-1, 0)) Then Set TopCell = _

ActiveCell Else Set TopCell = ActiveCell.End(xlUp)

If IsEmpty(ActiveCell.Offset(1, 0)) Then Set BottomCell = _

ActiveCell Else Set BottomCell = ActiveCell.End(xlDown)

Выделить смежные ячейки в строке с активной ячейкой

Dim LeftCell As Range

Dim RightCell As Range

If IsEmpty(ActiveCell) Then Exit Sub

On Error Resume Next

If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = _

ActiveCell Else Set LeftCell = ActiveCell.End(xlToLeft)

If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = _

ActiveCell Else Set RightCell = ActiveCell.End(xlToRight)

Выделить весь активный столбец

Выделить всю активную строку

Выделить рабочий лист

Выделить следующую пустую ячейку снизу

Do While Not IsEmpty(ActiveCell)

Выделить следующую пустую ячейку справа

Do While Not IsEmpty(ActiveCell)

Выделение от первой непустой до последней непустой ячеек в строке

Dim LeftCell As Range

Dim RightCell As Range

Set LeftCell = Cells(ActiveCell.Row, 1)

Set RightCell = Cells(ActiveCell.Row, 256)

If IsEmpty(LeftCell) Then Set LeftCell = LeftCell.End(xlToRight)

If IsEmpty(RightCell) Then Set RightCell = RightCell.End(xlToLeft)

If LeftCell.Column = 256 And RightCell.Column = 1 Then ActiveCell. _

Select Else Range(LeftCell, RightCell).Select

Выделение от первой непустой до последней непустой ячеек в столбце

Dim TopCell As Range

Dim BottomCell As Range

Set TopCell = Cells(1, ActiveCell.Column)

Set BottomCell = Cells(16384, ActiveCell.Column)

If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)

If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)

If TopCell.Row = 16384 And BottomCell.Row = 1 Then ActiveCell. _

Select Else Range(TopCell, BottomCell).Select

Некоторые наверно обращали внимание на то, что при записи макроса вводимые формулы имеют непонятый вид. Тут хотелось бы развеять все непонятки и показать, что в этих формулах нет ничего сложного, и в них всё логично, хоть они и выглядят по другому. Рассмотрим простую формулу сложения двух ячеек.

R — это строка, от слова Row;
C — это столбец, от слова Column;

В квадратных скобках указано на какое количество столбцов или строк необходимо переместится от заданной ячейки. Принцип отчёта такой же как и в команде рассмотренной самой первой в этом уроке — Offset. И как видно на примере, значения в квадратных скобках могут быть как отрицательные так и положительные.

В первом примере показано, что сумма в ячейке Cells(4, 5) равна сумме двух ячеек, одна из которых находится на третьем столбце влево от заданной ячейки, а другая на один столбец влево от заданной ячейки, при этом строка остаётся неизменной. На втором примере всё аналогично, только тут уже столбец остаётся неизменным а меняются строки.

Столбцы и строки

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

Для того чтобы оперировть какой-то одной строкой или столбцом, можно воспользоваться следующей записью:

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

Rows(6).Interior.ColorIndex = 5 ‘Строка

Columns(6).Interior.ColorIndex = 5 ‘Столбец

Если Вам необходимо выделить более одной строки или столбца, то можно воспользоваться следующей записью:

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

то мы сможем обработать целый диапазон строк и столбцов, но он сплошной. Если мы хотим обработать разные строки, например 1, 4, 6-8, и разные столбцы, например B, D, F-G, то запись необходимо произвести следующим образом:

При такой записи у нас получается уже диапазон. И умногих возник вопрос: "Каким образом можно одновременно выделить и строки и столбцы?". Пример ниже выделяет одновременно 8 строчку и столбец D:

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

Error. Обработка ошибок

Ну вот и пришёл тот долгожданный день, когда Вы задумались об обработке ошибок. Хорошо когда в программах работаете только Вы, и Вы знаете куда в ней можно нажать, а куда не стоит потому что случится ошибка. Но если Вы пишите программу, в которой работает несколько человек помимо Вас и они понятия не имеют что в ней можно и что нельзя, и они обязательно будут тыкать туда куда нельзя, и эти ошибки Вас просто погубят, потому что все будут грешить на вашу программу.
Конечно же в любой программе необходимо предусмотреть "защиту от дурака", но иногда "дурак" превосходит все ожидания. Если честно, я редко пользуюсь командами для обработки ошибок. Стараюсь предусматривать все возможные варианты действий пользователя, но это иногда заставляет написать такую огромную защиту, что сама программа того не стоит. Вообщем приступим.

Читайте также:  Бук не видит wifi

Искусственно создадим ошибку. Самый простой способ — взять любое число и поделить его на ноль. Напишем простенькую программу и на ней будем рассматривать различные способы обхода ошибки.

b = InputBox("Введите число отличное от 0", "Ввод данных", "0")

MsgBox "Результат: " & a & "/" & b & "=" & c, vbInformation, "Ответ"

Способ №1. Перенаправление программы

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

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

On Error GoTo errors

b = InputBox("Введите число отличное от 0", "Ввод данных", "0")

MsgBox "Результат: " & a & "/" & b & "=" & c, vbInformation, "Ответ"

MsgBox "Ошибка! Вводите корректные данные", vbCritical, "Ошибка"

Поясню. При возникновении ошибки, программа с места ошибки сразу перенаправится на errors: и уже будет продолжать выполнять код с этого места. То-есть с информационного сообщения о том, что у нас возникла ошибка, после этого сообщения программа так и продолжит выполнять макрос дальше, в данном случае программа просто закончится. В случае, если ошибки нет, нам покажется результат и код перенаправится на Endprimer:, так как туда нас перенаправил GoTo.
Если Вы не хотите чтобы макрос выполнялся дальше, то можно добавить команду для выхода из макросаExit Sub (завершение выполнения текущего макроса).

On Error GoTo errors

b = InputBox("Введите число отличное от 0", "Ввод данных", "0")

MsgBox "Результат: " & a & "/" & b & "=" & c, vbInformation, "Ответ"

MsgBox "Ошибка! Вводите кооректные данные", vbCritical, "Ошибка"

Способ №2. Настойчивость в исправлении ошибки

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

On Error GoTo errors

b = InputBox("Введите число отличное от 0", "Ввод данных", "0")

MsgBox "Результат: " & a & "/" & b & "=" & c, vbInformation, "Ответ"

MsgBox "Ошибка! Повторите ввод", vbCritical, "Ошибка"

b = InputBox("Введите число отличное от 0", "Ввод данных", "0")

Тут у нас добавилась только одна новая команда Resume, которая и будет перенапрявлять программу на место возникновения ошибки, тем самым требовать от нас правильного ввода данных, и пока мы их не введём, программа так и будет требовать от нас непонятного.

Способ №3. Предупреждён. Сообщение об ошибке

Хорошо, что разработчики VBA дали такой большой спектр обработки ошибок. Следующий способ уведомит Вас о произошедшей ошибке и выдаст её номер, а так же спросит Вас нужно ли выполнять программу дальше или всё таки прекратить её выполнение. Выглядеть это будет так.

On Error GoTo errors

b = InputBox("Введите число отличное от 0", "Ввод данных", "0")

MsgBox "Результат: " & a & "/" & b & "=" & c, vbInformation, "Ответ"

If Err.Number 0 Then

resultat = MsgBox("При выполнении программы произошла ошибка №" & Err.Number & _

vbNewLine & "Продолжить выполнение программы не смотря на ошибку?", _

vbYesNo + vbCritical, "Ошибка")

If resultat = vbYes Then

Тут у нас добавилось ещё одна новая команда Resume Next, которая говорит о том, что код необходимо продолжать выполнять дальше с места обнаружения ошибки, не смотря ни на что.

Способ №4. Очистка ошибки

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

On Error Resume Next

b = InputBox("Введите число отличное от 0", "Ввод данных", "0")

MsgBox "Результат: " & a & "/" & b & "=" & c, vbInformation, "Ответ"

If Err.Number 0 Then

MsgBox "Ошибка " & p & " очищена!", vbInformation, "Уведомление"

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

On Error Resume Next

b = InputBox("Введите число отличное от 0", "Ввод данных", "0")

MsgBox "Результат: " & a & "/" & b & "=" & c, vbInformation, "Ответ"

При такой записи макрос будет продолжать выполняться не смотря на все ошибки.

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

Ссылка на основную публикацию
Уровень интенсивности в дб формула
Очень часто новички сталкивается с таким понятием, как децибел. Многие из них интуитивно догадываются, что это такое, но у большинства...
Удалить программу через консоль
Операционная система Windows предлагает несколько способов для удаления установленных приложений и программ. Некоторые пользователи даже прибегают к использованию стороннего программного...
Удалить раздел жёсткого диска
Столкнулись с проблемой, что невозможно удалить EFI раздел с жёсткого диска в Windows? Не волнуйтесь данную проблему можно решить довольно...
Усилитель wifi сигнала для роутера какой выбрать
Привет! Поговорим сегодня про усилители Wi-Fi сигнала. Переезд столкнул меня лицом к лицу с новой проблемой – площадь увеличилась, а...
Adblock detector