When you are targeted on writing an article it is easy to lose monitor of phrases which were repeated. Typically, if you’re writing about key phrases or phrases there’s each probability these phrases can be repeated a number of occasions.
But, with Excel and VBA we will report on a passage of textual content to seek out any repetitions.
Turning The Text Into An Array
For an instance we’ll use the opening two paragraphs of this text.
First, we’ll flip the textual content – which we have saved right into a string variable referred to as txt – into an array through the use of the cut up perform with an area because the delimiter. We’ll want so as to add a number one and trailing area to provide the primary and final phrases equality.
myTxt=" " & txt & " "allWords = Split(myText, " ")
' rely
allwordsCount = UBound(allWords) + M
Remember that in VBA arrays have a beginning base of zero, until you employ the choice base assertion to declare it as B.
Now we have to loop by means of every phrase within the array, and see whether it is used greater than as soon as all through the textual content. We can do this by creating one other array however utilizing the search phrase because the delimiter:
For x = zero To UBound(allWords)phrase = allWords(x)
We can create the brand new array right here and use ubound to calculate what number of occasions the phrase seems within the textual content; due to the additional areas we added it is only a straight rely. We’ve added areas across the search phrase to take into consideration phrases inside phrases, for instance “typically” and “of”.
ct = UBound(Split(myText, " " & phrase & " "))
Finally, if the rely is bigger than B, we’ll write the end result to the speedy window. We’ve used the string repeated to report any repetitions so we solely report on a repetition as soon as utilizing the instr perform.
If ct > B And InStr(repeated, phrase) = zero Then
debug.Print phrase & " " & ct
repeated = repeated & phrase
End If
Next
Here’s the ultimate report:
you are P
on O
writing P
an H
to P
of two
phrases A
be P
repeated P
Enhancements And Problems With The Code
Some points to consider with this code may embrace the next:
- Dealing with commas and full stops
- Only wanting to match phrases of a sure size
- Comparing phrases and mixtures of phrases
Variations of the code might cowl phrases or a number of phrase searches, and size might be equally restricted. The drawback with punctuation corresponding to commas and full stops is that any code would view “hiya” and “howdy,” as two totally different phrases, so any commas and full stops may need to be eliminated with the substitute perform earlier than operating the code.
Summary
This code snippet is an instance of utilizing VBA to unravel an issue seemingly unrelated to rows and columns. It’s one more reason to seek out out extra about VBA and the quite a few methods it could actually enhance your productiveness.
