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

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

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



Но посмотреть на файл было бы куда лучше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.06.2017, 01:25
    #39469866
Cursky
Cursky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
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
| Ответить | Цитировать | Написать  
10.06.2017, 12:35
    #39469942
l-evgene
l-evgene
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
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
| Ответить | Цитировать | Написать  
10.06.2017, 12:51
    #39469943
Shocker.Pro
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
l-evgeneЗатем одинаковые позиции объединяются: в одной из строк суммируется общее количество в штуках и рублях, в остальных - штуки обнуляются. На все это уходит несколько секунд (не более 30).тут бы, конечно, SQL-запрос к листу на пару порядков быстрее сработал.


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

а после
Код: vbnet
1.
Application.ScreenUpdating=True
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.06.2017, 12:58
    #39469945
Shocker.Pro
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
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
| Ответить | Цитировать | Написать  
10.06.2017, 12:59
    #39469947
Shocker.Pro
Shocker.Pro
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
Еще вопрос, насколько адекватно работает функция LastRowInCol, которая постоянно вызывается в цикле
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.06.2017, 13:34
    #39469956
iMrTidy
iMrTidy
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
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
| Ответить | Цитировать | Написать  
10.06.2017, 20:11
    #39470086
Cursky
Cursky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
l-evgene,

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

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

Как правильно заметил Shoker.Pro, почему бы не воспользоваться средством, предназначенным для такой задачи? Сводную не пробовали сделать по вашей таблице? Тем более, что она вам и итоги сама посчитает, и оформит красиво ещё... да и удалять исходные данные - как-то не принято.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2017, 08:54
    #39470158
Сама доброта
Сама доброта
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
Совсем замордовали ТС. Он жаловался на одно, а ему еще и другое втюхивают. Надо бы сначала помочь ему с тем, что он конкретно просил, а потом уж можно и оптимизировать. А так его похоже напугали не мало.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2017, 12:04
    #39470187
l-evgene
l-evgene
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
Спасибо за участие. Скрипт не мой, поэтому не хотелось вносить серьезных изменений, чтобы чего-нибудь не сломать (а там около 30 процедур).
Решил просто:отсортировал таблицу и удалил лишние строки одним делитом.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2017, 15:45
    #39470246
l-evgene
l-evgene
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
Сама доброта, добрее надо быть, еще добрее
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.06.2017, 18:57
    #39470295
l-evgene
l-evgene
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Быстродействие в Excel VBA
l-evgeneСама доброта, добрее надо быть, еще добреея чесслово буду
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft Office [игнор отключен] [закрыт для гостей] / Быстродействие в Excel VBA / 15 сообщений из 15, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 438ms
Закрыть
start [/forum/topic.php?fid=61&tablet=1&tid=2172655]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 31ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 310ms
total:  438ms
x
x
Закрыть


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