Search code examples
vbaexcelcell

User defined worksheet function returns #VALUE! error instead of cell color index

Can someone please help me understand the problem with this function:

'the function returns the DISPLAYED color of specified cell
Public Function cellDisplayCol(ByRef c As Range) As Long
   cellDisplayCol = c.DisplayFormat.Interior.ColorIndex
End Function

Sub test()
   MsgBox cellDisplayCol(ActiveCell)
End Sub

the test works as expected but when I use the function in worksheet cell: =cellDisplayCol(A1)

it returns #VALUE! error...

Any ideas why? What should I fix?

Many thanks!

Solution

  • After checking on msdn, it appears you cannot use .DisplayFormat in UDF's:

    Note that the DisplayFormat property does not work in user defined functions. For example, in a worksheet function that returns the interior color of a cell, you use a line similar to:
    Range(n).DisplayFormat.Interior.ColorIndex. When the worksheet function executes, it returns a #VALUE! error.

    http://msdn.microsoft.com/en-us/library/office/ff838814(v=office.15).aspx