Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Оптимизация скорости работы кода при автозамене - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Оптимизация скорости работы кода при автозамене (Макросы/Sub)
Оптимизация скорости работы кода при автозамене
master-dd Дата: Среда, 09.01.2019, 00:16 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 76
Репутация: 1 ±
Замечаний: 0% ±

2016
Добрый вечер всем форумчанам!

Буду благодарен, если кто-нибудь подскажет, как можно оптимизировать скорость работы макроса.
Суть в том, что есть массив (колонка B, кол-во строк может быть разным) с текстовой информацией в каждой заполненной ячейке. Необходимо "очистить" текст от определённых символов, буквосочетаний и т.д. Я решил эту задачу (см. моё "творение" во вложении), но беда в том, что работает всё это очень не быстро (для примера я положил небольшой массив, хотя и тут виден процесс "размышлений"). Очевидно, каждый раз макрос перебирает каждую строку, что и замедляет процесс...

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

Заранее благодарен тем, кто откликнется!
К сообщению приложен файл: File1.xlsm(45.3 Kb)
 
Ответить
СообщениеДобрый вечер всем форумчанам!

Буду благодарен, если кто-нибудь подскажет, как можно оптимизировать скорость работы макроса.
Суть в том, что есть массив (колонка B, кол-во строк может быть разным) с текстовой информацией в каждой заполненной ячейке. Необходимо "очистить" текст от определённых символов, буквосочетаний и т.д. Я решил эту задачу (см. моё "творение" во вложении), но беда в том, что работает всё это очень не быстро (для примера я положил небольшой массив, хотя и тут виден процесс "размышлений"). Очевидно, каждый раз макрос перебирает каждую строку, что и замедляет процесс...

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

Заранее благодарен тем, кто откликнется!

Автор - master-dd
Дата добавления - 09.01.2019 в 00:16
boa Дата: Среда, 09.01.2019, 01:36 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 384
Репутация: 104 ±
Замечаний: 0% ±

2013, 365
master-dd,
Если у вас нет острой необходимости что-то писать на листе или читать с него, то никогда не делайте этого. Работайте с массивами, они в разы пошевелее


 
Ответить
Сообщениеmaster-dd,
Если у вас нет острой необходимости что-то писать на листе или читать с него, то никогда не делайте этого. Работайте с массивами, они в разы пошевелее

Автор - boa
Дата добавления - 09.01.2019 в 01:36
vikttur Дата: Среда, 09.01.2019, 02:00 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2921
Репутация: 525 ±
Замечаний: 0% ±

Нет, 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 - Среда, 09.01.2019, 02:58
 
Ответить
СообщениеНет, 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
Дата добавления - 09.01.2019 в 02:00
_Boroda_ Дата: Среда, 09.01.2019, 10:52 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15242
Репутация: 5987 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
master-dd, Вы не поверите, но на Вашем примере скорость уменьшается почти в 8 раз просто добавкой отключения автопересчета
Вначале макроса
[vba]
Код
Application.Calculation = xlCalculationManual
[/vba]
В окончании
[vba]
Код
Application.Calculation = xlCalculationAutomatic
[/vba]


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщениеmaster-dd, Вы не поверите, но на Вашем примере скорость уменьшается почти в 8 раз просто добавкой отключения автопересчета
Вначале макроса
[vba]
Код
Application.Calculation = xlCalculationManual
[/vba]
В окончании
[vba]
Код
Application.Calculation = xlCalculationAutomatic
[/vba]

Автор - _Boroda_
Дата добавления - 09.01.2019 в 10:52
master-dd Дата: Среда, 09.01.2019, 18:11 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 76
Репутация: 1 ±
Замечаний: 0% ±

2016
vikttur, спасибо! hands Это то, что нужно было! Правда мне пришлось кое-что изменить, а именно добавить замену всех заглавных букв на обычные, т.к. при таком способе обработки это, оказывается, имеет значение. Но для конечного результата размер регистра для меня не важен!

Прикладываю файл с финальным кодом (может кому пригодится - там на листе ForCode можно легко генерировать новые строки для замены).
К сообщению приложен файл: 9936855.xlsm(78.0 Kb)
 
Ответить
Сообщение vikttur, спасибо! hands Это то, что нужно было! Правда мне пришлось кое-что изменить, а именно добавить замену всех заглавных букв на обычные, т.к. при таком способе обработки это, оказывается, имеет значение. Но для конечного результата размер регистра для меня не важен!

Прикладываю файл с финальным кодом (может кому пригодится - там на листе ForCode можно легко генерировать новые строки для замены).

Автор - master-dd
Дата добавления - 09.01.2019 в 18:11
master-dd Дата: Среда, 09.01.2019, 18:14 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 76
Репутация: 1 ±
Замечаний: 0% ±

2016
_Boroda_, действительно быстрее, спасибо! Но решение от vikttur - именно то, что мне нужно было!
 
Ответить
Сообщение _Boroda_, действительно быстрее, спасибо! Но решение от vikttur - именно то, что мне нужно было!

Автор - master-dd
Дата добавления - 09.01.2019 в 18:14
boa Дата: Среда, 09.01.2019, 20:13 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 384
Репутация: 104 ±
Замечаний: 0% ±

2013, 365
master-dd,
добавить замену всех заглавных букв на обычные, т.к. при таком способе обработки это, оказывается, имеет значение.

впишите в начале модуля
[vba]
Код
Option Compare Text
[/vba]
что бы прописные были равны ЗАГЛАВНЫМ


 
Ответить
Сообщениеmaster-dd,
добавить замену всех заглавных букв на обычные, т.к. при таком способе обработки это, оказывается, имеет значение.

впишите в начале модуля
[vba]
Код
Option Compare Text
[/vba]
что бы прописные были равны ЗАГЛАВНЫМ

Автор - boa
Дата добавления - 09.01.2019 в 20:13
master-dd Дата: Среда, 09.01.2019, 22:26 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 76
Репутация: 1 ±
Замечаний: 0% ±

2016
boa, yes
 
Ответить
Сообщениеboa, yes

Автор - master-dd
Дата добавления - 09.01.2019 в 22:26
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Оптимизация скорости работы кода при автозамене (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!