Multiple String Comparison At once

Question

I have a silly piece of code that tests if a variable is a certain string:

If Target.Cell.Value = "1M" or Target.Cell.Value = "1N" or Target.Cell.Value = "1O" Then
...
End If

Is there a way to compress that If statement - ie compare against a list or something like that?

After testing for the different values do you apply a different action depending on the values or just need to know if the target contains any of the values tested? — EEM, Nov 27 '17 at 15:05

score 7 · Accepted Answer · answered Nov 27 '17 at 14:32

7

Select Case Target.Cell.Value
Case "1M","1N","10" 
     ...
end select

answered Nov 27 '17 at 14:32

C. Henke

151
1
13

Like this, the cleanest – keynesiancross Nov 27 '17 at 15:20

Vityata · Answer 2 · 2017-11-27T15:44:30.520

3

This is how I would do it:

Option Explicit

Public Function valueInArray(myValue As Variant, myArray As Variant) As Boolean

    Dim cnt As Long

    For cnt = LBound(myArray) To UBound(myArray)
        If CStr(myValue) = CStr(myArray(cnt)) Then
            valueInArray = True
            Exit Function
        End If
    Next cnt

End Function

Public Sub TestMe()

    Debug.Print valueInArray("test", Array("ta", "to", 1, 2, "test"))
    Debug.Print valueInArray("test1", Array("ta", "to", 1, 2, "test"))

End Sub

The valueInArray returns a boolean result, telling you whether the myValue is present in the array.

edited Nov 27 '17 at 15:44

answered Nov 27 '17 at 14:35

Vityata

42,633
8
55
100

Why don't you just do the test *while* you're looping through the array, then you can skip processing the whole array if your value is found? – Rory Nov 27 '17 at 15:21
@Rory - looping through array is really fast + the early exit from the loop still counts as the same complexity - https://stackoverflow.com/questions/6967738/what-is-big-o-of-a-loop – Vityata Nov 27 '17 at 15:25
But adding the function call to Match does add unnecessary overhead if you're going to loop through the whole array anyway. I guess I just don't see the point of doing both. – Rory Nov 27 '17 at 15:27
@Rory - now I remember why I did it :) - in general, the only think the loop does is to convert the `MyArray` to a string array. Then, it simply tries the `Match()` and thus it returns the answer. – Vityata Nov 27 '17 at 15:30
@Rory - if you remove the loop completely, and you leave only `valueInArray = Not IsError()` the following would be false: `valueInArray(3, Array(3, 4, 5, 6))` – Vityata Nov 27 '17 at 15:34
1

I can see that - I just don't see why you wouldn't simply test the values as you go and stop if it's found. What's the benefit of looping through the whole array and then calling `Match` which has to loop through the whole array again? I didn't say remove the *loop* - I said do the check inside the loop and remove the `MATCH`. – Rory Nov 27 '17 at 15:35
@Rory - it was probably because the loop was added after the `Match()` part :) But, yes, w/o the `Match()` it is probably better. – Vityata Nov 27 '17 at 15:45

Rory · Answer 3 · 2017-11-27T15:17:54.520

2

You could do something like this:

If Instr(1, ",1M,1N,1O,", "," & Target.Value & ",") <> 0 Then
...
End If

If you don't want the comparison to be case sensitive, add , vbTestCompare to the end the Instr check.

edited Nov 27 '17 at 15:17

answered Nov 27 '17 at 14:32

Rory

32,730
5
32
35

1

@Vityata Yup - I forgot to add a key bit when copying from the original code! – Rory Nov 27 '17 at 15:18

score 2 · Answer 4 · answered Nov 27 '17 at 14:46

This is a variation of @Vityata's excellent answer, which uses a ParamArray rather than a variant array argument:

Function Matches(s As String, ParamArray targets()) As Boolean
    Dim i As Long
    For i = 0 To UBound(targets)
        If s = targets(i) Then
            Matches = True
            Exit Function
        End If
    Next i
    Matches = False
End Function

Used like:

Sub test()
    Dim s As String
    s = "1N"
    If Matches(s, "1M", "1N", "10") Then
        Debug.Print s & " is valid"
    Else
        Debug.Print s & " is invalid"
    End If
    s = "2M"
    If Matches(s, "1M", "1N", "10") Then
        Debug.Print s & " is valid"
    Else
        Debug.Print s & " is invalid"
    End If   
End Sub

Output:

1N is valid
2M is invalid

score 0 · Answer 5 · answered Nov 27 '17 at 15:29

To test if a cell value equals a list of values try this:

Create a constant with the list of values to be tested:
In this case let's create two list
```
Const kComp1 As String = "1M,1N,10"  
Const kComp2 As String = "1,0,M,N"  
```

Then apply this function to the cells we need to test

Function Cell_CompareValue(sCell As String, sComp As String) As Boolean
Dim aComp As Variant
    aComp = Chr(167) & Replace(sComp, Chr(44), Chr(167)) & Chr(167)
    Cell_CompareValue = InStr(aComp, Chr(167) & sCell & Chr(167))
    End Function

Example
Let's apply the function for both constants to the values in the range B2:B8 showing the results for both lists in columns C and D

Sub TEST_Values()
Dim rgTrg As Range, rgCll As Range

    Set rgTrg = ThisWorkbook.Sheets("Sht(4)").Range("B3:B9")

    For Each rgCll In rgTrg.Cells
        With rgCll
            .Offset(0, 1).Value = Cell_CompareValue(.Value2, kComp1)
            .Offset(0, 2).Value = Cell_CompareValue(.Value2, kComp2)
    End With: Next

    End Sub

Results:

Multiple String Comparison At once

5 Answers5