Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA/VBS Random Word Generation

Posted on 2011-02-15
23
Medium Priority
?
1,782 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 3
  • +2
23 Comments
 
LVL 3

Expert Comment

by:Richard2k4
ID: 34901148
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
ID: 34901209
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
ID: 34901814
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Psy053
ID: 34903868
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
ID: 34905814
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
ID: 34905827
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
ID: 34927585
What does "qwe" mean in this context?
0
 

Author Comment

by:BEI_INC
ID: 34927614
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
ID: 34927905
No problem.  I was just curious.
0
 
LVL 14

Expert Comment

by:Psy053
ID: 34928579
Was my method not sufficient?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34928609
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
 

Author Comment

by:BEI_INC
ID: 34929326
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
ID: 34939698
The example I provided is complete though, you shouldn't really need do anything to it.
0
 

Author Comment

by:BEI_INC
ID: 34942622
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
ID: 34947377
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
ID: 34947432
Are you running the script in Word, or another office application?
0
 

Author Comment

by:BEI_INC
ID: 34951463
Excel
0
 
LVL 14

Expert Comment

by:Psy053
ID: 34951760
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
ID: 34951786
Thank you very much!
0
 
LVL 14

Accepted Solution

by:
Psy053 earned 2000 total points
ID: 35004043
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
ID: 35006998
Thank you SOOOO much!  It's just what I was looking for!!!
0
 
LVL 14

Expert Comment

by:Psy053
ID: 35007262
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
ID: 35007304
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

722 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