VBA: Regular Expressions

[Converemon]

I need help creating a function to compare strings; probably using regular expressions since it is a bit tricky. We have six strings:
String 1: "V06+K73+U55+M67"
String 2: "!M43"
String 3: "!U67"
String 4: "V06"
String 5: ""
String 6: ""

In this case, "!" means 'not' so this statement would evaluate to True. There will always be five columns of substrings, but any number of them can be blank. If they are, they should return True as well. The above should return False only if it had "!K73" or "D44" or something similar. I use this to test my regular expressions:

Function Test_traMLFB(sMLFB, sPattern) As Boolean
Dim RegX As Object
Dim RegMC
Set objRegX = CreateObject("VBScript.RegExp")
With objRegX
    .Pattern = sPattern
    If .Test(sMLFB) Then
        Set RegMC = .Execute(sMLFB)
        Test_traMLFB = True
    Else
        Test_traMLFB = False
    End If
End With
End Function

But I really don't think it will cut it for this. I have also tried variations of "Like" and "InStr" but it just doesn't work. Does anyone have any suggestions?

 

[Trevor G]

Perhaps using CASE Statements will provide a solution. Also is it necessary for VBA if a VLOOKUP would do what you want?

http://www.mrexcel.com/forum/excel-questions/465380-visual-basic-applications-case-statement.html

 

[Rick Rothstein]

I need help creating a function to compare strings; probably using regular expressions since it is a bit tricky. We have six strings:
String 1: "V06+K73+U55+M67"
String 2: "!M43"
String 3: "!U67"
String 4: "V06"
String 5: ""
String 6: ""

In this case, "!" means 'not' so this statement would evaluate to True. There will always be five columns of substrings, but any number of them can be blank. If they are, they should return True as well. The above should return False only if it had "!K73" or "D44" or something similar.

If I understand your question correctly, then I think this function will work the way you want...

Function MLFB(StrIn As String, ParamArray SubStr() As Variant) As Boolean
  Dim V As Variant
  For Each V In SubStr
    If Left(V, 1) = "!" Then
      If InStr(StrIn, Mid(V, 2)) Then Exit Function
    Else
      If InStr(StrIn, V) = 0 Then Exit Function
    End If
  Next
  MLFB = True
End Function

Just pass in the "big string" as the first argument and substring as arguments 2 on up (there is no need to specify the empty strings for missing substrings, but you can if you like. This function will also work as a UDF (user defined function) in case you want use it in a worksheet formula.