Function to rearrange/shuffle a word in excel

[vicmoment]

Please I need help creating a function that can shuffle the characters of a word.

e.g Peak as kepa, faithful as thfaiutl


Trust that the gurus would solve it

 

[Hui]

Vicmoment


Copy the following Function into a Code Module in VBA


to use just enter


=Scramble(C1)

or

=Scramble("Chandoo")

[pre]

Public Function scramble(Target As Variant)

On Error Resume Next
Dim CL As New Collection
Application.Volatile
scramble = ""

Do Until CL.Count = Len(Target)
R = Int(1 + Rnd * Len(Target))
CL.Add R, CStr(R)
Loop

For I = 1 To CL.Count
scramble = scramble & Mid(Target, CL(I), 1)
Next

End Function

[/pre]

 

[vicmoment]

You guys are out of this world. Brilliant.


Thanks

 

[vicmoment]

PLease help explain this part of the code


CL.Add R, CStr(R)


Thanks

 

[Hui]

Vicmoment


In the Scramble code, CL is a VBA Collection, think of it as a container to hold what ever is put inside.


CL.Add R, CStr(R)


CL.add adds items to the container


The item added is R, which in this case is a random number between 1 and the length of your word which is being scrambled


CStr(R) converts R to a String and that is used as a Key String to identify the object R in the Collection.


The neat part of this code is that the Collection won't accept two objects with the same Key CStr(R) and so the first Do/Loop keeps going until it has found a random Key for each character in the Word


The second For/Next loop then retrieves the items from the collection in order 1 to Len(Word), scrambling the word

 

[vicmoment]

Thanks so much for the explanations.


What prevents the collection from not accepting same Cstr(R)?


That was the part that made me ask for explanations in the first place.

 

[Hui]

Like a database the collection each entry has an optional Key

Once a key is used it is unique and can't be reused