Coding And Decoding Using Excel VBA

Excel macros

Very recently, a comment on a past post with a question turned out to be a very interesting challenge. Without further wait, here is the question:

“Is it possible to do a “ceasar cipher” of three over letters (input A and output is D)

(A=D, B=E, C=F, D=G, … X=A, Y=B, Z=C)
ABCDEFGHIJKLMNOPQRSTUVWXYZ
DEFGHIJKLMNOPQRSTUVWXYZABC

e.g. I LOVE CARS = L ORVH FDUV
e.g. WKDQN BRX = THANK YOU

Please help me, I am having difficulties in doing this in excel vlookup.”

As is always the case, there are many different ways to get this done. I personally felt it would be easier with vba than using vlookups. Since I would likely not be dealing with hundreds of different messages at once, using simple loops. Here is how I represented the issue:

As you can see, I wanted to be able to both uncode a message as he had asked but also use the same one to code something else. Here is the code that I used for the first part:

“Sub decode()

i = 4

‘go line by line
Do Until Cells(i, 2).Value = “”

mlength = Len(Cells(i, 2).Value)
j = 1: k = 1

dmessage = “”
‘go through the cell, character by character
Do Until j = mlength + 1
mcharacter = Mid(Cells(i, 2).Value, j, 1)

k = 1
‘for each character, find the correct coded
If mcharacter = ” ” Then
dmessage = dmessage & ” ”
End If
Do Until k = 30
If Cells(k, 11).Value = mcharacter Then
dmessage = dmessage & Cells(k, 10).Value
End If
k = k + 1
Loop

j = j + 1
Loop

Cells(i, 3).Value = dmessage
i = i + 1
Loop

End Sub”

I did very small modifications for the second part. You can see the end result here:

And as always, feel free to download the spreadsheeet here.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

One Feedback on "Coding And Decoding Using Excel VBA"

dilawar

i always read your post whenever mail comes. its good. i have one question that how van i calculate over time e.g. 08:00 to 17:30 pm morning full day and 17:30 to 20:00pm after office time and 11:00 pm to 02:35 am i want you to solve my question as i dont know the time calculation formula.