arrays - 将条件 bool 表达式或函数本身更改为正常的相等表达式

Question

使用以下 bool 函数检查单元格值是否等于“M8D”或“M8P”或“M20”)

Function m8_field(ByVal plf As String) As Boolean
     m8_field = (plf = "M8D" Or plf = "M8P" Or plf = "M20")
End Function

我像下面这样使用它并且它有效:

Dim arg As Range: Set arg = ActiveSheet.Range("D1:E20")
 Dim arr: arr = arg.Value2
  Dim r As Long
   For r = 1 To UBound(arr)
 If m8_field(arr(r, 2)) Then arr(r, 1) = "Good"
   Next
   

我需要更改最后一行 :

If m8_field(arr(r, 2)) Then arr(r, 1) = "Good"

进入

If arr(r, 2) = m8_field Then arr(r, 1) = "Good"

但我得到了

Compile error:Argument not optional on this part (m8_field)

提前任何学习帮助将不胜感激

Answer 1

标记列表中的字符串何时匹配

如果您使用一个函数，它会每次每行读取字符串数组，从而使其效率低下。

Sub m8_field_Sub()
     
    Const GoodStringsList As String = "M8D,M8P,M20"
     
    Dim GoodStrings() As String: GoodStrings = Split(GoodStringsList, ",")
     
    Dim arg As Range: Set arg = ActiveSheet.Range("D1:E20")

    Dim arr As Variant: arr = arg.Value2
     
    Dim r As Long
    
    For r = 1 To UBound(arr, 1)
        If IsNumeric(Application.Match(CStr(arr(r, 2)), GoodStrings, 0)) Then
            arr(r, 1) = "Good"
        'Else
        '    arr(r, 1) = "Bad" ' or e.g. arr(r, 1) = ""
        End If
    Next

    ' Write back to the range.
    'arg.Value = arr

End Sub

如果你想“看中”它:

Function GetGoodStrings() As String()
    Const GoodStringsList As String = "M8D,M8P,M20"
    GetGoodStrings = Split(GoodStringsList, ",")
End Function

Function m8_field(ByVal plf As String, GoodStrings() As String) As Boolean
     m8_field = IsNumeric(Application.Match(plf, GoodStrings, 0))
End Function

Sub TestTheFunctions()
    
    ' Read only once.
    Dim GoodStrings() As String: GoodStrings = GetGoodStrings
    
    Dim arg As Range: Set arg = ActiveSheet.Range("D1:E20")
    
    Dim arr As Variant: arr = arg.Value2
    
    Dim r As Long
    Dim plf As String
    
    For r = 1 To UBound(arr, 1)
        plf = CStr(arr(r, 2))
        If m8_field(plf, GoodStrings) Then ' needs parameters
            arr(r, 1) = "Good"
        'Else
        '    arr(r, 1) = "Bad" ' or e.g. arr(r, 1) = ""
        End If
    Next

    ' Write back to the range.
    'arg.Value = arr

End Sub