从工作表函数传递范围时获取单元格内部颜色失败 [英] Getting cell Interior Color Fails when range passed from Worksheet function
本文介绍了从工作表函数传递范围时获取单元格内部颜色失败的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图编写一个可以从一个单元格调用的简单函数,如果给定单元格的背景具有特定的背景颜色,则该函数将返回.
I am trying to get write a simple function one can call from a cell that would return if the background of a given cell has a specific background color.
从子例程调用时,此函数按预期工作,但从工作表调用时,此函数失败.在行
This function works as expected when called from a sub-routine, but fails when called from the worksheet. At the line
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"问题的解决方法.
Here's a workaround to the "DisplayFormat not available in a UDF" problem.
它使用 Evaluate
避开UDF上下文
It uses Evaluate
to side-step the UDF context
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相关的代码
Note also you really don't need all that RGB-related code
这篇关于从工作表函数传递范围时获取单元格内部颜色失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文