Why I see #VALUE! in excel while my function work well

Question

Hi everyone!

I write a function in Microsoft Visual Basic that gets a range of cells and another cell as argument and search in range and count how many cells have background color same as the second argument even if the color was set by a format condition.

the function works well when using "cell.DisplayFormat.Interior.Color" for compare colors but show "#VALUE!" instead output!

in another case when using "cell.Interior.Color" for compare colors it shows output but doesn't count correct because some cells set color with format condition and "cell.Interior.Color" just return default background color not the color that changes with format condition.

I have no idea what is happening, please help me.

here is the code of function with "cell.DisplayFormat.Interior.Color":

and the action of the code ( as you can see it count cells correct but don't show it ):

and here is the code of function with "cell.Interior.Color":

the action of this code ( as you can see it count cells wrong but show it well ):

Answer 1

Thanks for your answer.

Are you sure about it?

That's so weird.

I agree it is weird.

The function code will work if the function is called by a Sub routine or by calling it from "View Macros". It just won't work called from a worksheet cell.

There were comments in the Excel help file about this, but I can't find them now.

Also FWIW, a function called from a worksheet cell cannot alter/change another worksheet cell.

This was implemented many years ago as a security precaution.

'---
Nothing Left to Lose

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

(free excel programs)

Answer 2

Re: Range.DisplayFormat property

The Range.DisplayFormat property is disabled in a user designed function (UDF) when called from a worksheet cell.

That is the way it was designed, you can't do anything about it.

'---

Nothing Left to Lose

Answer 3

Thanks a lot for the help.

Answer 4

Thanks for your answer.

Are you sure about it?

That's so weird.