Microsoft Office Tutorials and References
In Depth Information
Text Manipulation Functions
Function SCRAMBLE(text As Variant) As String
‘ Scrambles its string argument
Dim TextLen As Long
Dim i As Long
Dim RandPos As Long
Dim Temp As String
Dim Char As String * 1
If TypeName(text) = “Range” Then
Temp = text.Range(“A1”).text
ElseIf IsArray(text) Then
Temp = text(LBound(text))
Else
Temp = text
End If
TextLen = Len(Temp)
For i = 1 To TextLen
Char = Mid(Temp, i, 1)
RandPos = WorksheetFunction.RandBetween(1, TextLen)
Mid(Temp, i, 1) = Mid(Temp, RandPos, 1)
Mid(Temp, RandPos, 1) = Char
Next i
SCRAMBLE = Temp
End Function
This function loops through each character and then swaps it with another character in a
randomly selected position.
You may be wondering about the use of Mid . Note that when Mid is used on the right side of an
assignment statement, it is a function. However, when Mid is used on the left side of the
assignment statement, it is a statement. Consult the Help system for more information about Mid .
Returning an acronym
The ACRONYM function returns the first letter (in uppercase) of each word in its argument. For
example, the following formula returns IBM:
=ACRONYM(“International Business Machines”)
The listing for the ACRONYM Function procedure follows:
Function ACRONYM(text As String) As String
‘ Returns an acronym for text
Dim TextLen As Long
Dim i As Long
text = Application.Trim(text)
TextLen = Len(text)
ACRONYM = Left(text, 1)
 
Search JabSto ::




Custom Search