База знаний KB0070
  2. Ресурсы
  3. База знаний
  4. KB0070

Почему при включенной надстройке think-cell медленно работает мой макрос Excel?

Распространенная проблема, из-за которой производительность макросов VBA снижается при использовании функции .Select. При выборе ячейки каждая отдельная надстройка Excel (в том числе и think-cell) получает уведомление о новой выбранной ячейке, что значительно снижает скорость работы макроса. Особенно это заметно сказывается на макросах, созданных с помощью средства записи макросов.

Microsoft также рекомендует избегать применения функции .Select в коде VBA для повышения производительности.

Пример. Как уйти от использования инструкции .Select

Рассмотрим следующий простой макрос AutoFillTable:

Sub AutoFillTable()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim nCount As Integer
    nCount = iRange.Cells.Count
    For i = 1 To nCount
        Selection.Copy
        If iRange.Cells.Item(i).Value = "" Then
            iRange.Cells.Item(i).Range("A1").Select
            ActiveSheet.Paste
        Else
            iRange.Cells.Item(i).Range("A1").Select
        End If
    Next
End Sub

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

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

Sub AutoFillTable2()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim iCellMaster As Excel.Range
    For Each iCell In iRange.Cells
        If iCell.Value = "" Then
            If Not iCellMaster Is Nothing Then
                iCellMaster.Copy (iCell)
            End If
        Else
            Set iCellMaster = iCell
        End If
    Next iCell
End Sub

В AutoFillTable2 есть два важных отличия от предыдущей реализации:

  1. Особенно обратите внимание на то, что в функции больше не проводится выбор ячейки. Вместо этого при обнаружении заполненной ячейки последняя сохраняется в переменной iCellMaster. После чего при обнаружении пустой ячейки все содержимое iCellMaster копируется в iCell.
  2. Для обхода каждой ячейки в диапазоне применяется цикл языка Visual Basic For Each … Next. Это также заметно повышает удобочитаемость кода.

Поделиться