Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
VBA. Медленная работа cells.copy и cells.format, На некоторых компьютерах.
 
#1
22.04.2025 15:47:13
Периодически напарываюсь на такое, но универсального решения не нашел.
Может кто-то уже сталкивался и нашел причины и методы обхода.

Проблема: один и тот же файл с одним и тем же кодом на одних компах обрабатывается быстро (1-2 секунды), на других -- медленно (30-40 секунд). Тормоза начинаются на копировании одной(!) ячейки в другую (cells(r1,c1).copy cells(r2,c2)) и на присвоении формата одной ячейке или диапазону из нескольких ячеек. На каждую такую операцию уходит в десятки раз больше времени на условно "медленных" компах, чем на "быстрых".

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

ScreenUpdating, Calculation и все такое прочее установлены правильно, да и на данном конкретном коде не сильно влияют на скорость (размер таблицы небольшой).

Загадочности добавляет тот факт, что иногда и на "медленных" компах оно вдруг начинает проворачиваться быстро, но потом снова тупит. Есть незначительные отличия в железе, плюс на некоторых компах под 64-разрядной windows установлен 32-разрядный Excel. Есть подозрение, что именно на 32-разрядных Excel 2019 происходит такой тупняк, но статистику еще не собрал.
 
 
 
#2
22.04.2025 18:34:11
Файл - пример и описание действий, приводящих к неэффективной работе макросов, помогли бы прояснить ситуацию.
Владимир
 
 
 
#3
22.04.2025 22:19:23
Пример выложить не могу, к сожалению. Но я описал все предельно точно. Именно операция копирования ячейки в ячейку (обе находятся на одном листе) и присвоение ячейки числового формата (cells(r,c).numberformat = str) на одном файле с одними и теми же данными на одних компах выполняется в десятки раз медленнее, чем на других. А в какой-нибудь день может пролететь за доли секунды, но потом опять тупит. А на других компах всегда работает быстро.

И это тестовый файл, в котором ничего не меняется. Но сталкивался с этим и на других файлах тоже, в основном именно при копировании ячейки в ячейку. Завтра поэкспериментирую на чистых файлах с чистым кодом, может быть прояснится что-нибудь.
 
 
 
#4
23.04.2025 12:00:10
sokol92
Во вложении простой пример.
Отличия по времени выполнения соответственно примерно в 20, в 4, в 3 и в 5 раз.
Способы 2 и 3 периодически меняются местами по скорости, но общее соотношение сохраняется. Особенно впечатляет отличия для самого очевидного (казалось бы) и короткого по коду способа копирования. Вместо этого, оказывается, гораздо быстрее получается перенести сначала значение, а потом скопировать формат.
Изменено: dhead - 23.04.2025 12:01:42
 
 
 
#5
23.04.2025 12:41:43
Спасибо за подготовленный пример.
Методы 2-3 100 раз копируют информацию в буфер обмена и вставляют информацию из буфера обмена. В эффективных программах необходимо минимизировать количество таких операций.

Общее замечание - следует минимизировать число взаимодействий между VBA и Excel. Копирование диапазона ячеек во много раз быстрее, чем копирование каждой ячейки этого же диапазона по отдельности.
Изменено: sokol92 - 23.04.2025 12:43:33
Владимир
 
 
 
#6
23.04.2025 13:52:11
sokol92, такое впечатление, что вы вообще не вникали в то, что я писал выше и в результаты выложенного примера тоже.

Цитата
sokol92 написал:
Общее замечание - следует минимизировать число взаимодействий между VBA и Excel
Я знаю, как оптимизировать обработку данных, сводя к минимуму взаимодействие с ячейками напрямую (обработать таблицу с миллионом строк и парой сотен столбцов за секунды – не проблема). Но как вы сформируете любую итоговую таблицу с нужный форматом КАЖДОЙ отдельной ячейки в ней, не взаимодействуя с Excel?

Цитата
sokol92 написал:
100 раз копируют информацию ... Копирование диапазона ячеек во много раз быстрее
100 – это как раз приблизительное число ячеек итогов в конкретной задаче, которые необходимо отформатировать в соответствии с данными и форматами ячеек на других листах. Думаете, я с диапазонами работать не умею?)

