Solved

VBA/VBS Random Word Generation

Posted on 2011-02-15
23
1,685 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 500 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Changing a few Outlook Options can help keep you organized!
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

630 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