Category Archives: VBA

VBA: How to Get A List From a Range of Numbers


A couple months ago, I did the exact opposite of this. I called it the ListToRange() Function. It’s weirdly helpful at work because I have to go back and forth between having numbers listed out and then having them clustered for other things.

Continue reading

Tagged , , , , , , ,

VBA: What is a Range Object?

Range Objects

“Range object” is an unnecessarily intimidating term for a collection of cells within your workbook. Say you need to run some complicated math or formatting over the same cells over and over throughout a long script. You could call on the column number and row every damn time, but who wants to do all that typing?

Continue reading

Tagged , , , , , , ,

VBA: How to Code a Letter Frequency Chart

Rolling Stones

I recently picked up a book called “Ghost in the Wires: My Adventures as the World’s Most Wanted Hacker” by Kevin Mitnick. To be totally honest, I’d never heard of Mitnick before. He was a little before my time—when he was arrested in 1995, I was still struggling to understand why the hell I had to learn fractions and everything I knew about computers was based on WarGames.

Continue reading

Tagged , , , , , , , ,

VBA: How to get a Range of Numbers from a List

ListToRange() Function

So I’ve finally reached that point where I’m writing Macros well enough to accomplish simple tasks, but the results are just not pretty to look at. One of my macros returns a list of numbers associated with some data in a worksheet that looks like this:

, 1, 2, 3, 4, 5, 7, 10, 11, 12, 34, 35, 36, 37

Kind of gross, right? I want a to have a concise list. And DEAR GOD, you’d think writing the logic to get 1-5, 7, 10-12, 34-37 would be breeze.

Continue reading

Tagged , , , , , , , , ,

VBA: How To Auto-save Attachments in Outlook

GG2C 1

So this week I wrote a script that will take an email from your Outlook and save all the attachments to a specified file. It was a learning process that took many unexpected twists and turns. There was a fair amount of cursing, cajoling, and a minor panic attack. Ultimately the bastard functions though, so I’m calling it a net gain.

Continue reading

Tagged , , , , , , , , ,

VBA: How to Use Dictionary Objects in Excel

A Deranged Alphabet Generator

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.

Continue reading

Tagged , , , , ,

VBA: How to Crack the Caesar Shift with Brute Force

Caesar Brute Force

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.

Continue reading

Tagged , , , , , , ,