Я использую VBA в Excel (Microsoft 365). В моей книге 50 листов. У меня есть макрос для сортировки листов по имени, реализованный следующим образом:

Private Sub set_speed_optimizations(enable As Boolean)
    If enable = True Then
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
    Else
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
    End If
End Sub
Sub sort_sheets_by_name()
    On Error GoTo handle_error
    set_speed_optimizations True

    Dim iSheets As Integer
    iSheets = ThisWorkbook.Sheets.Count

    Dim i As Integer
    Dim j As Integer

    ' Сортируем все листы в алфавитном порядке
    For i = 1 To iSheets - 1
        For j = i + 1 To iSheets
            If ThisWorkbook.Sheets(j).Name < ThisWorkbook.Sheets(i).Name Then
                ThisWorkbook.Sheets(j).Move Before:=ThisWorkbook.Sheets(i)
            End If
        Next j
    Next i

    On Error Resume Next

    ' Вручную перемещаем несколько определенных листов в начало панели листов
    ThisWorkbook.Sheets("First_0").Move Before:=ThisWorkbook.Sheets(1)
    ThisWorkbook.Sheets("First_1").Move Before:=ThisWorkbook.Sheets(1)

    ' Вручную перемещаем несколько определенных листов в конец панели листов
    ThisWorkbook.Sheets("Last_N-1").Move After:=ThisWorkbook.Sheets(iSheets)
    ThisWorkbook.Sheets("Last_N").Move After:=ThisWorkbook.Sheets(iSheets)

    ThisWorkbook.Sheets("Main").Activate

    Beep

' Не выходите из Sub до этого обработчика ошибок, потому что то, что следует, должно быть 
' выполнено в любом случае
handle_error:
    set_speed_optimizations False
End Sub

Этот макрос работает, но выполняется крайне медленно. Сортировка 50 листов по имени занимает около 15 минут.

Листы большие и сложные и содержат связи с книгами в общем доступе (с более чем 170000 ссылок на эти связанные книги). В дополнение к тем действиям, которые я предпринял в set_speed_optimizations, чтобы попытаться ускорить процесс, я также разорвал все связи с книгами.

После всего этого сортировка листов в алфавитном порядке по имени все еще очень, очень медленная. Как я могу определить, что вызывает эту медлительность, и решить эту проблему?

Правка для добавления заметки о ручном перемещении одного пустого листа

Один из листов в моей книге пуст. Если я устанавливаю Формулы–>Параметры расчета–>Ручной и затем вручную перетаскиваю свой пустой лист на одно место влево или вправо на панели листов, Excel зависает на около 11 секунд, прежде чем лист будет перемещен.

Правка для добавления заметок о использовании сортировки O(N * lg N) и использовании массива

Учитывая мою предыдущую правку о том, что даже ручное перемещение одного (пустого) листа происходит медленно и учитывая мой (маленький) случай N = 50, я опасался, что проблема может заключаться не в использовании неэффективного O(N^2) алгоритма сортировки. Тем не менее, я попробовал реализовать рекомендуемый O(N * lg N) алгоритм сортировки и, к сожалению, я теперь показал, что алгоритм сортировки не вносит значительного вклада в проблему.

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

Вот мой обновленный код:

Sub quick_sort(v_array As Variant, in_low As Long, in_high As Long)
    Dim pivot As Variant
    Dim tmp_low As Long
    Dim tmp_high As Long
    Dim tmp_swap As Variant

    tmp_low = in_low
    tmp_high = in_high

    pivot = v_array((in_low + in_high) \ 2)

    While (tmp_low <= tmp_high)
        While (v_array(tmp_low) < pivot And tmp_low < in_high)
            tmp_low = tmp_low + 1
        Wend

        While (pivot < v_array(tmp_high) And tmp_high > in_low)
            tmp_high = tmp_high - 1
        Wend

        If (tmp_low <= tmp_high) Then
            tmp_swap = v_array(tmp_low)
            v_array(tmp_low) = v_array(tmp_high)
            v_array(tmp_high) = tmp_swap
            tmp_low = tmp_low + 1
            tmp_high = tmp_high - 1
        End If
    Wend

    If (in_low < tmp_high) Then quick_sort v_array, in_low, tmp_high
    If (tmp_low < in_high) Then quick_sort v_array, tmp_low, in_high
