Create Random String

[random]

Option Explicit

Public Function RL(Cnt1 As Integer, Cnt2 As Integer, MySet As Integer)
Dim Rand As String
Dim i As Integer, RndNo As Integer, XSet As Integer
Dim MyCase As Integer

Application.Volatile
Select Case MySet
Case Is = "1" 'Upper case
MyCase = 65: XSet = 26
Case Is = "2" 'Lower Case
MyCase = 97: XSet = 26
Case Is = "3" 'Leading Capital
MyCase = 97: XSet = 26
Case Is = "4" 'Text digits
MyCase = 48: XSet = 10
Case Is = "5" 'Numeric digits
MyCase = 48: XSet = 10
End Select

If MySet = 3 Then 'Set leading character of "Name"
i = i + 1
Randomize
Rand = Rand & Chr(Int((26) * Rnd + 65))
End If
'Set random length of string
RndNo = Int((Cnt2 + 1 - Cnt1) * Rnd + Cnt1)
Do
i = i + 1
Randomize
Rand = Rand & Chr(Int((XSet) * Rnd + MyCase))
Loop Until i = RndNo
RL = Rand
'Convert string to number
If MySet = 5 Then RL = RL * 1

End Function


Thank you for this, but if you wanted each character within the string to be unique within the context of the string, how could you rewrite this?

For example, I wouldn't want to generate a random string of 5 letters where 1 letter shows up more than once, e.g. ABCDD.

Thanks in advance!

[Kieran]

Try this
Option Explicit
Public Function RL(Cnt1 As Integer, Cnt2 As Integer, MySet As Integer)
Dim Rand As String
Dim i As Integer, RndNo As Integer, XSet As Integer
Dim MyCase As Integer
Dim tmp As String

Application.Volatile
Select Case MySet
Case Is = "1" 'Upper case
MyCase = 65: XSet = 26
Case Is = "2" 'Lower Case
MyCase = 97: XSet = 26
Case Is = "3" 'Leading Capital
MyCase = 97: XSet = 26
Case Is = "4" 'Text digits
MyCase = 48: XSet = 10
Case Is = "5" 'Numeric digits
MyCase = 48: XSet = 10
End Select
If MySet = 3 Then 'Set leading character of "Name"
i = i + 1
Randomize
Rand = Rand & Chr(Int((26) * Rnd + 65))
End If
'Set random length of string
RndNo = Int((Cnt2 + 1 - Cnt1) * Rnd + Cnt1)
Do
i = i + 1
Randomize
tmp = Chr(Int((XSet) * Rnd + MyCase))
While InStr(1, Rand, tmp) <> 0
tmp = Chr(Int((XSet) * Rnd + MyCase))
Wend
Rand = Rand & tmp
Loop Until i = RndNo
RL = Rand
'Convert string to number
If MySet = 5 Then RL = RL * 1
End Function

[random]

Thanks Kieran!

I do have one more question now that that's settled:

Let's say I have one string that I'd like to cross-reference with the others to sort them from high to low, by # of the original string's letters show up in the other strings. I don't think Excel has that function built in, does it?

I could create an auto filter, but can only make 2 criterion in the custom option.

[mdmackillop]

Hi random,
Welcome to VBAX

You can probably carry out successive filters in VBA. Can you post a sample file demonstrating what you are after.

Manage Attachments in the Go Advanced reply section

[random]

spreadsheet example of what i'm trying to do attached. thanks.