用户定义的工作表函数返回#VALUE!错误而不是单元格颜色索引 [英] User defined worksheet function returns #VALUE! error instead of cell color index
问题描述
'该函数返回指定单元格的DISPLAYED颜色
公共功能cellDisplayCol(ByRef c As Range)As Long
cellDisplayCol = c.DisplayFormat.Interior.ColorIndex
结束功能
子测试()
MsgBox cellDisplayCol(ActiveCell)
End Sub
测试按预期工作,但是当我使用该功能在工作表单元格中:
= cellDisplayCol(A1)
它返回#VALUE!错误...
任何想法为什么?我应该怎么解决?
非常感谢!
在msdn上,您似乎无法在UDF中使用 .DisplayFormat
请注意
DisplayFormat
属性在用户定义的函数中不起作用。例如,在返回单元格的内部颜色的工作表函数中,您使用类似于以下行:
Range(n).DisplayFormat.Interior.ColorIndex
。当工作表函数执行时,它返回一个#VALUE!
错误。
http://msdn.microsoft.com /en-us/library/office/ff838814(v=office.15).aspx
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!
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
这篇关于用户定义的工作表函数返回#VALUE!错误而不是单元格颜色索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!