powered by simpleCommunicator - 2.0.39     © 2025 Programmizd 02
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Быстродействие в Excel VBA
15 сообщений из 15, страница 1 из 1
Быстродействие в Excel VBA
    #39469714
l-evgene
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
В макросе эксель нужно удалить 100 строк из листа. Странным образом это влияет на время выполнения.
При первом запуске макроса на удаление 100 строк уходит 140 сек. При втором - 1 000 сек. При 3-м - уже 2 000 сек.
Если закрыть и снова открыть файл, все повторяется с точностью +/- 2 сек.
С этим как-то можно бороться?
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39469727
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
А код удаления мы сможем увидеть?
А то кофейная гуща кончилась.
Да и l-evgeneна удаление 100 строк уходит 140 сектут либо файл огромный с большим количеством формул, либо алгоритм... впрочем, мне сложно придумать алгоритм, который будет столько времени удалять, так что опишите еще и файл.
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39469740
iMrTidy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
l-evgene,

Рискну предположить, что есть событие при обновлении листа, которое приводит к многочисленными пересчетам/обновлению данных. Для начала можно попробовать:

Код: vbnet
1.
2.
3.
Application.Calculation = xlCalculationManual
...удаление строк...
Application.Calculation = xlCalculationAutomatic



Но посмотреть на файл было бы куда лучше.
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39469866
Cursky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
l-evgene,

Sub test()
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Добавить таблицу (Ctrl+T)
Таблицу отсортировать по нужному столбцу
Отфильтровать ненужные строки по условию и удалить

Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub
Как-то так
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39469942
l-evgene
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Shocker.Pro,
Код удаления:
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
Function minifyBOM2()
'Merges same positions in BOM2
    Dim i As Integer, j As Integer
    Dim sh As Worksheet
    Set sh = Sheets("Закупка технолог")
                                                        Debug.Print 9, Timer, "начало"
    For i = 2 To LastRowInCol(sh, 1)
        For j = i + 1 To LastRowInCol(sh, 1) + 1
            If sh.Cells(i, 2).Value = sh.Cells(j, 2).Value And sh.Cells(i, 3).Value = sh.Cells(j, 3).Value Then
                sh.Cells(i, 5).Value = sh.Cells(i, 5).Value + sh.Cells(j, 5).Value
                sh.Cells(i, 7).Value = sh.Cells(i, 7).Value + sh.Cells(j, 7).Value
                sh.Cells(j, 5).Value = 0
            End If
        Next j
    Next i
                                                        Debug.Print 9, Timer, "цикл 1 окончание"

    For i = LastRowInCol(sh, 1) To 2 Step -1
        If sh.Cells(i, 5) = 0 Then
            sh.Rows(i).Delete
        End If
    Next i



                                                        Debug.Print 9, Timer, "цикл 2 окончание"
    sh.Cells(LastRowInCol(sh, 1) + 1, 6).Value = "ИТОГО:"
    sh.Cells(LastRowInCol(sh, 1) + 1, 7).Value = Application.WorksheetFunction.Sum(sh.Range("G2:G" & i))
                                                        Debug.Print 9, Timer, "окончание"
End Function

Суть в следующем: в ходе предыдущих операций таблица сначала полностью очищается, потом заполняется (это список комплектующих) - 130-150 строк.
Затем одинаковые позиции объединяются: в одной из строк суммируется общее количество в штуках и рублях, в остальных - штуки обнуляются. На все это уходит несколько секунд (не более 30).
Наконец, идет удаление строк с нулевым количеством. Вот здесь и происходит вышеописанный косяк. В коде это вот этот кусок
Код: vbnet
1.
2.
3.
4.
5.
    For i = LastRowInCol(sh, 1) To 2 Step -1
        If sh.Cells(i, 5) = 0 Then
            sh.Rows(i).Delete
        End If
    Next i

В приложении - обрабатываемый лист (это уже после мучительных удалений)
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39469943
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
l-evgeneЗатем одинаковые позиции объединяются: в одной из строк суммируется общее количество в штуках и рублях, в остальных - штуки обнуляются. На все это уходит несколько секунд (не более 30).тут бы, конечно, SQL-запрос к листу на пару порядков быстрее сработал.


Для начала попробуйте перед всем макросом
Код: vbnet
1.
Application.ScreenUpdating=False

