2

I am trying to write a VBA macro that takes a single letter of the alphabet from the active cell and replaces it with a letter five positions ahead (i.e. "a" becomes "f", "x" becomes "c"). This is to decode a file of encrypted text where all letters have been shifted back five positions.

So far I have tried using the ASCII value of the letter and converting from that integer back to a character, but this seems not to be working (run time error 13) and I can't help but wonder if there is a more efficient way. Here's what I have so far:

Sub DECODER()
Worksheets("Sheet1").Activate

Dim What As String
What = ActiveCell.Value

Dim dCode As String
If What = "" Then
dCode = What
Else
dCode = Chr(Asc((What) + 5))
End If

ActiveCell.Value = dCode

End Sub
EdC
  • 25
  • 5

1 Answers1

1

Without VBA:

With a single lower case alphabetic character in cell A1, in B1 enter:

=CHAR(IF(CODE(A1)>117,CODE(A1)-26,CODE(A1))+5)

enter image description here

and With VBA:

Public Function DeCoder(Sin As String) As String
    Dim num As Long

    If Sin Like "[a-z]" Then
        num = Asc(Sin) + 5
        If num > 122 Then num = num - 26
        DeCoder = Chr(num)
    Else
        DeCoder = Sin
    End If
End Function
Gary's Student
  • 95,722
  • 10
  • 59
  • 99