Цитата
sokol92 написал:
Методы 2-3 100 раз копируют информацию в буфер обмена и вставляют информацию из буфера обмена. В эффективных программах необходимо минимизировать количество таких операций.
Вы не заметили, что результаты теста противоречат вашим рекомендациям (и моим ожиданиям, кстати, тоже)? Внезапно через буфер обмена получается раз в 10 быстрее))

Короче, вопрос остается: в чем прикол? Почему на практически одинаковых машинах с плюс-минус одинаковыми процессорами, одинаковым объемом оперативы и соседними версиями Excel время выполнения операции копирования ячеек может отличаться в десятки раз? И как это можно обойти?
Пока что нашлось одно странное решение: вопреки интуиции, здравому смыслу и рекомендациям sokol92,  копировать через copy-pastespecial (на одних машинах получается быстрее раза в два, на других – в десять).

Может быть все-таки есть другие способы?
 
 
 
#7
23.04.2025 16:38:27
После сохранения в формате .xlsm (который можно анализировать) у меня (Excel 2016 Win 10) Ваши тесты выдают (стабильно) результаты такого порядка:(округлено до 2 знаков после запятой):

test1: 0.42
test2: 0,66
test3: 0,64
test4: 0,01

Остаюсь при своих рекомендациях.

P.S. У Вас макрос находится в модуле листа (а не стандартном модуле), так что не запускайте его кнопкой.
P.P.S. После команды PasteSpecial, если буфер обмена больше не нужен, лучше добавить строку:
Код
Application.CutCopyMode = False
Изменено: sokol92 - 23.04.2025 17:04:47
Владимир
 
 
 
#8
23.04.2025 17:19:54
sokol92, так я бы тоже оставался при таких же рекомендациях, если бы не увидел то, что вижу на 19 и 21 версиях Excel:) На разных машинах. Кстати, и диапазоны копируются точно так же. Rows(n).Copy Rows(m) проворачивается от двух до двадцати раз медленнее, чем Rows(n).Copy: Rows(m).PasteSpecial. У вас нет возможности проверить на других версиях? Вечером попробую на 2007.

Цитата
sokol92 написал:
P.S. У Вас макрос находится в модуле листа (а не стандартном модуле), так что не запускайте его кнопкой.
Намеренно находится там для чистоты эксперимента, потому что в конкретной задаче он может находиться только там. Кстати, а кнопка чем помешает?

Цитата
sokol92 написал:
P.P.S. После команды PasteSpecial, если буфер обмена больше не нужен, лучше добавить строку:К
Это просто тест, в реальном коде все на месте.

В общем, граждане! У кого есть возможность прогнать на своих компах, не проходите мимо. Любопытно же. Может хоть какая-то закономерность определится.
 
 
 
#9
23.04.2025 17:27:36
А какая может быть закономерность для разных машин, с разным объёмом свободной памяти, и набором программ, в частности, разными антивирусами )
 
 
 
#10
23.04.2025 17:50:38
Я думаю, что попытка за 0,66 сек 100 раз обновить буфер обмена является непростой задачей для MS Windows (да еще с учетом того, что может быть включено журналирование изменений буфера обмена). Можно добавить после обновлений буфера обмена вызов функции DoEvents, что повысит стабильность,  но замедлит время выполнения.
Владимир
 
 
 
#11
23.04.2025 17:54:08
Цитата
nilske написал:
А какая может быть закономерность для разных машин, с разным объёмом свободной памяти, и набором программ, в частности, разными антивирусами )

Закономерность такая, что по идее (и как пишет sokol92, ) на любом компе и на любой версии excel первый метод должен работать быстрее, чем второй и третий. Плюс по идее драматической разницы между ними быть не должно. Но пока что на разных компах 2019 и 2021 ведут себя с точностью до наоборот. Если у кого-то под такими же версиями будет работать так, как ожидается (особенно интересно посмотреть на 2019 32-разрядную), то гипотезу о зависимости от версий Excel можно будет отбросить, а она пока основная.
 
 
 
#12
23.04.2025 18:06:26
Цитата
sokol92 написал:
Я думаю, что попытка за 0,66 сек 100 раз обновить буфер обмена является непростой задачей для MS Windows
Тем не менее у меня на разных машинах оно работает гораздо быстрее, чем прямое копирование. Это и странно. И сильно сомневаюсь, что это как-то может повлиять на стабильность. Следующая после Copy операция не начнет выполняться, пока не завершится копирование, а копирование не завершится, пока винда не скажет, что оно завершилось)
 
 
 
