Excel - Random letter generator

Asked By Micheal Artindale on 13-Apr-08 06:31 PM

I am looking to create a spreadsheet that has random letter combinations
such that:
1) it can repeat itself,
2) the letter is never beside itself,
3) I can pick the letters,
4)I can pick the length of the combination
Any suggestions?
Thanks
Micheal

On Mon, 14 Apr 2008 13:16:17 -0700 (PDT), Harlan Grove wrote in
microsoft.public.excel:
[snip]
Harlan,
Thank you for your version. For my own edification (and only based on a
reading, not a test, of your code), I would like to summarise the
difference between our solutions, and ask if you agree.
My solution removes the picked letter from the original string for the
next pick; the previously picked letter is then re-added to the end of
the string. The idea is that for a random pick the position of the
letters does not matter.
Your solution is more concise. It remembers the previously picked
letter, and if it is picked in the next draw, it is replaced by the
following (in a round-robin way) letter.
Small critique: ISTM that picking the following letter in case of a
violation of rule 2) does not quite satisfy the demand for randomness.
So - is my reading of the method of your code correct? And, do you agree
that your method is somewhat less random than mine? (Not that I think it
matters much, given the vagaries of Excel's RND function. -- Writing
this made me realise only now that neither solution included a RANDOMIZE
statement. :-) )
--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Bernd P replied on 15-Apr-08 08:16 PM

Hello,
Harlan's code favours the i+1. char with double likelihood if i has
been chosen previously:
If you call foo("ABC",2), for example, then AB, BC and CA will appear
with likelihood 2/9 while AC, BA and CB will show up with only 1/9
likelihood.
This is just a "special form of randomness". If all but the previously
drawn char should appear with identical likelihood, you can use for
example:
Function RndStringNTWChar(s As String, n As Long) As String
'Create random string with non-twin characters.
's contains valid characters, n length of returned string.
Dim i As Long, j As Long, k As Long, m As Long
'Check for valid parameters
If s = "" Or n < 1 Or (Len(s) = 1 And n > 1) Then
RndStringNTWChar = CVErr(xlErrValue)
Exit Function
End If
'Prune any duplicates in s
i = 1
Do While i < Len(s)
s = Left$(s, i) & _
Application.WorksheetFunction.Substitute(Mid$(s, _
i + 1), Mid$(s, i, 1), "")
i = i + 1
Loop
i = Len(s)
'Recheck for valid parameters
If i = 1 And n > 1 Then
RndStringNTWChar = CVErr(xlErrValue)
Exit Function
End If
m = i
For n = n To 1 Step -1
j = Int(m * Rnd + 1)
If m <> i And j >= k Then j = j + 1
RndStringNTWChar = RndStringNTWChar & Mid$(s, j, 1)
k = j
m = i - 1
Next n
End Function
Regards,
Bernd

Michael Bednarek <mbATmbednarek....@BLACKHOLESPAM.NET> wrote...
...
...
Lack of randomness in my first udf was an error. I replied to Bernd P
with a fixed version.
VBA is generally inefficent with string processing, so I avoid it to
the extent I can. Thus only removing duplicate characters from the
string argument containing the possible characters.