Coding And Decoding Using Excel VBA

Excel macros

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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.



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.