run once function

[Przemek Wrzesiñski]

Hi,

I have following custom function:

Public Function InterestCapitalisation(freq As String) As Double
Dim source As Range
Set source = Application.Caller()
Dim qperiod As Integer
Dim qwynik As Double
qperiod = source.Offset(0, -4).Value
qwynik = qperiod / 3
Select Case freq
Case "monthly"
InterestCapitalisation = source.Offset(0, -1).Value
Case "quarterly"
If Not (Int(qwynik) * 3 = qperiod) Then
source.Value = 0
Else
InterestCapitalisation = source.Offset(0, -1).Value + _
source.Offset(-1, -1).Value + source.Offset(-2, -1).Value

End If
End Select

Unfortunately when function is evaluating once it's firing again causing
cyclic reference.

How can I remove this cyclic reference?

Przemek

End Function

 

[Joel]

The function is not causing the cyclic reference. It is fine. The problem
is the calling cell is being referenced by one of the by another cell in the
worksheet which also is being used insdie the functiuon. For example if

if in cell D10 contained the following
=InterestCapitalisation("abc")

In cell C9 had the formula
=D10 + 1

Your code has the following statement
InterestCapitalisation = source.Offset(0, -1).Value + _
source.Offset(-1, -1).Value + source.Offset(-2, -1).Value

If D10 changes to a new value
Then C9 changes
Because your formula has an Offset(-1,-1) which is C9 the formula now
returns a different value
Changing D10 again, changing C9 again, changing the value the function
returns.

The above just keeps on repeating forever.