UDF in conditional formatting formula

[Сергей Антохин]

Hello. I wrote UDF with excelDNA. I want to use it in conditional formatting formula, but I get an error "this type of reference cannot be used in a Conditional Formatting Formula"

[Govert van Drimmelen]

Could you send a bit more details on your function, and how you’re calling it from the Conditional Formatting?

 

I think Excel has some restrictions on array references in Conditional Formatting expressions – maybe there are other limitations too.

 

-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 https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Сергей Антохин]

Any functions. At first I wanted to use the function which has been successfully operating in cells, but got this error. Then I created a function that always returns true. In the cells it works. But conditional formatting gives the same error.

[Сергей Антохин]

[ExcelFunction(Description = "Проверка ячейки на достоверность")]

        public static bool UaTest(

            [ExcelArgument(Name = "Текст", Description = "Текст ячейки")] object reference

        )

        {

            return true;

        }

(on Russian "ИСТИНА" = "TRUE")

среда, 24 января 2018 г., 19:34:37 UTC+3 пользователь Govert van Drimmelen написал:

[Govert van Drimmelen]

OK, I tried it a bit and what worked was to make a Defined Name with the function call in, and then use the name in the conditional formatting.

1. I make this function 

        [ExcelFunction]

        public static object IsMultipleOfThree([ExcelArgument(AllowReference =true)] object input)

        {

            if (input is ExcelReference er &&

                er.GetValue() is double d)

            {

                return d % 3 == 0;

            }

            return false;

        }

2. I create a Defined Name:

Name: IsMult3

Refers To: =IsMultipleOfThree

3. I create a Conditional Formatting Rule for cell A1:

Format values where this formula is true: =IsMult3(A1)

And add some interesting format.

4. Test by changing the cell values to multiples of three or not.

I don't know if there is a way to use the UDF without the Defined Name.

I saw this trick in another post from this week: https://groups.google.com/forum/#!topic/exceldna/WgU2r56LylE

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Сергей Антохин [macs...@gmail.com]
Sent: 25 January 2018 11:45 AM
To: Excel-DNA
Subject: Re: [ExcelDna] UDF in conditional formatting formula

--

[louwill]

Hi Govert,

Creating a defined name to a UDF works, but I've noticed that when you have multiple cells containing a reference to a UDF, the UDF gets called when only changing a single cell (i.e. it's recalculating all cells referencing that named range in the workbook).  Short of caching cell values and evaluating the source caller in the UDF, are you aware of a way to fix this in an easier manner?

Thanks,

Will

To post to this group, send email to exc...@googlegroups.com.