我想重新创建显示为用户定义函数的IF语句

2024-05-15 • 问答

下面我有下面的IF OR语句，它可以完成我想要的工作，但是如果可以将其创建为用户定义的函数，我希望这样做

基本上，我有2列数据，这些列将永远只有红色琥珀色或绿色作为它们的值，并且我希望UDF能够查看列中的两个单元格并返回最坏情况，例如例如，如果一列为琥珀色而一列为绿色，则UDF将返回琥珀色，因为它比绿色差

=IF(OR(AZ8="Red",AY8="Red"),"Red",IF(OR(AZ8="Amber",AY8="Amber"),"Amber","Green"))

这是我到目前为止所拥有的

Function CalculateOverallRAG(CellRef1 As Range,CellRef2 As Range,RAGStatus As String) As String

  If CellRef1 = "Red" Or CellRef2 = "Red" Then
    RAGStatus = "Red"
  ElseIf CellRef1 = "Amber" Or CellRef2 = "Amber" Then
    RAGStatus = "Amber"
  Else
    RAGStatus = "Green"
  End If

  CalculateOverallRAG = RAGStatus

End Function

OP呈现的代码看起来工作正常。但是，如果输入是多个像元范围而不是单个像元怎么办？或者，如果传递了诸如“ Green”之类的文本文字而不是单元格引用怎么办？

如果要向最终用户显示UDF，则需要确保它对任何可能的输入都是安全的。

这是我想到的替代方法，其中涉及大量验证。这种情况显然不值得。

Option Explicit

Public Function CalculateOverallRAG( _
    ByVal Color1 As Variant,_
    ByVal Color2 As Variant _
) As Variant

    Dim ValidColor1 As Variant
    Dim ValidColor2 As Variant

    If Not TryConvertColorArg(Color1,ValidColor1) Then
        CalculateOverallRAG = CVErr(xlErrValue)
        Exit Function
    End If

    If Not TryConvertColorArg(Color2,ValidColor2) Then
        CalculateOverallRAG = CVErr(xlErrValue)
        Exit Function
    End If

    If ValidColor1 = "Red" Or ValidColor2 = "Red" Then
        CalculateOverallRAG = "Red"
    ElseIf ValidColor1 = "Amber" Or ValidColor2 = "Amber" Then
        CalculateOverallRAG = "Amber"
    Else
        CalculateOverallRAG = "Green"
    End If

End Function

Private Function TryConvertColorArg(ByVal Color As Variant,ByRef Out As Variant) As Boolean

    'Single Cell is acceptable
    If TypeOf Color Is Range Then
        If Color.Count = 1 Then
            Color = Color.Value
        Else
            TryConvertColorArg = False
            Exit Function
        End If
    End If

    'String value is acceptable
    If VarType(Color) = vbString Then
        Out = Color
    Else
        TryConvertColorArg = False
        Exit Function
    End If

    If Out = "Red" Or Out = "Amber" Or Out = "Green" Then
        TryConvertColorArg = True
    Else
        TryConvertColorArg = False
    End If

End Function

我想重新创建显示为用户定义函数的IF语句

PZY97649 回答：我想重新创建显示为用户定义函数的IF语句

大家都在问