View Full Version : Solved: User Defined Function not giving fresh results
jigar1276
07-24-2008, 02:26 AM
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
07-24-2008, 02:31 AM
You could try using Me.Calculate in the worksheet_Selectionchange event.
Bob Phillips
07-24-2008, 02:40 AM
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
07-24-2008, 02:44 AM
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
07-24-2008, 02:47 AM
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
07-24-2008, 02:48 AM
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
07-24-2008, 03:59 AM
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
07-24-2008, 05:19 AM
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
07-24-2008, 06:24 AM
as I said no UDF will do that because a colour change does not force a recalculation.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.