Solved Find Whole Word in Text

[MajP]

This is in relation to the demo I am doing using VBA extensibility.

How to find unused object/items?

@KitaYama I just updated a module three times and couldn't get the date to change in MSysObjects. When you look by turning on details in the Nav Pane, the modified date for ALL FOUR of the modules in this database changed to 8:12 today. I cannot explain why. Here is from a different db that...

www.access-programmers.co.uk

Does anyone have code to find a whole word in text? Want to pass in a line of text and search text and return if matched.

I am looking for strings of "code" in code so these are some of the possibilities.

If looking for "form1" it could be sorrounded by (),[],.,!,', or space

Forms!Form1.Controls
Forms("form1")
Forms_Form1
[Form1]
'Form1'

Also the thing I am searching might have these same characters within the string such as if looking for these strings
"Docmd.Openform"
"Forms!Form1"

Thanks. I assume this can be done with regex (not my strength) where whole word can be lead or followed with a non alpha numeric. But not sure how you do not return
Form123.
or Form1

My simple search got rid of all non alphnumeric characters and replaced with space. This worked fine for finding
Form1
but can no longer search for "Debug.print"
I can improve my search by only removing characers not in the search string (95%) solution.

 

[ebs17]

@Josef P. already gave you the hint to use regular expressions:

sPattern = "\bform1\b"

\b ... the comparison is on a boundary between word characters (\w) and non-word characters (\W), so it must be a word boundary

So only whole words are found, but not parts of words like in Instr.
My RegEx library of usable methods in VBA:

"Intelligente" Textanalyse - MS-Office-Forum

Es gibt eine Reihe von Aufgaben, da stößt die Anwendung und Kombination von VBA-Standardfunktionen (Left, Right, Mid, Instr, InstrRev, Split) an Grenzen, insbesondere wenn die zu analysierenden Textstellen variabel sind und sich nur schwer bis gar nicht in (herkömmliche) Regeln fassen lassen...

www.ms-office-forum.net

 

[Josef P.]

Try: [\[!."_']([Ff]orm1)[!."\s\]'] or (?:\b|Form_)[Ff]orm1\b .. Note: Form_Form1 not Forms_Form1
simple online test: https://regex101.com/

I let ChatGPT help me with (complex) RegExp patterns more and more often. It usually takes several attempts, but most of the time it works. Especially if you give examples and expected hits.

 

[MajP]

Thanks. I think I was overthinking it. Even if the search word begins or ends in a boundary (Me!, Docmd., Form_, etc) it still works. Only exception I find is if the user wanted to search for usage where the search word is entered using single or double quotes. They are looking for
reports("report1") but not reports!report1

They will just have to enter without quotes and get both back.