excel - 通过 udf 应用条件格式时遇到问题

Question

因此，我正在开发一个 udf，我最终将把它做成一个插件来帮助处理数据，如果可以突出显示单元格以引起用户对任何问题的注意，那将是首选。

udf如下：

Function AnalyzeData()
Dim Headers() As Variant
Dim Row1 As Variant

Headers = Array(Row1, Row2, Row3, Row4, Row5)

Row1 = Array("Final Curve", "", "Cr", "Fe", "Ni", "Cu", "Zn", "As", "Ag", "Cd", "Sn", "Pb")

Row2 = Array("Level", "Nominal", 100, 100, 100, 100, 100, 100, 100, 100, 100, 100)
col1 = Array("Level", "Cal 1", "Cal 2", "Cal 3", "Cal 4", "Cal 5", "Cal 6", "Cal 7", "Cal 8", "Cal 9", "Cal 10", "", "", "Level", "Cal 1", "Cal 2", "Cal 3", "Cal 4", "Cal 5", "Cal 6", "Cal 7", "Cal 8", "Cal 9", "Cal 10", "", "")
col2 = Array("", "Nominal", "0.1", "0.2", "0.5", "1.0", "2.5", "5.0", "10", "20", "30", "50", "", "", "Nominal", "0.1", "0.2", "0.5", "1.0", "2.5", "5.0", "10", "20", "30", "50", "", "")

Dim Location() As Variant

Select Case TypeName(Application.Caller)
    Case "Range"
        Location = Array(Application.Caller.Row, Application.Caller.Column)
        If Location(0) = 1 And Location(1) = 1 Then
            BuildArrays
            MethodCheck
            On Error Resume Next
            threecf

            AnalyzeData = Row1(0)
        Else
            Select Case Location(1)

                Case 1 - 2
                    Case 1
                        AnalyzeData = col1(Location(0) - 2)
                    Case 2
                        AnalyzeData = col2(Location(0) - 1)
                Case Else

                    Select Case Location(0)

                        Case 1, 14
                            AnalyzeData = SelectedAnalytes(Location(1) - 3)
                        Case 2, 15
                            AnalyzeData = Row2(Location(1) - 1)
                        Case 3 To 12
                            AnalyzeData = Round(STDArray(Location(0) - 2,Location(1) - 3), 3)
                        Case 13, 26
                            AnalyzeData = ""
                        Case 16 To 25
                            If CalValues(Location(1) - 3, Location(0) - 15) = "" Then
                                AnalyzeData = "N/A"
                            ElseIf Round(STDArray(Location(0) - 15, Location(1) - 3) / CalValues(Location(1) - 3, Location(0) - 15) * 100, 0) <= 100 + Acceptance And Round(STDArray(Location(0) - 15, Location(1) - 3) / CalValues(Location(1) - 3, Location(0) - 15) * 100, 0) >= 100 - Acceptance Then
                            AnalyzeData = Round(STDArray(Location(0) - 15, Location(1) - 3) / CalValues(Location(1) - 3, Location(0) - 15) * 100, 0) & "%"
                            Else
                                AnalyzeData = "Fail acceptance"
                            End If

                        End Select
                End Select
        End If

    Case "String"
        AnalyzeData = Application.Caller
    Case "Error"
        AnalyzeData = "Error"
    Case Else
        AnalyzeData = "unknown"
End Select

End Function

而“threecfs”子是

Public Sub threecf()
Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
Set rg = Range("C2", Range("C2").End(xlDown))

'clear any existing conditional formatting
rg.FormatConditions.Delete

'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlGreater, "=1")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlLess, "=$a$1")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "=$a$1")

'define the format applied for each conditional format
With cond1
.Interior.Color = vbGreen
.Font.Color = vbWhite
End With

With cond2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With

With cond3
.Interior.Color = vbYellow
.Font.Color = vbRed
End With

End Sub

我是通过在线寻求帮助找到的。所以没有线条

            On Error Resume Next
            threecf

我的函数可以正常工作并适当地分析数据，但是当单元格 A1 调用“threecf”子程序时，如果没有“on error resume next”，它就不会继续出错，并且根本不应用条件格式。

如果“threecf”中的范围更改为

rg=cells(1,1)

那么该函数仍然需要“on error resume next”，但应用了条件格式但不正确。它添加了条件，但没有为每个条件设置格式。

如果我通过 F5 运行“threecfs”，那么它可以正常工作。

我对通过 udf 应用条件格式的理解是否不正确，因为这无法完成？

理想情况下，我的 udf 将计算来自我的数据的值的恢复百分比，并且将应用条件格式来突出显示有问题的值，但这是否需要由每个单元格而不是应用它的起始单元格来完成？

标签： excelvbauser-defined-functionsconditional-formatting