Change conditional Boolean expression or the function itself into normal equal expression

user_17797849 0
Viewed 35

Using the below Boolean function to check if the cell value equals "M8D" or "M8P" or "M20") 

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

And I use it like below and it works: 

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

What I need to change the last line : 

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

Into

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

But I got

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

In advance any learning help will be appreciated

vba excel function arrays
1 Answers

Flag When a String From a List Matches

  • If you would use a function, it would read the array of strings each time per row making it inefficient.
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
  • If you wanna get 'fancy' about it:
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
user_98140690
Related