Random in VBA?

Have you ever tried to use the “random” function in Excel? If you have, you may have noticed a pattern, which is exactly what you should not notice while running a randomizer. This is the story of how I learned that the hard way. I began my project out of a sense of irrational paranoia. Like many, I am constantly bombarded with “Use suggested password?” notifications. I immediately distrusted them. How do I know they are truly random? How do I know some devious Blackhat didn’t compromise your password generation and is logging all of the suggested passwords? How do I know I am not a brain in a vat? There was only one logical solution. Make my own password generator that would store the password in an Excel workbook.

I immediately went about making a list of the characters I would consider using in my passwords, excluding any that look too similar to another character. Goodbye Zero! Hasta la vista Sheffer Stroke! After that laborious task was complete, I wrote a short, effective (or so I foolishly though) script that would concatenate a “random” string of a specified length and store it on a worksheet along with the site that the password was for. Success! Goodbye questionable “Suggested” passwords! Hello passwords I could rely on.

I am a little embarrassed how long it took to discover the flaw in my password generator. A full 31 day month later I discovered a pattern. A shocking pattern that still haunts me. Most of my password that I had created had the exact same string, only varying in length. “SechqQW:]%&\P”. How could this be? I knew that there was a minuscule chance that two randomly generated passwords would be the same, but 80% of them being exactly the same? Something was wrong, and I wouldn’t rest until I had fixed the issue!

I immediately did a hot fix that allowed me to view the generated password before it was entered on my list and have it create another one. The first password that the sheet would suggest after opening the workbook was always the same. Every character generated by the Rnd() function went in a specific order. I began researching the function and lo and behold I discovered the issue. Excel’s “random” number generator works by outputting a value between 0 and 1. It then uses that number as a seed to generate the next number, and the new number as a seed for the next number. “But John,” you might ask, “so long as the first generated number is random, then the seeds would be random, and the function would work exactly as intended!” Yes, very good point. And here is where the problem lies. The first randomly generated number is ALWAYS THE SAME!

How could this be called “random”? How was this flaw not accounted for when designing the function? I asked myself these questions, and immediately dismissed them and set about fixing the problem at hand, which was, as it turns out, pretty simple. I made several lists of characters in random orders for the function to operate on and inserting the current time, day, and year into a formula that told the script which list to use. That done I rested easily knowing that I had passwords that were randomly generated by me. Take that “suggested” passwords!

“Paranoid? Probably. But just because you’re paranoid doesn’t mean there isn’t an invisible demon about to eat your face.”
― Jim Butcher

Previous Post

I am a developer in Albuquerque, New Mexico. I have experience in VBA and Python and am always learning more languages and tools to help me progress as a developer. I enjoy tea, brie, and D and D.

Leave a Reply