Results 1 to 8 of 8

Scramble and Unscramble a message

This is a discussion on Scramble and Unscramble a message within the Excel Questions forums, part of the Question Forums category; Hi, I have an excel file like this with all the alphabet letters: Original character Changed character a e b ...

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    3

    Smile Scramble and Unscramble a message

    Hi,

    I have an excel file like this with all the alphabet letters:

    Original character Changed character
    a e
    b v
    c d
    d I
    e z

    I need two write two subs, Scramble and Unscramble. In each, ask the user for a message in an input box. In the Scramble sub, this will be an original message; in the Unscramble sub, it will be a scramble message. Then in the Scramble sub, scramble the message and display it. In the unscramble sub, unscramble the message and display it.

    I would appreciate any help or can anyone point me in the right direction.

    Thank you

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824

    Default Re: Scramble and Unscramble a message

    Hi Naty. You will need to have some sort of "key" to reference. That is, some sort of standard to to convert your message between scrambled to unscrambled...

  3. #3
    New Member
    Join Date
    Mar 2010
    Posts
    3

    Default Re: Scramble and Unscramble a message

    Hi Tom,

    Thanks for the reply. They gave me the key on an excel file a column with the alphabet and the next column just random letters. Is that what you mean?

    Like this:

    Original character Changed character
    a e
    b v
    c d
    d I
    e z
    f y
    g b
    h m
    I r
    j n

    Thank you

  4. #4
    Board Regular bertie's Avatar
    Join Date
    Jun 2009
    Location
    Coatbridge, Scotland
    Posts
    1,742

    Default Re: Scramble and Unscramble a message

    A simple approach would be to have two arrays, one holding the orignal characters, the other its coded equivalent.

    Then loop through the input message one character at a time, find the character's position in the original array and take the corresponding character in the second array and build up a coded string.

    To use paste into the ThisWorkbook module
    Step through the Scramble Procedure (F8) and open the Locals Window, View => Locals Window, this will let you see the coded string being assembled.

    Code:
    Option Explicit
     
    Sub Scramble()
       Dim aOriginal()
       Dim aCoded()
       Dim msg As String
       Dim msgEncrypt As String
       Dim msgDecode As String
     
       aOriginal = Array("A", "B", "C", "D", "E")
       aCoded = Array("E", "V", "D", "I", "Z")
       msg = "DEAD" 'InputBox("Please Enter a Word.")
       msgEncrypt = Encrypt(msg, aOriginal, aCoded)
       Debug.Print msgEncrypt
     
       msgDecode = Decode(msgEncrypt, aOriginal, aCoded)
       Debug.Print msgDecode
    End Sub
     
     
    Function Encrypt(msg As String, _
                      a1 As Variant, _
                      a2 As Variant) As String
       Dim lenMsg As Integer
       Dim counter As Integer
       Dim myChr As String
       Dim myCode As String
       Dim i As Integer
     
       lenMsg = Len(msg)
     
       Do
          counter = counter + 1
          myChr = Mid(msg, counter, 1)
          For i = 0 To UBound(a1)
             If a1(i) = myChr Then
                myCode = myCode & a2(i)
                Exit For
             End If
          Next i
       Loop While counter < lenMsg
       Encrypt = myCode
    End Function
     
     
    Function Decode(msg As String, _
                   a1 As Variant, _
                   a2 As Variant) As String
       Dim lenMsg As Integer
       Dim counter As Integer
       Dim myChr As String
       Dim myCode As String
       Dim i As Integer
     
       lenMsg = Len(msg)
     
       Do
          counter = counter + 1
          myChr = Mid(msg, counter, 1)
          For i = 0 To UBound(a2)
             If a2(i) = myChr Then
                myCode = myCode & a1(i)
                Exit For
             End If
          Next i
       Loop While counter < lenMsg
       Decode = myCode
    End Function

  5. #5
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,225

    Default Re: Scramble and Unscramble a message

    natygrosso,

    Working with your original 5 characters.


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Adding the Macro
    1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


    Code:
    Option Explicit
    Sub Test()
    ' hiker95, 03/11/2010
    Dim MyReply As String, MyMsg As String
    MyReply = InputBox("Enter 'S' to Scramble, 'U' to Un-Scramble.")
    If MyReply = "S" Or MyReply = "s" Then
      MyMsg = InputBox("Enter your text string to Scramble.")
      MsgBox "Entered text:  " & MyMsg & vbCrLf & vbCrLf & _
             "  Scrambled:  " & Scramble(MyMsg, 0)
    
    ElseIf MyReply = "U" Or MyReply = "u" Then
      MyMsg = InputBox("Enter your text string to Un-Scramble.")
      MsgBox "Scrambled text:  " & MyMsg & vbCrLf & vbCrLf & _
             " un-scrambled:  " & Scramble(MyMsg, 1)
    End If
    End Sub
    
    
    Public Function Scramble(InString, SU As Integer) As String
    ' hiker95, 03/11/2010
    '
    ' =Scramble(A1,0)
    ' 0 for Scramble, 1 for Un-Scramble
    '
    Dim SL As Long, SC As String, SUsw As Integer, FS As String, a As Long
    SL = Len(InString)
    SUsw = SU
    If SUsw = 0 Then
      For a = 1 To SL
        SC = Mid(InString, a, 1)
        Select Case SC
          Case " "
            FS = " "
          Case "a"
            FS = "e"
          Case "b"
            FS = "v"
          Case "c"
            FS = "d"
          Case "d"
            FS = "l"
          Case "e"
            FS = "z"
          Case Else
            FS = "?"
        End Select
        Scramble = Scramble & FS
      Next a
    ElseIf SUsw = 1 Then
      For a = 1 To SL
        SC = Mid(InString, a, 1)
        Select Case SC
          Case " "
            FS = " "
          Case "e"
            FS = "a"
          Case "v"
            FS = "b"
          Case "d"
            FS = "c"
          Case "l"
            FS = "d"
          Case "z"
            FS = "e"
          Case Else
            FS = "?"
        End Select
        Scramble = Scramble & FS
      Next a
    End If
    End Function

    Then run the "Test" macro.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  6. #6
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,225

    Default Re: Scramble and Unscramble a message

    natygrosso,

    And, you can use the Function Scramble like this:


    Sheet1

     ABC
    1OriginalScramble 0Unscramble 1
    2abcde zevdlz ?abcde ?
    3bcdea 7vdlze ?bcdea ?
    4edcba &zldve ?edcba ?
    5abc de a bcde ~evd lz e vdlz ?abc de a bcde ?
    6   

    Spreadsheet Formulas
    CellFormula
    B2=Scramble(A2,0)
    C2=Scramble(B2,1)
    B3=Scramble(A3,0)
    C3=Scramble(B3,1)
    B4=Scramble(A4,0)
    C4=Scramble(B4,1)
    B5=Scramble(A5,0)
    C5=Scramble(B5,1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  7. #7
    Board Regular
    Join Date
    Mar 2008
    Posts
    2,638

    Default Re: Scramble and Unscramble a message

    Excellent job wasting someone's time at another forum.

    Cross-post:
    http://www.excelforum.com/excel-prog...a-message.html
    Where to upload Excel files so threads don't take 20+ posts to get the correct answer from guessing:

    MediaFire: http://www.mediafire.com/
    FileFactory: http://www.filefactory.com/

    Also, in your thread or in your signature TELL US WHAT VERSION OF EXCEL YOU'RE USING!!!

    I'm using Excel 2007. My solutions will be appropriate for that version unless you tell us otherwise.

  8. #8
    New Member
    Join Date
    Mar 2010
    Posts
    3

    Default Re: Scramble and Unscramble a message

    I want to apologize to everyone that got affected by my mistake. I didn't mean any harm or wasting anyones time. It was plain ignorance on the forum subject. I assure that the lesson was learned and it would never happen again.Thank you very much for everyones help it is greatly appreciate it.
    Thank you
    Naty

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com