Using VBA To Acknowledge Repeated Words In The Passage Of Text

When you're dedicated to writing and submitting articles you can forget words that have been repeated. Typically, when you're covering keywords and phrases or words there's every chance individuals words will probably be repeated several occasions.

But, with Stick out and VBA we could group of a passage of text to discover any repetitions.

Turning The Writing Into A Variety

With an example we'll utilize the opening two sentences need to know ,.

First, we'll turn the writing - which we've saved in to a string variable referred to as txt - into a variety while using split function getting an area since the delimiter. We'll need to provide a leading and trailing space to possess first and last words equality.

myTxt=" " & txt & " "

allWords = Split(myText, " ")

' count

allwordsCount = UBound(allWords)   1

Bear in mind that in VBA arrays have a very beginning base of , unless of course obviously you employ the option base statement to declare it 1.

We now have to loop through each word inside the array, and discover whether it's used more frequently than once with the text. Are likely to that by creating another array but while using the search word since the delimiter:

For x =  To UBound(allWords)

word = allWords(x)

We could make the new array here and rehearse ubound to calculate the amount of occasions the word appears inside the text because of the extra spaces we added it's just a vertical count. We've added spaces across the search word to consider words within words, for example "frequently" and "of".

ct = UBound(Split(myText, " " & word & " "))

Finally, once the count is much more than 1, we'll write the finish result for the immediate window. We've used the string repeated to record any repetitions and then we only group of a repetition once while using the instr function.

If ct > 1 And InStr(repeated, word) =  Then

debug.Print word & " " & ct

repeated = repeated & word

Finish If


Here's the best report:

you're 2

on 3

writing 2

an 5

to 2

of two

words 3

be 2

repeated 2

Enhancements And Difficulties With The Code

Some issues to think about using this code can include the following:

Dealing with commas and full stops

Only trying to compare words from the certain length

Evaluating phrases and mixtures of words

Variations in the code could cover phrases or multiple word searches, and length may be similarly restricted. The problem with cfor instance commas and full stops is always that any code would view "hello" and "hello," as two different words, so any commas and full stops might need to be removed while using replace function prior to running the code.


This code snippet is a great one of utilizing VBA to solve a problem apparently unrelated to rows and posts. It's one other reason to learn more about VBA as well as the numerous ways it might increase your productivity.