Link to home
Start Free TrialLog in
Avatar of BEI_INC

asked on

VBA/VBS Random Word Generation

I found some stuff to generate random letters, but nothing to generate a random WORD.

Can this be done?
Avatar of Richard2k4
Flag of United States of America image

have your script pull in a url.  Search url for word at specific place.

use random word site....such as
Avatar of BEI_INC


Thanks Richard, but that's WAY over my head LOL.

I have some VB experience but not that much
Avatar of David Lee

That depends on what "random word" means in this context.  If you have a list of words and you want to randomly pick out one of them, then that's pretty simple.  Load the words in an array and generate a random number between the upper and lower bounds of the array.  If instead you mean generate random letters and spell a random word with them, then that's a lot more difficult.
How about generating random letters and passing them through Words Spelling Suggestions engine?

This VBA script generates a string between 2 and 20 letters which it then passes through Word for Spelling Suggestions. If there is more than 1 suggestion, the script will randomly select one of the suggestions, however, if Word offers no suggestions, the script will re-run until it comes up with a word.

For better results, you could tweak how the script determines its random letters - for example, you could prevent letters from occurring more than once or twice in the string, or you could prevent uncommon letters (q, x, and z) from being allowed in the string.

Sub GenerateString()

Dim strRndLetters As String
Dim intRndSelection As Integer
Dim intNoL As Integer
Dim arrSuggest As SpellingSuggestions

strRndLetters = ""

' Randomly determines the number of letters to be in the random word
intNoL = (20 - 2) * Rnd() + 2

' Randomly determines of each letter in the random word
For i = 1 To intNoL
 strRndLetters = strRndLetters + Chr(CInt(Rnd * 25) + 97)

Result = GenerateWord(strRndLetters)

End Sub

Function GenerateWord(strRndLetters)

' The random letters get passed through Words Spelling Suggestions
Set arrSuggest = GetSpellingSuggestions(Word:=strRndLetters, suggestionmode:=wdSpellword)

' Checks whether any suggestions have been returned, if not, starts again
If arrSuggest.Count = 0 Then
 Exit Function
 ' Randomly selects one of the suggestions - if there is more than one
 intRndSelection = (arrSuggest.Count - 1) * Rnd() + 1
 GenerateWord = arrSuggest(intRndSelection)
 MsgBox GenerateWord
End If
End Function

Open in new window


The easiest way is to use a formula like this:


It's in the attached file.

ps. The 'random' words are not actual real words. They are only collections of random letters. If want to select real words at random then comment ID:34901814 tells you how to do it - at least in principle.

What does "qwe" mean in this context?
Avatar of BEI_INC


Sorry, I was just closing the question, what I wanted to do was a little harder than I thought.  I am doing a couple of things at once at work, was just in a hurry.
No problem.  I was just curious.
Was my method not sufficient?

It would be useful to hear some feedback from you before you decided to close the question.

However feedback even now would be good.

Avatar of BEI_INC


Sorry, I have had my hands full at work.  Psy053, I was hoping it was not as difficult, I am not THAT VB litterate, I know enough to get a couple of things done.  I was hoping it was a simple command that would do it.  I have decided to take it out of my project, as it was a passing thought anyway.
The example I provided is complete though, you shouldn't really need do anything to it.
Avatar of BEI_INC


I get "Compile Error: User-defined tyoe not definded" on

Dim arrSuggest As SpellingSuggestions

Open in new window

OP is testing supplied method, objection is to keep question open beyond the propsed closing date of today.
Are you running the script in Word, or another office application?
Avatar of BEI_INC


Hmm, the VBA was written in word, where it should work. I will see if I can re-write for excel.
Avatar of BEI_INC


Thank you very much!
Avatar of Psy053
Flag of Australia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BEI_INC


Thank you SOOOO much!  It's just what I was looking for!!!
No problems at all.

Truth be told, this has been my favorite script question to respond to so far, I think the answer is a little outside the box, and was fun to come up with.
Avatar of BEI_INC


It was very outside the box and creative.  Again, knowing VB, I was thinking it would just be Random() or something like that.  Glad you had fun! LOL!