VBA Express : Excel - Create Random Strings

Excel

Create Random Strings

Ease of Use

Easy

Version tested with

2000 / 2002

Submitted by:

mdmackillop

Description:

Creates a random string of letters in lengths between chosen limits

Discussion:

Inspired by VBAX member, DRJ, this macro creates text you can use for testing macros and other formulas and functions in Excel.

Code:

instructions for use

			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

How to use:

Copy the code above.
Open Excel.
Alt + F11 to open VBE.
Insert-Module.
Paste the code into the code window at right.
Set the MyCase value for Upper or Lower case as indicated and desired.
Close VBE (Alt + Q or press the X in the top right hand corner).
In a cell type: =RL(x,y,z) where x and y represent the minimum and maximum string lengths and z represents the text type. For z: 1 = Upper case, 2 = Lower case, 3 = Initial capital, 4 = Numeric text, 5 = numbers.
If required, Copy the new data strings and then Paste Special-Values to prevent further changes.

Test the code:

In a cell type =RL(3,8,3) for a string such as "Mbvhf" ; Copy down to fill a range of cells. You should get a series of strings between 3 and 8 characters long, with leading capitals. For a combination you can enter =RL(3,3,4) & " - " & RL(6,6,1) for "012 - GHEDSJ"

Sample File:

Strings.zip 10.2KB

Approved by mdmackillop

This entry has been viewed 223 times.