Hi All,

Once again I am being lazy. Does someone have either a function or an
elegant approach to take a text string and randomise it (to create a password
for WinZip in this instance)?

Thanks in Advance and Best Regards,

Matt

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

That looks just the ticket.

However is there something else I need to do to get EXcel to "recognise" the
function? (It isn't working).

I tried adding "Option Explicit" at the top of the module to attempt to
emulate another function I have in another module that works.

"Max" wrote:

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

OK - I've read the Help - it's out of scope cos it's in Percy.

"Max" wrote:

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

What is "Percy"?


Gord Dibben MS Excel MVP

On Mon, 2 Feb 2009 16:44:00 -0800, veryeavy
wrote:

OK - I've read the Help - it's out of scope cos it's in Percy.

"Max" wrote:

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

Percy is (London?) drug slang for personal, as in "for personal use".

In this case I am referring to the Personal Macro workbook.

HTH.

"Gord Dibben" wrote:

What is "Percy"?


Gord Dibben MS Excel MVP

On Mon, 2 Feb 2009 16:44:00 -0800, veryeavy
wrote:

OK - I've read the Help - it's out of scope cos it's in Percy.

"Max" wrote:

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

Once again I am being lazy. Does someone have either a function or an
elegant approach to take a text string and randomise it (to create a
password for WinZip in this instance)?

One way is

(a) put the letters A-Z in A1:A26 (or your favorite characters)

(b) put =RAND() in B1:B26

(c) sort A:B by column B

(d) take the A1:A9 values as a 9-letter pasword (or more or fewer)

Not elegant and not function, but I find it fast and easy.

You must be a mind reader - that is exactly what I was doing with a touch of
"TRANSPOSE" thrown in.

Suggest you try what Max suggested as it is very simple to operate (and if
you haven't already experimented with user-defined functions most
educational).

Thanks!

"MyVeryOwnSelf" wrote:

Once again I am being lazy. Does someone have either a function or an
elegant approach to take a text string and randomise it (to create a
password for WinZip in this instance)?

One way is

(a) put the letters A-Z in A1:A26 (or your favorite characters)

(b) put =RAND() in B1:B26

(c) sort A:B by column B

(d) take the A1:A9 values as a 9-letter pasword (or more or fewer)

Not elegant and not function, but I find it fast and easy.

Thought it might be some type of slang.

If the UDF is in Personal.xls you must preface with that filename.

=Personal.xls!SCRAMBLE(text)


Gord

On Mon, 2 Feb 2009 17:19:01 -0800, veryeavy
wrote:

Percy is (London?) drug slang for personal, as in "for personal use".

In this case I am referring to the Personal Macro workbook.

HTH.

"Gord Dibben" wrote:

What is "Percy"?


Gord Dibben MS Excel MVP

On Mon, 2 Feb 2009 16:44:00 -0800, veryeavy
wrote:

OK - I've read the Help - it's out of scope cos it's in Percy.

"Max" wrote:

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