макросы Excel 2010

[Grom]

Доброго времени.
Кто-нибудь обращал внимание, что макросы в Excel 2010 работают на порядок медленнее Excel 2003?

[Денис]

Обращал. Медленнее. Заговор?

[Grom]

Как победить?

[Денис]

Отключить какой-нибудь аудит (журналирование) коих понадобавляли в 2010. Я точно не знаю, не сталкивался с необходимостью оптимизировать старые скрипты в новых офисах. А, кстати, надо перелопатить код на наличие "грязных хаков" работавших быстро лишь в старых офисах. Например, чисто навскидку представим себе, что в 2003 была функция Foo(), активно юзаемая твоим скриптом, в 2010 сделали оптимизированную функцию Bar(), реализующую и старую Foo и еще кучу всего, а функция Foo оставлена для совместимости, но передает работу в Bar с такими общими параметрами, что та тормозит раз в 20.

[iGrok]

В 2007 очень сильно начали тормозить макросы, обрабатывающие и меняющие большое кол-во ячеек по многу раз. Насколько я помню, там что-то изменилось с алгоритмом обновления информации в ячейке, или что-то в этом духе.

Рецепт общий - делаем профилирование, смотрим, что тормозит, и вперёд - оптизировать.

[Grom]

Код: Выделить всё

   For i = 2 To b
        If Cells(i, 1) = "XXX" Then
            Rows(i).Delete Shift:=xlUp
            b = b - 1
            i = i - 1
        End If
    Next i

таблица на 3 - 4 тыс. строк обрабатывается 30-40 мин (в 2003 около минуты). Попробую сделать обработку в массиве, а потом результаты сохранять на листе.

Спасибо за обсуждение.

[iGrok]

Поставь Application.ScreenUpdating = False перед циклом и Application.ScreenUpdating = True после, и посмотри, что изменится.
2007/2010 под рукой нету, чтобы проверить.

[Grom]

не помогает.

[Qwertiy]

For i = 2 To b

Попробуй

Код: Выделить всё

Dim i As Integer
For i = b to 2 Step -1
...
Next i

[Qwertiy]

Код: Выделить всё

   For i = 2 To b
        If Cells(i, 1) = "XXX" Then
            Rows(i).Delete Shift:=xlUp
            b = b - 1
            i = i - 1
        End If
    Next i

Кстати, ты в курсе, что b = b - 1 не изменяет количество итераций? Используй While вместо For, а лучше, вариант из предыдущего моего поста

[Grom]

в курсе. но это и не надо - т.к. количество итераций равно количеству строк.

долго обрабатывается

Код: Выделить всё

Rows(i).Delete Shift:=xlUp


а пока поднял excel 2003 параллельно и обрабатываю на нем

[Qwertiy]

в курсе. но это и не надо - т.к. количество итераций равно количеству строк.

Неа, количество строк уменьшается при удалении, так что надо

Попробуй

Код: Выделить всё

Dim i As Integer
For i = b to 2 Step -1
  If Cells(i, 1) = "XXX" Then
    Rows(i).Delete Shift:=xlUp
  End If
Next i

Интересно, на сколько это быстрее. Скорее всего, именно на сдвиг тратится время.

И ещё, попробуй отключить пересчёт листа на время выполнения макроса.

[ger_kar]

For i = b to 2 Step -1

Прикольно, но я видимо немного туповат (а может и много) но я так и не понял, что она может дать в плане прироста производительности. Какая разница, будет ли цикл идти вперед или обратно? Qwertiy можешь разжевать, что и как?

А по вопросу удаления строк, может их не по одной удалять, а скажем блоками штук по 10 или даже больше, все зависит от количества, которое можно указать в Range (тут нужен эксперимент). Т.е. при совпадении данных ячейки не удалять строку сразу, а динамически собирать строку с диапазонами удаления для Range добавляя в диапазон, только что совпавшую строку, а потом геть! и удалять сразу блок таких строк и опять все по новой.

[ger_kar]

Кстати видимо проблема не в том, что макросы в Excel2010 стали медленнее работать по сравнению с 2003, а в том, что максимальные размеры самой таблицы увеличились. Причем в случае удаления строки целиком, как в этой теме приводит к тому, что перестраиваются все строки диапазона от искомой строки и до конца, и при этом не важно, какая часть заполнена данными, т.е. размер пользовательской таблицы. Так как диапазон таблицы значительно вырос, соответственно требуется больше операций для ее обработки, отсюда и задержки.

