built-in function doesn't work properly

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

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

In article <[email protected]>,
[email protected] says...
> 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
>
>

"Calaozao" <[email protected]> wrote in message
news:[email protected]...
> 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