Solved: User Defined Function not giving fresh results

[jigar1276]

Hi Experts,

I have written few UDFs and they are working fine. Once I change the values of source data the cell with UDF is not giving the fresh result. If i press "F2" followed by Enter key in the cell with UDF than it is giving the fresh result. If I close and reopen the file than also I am getting fresh results.

Please guide me how can I get my UDFs to give fresh results once the source values are changed without pressing F2 and Enter key or reopening the file?
:help

[Simon Lloyd]

You could try using Me.Calculate in the worksheet_Selectionchange event.

[Bob Phillips]

The way to get UDFs to auto-recalculate on a dat change is to pass that data to the UDF through an argument.

Give us an example of one, and we can shwo you the approach.

[jigar1276]

Thanks for the reply simon, I noticed that some UDFs are giving me fresh results, but some are not without using Me.Calculate. Why is that?

[Simon Lloyd]

I've no idea!, you need to follow xld's suggestion and supply the udf's, a sample workbook would be of great benefit!

[jigar1276]

hi Xld,

I wrote the below UDF
Function CountSIC(ShtName As String, TL As String, FormName As String, ErrType As String)
Dim i As Long, iResult As Long, AskSheet As Sheets, iLastRow As Long
iLastRow = Sheets(ShtName).Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Select Case LCase(ErrType)
Case "user"
If LCase(Sheets(ShtName).Range("S" & i).Value) = LCase(TL) And LCase(Sheets(ShtName).Range("B" & i)) = LCase(FormName) And Sheets(ShtName).Range("D" & i).Interior.ColorIndex = 39 Then
iResult = iResult + 1
End If
Case "manuel"
If LCase(Sheets(ShtName).Range("S" & i).Value) = LCase(TL) And LCase(Sheets(ShtName).Range("B" & i)) = LCase(FormName) And Sheets(ShtName).Range("D" & i).Interior.ColorIndex = 35 Then
iResult = iResult + 1
End If
Case "auditor"
If LCase(Sheets(ShtName).Range("S" & i).Value) = LCase(TL) And LCase(Sheets(ShtName).Range("B" & i)) = LCase(FormName) And Sheets(ShtName).Range("D" & i).Interior.ColorIndex = 6 Then
iResult = iResult + 1
End If
Case "ocr"
If LCase(Sheets(ShtName).Range("S" & i).Value) = LCase(TL) And LCase(Sheets(ShtName).Range("B" & i)) = LCase(FormName) And (Sheets(ShtName).Range("D" & i).Interior.ColorIndex <> 39 And Sheets(ShtName).Range("D" & i).Interior.ColorIndex <> 35 And Sheets(ShtName).Range("D" & i).Interior.ColorIndex <> 6) Then
iResult = iResult + 1
End If
End Select
Next i
CountSIC = iResult
End Function

[Bob Phillips]

Something like this, where you would call it like so

=COUNTSIC(A1:A15,B1:B15,C1:C15,D1:D15,"TL","FormName","manuel")


Function CountSIC(ByRef RangeToCheck As Range, _
ByRef RangeTL As Range, _
ByRef RangeFormName As Range, _
ByRef RangeColour As Range, _
ByVal TL As String, _
ByVal FormName As String, _
ByVal ErrType As String) As Long
Dim i As Long, iResult As Long, AskSheet As Sheets, iLastRow As Long

iLastRow = RangeToCheck.Parent.Cells(Rows.Count, RangeToCheck.Column).End(xlUp).Row

For i = 1 To iLastRow

If LCase(RangeTL.Cells(i, 1)) = LCase(TL) And _
LCase(RangeFormName.Cells(i, 1)) = LCase(FormName) Then

Select Case LCase(ErrType)

Case "user"
If RangeColour.Cells(i, 1).Interior.ColorIndex = 39 Then iResult = iResult + 1
Case "manuel"
If RangeColour.Cells(i, 1).Interior.ColorIndex = 35 Then iResult = iResult + 1
Case "auditor"
If RangeColour.Cells(i, 1).Interior.ColorIndex = 6 Then iResult = iResult + 1
Case "ocr"
If RangeColour.Cells(i, 1).Interior.ColorIndex <> 39 And _
RangeColour.Cells(i, 1).Interior.ColorIndex <> 35 And _
RangeColour.Cells(i, 1).Interior.ColorIndex <> 6 Then iResult = iResult + 1
End Select
End If
Next i
CountSIC = iResult
End Function


but be aware that a clour change will not trigger this or any other UDF.

[jigar1276]

Thanks Xld, you said that color change will not trigger this, actually i wanted to trigger this on color. anyway thanks again for your inputs.

[Bob Phillips]

as I said no UDF will do that because a colour change does not force a recalculation.