End Sub

Private Sub set_speed_optimizations(enable As Boolean)
    If enable = True Then
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.EnableEvents = False
    Else
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.EnableEvents = True
    End If
End Sub

Sub sort_sheets_by_name()
    On Error GoTo handle_error
    set_speed_optimizations True

    Dim i As Long
    Dim num_sheets As Long
    Dim sheet_names()

    num_sheets = ThisWorkbook.Sheets.Count
    ReDim sheet_names(1 To num_sheets)

    For i = LBound(sheet_names) To UBound(sheet_names)
        sheet_names(i) = ThisWorkbook.Sheets(i).Name
    Next

    quick_sort sheet_names, LBound(sheet_names), UBound(sheet_names)

    Dim old_timer As Double

    For i = LBound(sheet_names) To UBound(sheet_names)
        Debug.Print sheet_names(i) & ",";
        old_timer = Timer()
        ThisWorkbook.Sheets(sheet_names(i)).Move After:=ThisWorkbook.Sheets(num_sheets)
        Debug.Print (Timer() - old_timer)
    Next

    ThisWorkbook.Sheets("Main").Activate

    Beep

' Не выходите из Sub до этого обработчика ошибок, потому что то, что следует, должно быть 
' выполнено в любом случае
handle_error:
    set_speed_optimizations False
End Sub

С момента моего оригинального поста количество листов, с которыми я работаю, увеличилось с N = 50 до N = 52.

Я также внедрил временные показатели по листам, как рекомендуется, и представлю сводную статистику по всем 52 листам ниже. Все временные показатели имеют единицы секунд, затраченные на перемещение одного листа.

Что меня удивило, так это то, что оптимизации скорости, реализованные в set_speed_optimizations(), дали лишь около 15% улучшения. Я утверждаю это, потому что быстрый эксперимент показал, что перемещение листа приводит к перерасчету (если автоматический перерасчет включен). Поскольку мои листы очень сложные, я бы подумал, что ненужный перерасчет будет львиной долей проблемы. Однако это, похоже, не так.

Упрощение проблемы

Возможно, упростить проблему, сосредоточившись на моем прежнем эксперименте с перемещением одного (пустого) листа на одно место на панели листов. Как уже было сказано, при выключенном автоматическом перерасчете это занимает около 11 секунд.

Что может вызвать перемещение пустого листа на одно место на панели листов, при выключенном автоматическом перерасчете, занять 11 секунд?

После всего этого сортировка листов в алфавитном порядке по имени все еще очень, очень медленная. Как я могу определить, что вызывает эту медлительность, и решить эту проблему?

Чтобы определить приблизительную оценку времени обработки, используйте функцию таймера – функцию timer(). Она возвращает вам значение счетчика, основанное на секундах.

Если вы вставите две строки кода в ваш цикл и инициализируете переменную old_timer перед вложенным циклом

DIM oldtimer as double

  If ThisWorkbook.Sheets(j).Name < ThisWorkbook.Sheets(i).Name Then
     Debug.Print timer()-oldtimer
     oldtimer=timer()
     ThisWorkbook.Sheets(j).Move Before:=ThisWorkbook.Sheets(i)

  End If

вы найдете время для каждой операции перемещения в окне вывода в редакторе VBA.
По какой-то причине, которую я не знаю, я предполагаю, что функция перемещения листа довольно медленная.
Вы должны минимизировать такие операции. Чтобы проследить за этим, вставьте счетчик для циклов:

перед вашим циклом:

DIM oldtimer as double

DIM movcount as long

movcount=0

внутри вашего цикла:

  If ThisWorkbook.Sheets(j).Name < ThisWorkbook.Sheets(i).Name Then
               movcount=movcount+1
               Debug.Print movcount, timer()-oldtimer
               oldtimer=timer()

  ThisWorkbook.Sheets(j).Move Before:=ThisWorkbook.Sheets(i)

  End If

Cybernetic.nomad описывает решение в своем комментарии!
Вы должны перемещать лист только один раз.
Если вы разделите movcount на iSheets, вы получите представление о том, на сколько вы можете улучшить время выполнения вашего конкретного листа Excel.