а после
Код: vbnet
1.
Application.ScreenUpdating=True
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39469945
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Shocker.Proтут бы, конечно, SQL-запрос к листу на пару порядков быстрее сработал.Можно попробовать уменьшить количество обращений к листу типа
Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
    For i = 2 To LastRowInCol(sh, 1)
        a2=sh.Cells(i, 2).Value
        a3=sh.Cells(i, 3).Value
        a5=sh.Cells(i, 5).Value
        a7=sh.Cells(i, 7).Value
        For j = i + 1 To LastRowInCol(sh, 1) + 1
            If a2 = sh.Cells(j, 2).Value And a3 = sh.Cells(j, 3).Value Then
                a5 = a5 + sh.Cells(j, 5).Value
                a7 = a7 + sh.Cells(j, 7).Value
                sh.Cells(j, 5).Value = 0
            End If
        Next j
        sh.Cells(i, 5).Value=a5
        sh.Cells(i, 7).Value=a7
    Next i

даже это должно сильно ускорить пересчет
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39469947
Фотография Shocker.Pro
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Еще вопрос, насколько адекватно работает функция LastRowInCol, которая постоянно вызывается в цикле
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39469956
iMrTidy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
l-evgene,

Попробуйте так с проблемным моментом:

Код: vbnet
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
Function minifyBOM2()

...

ExcelReflection False

For i = LastRowInCol(sh, 1) To 2 Step -1
    If sh.Cells(i, 5) = 0 Then
        sh.Rows(i).Delete
    End If
Next

ExcelReflection True

...

End Function

Private Sub ExcelReflection(bEnabled As Boolean)
On Error Resume Next

Application.DisplayAlerts = bEnabled
Application.ScreenUpdating = bEnabled
Application.EnableEvents = bEnabled
Application.Calculation = IIf(bEnabled, xlCalculationAutomatic, xlCalculationManual)
Application.DisplayStatusBar = bEnabled

End Sub



Да и в принципе можно всю функцию обернуть в ExcelReflection.
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39470086
Cursky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
l-evgene,

Вы ерундой занимаетесь, обращаетесь в каждом цикле к ячейкам листа, а потом удаляете строки по одной.
И Application.ScreenUpdating здесь не поможет
Лучше заносите всю вашу таблицу в переменную-массив и обрабатывайте. Через массив оставляете только нужные строки (сами придумаете как).
Потом возвращаете значения из массива на рабочий лист.

30 секунд на 150 строк - это цензурно выражаясь, очень много. На такую задачу должно уходить 0,0...30 миллисекунд максимум.
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39470146
Фотография AndreTM
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Да уж точно...
У вас там тормоза не только из-за процедуры удаления, но и из-за придуманной вами процедуры для расчета суммы по уникам (да ещё и с обработкой прямо на листе!). LastRowInCol(), опять же, вызываемая после To в For - это на каждой итерации одно и то же число рассчитывается. Опять же, как правильно замечено, если ячейки, которые обрабатываются/удаляются, зависимы - то влияет автопересчет и прочее.

Как правильно заметил Shoker.Pro, почему бы не воспользоваться средством, предназначенным для такой задачи? Сводную не пробовали сделать по вашей таблице? Тем более, что она вам и итоги сама посчитает, и оформит красиво ещё... да и удалять исходные данные - как-то не принято.
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39470158
Совсем замордовали ТС. Он жаловался на одно, а ему еще и другое втюхивают. Надо бы сначала помочь ему с тем, что он конкретно просил, а потом уж можно и оптимизировать. А так его похоже напугали не мало.
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39470187
l-evgene
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Спасибо за участие. Скрипт не мой, поэтому не хотелось вносить серьезных изменений, чтобы чего-нибудь не сломать (а там около 30 процедур).
Решил просто:отсортировал таблицу и удалил лишние строки одним делитом.
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39470246
l-evgene
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Сама доброта, добрее надо быть, еще добрее
...
Рейтинг: 0 / 0
Быстродействие в Excel VBA
    #39470295
l-evgene
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
l-evgeneСама доброта, добрее надо быть, еще добреея чесслово буду
...
Рейтинг: 0 / 0
15 сообщений из 15, страница 1 из 1
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Быстродействие в Excel VBA
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]