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

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

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



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


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

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

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

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

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


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