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 “VBA: How to get a Range of Numbers from a List”

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 “VBA: How to Use Dictionary Objects in Excel”

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 “VBA: How to Crack the Caesar Shift with Brute Force”

VBA: How to Auto-Generate Personalized Emails in Excel

An Outlook VBA Tutorial

I work one of those weird jobs where the company still uses Outlook. I’m a big fan of Gmail and Google Docs, but I really like having the option of VBAing my emails.

Say, for instance, I want to send the same email to multiple people. I don’t want to put them all in a single email thread though—inevitably someone will hit Reply All, then someone else will hit Reply All, then someone else will get curmudgeonly over having a huge email chain clogging their feed.

Continue reading “VBA: How to Auto-Generate Personalized Emails in Excel”

VBA: How to Return the Last Row and Column in Excel

VBA Header2

I was done with this week by about noon on Monday, so I’m four and a half days overdue for a Jessica Jones marathon.

Which is why I’m keeping this short and sweet. This week, I learned how to return the last row and column in an Excel spreadsheet. It’s insanely helpful if you want to copy and paste the same information to multiple workbooks using VBA.

Continue reading “VBA: How to Return the Last Row and Column in Excel”

VBA: How to Write Your Own Function

VBA Header

So last week, I learned that VBA buttons are supremely helpful. For moving data between workbooks or breaking down information from a KML file, these are ideal. Especially the KML file importer because it’s basically populating a full on spreadsheet.

But if you want to have a function run very specific math or customized scraping on a string that’s only going to affect one cell, go with the UDF. Sounds like a terrible disease, doesn’t it? In Excel, it stands for User Defined Function.

Continue reading “VBA: How to Write Your Own Function”