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
- Klik in Excel op
Alt + F11
om de VBA-editor te openen. - Invoegen → Module.
- Plak de onderstaande code in de module.
- Sluit de editor en voer de macro uit
CreateVigenereSquare
(je kunt op de knop drukkenAlt+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