[Qwertiy]

Какая разница, будет ли цикл идти вперед или обратно?

Я исходил из того, что важны только заполненные данные, а пустые строки не изменяются при сдвиге. Если это не так, то разницы соответственно никакой.
Рассмотрим случай, когда удаляются все n строк. При прохождении снизу вверх сдвиг будет выполнен n раз только для пустых строк (я считаю, что каждый такой сдвиг эквиваленитен сдвигу одной строки). При прохождении сверху вниз, количество сдвинутых строк будет пропорционально n^2 (если точнее, то n+(n-1)+(n-2)+...+1 = n*(n+1)/2). Аналогичное улучшение есть и при удалении не всех строк, в общем случае ассимптотика тоже O(n^2), однако, чем больше строк удаляется, тем больше выигрыш по сравнению с исходным вариантом.

и при этом не важно, какая часть заполнена данными

Я считал Excel достаточно хорошей программой, чтобы это было не так.
Вообще, сомнительно, чтобы это действительно было так, т. к. количество ячеек очень большое, поэтому хранить информацию о каждой пустой ячейке отдельно потребовало бы слишком много памяти и времени на обработку.

Проще было бы просто проверить, как такое изменение цикла скажется на производительности. Я бы сам проверил, но у меня нету Excel'я, причём никакого

Кстати, мне казалось, что, по сравнению с 2003-им, изменилось только количество столбцов, а количество строк осталось. Или нет?

[Qwertiy]

Т.е. при совпадении данных ячейки не удалять строку сразу, а динамически собирать строку с диапазонами удаления для Range добавляя в диапазон, только что совпавшую строку, а потом геть! и удалять сразу блок таких строк и опять все по новой.

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

[ger_kar]

Вообще, сомнительно, чтобы это действительно было так, т. к. количество ячеек очень большое, поэтому хранить информацию о каждой пустой ячейке отдельно потребовало бы слишком много памяти и времени на обработку.

Нет речь не о ячейках, а о диапазоне. Я конечно не знаю, как там внутри все устроено, но при таких операциях как удаление строки как таковой, а не только данных которые в ней содержались, видимо идет перестроение общего диапазона. Дело в том, что если удалять пустые строки, например за пределами области данных, то на эту операцию также расходуется довольно много времени, поэтому я и сделал такое предположение. Конечно вряд-ли Excel хранит данные о все пустых ячейках (это было - бы действительно очень глупо), но а диапазонах информация наверняка содержится и видимо именно она и перестраивается.

Кстати, мне казалось, что, по сравнению с 2003-им, изменилось только количество столбцов, а количество строк осталось. Или нет?

Добавилось и количество строк и количество столбцов.

можно ли удалить строки таким образом.

Конечно можно и не только строки или столбцы, а любые диапазоны не смежных ячеек
Например: Range("1:1,3:3,6:6,10:10,15:15").Delete Shift:=xlUp удалит 1, 3, 6, 10, 15 строки.

[Qwertiy]

Например: Range("1:1,3:3,6:6,10:10,15:15").Delete Shift:=xlUp удалит 1, 3, 6, 10, 15 строки.

А как оно в плане эффективности?

[ger_kar]

А как оно в плане эффективности?

Я сам не тестировал, но я думаю это сделает Grom и сообщит нам. Я думаю, что для него, при всем богатстве выбора, другой альтернативы пока нет .

[Calvin]

Если подробней, то можно сделать так:

Код: Выделить всё

Dim i As Integer
Dim NewRange as range
    For i = 2 To b
        If Cells(i, 1) = "XXX" Then
           if NewRange is nothing then
               Set NewRange =Rows(i)
           else
               Set NewRange =Union(NewRange, Rows(i))
           end if
        End If
    Next i
   if not NewRange is nothing then
          NewRange.Delete
   end if
   Set NewRange = nothing       


[ger_kar]

А на скорость вообще эту идею, в смысле удаление не по одной строке, а диапазонами, кто нибудь удосужился проверить? Я бы проверил будь у меня Excel2010, но я пока пользуюсь 2003. И Grom тоже чего-то не написал, получилось что-нибудь у него или нет.