a function or an elegant approach to take a text string
and randomise it (to create a password for WinZip in this instance)?

One good way that I know of is to install & use
the UDF Scramble below by Jim Cone ..
(UDF = user defined function)

To install the UDF:
Press Alt+F11 to go to VBE
Click Insert Module
Copy n paste the UDF into the code window (whitespace on right)
Press Alt+Q to get back to Excel

In Excel,
Assuming your data to be scrambled is in A1 down,
Put in B1: =scramble(A1,x)
Copy B1 down to return the random scrambles

Press F9 to re-scramble
The "x" can be any entry, number or text (any length), as the second
argument


' Apr 14, 2002 - Created - James Cone - San Francisco USA
' Scrambles the order of the data in a single-cell.
' Nov 02, 2003 - Last update
'--------------------------------------
Function SCRAMBLE(Optional ByRef UserText As Variant, _
Optional ByRef Everytime As Variant) As String
On Error GoTo Scorched
Dim i As Long
Dim Num As Long
Dim NewPosition As Long
Dim Temp As String
If IsMissing(UserText) Then
SCRAMBLE = "No data"
Exit Function
' No quotes automatically generates an error from the worksheet.
ElseIf IsError(UserText) Then
SCRAMBLE = "Error - try adding quote marks around your entry."
Exit Function
End If
Application.Volatile (Not IsMissing(Everytime))
If TypeName(UserText) = "Range" Then UserText = UserText(1).Value
Num = Len(UserText)
If Num 0 Then
For i = 1 To Num
Temp = Mid$(UserText, i, 1)
NewPosition = Int(Num * Rnd + 1)
Mid$(UserText, i, 1) = Mid$(UserText, NewPosition, 1)
Mid$(UserText, NewPosition, 1) = Temp
Next ' i
SCRAMBLE = UserText
Else
SCRAMBLE = "No data" 'Can result from entering ""
End If
Exit Function
Scorched:
SCRAMBLE = "Error " & Err.Number
End Function
'--------------------
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---