Solved

VBA/VBS Random Word Generation

Posted on 2011-02-15
23
1,408 Views
Last Modified: 2012-05-11
I found some stuff to generate random letters, but nothing to generate a random WORD.

Can this be done?
0
Comment
Question by:BEI_INC
  • 8
  • 8
  • 3
  • +2
23 Comments
 
LVL 3

Expert Comment

by:Richard2k4
Comment Utility
have your script pull in a url.  Search url for word at specific place.

use random word site....such as

http://watchout4snakes.com/creativitytools/RandomWord/RandomWordPlus.aspx
0
 

Author Comment

by:BEI_INC
Comment Utility
Thanks Richard, but that's WAY over my head LOL.

I have some VB experience but not that much
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
Hi, BEI_INC.

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.
0
 
LVL 14

Expert Comment

by:Psy053
Comment Utility
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)
Next

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
 GenerateString
 Exit Function
Else
 ' 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

0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
BEI_INC,

The easiest way is to use a formula like this:

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))

It's in the attached file.

Patrick
random-words-01.xls
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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.

Patrick
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
What does "qwe" mean in this context?
0
 

Author Comment

by:BEI_INC
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
No problem.  I was just curious.
0
 
LVL 14

Expert Comment

by:Psy053
Comment Utility
Was my method not sufficient?
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
BEI_INC,

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

However feedback even now would be good.

Patrick
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:BEI_INC
Comment Utility
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.
0
 
LVL 14

Expert Comment

by:Psy053
Comment Utility
The example I provided is complete though, you shouldn't really need do anything to it.
0
 

Author Comment

by:BEI_INC
Comment Utility
I get "Compile Error: User-defined tyoe not definded" on

Dim arrSuggest As SpellingSuggestions

Open in new window

0
 
LVL 14

Expert Comment

by:Psy053
Comment Utility
OP is testing supplied method, objection is to keep question open beyond the propsed closing date of today.
0
 
LVL 14

Expert Comment

by:Psy053
Comment Utility
Are you running the script in Word, or another office application?
0
 

Author Comment

by:BEI_INC
Comment Utility
Excel
0
 
LVL 14

Expert Comment

by:Psy053
Comment Utility
Hmm, the VBA was written in word, where it should work. I will see if I can re-write for excel.
0
 

Author Comment

by:BEI_INC
Comment Utility
Thank you very much!
0
 
LVL 14

Accepted Solution

by:
Psy053 earned 500 total points
Comment Utility
Sorry for the delay.

I have re-written the script so that it works from Excel. It is worth noting though that it still actually uses Word to get the Spelling Suggestions (that all happens in the background, hidden from the user).

Also, I made a small tweak to this version so that it will only return a word with 4 or more characters - this has greatly improved the quality of results in my testing.


Sub RandomWord()
 
Dim Word As Object
Dim strRndLetters As String
Dim strRndWord As String
Dim intRndSelection As Integer
Dim intNoL As Integer
Dim cntArray As Integer
Dim arrSuggest

Set Word = CreateObject("Word.Application")
Word.Visible = False
Word.Documents.Add


Do Until cntArray > 0 And Len(strRndWord) > 4 And Not InStr(strRndWord, " ")
 strRndLetters = ""
 
 intNoL = (20 - 2) * Rnd() + 2
 
 For i = 1 To intNoL
  strRndLetters = strRndLetters + Chr(CInt(Rnd * 25) + 97)
 Next
 
 Word.Selection.Text = strRndLetters
 
 Set arrSuggest = Word.GetSpellingSuggestions(Word:=Word.Selection.Text, suggestionmode:=wdSpellword)
 cntArray = arrSuggest.Count
 
 If arrSuggest.Count > 0 Then
    intRndSelection = (arrSuggest.Count - 1) * Rnd() + 1
    strRndWord = arrSuggest(intRndSelection)
 End If
Loop

MsgBox strRndWord
Word.Quit SaveChanges:=wdDoNotSaveChanges

End Sub

Open in new window

0
 

Author Closing Comment

by:BEI_INC
Comment Utility
Thank you SOOOO much!  It's just what I was looking for!!!
0
 
LVL 14

Expert Comment

by:Psy053
Comment Utility
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.
0
 

Author Comment

by:BEI_INC
Comment Utility
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!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now