Imagine you have a combination lock with 4 digits. If you wack your head against a car hood by accident and forget the combination, this means you have a problem with 255 wrong answers and 1 right one. If you had a few days to spare, you could try all of them. The marvelous thing about computers is that they can, quite easily, auto-generate all 256 possibilities and beat the lock senseless with them. Until it opens.
That’s a brute force attack.
So some time ago, I posted a Caesar Shift encipherer/decipherer with AutoHotkey. And that was an enjoyable exercise, but Caesar isn’t a great option for encoding messages. There are 25 possible shifts to the English alphabet. That means only 24 wrong answers and 1 right one. If the text you’ve shifted is an English world, it’s going to pop out among 24 other nonsense words.
It’s actually a lot easier to crack if you have a full block of text—then you can just look for the most frequent letters (vowels, s, r, t, and combinations like ing), plug them in, and word backwards.
But let’s say you have a single five-letter word. Frequency distribution is less helpful there. That’s when you might want to just shift every letter and look for something familiar.
How to Loop through each Character in a String with VBA
The mid function is key in VBA. Mid returns a slice of a string.
Mid(StringToSlice, StartingPosition, LengthOfSlice)
So if you set a variable for the position in the string and run it through a loop, you can use mid to cut out each character. Like so:
Dim Counter As Integer
For Counter = 1 To Len(String)
Mid(String, Counter, 1)
What’s fun about the mid function is that it means you don’t even need to set up an array for the alphabet. You can just make an alphabet string and loop through that.
The rest is explained below.
The Copy/Paste Version
Dim Counter As Integer Dim rot As Integer Dim Alphabet As String Dim AlphabetPos As Integer Alphabet = "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz" CipherText = Cells(2, 1).Value rot = 1 Do While rot < 26 'Use the Mid function to get the specific character For Counter = 1 To Len(CipherText) AlphabetPos = InStr(Alphabet, Mid(CipherText, Counter, 1)) DeCipheredNum = AlphabetPos + 26 - rot DeCiphered = Mid(Alphabet, DeCipheredNum, 1) DeCipheredText = DeCipheredText & DeCiphered Next Cells(rot + 1, 3).Value = DeCipheredText rot = rot + 1 DeCipheredText = "" Loop End Sub