UDF with Loops very slow and inefficient

Question

I have a code that sorts through thousands of lines in a spreadsheet and when it finds a row that has a specific match in two different columns, it returns a value in a third column. However this UDF is used thousands of times and with each running thousands of loops, its very slow. Is there a way to speed up or make this more efficient?

Dim SearchSheet As Worksheet
Dim PN As Integer
Dim MdlCol As Integer
Dim Mdl As String
Dim Result As Integer
Dim FinalRow As Integer
Dim i As Integer
Application.Volatile True
Select Case True
    Case Number < WorksheetFunction.CountA(Sheet2.Range("A:A")) And Model = "1A"
        Set SearchSheet = Sheet2
        PN = 3
        MdlCol = 5
        Mdl = "1A"
        Result = 30
    Case Number < WorksheetFunction.CountA(Sheet2.Range("A:A")) And Model = "1B"
        Set SearchSheet = Sheet2
        PN = 3
        MdlCol = 6
        Mdl = "1B"
        Result = 30
    Case Number < WorksheetFunction.CountA(Sheet2.Range("A:A")) And Model = "1C"
        Set SearchSheet = Sheet2
        PN = 3
        MdlCol = 7
        Mdl = "1C"
        Result = 30
    Case Number >= WorksheetFunction.CountA(Sheet2.Range("A:A")) And Model = "1A"
        Set SearchSheet = Sheet3
        PN = 2
        MdlCol = 18
        Mdl = "-1A"
        Result = 80
    Case Number >= WorksheetFunction.CountA(Sheet2.Range("A:A")) And Model = "1B"
        Set SearchSheet = Sheet3
        PN = 2
        MdlCol = 19
        Mdl = "-1B"
        Result = 80
    Case Number >= WorksheetFunction.CountA(Sheet2.Range("A:A")) And Model = "1C"
        Set SearchSheet = Sheet3
        PN = 2
        MdlCol = 20
        Mdl = "-1C"
        Result = 80
End Select
FinalRow = WorksheetFunction.CountA(SearchSheet.Range("A:A")) + 10

For i = 2 To FinalRow
    If SearchSheet.Cells(i, PN) = PartNumber And SearchSheet.Cells(i, MdlCol) = Mdl Then
            If SearchSheet.Cells(i, Result).Value = "X" Then
                CalibrationRequired = "Y"
            Else
                CalibrationRequired = SearchSheet.Cells(i, Result).Value
            End If
        Exit For
    End If
Next i
End Function ```

`If SearchSheet.Cells(i, PN) = PartNumber And SearchSheet.Cells(i, MdlCol) = Mdl Then`... you could just use `WorksheetFunction.CountIfs` here. You don't need to loop. — BigBen, May 21 '20 at 12:57
I am not sure I follow. That would give me the count of occurences with the matching criteria, btu I would need to return the value of that row and specific column (Result). — Blake Grier, May 21 '20 at 14:33
Then you would do a [lookup on two columns](https://stackoverflow.com/questions/42492758/vlookup-using-2-columns-to-reference-another) to achieve the same thing as `CalibrationRequired = SearchSheet.Cells(i, Result).Value`. — BigBen, May 21 '20 at 14:33
I started with index match to reference both pieces of criteria but was even worse, which is why I moved to a UDF. Its calculating faster than all the index match arrays, but is still slow. — Blake Grier, May 21 '20 at 15:01
Then I'd second the suggestion of just using a `Variant` array instead of looping cell-by-cell. — BigBen, May 21 '20 at 15:03
Thank you. I am trying to find more information on how to create a variant array and then tie it back to the original data for a result. — Blake Grier, May 21 '20 at 15:32
Once the variant array is created, are you not looping through the array as well to find the matches? — Blake Grier, May 21 '20 at 15:44
Yes... but looping through an array is *much* faster than looping cell-by-cell. — BigBen, May 21 '20 at 15:47
Btw, [use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). — BigBen, May 21 '20 at 15:50

score 0 · Answer 1 · answered May 21 '20 at 13:07

0

I would suggest:

put LastARow=WorksheetFunction.CountA(SearchSheet.Range("A:A")) once at the start and re-use LastARow rather than repeating the COUNTA many times.
Instead of looping down to final row and looking at each cell in turn, get all the data into a variant array and loop on that
Avoid the VBE UDF slowdown bug by initiating calculation from VBA

answered May 21 '20 at 13:07

Charles Williams

23,121
5
38
38

Totally offtopic, but how do you achieve the third point Charles? – Damian May 21 '20 at 14:02
1

1.) I don't follow the first item. The FinalRow which is the CountA is already running jsut once, prior to the start of the loop. The loop is going line by line checking for matching criteria in two columns. 2.) I wasn't sure how to make the ranges into a variant array (assuming 2 for the two columns with criteria). Or is it jsut 1 array with the entire data set and things move faster simply being in a variant array? 3.) What is VBE? Are you saying don't use User defined functions? – Blake Grier May 21 '20 at 14:35
@Damian https://fastexcel.wordpress.com/2011/06/13/writing-efficient-vba-udfs-part-3-avoiding-the-vbe-refresh-bug/ – Charles Williams May 22 '20 at 13:31
@Blake 1. Each of your Case statements has the same COUNTA so only do it once, and you only need to redo the COUNTA if Sheet3 is selected. 2) Using Variant Arrays is orders of magnitude faster than processing cell-by-cell 3) The VBE is the Visual Basic Editer - there is a long-standing performance bug with VBA UDFs that does a slow refresh of the VBE title bar every time a UDF is calculated. If you have thousands of calls to the UDF it will be very slow. see https://fastexcel.wordpress.com/2011/06/13/writing-efficient-vba-udfs-part-3-avoiding-the-vbe-refresh-bug/ – Charles Williams May 22 '20 at 13:40

UDF with Loops very slow and inefficient

1 Answers1