Macro for ROT13?

[vanclute]

Amazingly, I'm having a hell of a time finding a way to do ROT13 encoding/decoding via a simple macro. I've found a few examples of VB functions but I'm unclear how to get them to work on a selection on a spreadsheet. I want to hilight an area, trigger a macro, and have the selection ROT13ed.

Anyone have the code to accomplish this?

Thanks!

Jonathan

 

[Kenneth Hobson]

Sound like a homework assignment. On the assumption that it is not:

Sub ROT13Selection()
    Dim c As Range
    For Each c In Selection
        c.Value = ROT13(c.Value)
    Next c
End Sub

Sub Test()
    Dim s As String
    s = ROT13([A1])
    MsgBox "Original String: " & [A1] & vbCrLf & _
        "ROT13 String: " & ROT13([A1]) & vbCrLf & _
        "ROT13(ROT13)) String: " & ROT13(ROT13([A1])), _
        vbInformation, "ROT13"
End Sub


'ftp://ftp.pbhq.de/pbalgo/rot13-nl.bas
'
' ROT-13 encoder/decoder
' Created 26-02-1997
' By Marc van den Dikkenberg (pb@excelsior.xs4all.nl)
'
'Similar to, http://pitel-lnx.ibk.fnt.hvu.nl/~excel/pb.html
Function ROT13(Test$) As String
  Dim tt As Long, x As Integer, y As Integer
  For tt = 1 To Len(Test$)
    y% = 0
    x% = Asc(Mid$(Test$, tt, 1))
    If (x% > 64 And x% < 91) Or (x% > 96 And x% < 123) Then
      y% = 13
      x% = x% - y%
      If x% < 97 And x% > 83 Then x% = x% + 26 Else If x% < 65 Then x% = x% + 26
    End If
    Mid$(Test$, tt, 1) = Chr$(x%)
  Next tt
  ROT13 = Test$
End Function

 

[vanclute]

LOL homework! Ewwww!!! Perish the thought!

No actually, unless I count it as homework for myself. I'm just trying to find a simple quick & dirty (clean) way to decode bits of data I'm passing in a URL and then retrieving in Excel. Doesn't need to be hashed or anything extreme like that, just simple & fast.

Thanks so much, I'm still fairly new to the VB side of Excel.

Jonathan

 

[cnstarz]

Hey, I know this thread is over 10 years old, but I just happened to create this today right before I stumbled across this thread. It allows you to perform any ROT operation (ROT13 is default). Figured I'd share it in case anyone else needed something like this.

Public Function ROT(ByVal s As String, Optional ByVal r As Integer = 13) As String
    Dim c, f, i As Integer
    For i = 1 To Len(s)
        c = Asc(Mid(s, i, 1))
        If (c >= 97 And c <= 122) Or (c >= 65 And c <= 90) Then
            If c >= 97 Then f = 97 Else f = 65
            c = r + c - f
            Do: c = c Mod 26: Loop While c > (26 - 1)
            c = c + f
        End If
        ROT = ROT & Chr(c)
    Next i
End Function

If you don't like the idea of performing the same conditional twice (as happens above), then you can also use below:

Public Function ROT(ByVal s As String, Optional ByVal r As Integer = 13) As String
    Dim c, f, i As Integer
    For i = 1 To Len(s)
        c = Asc(Mid(s, i, 1))
        If c >= 97 And c <= 122 Then
            f = 97
        ElseIf c >= 65 And c <= 90 Then f = 65
        Else: GoTo Build: End If
        c = r + c - f
        Do: c = c Mod 26: Loop While c > (26 - 1)
        c = c + f
        Build: ROT = ROT & Chr(c)
    Next i
End Function

Sample Output:

ROT("abc123xyz. ABC789XYZ!")    ' Default ROT13
nop123klm. NOP789KLM!

ROT("abc123xyz. ABC789XYZ!",1)    ' ROT1
bcd123yza. BCD789YZA!

ROT("abc123xyz. ABC789XYZ!",5)    ' ROT5
fgh123cde. FGH789CDE!

 

[vanclute]

OMG if that isn't a blast from the past. I can't even remember what exactly I wanted this for anymore! But thanks for sharing your solution, I'm sure it'll help someone someday. =)