Ascii udf assistance

[simico]

Hello Mr Excel people of the world,

I am in need of some assistance. I have a formula which takes a cell, converts each character to ascii, subtracts 133, and then converts it back to a character. I need the same thing, but in a UDF. I'm sure there is a simpler way in VBA than what I am doing via formula. It's just a simple cypher I am doing in another program that I want to be able to de-cypher in Excel.

Here is the formula, with B2 being the cell with the sample data below:

=CHAR(CODE(B2)-133)&CHAR(CODE(MID(B2, 2, 1)) -133)&CHAR(CODE(MID(B2, 3, 1)) -133)&CHAR(CODE(MID(B2, 4, 1)) -133)&CHAR(CODE(MID(B2, 5, 1)) -133)&CHAR(CODE(MID(B2, 6, 1)) -133)&CHAR(CODE(MID(B2, 7, 1)) -133)&CHAR(CODE(MID(B2, 8, 1)) -133)

Sample data:
ƾ¾ÍÙ¾¾·

Sample results:
A99HT992

Any help would be greatly appreciated!



Excel 2007
Windows 7

 

[tybaltlives]

Try this

Function shifter(inputcode)
Dim codeA$(8)

For i = 1 To 8
codeA$(i) = Chr(Asc(Mid(inputcode, i, 1)) - 133)
shifter = shifter + codeA$(i)

Next i
End Function

 

[simico]

Ooh, that works very nicely. Thank you very much!

 

[tybaltlives]

you are welcome. I try to help out in response to the many times I receive answers from the MR Excel board.