我正在尝试编写一个可以从一个单元格调用的简单函数,如果给定单元格的背景具有特定的背景颜色,则该函数将返回。
从子例程调用时,此函数按预期工作,但从工作表调用时,此函数失败。在生产线上
IntColor = Cell.DisplayFormat.Interior.Color
这是所有代码
Option Explicit
Public Function GetCellRGB(Rng As Range) As Integer()
Dim Result(1 To 3) As Integer
Dim Cell As Range
Set Cell = Rng.Cells(1, 1)
Dim IntColor As Integer
' when called from worksheet, function exits here with a #VALUE error
IntColor = Cell.DisplayFormat.Interior.Color
Result(1) = IntColor Mod 256 ' red
Result(2) = IntColor \ 256 Mod 256 ' green
Result(3) = IntColor \ 65536 Mod 256 ' blue
GetCellRGB = Result
End Function
Public Function IsColor(Rng As Range, R As Integer, G As Integer, B As Integer) As Boolean
Dim Vals() As Integer
Vals = GetCellRGB(Rng)
If R = Vals(1) And G = Vals(2) And B = Vals(3) Then
IsColor = True
Else
IsColor = False
End If
End Function
' This works as expected
Sub ColorTest()
Dim Rng As Range
Set Rng = ThisWorkbook.ActiveSheet.Range("A1")
Debug.Print IsColor(Rng, 255, 0, 0)
End Sub
这是“无法在UDF中使用DisplayFormat”问题的解决方法。
它用于Evaluate
回避UDF上下文
Public Function DFColor(addr)
DFColor = Range(addr).DisplayFormat.Interior.Color
End Function
Function CFColorMatches(rng As Range, R As Long, G As Long, B As Long)
CFColorMatches = (rng.Parent.Evaluate("DFColor(""" & rng.Address & """)") = RGB(R, G, B))
End Function
还要注意,您真的不需要所有与RGB相关的代码
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句