Блог

Главная - Вопросы по программированию - Способы ускорить функцию поиска в VBA?

Способы ускорить функцию поиска в VBA?

#excel #vba #performance

#excel #vba #Производительность

Вопрос:

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

При запуске это занимает слишком много времени (до такой степени, что мне приходится прерывать выполнение макроса). Мне нужно создать одну и ту же функцию поиска для 8 разных полей на основе одних и тех же критериев соответствия. Любые советы о том, как ускорить этот запрос или построить его более динамичным способом, чтобы я мог просматривать все 8 столбцов одновременно, а не создавать функции и вспомогательные элементы для каждого поля поиска?

     Function fcst_bal_find(ByVal Anode As String, ByVal LoB As String, ByVal Month As String, ByVal Year As String) As Variant

        Dim Fcst_Essbase As Worksheet
        Dim fcst_rowcnt
        Dim act_rowcnt
        
        fcst_rowcnt = Sheets("Date Dims").Range("B7")
        act_rowcnt = Sheets("Date Dims").Range("B8")
        Set Fcst_Essbase = Sheets("Fcst Essbase Pull")


        For i = 2 To fcst_rowcnt   4
            If WorksheetFunction.Trim(Fcst_Essbase.Cells(i, 1).Value) = Anode Then
                If WorksheetFunction.Trim(Fcst_Essbase.Cells(i, 2).Value) = LoB Then
                    If WorksheetFunction.Trim(Fcst_Essbase.Cells(i, 3).Value) = Month Then
                        If "Y" amp; Right(WorksheetFunction.Trim(Fcst_Essbase.Cells(i, 4).Value), 2) = Year Then
                    fcst_bal_find = Fcst_Essbase.Cells(i, 5).Value
                    Exit Function
                        End If
                    End If
                End If
            End If
        Next i

        fcst_bal_find = "N/A"

    End Function


    Sub balfcst_find()

        Dim fcst_tab As Worksheet
        Dim match As Variant
        Dim Anode As String
        Dim LoB As String
        Dim Month As String
        Dim Year As String
        Dim fcst_rowcnt
        Dim act_rowcnt
        
        fcst_rowcnt = Sheets("Date Dims").Range("B7")
        act_rowcnt = Sheets("Date Dims").Range("B8")
        Set fcst_tab = Sheets("Cartesian Product - Fcst")

    For i = 2 To fcst_rowcnt
        Anode = fcst_tab.Range("A" amp; i).Value
        LoB = fcst_tab.Range("B" amp; i).Value
        Month = fcst_tab.Range("C" amp; i).Value
        Year = fcst_tab.Range("D" amp; i).Value
        match = fcst_bal_find(Anode, LoB, Month, Year)
        fcst_tab.Cells(i, 5) = match ' test the output
    Next i

    End Sub

Комментарии:

1. Используйте альтернативный массив — считайте рассматриваемый диапазон в массив и зацикливайте массив вместо зацикливания ячеек, как вы делаете сейчас.

2. ОК. Быстрее всего для поиска данных используется автофильтр, за которым следуют словари. Также вы пробовали yes bigbens comment — перебирать массив в противоположность ячейкам. это тоже было бы быстрее.

3. По моему опыту, «АвтоФильтр` не является быстрым @DavidWooley-AST. Это медленно и может даже давать ошибки. Вариант массива — это путь.

4. ОК. Я согласен . Я шел по временам некоторых VBA YouTubers , ирландца. «Освоение макросов Excel» . Зависит от того, какой процесс и для какой цели. Но мне всегда нравится бросать вызов самому себе..

5. @ David Wooley — AST: Вы, вероятно, имели в виду расширенный фильтр , и вы говорите о Поле Келли из Excel Macro Mastery .

Ответ №1:

Вот пример использования variant array для сопоставления чего-либо из моего текущего проекта. Вы можете изменить в соответствии с вашими потребностями.

 Private Function verifyMod(modValue As Double, state As String) As Boolean
    
    If Len(modValue) Then
        
        Dim modTable As ListObject
        Set modTable = lookupsAUState.ListObjects("stateWritingCoModMinMax")
        
        Dim v As Variant
        v = modTable.DataBodyRange.value
        
        Dim company As String
        company = StrConv(xmlCo.Range("insuranceCompanyName"), vbProperCase)
        
        Dim x As Long
        For x = LBound(v) To UBound(v)
        
            If v(x, modTable.ListColumns("Company").index) = company Then
            
                If v(x, modTable.ListColumns("State").index) = state Then
                    
                    If modValue >= v(x, modTable.ListColumns("Min").index) And modValue <= v(x, modTable.ListColumns("Max").index) Then
                    
                        verifyMod = True
                    
                    Else
                        
                        MsgBox state amp; " allows for modifications between " amp; v(x, modTable.ListColumns("Min").index) amp; " and " amp; v(x, modTable.ListColumns("Max").index) amp; ". Please enter a modification within that range."
                        
                    End If
                    
                    Exit For
                    
                End If
                
             End If
             
        Next
        
    End If
    
End Function