#13
23.04.2025 18:30:19
Проверил на 2007.

test1: 0.16
test2: 0,31
test3: 0,30
test4: 0,01

То есть так, как оно по идее должно быть, и как у sokol92 на 2016.
 
 
 
#14
24.04.2025 07:44:43
Посмотрите на "медленных" компах, не включены ли программы для слежения за буфером обмена (различные переключалки раскладки клавиатуры типа Punto Switcher и т.п., логирования буфера обмена - ClipBoard или встроенная в винду Win+V) - из-за них тоже могут быть тормоза
 
 
 
#15
24.04.2025 08:17:37
irabel, во-первых, там нет ничего такого, а во-вторых – вы результаты-то видели? через буфер обмена получается в десятки раз быстрее, чем при прямом копировании. Поэтому и антивирусы не на первом месте среди подозреваемых. Да и временно отключить я их сам не смогу, надо сначала гипотезу с версиями Excel опровергнуть.
 
 
 
#16
24.04.2025 09:44:07
Цитата
написал:
чем при прямом копировании.
А прямое копирование разве проходит мимо буфера обмена?


Мои результаты: Excel 2013 (64)
test1: 17,46
test2: 13,71
test3: 0,71

Второй раз:
test1: 30,61
test2: 0,71
test3: 1,17

Третий раз:
test1: 0,48
test2: 0,69
test3: 0,68

Четвертый раз:
test1: 0,5
test2: 1,17
test3: 0,71

В 1м и 2м случае включен журнал буфера обмена, во 3м и 4м- выключен

Тест на рабочем компе (Excel 2016, 32) с антивирусом в параноидальном режиме
включен журнал буфера обмена:
test1: 1,3
test2: 33,36
test3: 3,91

выключен журнал буфера обмена:
test1: 1,56
test2: 32,58
test3: 3,24

включен журнал буфера обмена:
test1: 30,953125
test2: 2,9375
test3: 1,83203125

включен журнал буфера обмена:
test1: 1,09375
test2: 31,96875
test3: 2,01171875

Немного поменял код, очистка перед каждым этапом, результаты копируются в ячейку.
Код
Sub test1()
    Dim n As Integer
    Dim t As Double
    Dim log As String
    Dim calcMode As XlCalculation
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    calcMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    With ActiveSheet
        .Columns(2).Clear
        t = Timer

        For n = 1 To 100
            .Cells(n, 1).Copy .Cells(n, 2)
        Next n

        log = "test1: " & Timer - t
        .Columns(2).Clear
        t = Timer

        For n = 1 To 100
            .Cells(n, 1).Copy
            .Cells(n, 2).PasteSpecial
        Next n

        log = log & vbLf & "test2: " & Timer - t
        .Columns(2).Clear
        t = Timer

        For n = 1 To 100
            .Cells(n, 2) = .Cells(n, 1)
            .Cells(n, 1).Copy
            .Cells(n, 2).PasteSpecial xlPasteFormats
        Next n

        log = log & vbLf & "test3: " & Timer - t
        .Columns(2).Clear
        t = Timer

        For n = 1 To 100
            .Cells(n, 2) = .Cells(n, 1)
            .Cells(n, 2).NumberFormat = "0.000"
        Next n

        log = log & vbLf & "test4: " & Timer - t
        'MsgBox log
        .Cells(1, 8) = log
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = calcMode

End Sub
Изменено: irabel - 24.04.2025 10:53:46
 
 
 
#17
24.04.2025 11:12:34
Цитата
irabel написал:
А прямое копирование разве проходит мимо буфера обмена?
До ваших результатов считал, что да.. Какой смысл задействовать буфер обмена, если известен пункт назначения? Это как если бы Васе нужно было переложить пачку денег из правой руки в левую, но он задействовал бы для этого еще Петю) Да и Майкрософт говорит про необязательный параметр Destination метода Copy вот что:
Цитата
Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard.
Что, конечно, можно интерпретировать по-разному, но как-бы намекает, что если destination не указан, то копирует в буфер, а если указан, то просто создает в нужном месте копию. И это косвенно подтверждает ваш антивирус тоже.
 
 
 
Страницы: 1
Читают тему
Наверх