VBA Walkthrough #2: Message Encoder & Decoder

This VBA macro is hopefully a bit more interesting than the last, and shows how I created a message encryptor/decryptor in Excel using VBA/macros.

The lesson covers these specific areas of VBA usage:

– For…Next loop (including nested loops)

– If…ElseIf…Then Statement

– Excel functions: CHAR() / LEN() / VLOOKUP()

– VBA functions: Mod / Mid / Asc / Chr

Download the Message Encryptor & Decryptor here to follow along with the video lesson.

This entry was posted in VBA / Macros and tagged , , , , , , , . Bookmark the permalink.

7 Responses to VBA Walkthrough #2: Message Encoder & Decoder

  1. Anonymous says:

    Hi: Thanks for this extremely impressive tutorial!
    I have a question for these passkeys: I understand the inner loop (from 1 to the last character of the message) and how it transforms the old to new character. However, why we need the four passkeys (e.g., D10:D13 in sheet 1 and D2 in sheet 2). What is its role in encoding/decoding the message?
    Thanks!

    • Firstly, appreciate the kinds words about the lesson! I think in order to answer the question you need to put yourself in the position of two ‘Spies’ that would be using this to communicate.

      Spy 1 creates the passcode using the template, and choosing his own passkeys (I chose 4 just to make it harder to crack). Then, Spy 1 sends his secret message to Spy 2 WITHOUT the passkeys entered in. That way anyone who intercepts it will just see gibberish. He likely just emails the ‘message’ text and not the file, they would each have a local copy of the file.

      They meet in a dark and dirty alley, Spy 1 leans in close and reveals his 4 passkeys (44 / 2 / 15 /10) for Spy 2 to use to decode it. Spy 2 goes home and enters them in. No need for internet access, and no way for anyone to figure it out without both pieces.

      Hope that makes sense! It’s been a while since I made the lesson and didn’t research cryptography or anything, so I just made up what I thought would be a fun example πŸ™‚ Thanks!

      Ben

  2. Nicka says:

    Hi i just needed help, can you teach me how can i copy the PO i have generated to another worksheet without erasing the previous entry, (beginner homework number 2), i’ve already accomplished the copying part but somehow it overwrites the previous copied PO, what codes should i use in order to copy the new generated PO under the previous ones.

    here’s the code that i used

    Sub ButtonToCopy()
    β€˜
    β€˜ ButtonToCopy Macro
    β€˜

    β€˜
    Range(β€œB2:N23”).Select
    Selection.Copy
    Sheets(β€œSheet4”).Select
    Range(β€œA2:M23”).Select
    ‘I need to put here something like the “rowstart” to adjust the copied PO so it will not overwrite the previous ones

    ActiveSheet.Paste
    End Sub

    Thanks πŸ˜€

  3. Anonymous says:

    Hi i just needed help, can you teach me how can i copy the PO i have generated to another worksheet without erasing the previous entry, (beginner homework number 2), i’ve already accomplished the copying part but somehow it overwrites the previous copied PO, what codes should i use in order to copy the new generated PO under the previous ones.

    here’s the code that i used

    Sub ButtonToCopy()

    ‘ ButtonToCopy Macro


    Range(“B2:N23”).Select
    Selection.Copy
    Sheets(“Sheet4”).Select
    Range(“A2:M23”).Select <——-I need to put here something like the "rowstart" to adjust
    ActiveSheet.Paste the copied PO so it will not overwrite the previous ones
    End Sub

    Thanks πŸ˜€

  4. Anonymous says:

    Hi,

    Great post.

    I have managed to modify your VBA macro so that it includes batch encoding/decoding. But in terms of performance it’s very slow with large batches (seven strings with five characters per seconds). For small batches it works great.

    I was wondering if you had any recommendations for simple encoding and decoding, like the one you have here, but that would perform better with batch conversion.

    OS

    – Bergen

  5. SB says:

    Hi Ben, I would just like to show my appreciation via this comment as nobody has yet made a comment on this video and simply say – you are awesome!!!

    I don’t think I’ll be able to make the VBA you run throughout this video (not anytime soon anyway) but the fact that I can now even create a click button and simply ask ‘What is you name?’ and follow it with ‘How are you doing ‘VariableName’? is a good start – so thank you.

    SB
    London

Comments are closed.