Hier is een kant-en-klare VBA-macro die een volledig Vigenère-vierkant in je werkmap bouwt met de cel linksboven op A1 (A1 leeg gelaten, kolomkoppen A→Z in B1 :AA1 en rijkoppen A→Z in A2 :A27 , tabel in B2 :AA27 ).

Hoe te gebruiken

1. Klik in Excel op Alt + F11 om de VBA-editor te openen.
2. Invoegen → Module.
3. Plak de onderstaande code in de module.
4. Sluit de editor en voer de macro uit CreateVigenereSquare (je kunt op de knop drukken Alt+F8 en de macro uitvoeren).

Option Explicit

Sub CreateVigenereSquare()
    Dim ws As Worksheet
    Dim i As Long, j As Long
    Dim letter As String
    
    ' Use the active sheet (or set: Set ws = ThisWorkbook.Worksheets("Sheet1"))
    Set ws = ActiveSheet
    
    Application.ScreenUpdating = False
    
    ' Clear an area big enough (A1:AA27)
    ws.Range("A1:AA27").Clear
    
    ' Put column headers A..Z in B1:AA1 and row headers A..Z in A2:A27
    For j = 0 To 25
        ws.Cells(1, j + 2).Value = Chr(65 + j)     ' B1..AA1
        ws.Cells(j + 2, 1).Value = Chr(65 + j)     ' A2..A27
    Next j
    
    ' Fill the 26x26 Vigenère table starting at B2
    For i = 0 To 25        ' row offset (0 = A)
        For j = 0 To 25    ' column offset (0 = A)
            letter = Chr(((i + j) Mod 26) + 65)
            ws.Cells(i + 2, j + 2).Value = letter
        Next j
    Next i
    
    ' Formatting: bold headers, center text, borders, autofit
    With ws.Range("A1:AA27")
        .Font.Name = "Calibri"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    
    ws.Range("B1:AA1").Font.Bold = True
    ws.Range("A2:A27").Font.Bold = True
    
    ' Add thin borders around the square (headers + table)
    With ws.Range("A1:AA27").Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    
    ' Make cells square-ish and readable
    ws.Range("A1:AA27").Columns.AutoFit
    Dim c As Range
    For Each c In ws.Range("A1:AA27").Columns
        c.ColumnWidth = Application.Max(3, c.ColumnWidth) ' ensure not too narrow
    Next c
    
    ' Freeze panes to keep headers visible (freeze below row1 and right of colA)
    ws.Activate
    ws.Range("B2").Select
    ActiveWindow.FreezePanes = True
    
    Application.ScreenUpdating = True
    
    MsgBox "Vigenère square created at A1 (headers in row1/colA).", vbInformation
End Sub



' === Utility functions ===
Private Function CleanText(ByVal s As String) As String
    Dim i As Long, ch As String, outStr As String
    outStr = ""
    s = UCase(s)
    For i = 1 To Len(s)
        ch = Mid(s, i, 1)
        If ch >= "A" And ch <= "Z" Then
            outStr = outStr & ch
        End If
    Next i
    CleanText = outStr
End Function

Private Function FiveBlocks(ByVal s As String) As String
    Dim i As Long, outStr As String
    outStr = ""
    For i = 1 To Len(s)
        outStr = outStr & Mid(s, i, 1)
        If i Mod 5 = 0 And i < Len(s) Then
            outStr = outStr & " "
        End If
    Next i
    FiveBlocks = outStr
End Function

