vbaexceluser-defined-functionsacceleration

Udf acceleration


There is a Udf that works well , but slowly . I know how to accelerate Sub :

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Is this suitable for Function ? If not , how can I speed up Udf ?

Function Fav(Diapozon As Range) As Long
    Application.Volatile

    Dim n As Long

    For x = 1 To 4
        For y = 0 To 1
            If Diapozon.Value = Cells(31, 3).Value Then
                n = 0
                Exit For
            End If

            If Diapozon.Value = Cells(x + 29, y + 10).Value Or Diapozon.Offset(0, 1).Value = Cells(x + 29, y + 10).Value Then
                n = 1
            End If
         Next y
     Next x

     Fav = n
End Function

Solution

  • I agree with one of the comments about losing the Application.Volatile. However, I'm going to elaborate a bit more than what would fit in a comment.

    As @JvdV pointed out, using Application.Volatile will cause a recalculation whenever anything changes. This can majorly slow down calculations (and workbooks, as more or larger ones are opened).

    However, I can also see from your Cells(..., ...).Value's that with how the UDF is currently programmed, it may not always accurately update without the Application.Volitile if one of the values in the hardcode-referenced cells' changes.

    One alternative, would be to re-work the UDF to include the ranges it's checking Diapozon against as additional input parameters. By including those ranges as parameters in the actual UDF, it tells Excel that the UDF depends on those ranges and should be recalculated whenever one of them changes.

    For example, in the UDF code below, nextDiapozon is Diapozon.Offset(0, 1), nonMatch is Range("C31") equivalent to Cells(31, 3), and rngCompare is Range("J30:K33") equivalent to the cells you were cycling through:

    Function Fav(Diapozon As Range, nextDiapozon As Range, nonMatch As Range, rngCompare As Range,) As Long
        Dim n As Long 'Default start value = 0
        Dim cell_var as Variant
    
        If Diapozon.Value <> nonMatch.Value then
            For each cell_var in rngCompare
                If Diapozon.Value = cell_var.Value Or nextDiapozon.Value = cell_var.Value Then
                    n = 1
                End If
            Next cell_var
        End If
    
        Fav = n
    End Function