Solved Regular Expression to detect end of word

[NauticalGent]

One for me as well please John. Email in my signature line

Done. Sent a quid pro quo request too!

 

[NauticalGent]

Me too, please. Email in my sig. PM sent. Grazie mille!

Done!

 

[strive4peace]

thanks, John!

 

[isladogs]

Thanks John.
No such thing as a free lunch in my case then

 

[sxschech]

This might answer post#29

need to know where the word IS

Copied as is from my code, so there is a default pattern, but the code allows for providing your own pattern. First function finds the start position and second function returns the length of the text.

Examples:

intDatePos = RegExPatternStart(Me.txtSubject)

intDateLen = (RegExPatternLen(stFileName, "^(\s|-){0,3}"))
stFileName = Mid(stFileName, intDateLen)

Function RegExPatternStart(strValue As String, Optional PatternText As String, Optional blnCase As Boolean = True, Optional blnBoolean = True) As String
'CHAMATCH
'Get the start position of the pattern within string
'https://stackoverflow.com/questions/8301622/excel-vba-regex-match-position
'Get the length of the pattern within string
'https://developer.rhino3d.com/guides/rhinoscript/vbscript-regexp-objects/
'20190701
    Dim objRegEx As Object
    Dim strPattern As String
    Dim objPosition As Object
    Dim strPosition As String
    Dim strLength As String
    
    ' Create regular expression.
    Set objRegEx = CreateObject("VBScript.RegExp")
    'objRegEx.Pattern = "[\d]+[\/\-\.][\d]+[\/\-\.][\d]+"
    If Len(PatternText) = 0 Then
        strPattern = "[\d]+[\/\-\.][\d]+[\/\-\.][\d]+"
    Else
        strPattern = PatternText
    End If
    objRegEx.Pattern = strPattern
    objRegEx.IgnoreCase = blnCase

    ' Do the search match.
    Set objPosition = objRegEx.Execute(strValue)
    strPosition = objPosition(0).FirstIndex
Debug.Print objPosition(0).Value
    RegExPatternStart = strPosition
End Function

Function RegExPatternLen(strValue As String, Optional PatternText As String, Optional blnCase As Boolean = True, Optional blnBoolean = True) As String
'CHAMATCH
'Get the start position of the pattern within string
'https://stackoverflow.com/questions/8301622/excel-vba-regex-match-position
'Get the length of the pattern within string
'https://developer.rhino3d.com/guides/rhinoscript/vbscript-regexp-objects/
'20190701
    Dim objRegEx As Object
    Dim strPattern As String
    Dim objPosition As Object
    Dim strPosition As String
    Dim strLength As String
    
    ' Create regular expression.
    Set objRegEx = CreateObject("VBScript.RegExp")
    'objRegEx.Pattern = "[\d]+[\/\-\.][\d]+[\/\-\.][\d]+"
    If Len(PatternText) = 0 Then
        strPattern = "[\d]+[\/\-\.][\d]+[\/\-\.][\d]+"
    Else
        strPattern = PatternText
    End If
    objRegEx.Pattern = strPattern
    objRegEx.IgnoreCase = blnCase

    ' Do the search match.
    Set objPosition = objRegEx.Execute(strValue)
    strLength = objPosition(0).Length

    RegExPatternLen = strLength + 1
End Function

 

[strive4peace]

thanks to everyone who offered help on this adventure ... Uncle Gizmo, Maj, theDBguy, Gasman, James, Isaac, HalloweenWeed , Tera, Arne, John, Colin, sxschech ... and Mick for inspiring me to do it!

So I ended up not using regular expressions because I didn't see how performance could be enhanced. I colored code the same way I did before, for myself, but now I made an add-in so it is even more convenient to use. It should work in any version of Access greater than or equal to 2007. If you see any errors or bugs, please tell me!

If your code is long, uncheck box to color keywords or it might be too many characters to post!

The default formatting style is BBCode using CODE=rich, but you can easily change this to use HTML tags for posting on a website.

Tool > Add-in > Color VBA Code - Green comments, Blue Keywords
https://msaccessgurus.com/tool/Addin_ColorCode.htm

