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
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