Conditional update of a cell

[Guido De Bouver]

hello all,

standard Excel formula's do not support the conditional update of a cell.

there is no stndard formula, eg =IF(condition;value) which would update the value of a cell only if a certain condition is true, and leave the value unchanged if the condition was false.

I have tried to do this with standard Excel logic but always ended up with circular references.

Therefor I am trying with Excel DNA. Is this possible with Excel DNA ?

thanks for your much appreiacted thoughts.

Also thanks for this wonderfull software - it helped me to implement things that were deemed impossible before !!!

[Govert van Drimmelen]

Hi Guido,

Calling ExcelReference.GetValue() on the result of xlfCaller does give you the previous value from the cell.

You can only read the current value during normal recalculations, not when re-entering the formula with F2 or the function wizard.

For example, make this function:

    [ExcelFunction(IsMacroType = true)]

    public static double UseIfBiggerThanCurrentValue(double input)

    {

        var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

        var currentValue = (double)caller.GetValue();

        if (input > currentValue)

            return input;

        else

            return currentValue;

    }

 

Then put into cell A1 the formula: =UseIfBiggerThanCurrentValue(E1)

Then change the value of E1, and see how A1 updates.

If E1 is bigger than the old value in A1, the bigger value is used.

If E1 is not bigger, then the old value of A1 is retained.

-Govert

[Guido De Bouver]

thanks Govert, this worked fine, you're too kind.

However, it still doesnt work properly - sorry if I am asking stupid questions...

I have seen that the UDF only works if i declare the return function as an object. If I try to declare the return array as a boolean, the UDF doesnt even show up in my functions list in Excel.

This does not work :

    <ExcelDna.Integration.ExcelFunction(Category:="GuidoTools", Description:="Set/reset function", IsMacroType:=True)> _

    Public Function GuidoTools_SetResetWitboolean( _

           <ExcelDna.Integration.ExcelArgument(AllowReference:=True)> ByVal setcondition As Boolean, _

           <ExcelDna.Integration.ExcelArgument(AllowReference:=True)> ByVal resetcondition As Boolean) _

           As Boolean(,)

        Dim x(0, 2) As Boolean

        Dim caller As ExcelReference = XlCall.Excel(XlCall.xlfCaller)

        Dim a As Boolean = CBool(caller.GetValue(0, 0))

        If setcondition Then

            x(0, 0) = setcondition

        Else

            x(0, 0) = a

        End If

        x(0, 1) = setcondition

        x(0, 2) = resetcondition

        Return x

    End Function

This works...

    <ExcelDna.Integration.ExcelFunction(Category:="GuidoTools", Description:="Set/reset function", IsMacroType:=True)> _

    Public Function GuidoTools_SetResetWitboolean( _

           <ExcelDna.Integration.ExcelArgument(AllowReference:=True)> ByVal setcondition As Boolean, _

           <ExcelDna.Integration.ExcelArgument(AllowReference:=True)> ByVal resetcondition As Boolean) _

           As object(,)

However, when using the second approach, I have problems to cast a boolean into an object.

So question :

How can I declare the return array to be of a particular type, and not only object ??? Any attempt to return a boolean array seem to fail on my end.

thnaks for your much appreicated help, and congrats for this wonderfull piece of software.

guido

 

[Govert van Drimmelen]

Hi Guido,

 

Excel-DNA only supports object[,] and double[,] array types.

 

Other conversions will have to be done in your code.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.