Excel's formula value

[sphinx_ps]

I have some custom formula =CFORMULA(...).
I put it in some cell, for example A1.

When this formula executes I have a value, but if some condition in the formula not true I don't want any value. I want the value in A1 to remain the same.

For example:
=IF(C1>5;M1;A1)
If I write like this I have cycling. It is not good. I want just to return the value that already was in A1. I change the =CFORMULA with =IF just to explain the problem. Actually I have my own formula.

How is it possible to do?

[JeanRage]

Hi,

It seems to me you should be including this condition to your own UDF ...

HTH

[sphinx_ps]

Hi,

If I'm including condition with the cell A1 (where the formula CFORMULA is located) I have a cycling.

[DonkeyOte]

It's a circular reference, enable iteration.

[sphinx_ps]

But I don't want circular references. Is there a way to avoid this?

[DonkeyOte]

Not in the context you describe, no... (ie functions and use of function result in function)

[DonkeyOte]

Without knowing the ins and outs of what you're doing you will in essence need to utilise Events if you wish to use this type of approach (at least that's my belief).

The simplest solution would be store an independent copy of the "current" calculation output in another cell - ie a constant with no precedents.

If we use the simple example as outlined previously:

Please Login or Register  to view this content.

and choose to convert this to a UDF but avoid circular references you could do something along the lines of:

Please Login or Register  to view this content.

Called like so:

Please Login or Register  to view this content.

As you can see this is not goign to work at this point given we have nothing in N1 and are not referring to the current A1 result... this is where we use Events

Please Login or Register  to view this content.

The above event will fire after the UDF has been calculated - so post calculation the current result is stored as a constant in N1 thus the next time the UDF is invoked the value in N1 will be used as rngFalse - ie where the test fails and "current" result is to persist.

To be the best of my knowledge (which is limited) there's no way of doing the same, avoiding circular references using only functions be they native / UDF.

[sphinx_ps]

Thank you for your answers

I've got what you suggested.
But I can't use worksheet functions in VBA.

I found that if I write my function like this (CFORMULA in A1) I'll avoid the cycling.

Please Login or Register  to view this content.

A1: =CFORMULA(B1;C1)

But maybe you know some Excel's inbuilt options to use:

A1: =CFORMULA(B1;C1;A1)

Maybe some formula to do:

A1: =CFORMULA(B1;C1;SomeFormula(A1))

Has Excel got such formulas?

[DonkeyOte]

Per forum rules please edit your prior post & encase your UDF above within CODE tags

Interesting re: use of .Text over .Value - I didn't know that, thanks.

Regards your question - if the cell is an explicit precedent of itself the function is always circular....
your prior function works because it is not an explicit reference (and because it uses Text rather than Value)

[DonkeyOte]

If your question relates to substituting the explicit A1 reference in the code itself such that instead it refers to the cell being calculated (be it A1, A2 etc...) - use:

Please Login or Register  to view this content.

[sphinx_ps]

Yes. This is it.
Thank you for your answers.