and here is the pretty code (on the same page) for this tool, made with this tool!
https://msaccessgurus.com/tool/Addin_ColorCode.htm#Code

You can choose to use BBCode tags or HTML, or something else.

 

[Gasman]

Thank you Crystal.

 

[arnelgp]

very nice, can't wait to test it.
there is minor glitch.
people new to access doesn't know to avoid reserved words
on their code (afaik, this is typically valid unless you are warned
by the vba editor on the first place).
type/paste this code as an example to the Color Coder.
you can also try some variations using keywords:

Private Sub t()

    Dim label As String
    Dim textbox As String
    Dim class As String
    
    label = "arnel "
    textbox = "gp"
    class = label & textbox
    Debug.Print class
End Sub

 

[strive4peace]

very nice, can't wait to test it.
there is minor glitch.
people new to access doesn't know to avoid reserved words
on their code

thanks, Arne, good idea to identify reserved words too! ... with an option to color them red (or something else). I also thought about putting a function list in too. For instance, IIF is in the keyword list but its not a keyword. Its active flag is false. I left a few in that should have otherwise come out. I checked a lot of code to be as thorough as I could to note the words that turn blue -- been posting it about a year on my site with colors, so I've changed the initial list I started with a lot. Thanks for sharing

 

[deletedT]

@strive4peace very nice.
It may be too much of a request. But is it possible to allow dragging and changing the size of codeOrig? I know it's not a native behavior for Access text boxes, just thought you may have ideas on that.

 

[strive4peace]

Arne, what is the regular expression pattern to find the first single quote that doesn't have a double quote before or after it?

so it wouldn't find this "'"

 

[strive4peace]

@strive4peace very nice.
It may be too much of a request. But is it possible to allow dragging and changing the size of codeOrig? I know it's not a native behavior for Access text boxes, just thought you may have ideas on that.

good idea to change the width, Tera, but rather than click and drag would be easier to use a command button. When someone has a larger monitor, it can easily get wider! As of now, both of the code boxes have anchoring set to grow as the form gets taller -- and the code result also gets wider (there is no internal setting to split growing wider between two controls side-by-side).

You can press SHIFT-F2 for the Zoom box when looking at a value, which can be resized and formatted

thanks, Tera

 

[strive4peace]

ps, Tera, you can also go into the form design and make the original code control wider for yourself (smile)

 

[deletedT]

You can press SHIFT-F2 for the Zoom box when looking at a value, which can be resized and formatted

thanks, Tera

I'd forgotten about this. Thanks

 

[arnelgp]

    sPattern = "^([^\" & Chr(34) & "])*[\'](.)*"
    If Trim(p & "") = "" Then Exit Function
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = sPattern
        Debug.Print .test(p) '<==   it will return True if it the line
                             '      has spaces and single (') + other text(or no text after) OR
                             '      begins with (') + other text(or no text after).
                                  
    End With

 

[strive4peace]

thanks Arne. Is it possible to find the POSITION of the first single quote that doesn't have double quotes around it?

 

[strive4peace]

Arne, trying to get a more efficient way to find a comment ~

 

[theDBguy]

Now that I see how to get the words with Regular Expressions (thanks theDBguy especially, I would love to know ... perhaps there is a way to return the entire substring that was tested? with the outside text that wasn't counted in the word? ... and can I specify a replacement phrase in the regular expression? Small words like IN are everywhere!

Hi Crystal (@strive4peace). Just playing with it some more, I was able to extract more information about each match that maybe you can use to figure out the characters that failed the match.

 

[theDBguy]

Hi Crystal (@strive4peace). Just playing with it some more, I was able to extract more information about each match that maybe you can use to figure out the characters that failed the match.

View attachment 81684

In contrast, the above image was using the pattern: "\b\w+\b", while this one below is using the pattern: "\W"

 

[theDBguy]

In contrast, the above image was using the pattern: "\b\w+\b", while this one below is using the pattern: "\W"

View attachment 81687

Ah, so by combining both patterns as: "\b\w+\b\W", I got this. Maybe, this is what you wanted?