' === ENCRYPT USING TABLE ===
Sub VigenereEncryptTable()
    Dim ws As Worksheet
    Dim plain As String, key As String, cipher As String
    Dim i As Long, pChar As String, kChar As String
    Dim rowNum As Long, colNum As Long
    Dim keyLen As Long
    
    Set ws = ActiveSheet
    
    plain = CleanText(ws.Range("AD1").Value)
    key = CleanText(ws.Range("AD2").Value)
    keyLen = Len(key)
    cipher = ""
    
    For i = 1 To Len(plain)
        pChar = Mid(plain, i, 1)
        kChar = Mid(key, ((i - 1) Mod keyLen) + 1, 1)
        
        ' Find row for plaintext letter (in col A, rows 2:27)
        rowNum = Application.Match(pChar, ws.Range("A2:A27"), 0) + 1  ' offset +1 because Match is relative
        
        ' Find col for key letter (in row 1, cols B:AA)
        colNum = Application.Match(kChar, ws.Range("B1:AA1"), 0) + 1
        
        ' Get intersection
        cipher = cipher & ws.Cells(rowNum, colNum).Value
    Next i
    
    ws.Range("AD3").Value = FiveBlocks(cipher)
    MsgBox "Encryption complete using Vigenère square.", vbInformation
End Sub

' === DECRYPT USING TABLE ===
Sub VigenereDecryptTable()
    Dim ws As Worksheet
    Dim cipher As String, key As String, plain As String
    Dim i As Long, cChar As String, kChar As String
    Dim colNum As Long, rowNum As Long
    Dim keyLen As Long
    Dim rng As Range, f As Variant
    
    Set ws = ActiveSheet
    
    cipher = CleanText(ws.Range("AD5").Value)
    key = CleanText(ws.Range("AD6").Value)
    keyLen = Len(key)
    plain = ""
    
    For i = 1 To Len(cipher)
        cChar = Mid(cipher, i, 1)
        kChar = Mid(key, ((i - 1) Mod keyLen) + 1, 1)
        
        ' Find col for key letter
        colNum = Application.Match(kChar, ws.Range("B1:AA1"), 0) + 1
        
        ' Look in that column (rows 2:27) for ciphertext letter
        Set rng = ws.Range(ws.Cells(2, colNum), ws.Cells(27, colNum))
        f = Application.Match(cChar, rng, 0)
        
        If IsError(f) Then
            plain = plain & "?"
        Else
            rowNum = f + 1
            ' Row header in col A
            plain = plain & ws.Cells(rowNum, 1).Value
        End If
    Next i
    
    ws.Range("AD7").Value = FiveBlocks(plain)
    MsgBox "Decryption complete using Vigenère square.", vbInformation
End Sub

Opstelling gemaakt in Excel 2016.
AD1 PlainText: THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG
AD2 Key: LANDBOUWGESCHRIFT
AD3 Ciphertext

AD5 Ciphertext
AD6 Key: LANDBOUWGESCHRIFT
AD7 PlainText: comes here automatically

Het vierkant is ingedeeld zoals hierboven beschreven (A1 leeg gelaten, kolomkoppen A→Z in B1:AA1 en rijkoppen A→Z in A2:A27, tabel in B2:AA27).
Criterium voor het versleutelen: houd er in de code rekening mee dat je de traditie volgt door alle spaties en leestekens te verwijderen, alle letters naar hoofdletters te converteren en het resultaat in blokken van 5 letters te verdelen.

Hoe het werkt het nou?

• AD1 → Invoer plaintext ( THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG)
• AD2 → Key: ( LANDBOUWGESCHRIFT) zonder haakjes
• Uitvoeren: VigenereEncryptTable→ resulteert in AD3 (cijfer, blokken van 5 letters).
• Kopieer/plak AD3 in AD5 (cijfertekst om te testen).
• AD6 → Key: ( LANDBOUWGESCHRIFT) zonder haakjes
• Uitvoeren → VigenereDecryptTable resultaat in AD7 (plaintext, gegroepeerd).

Het kan trouwens ook zonder het vierkant door gebruik van ASCII. De ASC() functie converteert een teken naar het bijbehorende ASCII-codenummer, terwijl de 
CHR() functie het tegenovergestelde doet: een ASCII-nummer terug converteren naar de bijbehorende tekenweergave. Bijvoorbeeld:
ASC("A") zou retourneren 65 en  CHR(65)zou retourneren "A". Maar hier gebruiken we het vierkant