Кроме того, ваш код реализует очень медленный алгоритм сортировки, его скорость составляет порядка iSheet^2, специалисты по IT говорят, что ваш код находится в O(n^2).
Обычно класс скорости важен только для больших n (в вашем случае: iSheet), но в вашем случае каждая операция перемещения, похоже, является тяжелым бременем.

Подсказка:
Эффективная сортировка данных в VBA для Excel без использования функций рабочего листа требует некоторого программирования с использованием не фиксированных массивов, а динамических структур на основе коллекций VBA. Хорошие алгоритмы сортировки являются членами класса O(n * log(n)). O(n * n), однако, считается медленным.

Я буду рад помочь вам оптимизировать процесс сортировки листов в вашем коде Excel VBA. Вот несколько дополнительных соображений и потенциальных решений на основе предоставленной информации:

1. Структура и сложность листа:

• Связи с книгами: Хотя разрыв связей с книгами может улучшить производительность, важно учитывать их необходимость. Если они критичны для ваших расчетов, рассмотрите альтернативные способы их управления или оптимизации связанных формул.
• Сложные формулы и расчеты: Проанализируйте формулы и расчеты в ваших листах. Если они вычислительно затратные, упростите их или рассмотрите возможность использования более эффективных функций или методов.
• Условное форматирование и проверка данных: Эти функции могут значительно повлиять на производительность, особенно если они включают сложные правила или большие наборы данных. Минимизируйте их использование или оптимизируйте их условия.
• Сводные таблицы: Сводные таблицы могут медленно обновляться, особенно с большими наборами данных или сложными расчетами. Если возможно, упростите их структуру или рассмотрите возможность использования других методов анализа данных.

2. Оптимизация кода VBA:

• Избегайте ненужных циклов: Минимизируйте количество циклов и вложенных циклов в вашем коде. Если возможно, используйте операции на основе массивов или встроенные функции для более эффективных расчетов.
• Условия раннего выхода: Если условие выполняется раньше в цикле, используйте Exit For или Exit Do, чтобы избежать ненужных итераций.
• Объявление переменных: Объявляйте переменные с соответствующими типами данных, чтобы оптимизировать использование памяти и производительность.
• Обработка ошибок: Используйте On Error GoTo экономно и только когда это необходимо. Чрезмерная обработка ошибок может замедлить ваш код.
• Ссылки на объекты: Избегайте ненужных ссылок на объекты. Используйте Set, чтобы назначить объекты, и освободите их, используя Set obj = Nothing, когда закончите.

3. Настройки Excel:

• Режим расчета: Убедитесь, что xlCalculationManual установлен в вашей подпрограмме set_speed_optimizations. Это предотвращает ненужные перерасчеты во время процесса сортировки.
• ScreenUpdating и EnableEvents: Установите эти свойства в False, чтобы избежать визуальных обновлений и событий во время сортировки.
• Параметры расчета: Изучите параметры расчета в настройках Excel. Например, вы можете рассмотреть возможность отключения итеративных расчетов или настройки параметров точности, если это необходимо.

4. Дополнительные советы:

• Временная книга: Если ваша книга крайне велика или сложна, рассмотрите возможность создания временной книги для выполнения сортировки, а затем копирования результатов в оригинальную книгу.
• Внешние инструменты сортировки: Если VBA все еще слишком медленно, изучите внешние инструменты или библиотеки сортировки, которые могут предложить лучшую производительность.

Специфические соображения для вашего случая:

• Ручное перемещение листа: Замедление ручного перемещения пустого листа может быть связано с основными расчетами или связанными данными в книге, даже если сам лист пуст. Попробуйте изолировать проблему, создав новую пустую книгу и переместив лист в ней.
• Сортировка на основе массивов: Как вы уже реализовали, использование алгоритма сортировки на основе массива, такого как Быстрая сортировка, может быть более эффективным, чем вложенные циклы.
• Профилирование производительности: Рассмотрите возможность использования инструмента профилирования производительности для выявления конкретных узких мест в вашем коде. Это может помочь вам сосредоточить ваши усилия по оптимизации на самых значительных областях.

Тщательно учитывая эти факторы и реализуя соответствующие оптимизации, вы сможете значительно улучшить производительность процесса сортировки листов в Excel VBA.