built-in function doesn't work properly

[Guest]

This simple Function doesn't update the result.... I have to edit the cell to
calculate new result. The easiest way i found is to prees 'F2' and 'Enter'
for each cell that have this function...

Thanks for any help


Function CupaoActual(RangeDatasCupao As Range) As Byte
Dim j As Byte

For j = 1 To RangeDatasCupao.Count
If RangeDatasCupao(1, j) > Range("TodayDate") Then
CupaoActual = j - 1
Exit Function
End If
Next

End Function

 

[Tushar Mehta]

You should pass as arguments *all* ranges you use in the function. In
your case, you are not passing the range named TodayDate.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

 

[Fredrik Wahlgren]

This simple Function doesn't update the result.... I have to edit the cell to
calculate new result. The easiest way i found is to prees 'F2' and 'Enter'
for each cell that have this function...

Thanks for any help


Function CupaoActual(RangeDatasCupao As Range) As Byte
Dim j As Byte

For j = 1 To RangeDatasCupao.Count
If RangeDatasCupao(1, j) > Range("TodayDate") Then
CupaoActual = j - 1
Exit Function
End If
Next

End Function

There are two solutions

1) Pass the Range "TodayDate" to the UDF or use get it within the UDF. As it
is, the UDF is made in such a way that it prevents Excel from understanding
the it should recalculate all calls to CupaoActual when something within the
range does change. Maybe you should pass it as Date rather than range

2) After the declaration of j, insert Application.Volatile (True) This will
force Excel to always recalculate this function whether Excel thinks it
needs to or not.

The first solution is the better.

/Fredrik