Vigenere cipher, can encrypt - decryption is troublesome!

**varg111** · 11-04-2012, 05:32 PM

hi there, i'm currently working on a spreadsheet that can encrypt and decrypt plaintext, using the vigenere method.

I can encrypt the text successfully as you can see in the attached worksheet, however i'm having trouble with the decryption. I know the maths formula to work it out is the following, but I just can't figure this out in excel.

Ma = Ca – Kb (mod 26)
(Where C = Code, M = Message, K = Key, and where a = the ath character of the message bounded by the message, and b is the bth character of the Key bounded by the length of the key

I don't think i'm too far off, any help would be appreciated

**shg** · 11-04-2012, 06:20 PM

How about a UDF?

      ----A---- -----B------ --------------C---------------
  1         Key LEMON        B1: Input                     
  2   Plaintext ATTACKATDAWN B2: Input                     
  3      Cypher LXFOPVEFRNHR B3: =Vigenere(B2, $B$1, TRUE) 
  4   Plaintext ATTACKATDAWN B4: =Vigenere(B3, $B$1, FALSE)

Function Vigenere(ByVal sInp As String, _
                  ByVal sKey As String, _
                  Optional bEncrypt As Boolean = True) As String

    Dim i         As Long
    Dim j         As Long

    sInp = UCase(Replace(sInp, " ", ""))
    sKey = Replace(sKey, " ", "")
    sKey = Left(WorksheetFunction.Rept(sKey, Len(sInp) \ Len(sKey) + 1), Len(sInp))

    If bEncrypt Then
        For i = 1 To Len(sInp)
            j = Asc(Mid(sInp, i, 1)) + Asc(Mid(sKey, i, 1)) - 130
            If j > 25 Then j = j - 26
            Mid(sInp, i) = Chr(j + 65)
        Next i
    Else
        For i = 1 To Len(sInp)
            j = Asc(Mid(sInp, i, 1)) - Asc(Mid(sKey, i, 1))
            If j < 0 Then j = j + 26
            Mid(sInp, i) = Chr(j + 65)
        Next i
    End If

    Vigenere = sInp
End Function