VBA Script Translation

[Huizar]

Hello Gurus,

I need help translating some VBA language into simple English language. I have an idea what the output is but just need simple translation as to what is happening specifically. I know the code is looking for specific text. Thank ahead.

    Dim regEx As Object
    Dim allMatches As Object
    Set regEx = CreateObject("vbscript.regexp")
   
    regEx.Pattern = ("K\d{8}")
    regEx.Global = True
    regEx.ignorecase = True
   
    Set allMatches = regEx.Execute(targetString)
   
    If allMatches.Count > 0 Then
        isKstring = True
    Else
        isKstring = False
    End If
   
    'clean up
    Set regEx = Nothing
    Set allMatches = Nothing
 
End Function
 
Public Function isCase(targetString As String) As Boolean
 
    Dim regEx As Object
    Dim allMatches As Object
    Set regEx = CreateObject("vbscript.regexp")
   
    Dim regEx2 As Object
    Dim allMatches2 As Object
    Set regEx2 = CreateObject("vbscript.regexp")
   
    regEx.Pattern = ("case")
    regEx.Global = True
    regEx.ignorecase = True
   
    regEx2.Pattern = ("no")
    regEx2.Global = True
    regEx2.ignorecase = True
   
    Set allMatches = regEx.Execute(targetString)
    Set allMatches2 = regEx2.Execute(targetString)
   
    
    If allMatches.Count > 0 And allMatches2.Count > 0 Then
        isCase = True
    Else
        isCase = False
    End If
   
    'clean up
    Set regEx = Nothing
    Set allMatches = Nothing
 
End Function
 
Public Function isCRTS(targetString As String) As Boolean
 
    Dim regEx As Object
    Dim allMatches As Object
    Set regEx = CreateObject("vbscript.regexp")
   
    regEx.Pattern = ("CRTS")
    regEx.Global = True
    regEx.ignorecase = True
   
    Set allMatches = regEx.Execute(targetString)
   
    If allMatches.Count > 0 Then
        isCRTS = True
    Else
        isCRTS = False
    End If
   
    'clean up
    Set regEx = Nothing
    Set allMatches = Nothing
 
End Function

 

[JLGWhiz]

Someone has developed some interactive User Defined Functions that use Regular Expression to locate data and produce a value somewhere on a worksheet. You can read the code almost as if it were a text statement for the most part. It is written more in VB Script than in VBA. If basically looks for a string pattern, determined by the User input and returns a boolean result based comparison of the regular expression. Each function is for a different determination but uses the same method for comparison.

 

[Huizar]

Hey JLG. Thank for your response. I get what you are saying. im trying to figure out what is it telling it to do. For example, i see that it tells it to look for "CRTS", "CASE", etc. That is as much as i know. Can you translate it into an excel formula so I have a better idea what is going on. Im the worst at reading code. Thanks for you help.

 

[RoryA]

im trying to figure out what is it telling it to do. For example, i see that it tells it to look for "CRTS", "CASE", etc. That is as much as i know.

That is it in a nutshell. What more do you want to know?

It's basically the equivalent of an ISNUMBER(SEARCH()) combination but with better pattern matching - the first one looks for K followed by an 8 digit number.

 

[Huizar]

Hey Rory,

I'm don't really know how to read vbscript, so i just looked at the words that look familiar to me. So, let me see if I am translating this correctly, if the text string has K with an 8 digit number then count it as 1, if nothing then 0. If the text string has "case" in it then count it as 1, if nothing then 0. If the text string has "CRTS" in it then count it as 1, if nothing then 0. Am i framing it right?

Thanks

 

[RoryA]

The second one has to contain both "case" and "no", and the function returns True or False accordingly.

 

[Huizar]

Thank you.