Буду благодарен, если кто-нибудь подскажет, как можно оптимизировать скорость работы макроса. Суть в том, что есть массив (колонка B, кол-во строк может быть разным) с текстовой информацией в каждой заполненной ячейке. Необходимо "очистить" текст от определённых символов, буквосочетаний и т.д. Я решил эту задачу (см. моё "творение" во вложении), но беда в том, что работает всё это очень не быстро (для примера я положил небольшой массив, хотя и тут виден процесс "размышлений"). Очевидно, каждый раз макрос перебирает каждую строку, что и замедляет процесс...
Прошу подсказать, каким образом можно изменить код, чтобы всё работало быстрее.
Заранее благодарен тем, кто откликнется!
Добрый вечер всем форумчанам!
Буду благодарен, если кто-нибудь подскажет, как можно оптимизировать скорость работы макроса. Суть в том, что есть массив (колонка B, кол-во строк может быть разным) с текстовой информацией в каждой заполненной ячейке. Необходимо "очистить" текст от определённых символов, буквосочетаний и т.д. Я решил эту задачу (см. моё "творение" во вложении), но беда в том, что работает всё это очень не быстро (для примера я положил небольшой массив, хотя и тут виден процесс "размышлений"). Очевидно, каждый раз макрос перебирает каждую строку, что и замедляет процесс...
Прошу подсказать, каким образом можно изменить код, чтобы всё работало быстрее.
master-dd, Если у вас нет острой необходимости что-то писать на листе или читать с него, то никогда не делайте этого. Работайте с массивами, они в разы пошевелее
[vba]
Код
Sub NewMacros() Dim Start!: Start = Timer ' перебрасываем все данные в массив MyArr = Sheets("Temp1").Range("B1:B1001").Value ' дальше работаем с массивом For i = LBound(MyArr) To UBound(MyArr) MyArr(i, 1) = MyReplace(CStr(MyArr(i, 1))) Next i ' возвращаем обработанный массив на место Sheets("Temp1").Range("B1:B1001").Value = MyArr
Debug.Print "Затрачено: " & Format(Timer - Start, "#0.00") & " сек." End Sub
Function MyReplace(s As String) As String s = Replace(s, "a.", "a ") ' ... Здесь вписываете остальные реплэйсы s = Replace(s, "9 "" ", "9"" ") MyReplace = s End Function
[/vba]
master-dd, Если у вас нет острой необходимости что-то писать на листе или читать с него, то никогда не делайте этого. Работайте с массивами, они в разы пошевелее
[vba]
Код
Sub NewMacros() Dim Start!: Start = Timer ' перебрасываем все данные в массив MyArr = Sheets("Temp1").Range("B1:B1001").Value ' дальше работаем с массивом For i = LBound(MyArr) To UBound(MyArr) MyArr(i, 1) = MyReplace(CStr(MyArr(i, 1))) Next i ' возвращаем обработанный массив на место Sheets("Temp1").Range("B1:B1001").Value = MyArr
Debug.Print "Затрачено: " & Format(Timer - Start, "#0.00") & " сек." End Sub
Function MyReplace(s As String) As String s = Replace(s, "a.", "a ") ' ... Здесь вписываете остальные реплэйсы s = Replace(s, "9 "" ", "9"" ") MyReplace = s End Function
Нет, Replace заменяет во всем диапазоне. Но работа с объектами листа медленная, при каждой замене идет обращение к диапазону. Чем больше замен, тем больше тормоза.
Улучшить читабельность кода можно, если замены предварительно внести в массив: [vba]
Код
Sub ArrayReplace() Dim aRepl(1 To 105, 1 To 2) ' если не ошибся - 105 замен aRepl(1, 1) = "a.": aRepl(1, 2) = "a " aRepl(2, 1) = "b.": aRepl(2, 2) = "b " aRepl(3, 1) = "c.": aRepl(3, 2) = "c " ' ................. aRepl(28, 1) = "2-in-1": aRepl(28, 2) = "2inn1" ' ................. aRepl(105, 1) = "9 "" ": aRepl(105, 2) = "9"" "
Call ReplaceData(aRepl) End Sub
Sub ReplaceData(aRepl()) Dim i As Long Application.ScreenUpdating = False
With Sheets("Temp1").Range("B1:B1001") For i = 1 To UBound(aRepl) .Replace What:=aRepl(i, 1), Replacement:=aRepl(i, 2), MatchCase:=False Next i End With
Application.ScreenUpdating = True End Sub
[/vba] Попробовать ускорить - в ReplaceData добавить масив для данных и все замены производить в памяти. [vba]
Код
Sub ReplaceData(aRepl()) Dim aData Dim i As Long, k As Long aData = Sheets("Temp1").Range("B1:B1001").Value
Application.ScreenUpdating = False
For k = 2 To UBound(aData) For i = 1 To 105 aData(k, 1) = Replace(aData(k, 1), aRepl(i, 1), aRepl(i, 2)) Next i Next k
Sheets("Temp1").Range("B1:B1001").Value = aData Application.ScreenUpdating = True MsgBox "OK", 64, "" End Sub
[/vba] Различия: если замены прозводить на листе - одна замена для всего диапазона. При замене в массиве - каждая замена производится в каждом тексте отдельно. Т.к. обработка в памяти намного быстрее работы с листом, возможно, обработка по второму варианту пройдет быстрее. Почему сомнения? Чем больше строк и замен, тем больше итераций обработки массива. Например, для 10 значений и 5 замен на листе - 5 итераций, в массиве придется менять 50 раз.
Нет, Replace заменяет во всем диапазоне. Но работа с объектами листа медленная, при каждой замене идет обращение к диапазону. Чем больше замен, тем больше тормоза.
Улучшить читабельность кода можно, если замены предварительно внести в массив: [vba]
Код
Sub ArrayReplace() Dim aRepl(1 To 105, 1 To 2) ' если не ошибся - 105 замен aRepl(1, 1) = "a.": aRepl(1, 2) = "a " aRepl(2, 1) = "b.": aRepl(2, 2) = "b " aRepl(3, 1) = "c.": aRepl(3, 2) = "c " ' ................. aRepl(28, 1) = "2-in-1": aRepl(28, 2) = "2inn1" ' ................. aRepl(105, 1) = "9 "" ": aRepl(105, 2) = "9"" "
Call ReplaceData(aRepl) End Sub
Sub ReplaceData(aRepl()) Dim i As Long Application.ScreenUpdating = False
With Sheets("Temp1").Range("B1:B1001") For i = 1 To UBound(aRepl) .Replace What:=aRepl(i, 1), Replacement:=aRepl(i, 2), MatchCase:=False Next i End With
Application.ScreenUpdating = True End Sub
[/vba] Попробовать ускорить - в ReplaceData добавить масив для данных и все замены производить в памяти. [vba]
Код
Sub ReplaceData(aRepl()) Dim aData Dim i As Long, k As Long aData = Sheets("Temp1").Range("B1:B1001").Value
Application.ScreenUpdating = False
For k = 2 To UBound(aData) For i = 1 To 105 aData(k, 1) = Replace(aData(k, 1), aRepl(i, 1), aRepl(i, 2)) Next i Next k
Sheets("Temp1").Range("B1:B1001").Value = aData Application.ScreenUpdating = True MsgBox "OK", 64, "" End Sub
[/vba] Различия: если замены прозводить на листе - одна замена для всего диапазона. При замене в массиве - каждая замена производится в каждом тексте отдельно. Т.к. обработка в памяти намного быстрее работы с листом, возможно, обработка по второму варианту пройдет быстрее. Почему сомнения? Чем больше строк и замен, тем больше итераций обработки массива. Например, для 10 значений и 5 замен на листе - 5 итераций, в массиве придется менять 50 раз.vikttur
Сообщение отредактировал vikttur - Среда, 09.01.2019, 02:58
vikttur, спасибо! Это то, что нужно было! Правда мне пришлось кое-что изменить, а именно добавить замену всех заглавных букв на обычные, т.к. при таком способе обработки это, оказывается, имеет значение. Но для конечного результата размер регистра для меня не важен!
Прикладываю файл с финальным кодом (может кому пригодится - там на листе ForCode можно легко генерировать новые строки для замены).
vikttur, спасибо! Это то, что нужно было! Правда мне пришлось кое-что изменить, а именно добавить замену всех заглавных букв на обычные, т.к. при таком способе обработки это, оказывается, имеет значение. Но для конечного результата размер регистра для меня не важен!
Прикладываю файл с финальным кодом (может кому пригодится - там на листе ForCode можно легко генерировать новые строки для замены).master-dd
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Это то, что нужно было! Правда мне пришлось кое-что изменить, а именно добавить замену всех заглавных букв на обычные, т.к. при таком способе обработки это, оказывается, имеет значение. Но для конечного результата размер регистра для меня не важен!
