Пользовательская функция поиска Excel vba: быстрая при использовании вручную, медленная в макросах

Мне пришлось написать свою собственную функцию поиска, но ее производительность сильно различается в зависимости от того, вызываю ли я ее вручную на самом листе (через '=') или через макросы. Мои макросы делают следующее:

Function betterSearch(searchCell As Range, aCol As Range, bCol As Range)
        For Each cell In aCol
            If LCase(cell.Value) = LCase(searchCell.Value) Then
                    betterSearch = bCol.Cells(cell.row, 1)
                    Exit For
            End If
            betterSearch = "Not found"
        Next
End Function

Таким образом, макрос открывает resultsWorkbook и dataWorkbook, а затем выполняет поиск четырех значений в столбце A из resultWorkbook в dataWorkbook и возвращает соответствующие данные из какого-либо другого столбца dataWorkbook.

'...opening resultWorkbook and dataWorkbook
For aRow = 6 To 9
            resultWorkbook.Worksheets("B3").Cells(aRow, 125).Value = _
                betterSearch(resultWorkbook.Worksheets("B3").Cells(aRow, 1) _
                , dataWorkbook.Worksheets("page 1").Range("A:A") _
                , dataWorkbook.Worksheets("page 1").Range("Z:Z"))

             resultWorkbook.Worksheets("B3").Cells(aRow, 126).Value = _
                betterSearch(resultWorkbook.Worksheets("B3").Cells(aRow, 1) _
                , dataWorkbook.Worksheets("page 1").Range("A:A") _
                , dataWorkbook.Worksheets("page 1").Range("I:I"))
Next aRow

Это очень медленно - занимает несколько минут для 1 файла. Но когда я открываю этот файл вручную и просто ввожу формулу и нажимаю Enter, она вычисляется мгновенно.

Что здесь не так? Откуда такое поведение?


person Ans    schedule 04.07.2017    source источник


Ответы (1)


Это может быть медленным, если он попытается выполнить поиск по всем столбцам A: A, I: I (1+ миллионов ячеек).

Попробуйте обновить свой betterSearch следующим образом:

Function betterSearch(searchCell As Range, aCol As Range, bCol As Range)
    Dim itm As String, col1 As Variant, col2 As Variant, r As Long

    With ActiveSheet
        col1 = .UsedRange.Columns(aCol.Column)
        col2 = .UsedRange.Columns(bCol.Column)
    End With

    betterSearch = "Not found"
    itm = LCase(searchCell.Value2)

    For r = 1 To UBound(col1)
        If Len(col1(r, 1)) > 0 Then
            If LCase(col1(r, 1)) = itm Then
                betterSearch = col2(r, 1)
                Exit Function
            End If
        End If
    Next
End Function
person paul bica    schedule 04.07.2017