UDF to keep current value on FALSE from IF statement

[dhrugupta]

Hi,

I am using the below UDF to retain old value when if condition is false.

Formula I am using is :
=IF(AND(C9="Short",C10="Call",C11=9600),INDEX(OptionChain,MATCH(C11,OptionChain[Strike Price],0),9),CurrentValue())

Code of CurrentValue() is :
Function CurrentValue() As Variant
CurrentValue = Application.Caller.Value
If Val(CurrentValue) Then
CurrentValue = Val(CurrentValue)
ElseIf IsNumeric(CurrentValue) Then
CurrentValue = Val(CurrentValue)
End If
End Function

When IF statement condition is met, cell changes as per index search value but when condition is not met, value remains unchanged but I am getting message circular reference error.

Kindly help me correct my UDF. Thanks in advance


Rgds,
Dhruv

[Kaper]

See the hint in a yellow banner near the top of the page.

[MrShorty]

Even though you have tried to bury the circular reference inside of the UDF, Excel still knows that there is a circular reference. You either need to enable iteration (Excel options) or you need to eliminate the circular logic (at least in the spreadsheet).

One advantage of enabling iteration, is that you can accomplish this without any VBA at all. =IF(condition,formula,reference to current cell). The main disadvantage to enabling iteration is, if you ever accidentally create a circular reference, you will no longer get the circular reference warning.

If you decide not to enable iteration, then you need to do something to eliminate the circular logic from the spreadsheet. In this case, that probably means processing the If condition in VBA (maybe in a calculate event procedure).

Please Login or Register  to view this content.

Your choice how you would prefer to do this. We would be able to be more specific in our help if you provide a sample file as suggested.

[dhrugupta]

I have attached a sample worksheet. In case I change strike, at CMP there is a reference error cause I am using CurrentValue() UDF there.

Thanks for your help …

Rgds,
Dhruv

[MrShorty]

Since I routinely operate with iteration enabled, your spreadsheet works just fine for me. I can replace the currentvlaue() UDF with F9, and the formula works just fine without needing VBA. Are you allowed to enable iteration? Are you comfortable with that option?

[dhrugupta]

Hi,

Thanks for your response. I do know the option of enabling iteration but I thought it would be better if I could do it without enabling it as I would not know in case I add another formula with circular reference.