Ok, I’ve been dancing around dictionaries in VBA for weeks—mainly because they seemed confusing. And I’m lazy. Turns out, they are supremely helpful storage devices. If say, you want a randomly ordered alphabet (otherwise known as a deranged alphabet) for a simple substitution cipher, the easiest way to store each letter with it’s new associated value is with a dictionary.
How to Script a Dictionary
‘This creates a dictionary
Dim dictionaryname as Object
Set dictionaryname = CreateObject(“Scripting.Dictionary”)
‘This assigns a value to to a key
dictionaryname.Add Key:=”Key Name“, Item:=”The Item“
‘This gets a value with the key
But before actually filling the dictionary with the alphabet, you’ll have to randomize the order. Unfortunately, I haven’t seen a way to shuffle a dictionary in VBA, but you can get somewhat creative with the Rnd (Random) and Mid function.
First, you set up a string to work with:
alphabet = “abcdefghijklmnopqrstuvwxyz“
Then set up a loop to run for the length of the string, decreasing by one for each letter of the alphabet that’s getting pushed into the dictionary. You’ll also need a variable to represent the number of letters left in the alphabet.
alphacount = 26
For i = 0 To Len(alphabet) – 1
Now we generate a random number between however many characters are left in the alphabet and one. I want to have a column with my randomized alphabet next to the regular one. Before starting up my VBA, I put the alphabet into Column A. Setting Cells(i+1, 2) to the character in the randomValue position will push those values into Column B.
randomValue = Int((alphacount) * Rnd()) + 1
Cells(i+1, 2).Value = Mid(alphabet, randomValue,1)
With those two columns set, populating the dictionary is a quick matter of:
dictionaryName.Add Key:=Cells(i + 1, 1).Value, Item:=Cells(i + 1, 2).Value
Last, you’ll need to set everything up for the next loop. Take down alphabetcount by one to generate a smaller random number and slice the letter at the randomized position out of the alphabet string.
alphacount = alphacount – 1
‘In VBA, you can use & to concatenate two strings
alphabet = Left(alphabet, randomValue – 1) & Right(alphabet, Len(alphabet) – randomValue)
That alone will get you a deranged alphabet. But if you actually want to translate a message into that alphabet, you’ll need to run one last block of code. This takes the string in Column E, Row 2 and runs each character through a loop, appending the new value to a variable called NewText. The last line puts NewText into Column E, Row 5.
CipherText = Cells(2, 5).Value
For n = 0 To Len(CipherText) – 1
Letter = Mid(CipherText, n + 1, 1)
NewText = NewText + dict(Letter)
Cells(8, 5).Value = NewText
If all has gone according to plan, you should be able to put in a giant block of text and derange it in a single click. No spaces or punctuation (because that would make it too easy to read).
Here’s a screenshot of how I set up my spreadsheet beforehand:
Keep in mind, VBA populates columns B, C and Cell E8. You provide everything else.
One preliminary disclaimer. This script does not work with mixed case strings. In the plain text, you’ll need to keep it all lowercase.
Sub DerangedAlphabet() Dim CipherText As String Dim NewText As String Dim dict As Object Dim alphabet As String Dim randomValue As Integer Set dict = CreateObject("Scripting.Dictionary") NewText = "" alphabet = "abcdefghijklmnopqrstuvwxyz" alphacount = 26 For i = 0 To Len(alphabet) - 1 randomValue = Int((alphacount) * Rnd()) + 1 'Cells(i + 1, 1).Value = randomValue Cells(i + 1, 2).Value = Mid(alphabet, randomValue, 1) dict.Add Key:=Cells(i + 1, 1).Value, Item:=Cells(i + 1, 2).Value alphacount = alphacount - 1 alphabet = Left(alphabet, randomValue - 1) & Right(alphabet, Len(alphabet) - randomValue) Cells(i + 1, 3).Value = alphabet Next CipherText = Cells(2, 5).Value For n = 0 To Len(CipherText) - 1 Letter = Mid(CipherText, n + 1, 1) NewText = NewText + dict(Letter) Next Cells(8, 5).Value = NewText End Sub
2 Replies to “VBA: How to Use Dictionary Objects in Excel”
nice keep up the vba – excel tutos
LikeLiked by 1 person
Thanks ARJ 🙂 Many more coming soon!