Udf acceleration

Question

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

First thing I would recommend is to ditch the `Application.Volatile`. Not like you change values within your UDF, but the [function](https://learn.microsoft.com/en-us/office/vba/api/excel.application.volatile) recalculates when ANY cell on ANY workbook in the application window changes value. — JvdV, Aug 27 '18 at 10:37
Why looping? Application.match will do. ++ Give Cells(x+29..etc as a parameter and not hardcoded in your UDF — EvR, Aug 27 '18 at 10:54

score 1 · Accepted Answer · answered Aug 27 '18 at 13:00

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

Udf acceleration

1 Answers1