Tag Archives: 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

Tagged , , , , , , ,

VBA: How to Condense Two Columns into a List

VBA Column Condenser

You know what I’ve wanted all my life? A way to condense columns into comma separated lists. I’ve been in multiple jobs where this would’ve been handy because it’d be so much easier to see all the things in a few rows.

Continue reading

Tagged , , , , , , ,

VBA: How to Get a Spreadsheet of File Names

VBA Header

I recently found a really cool article from Software Solutions Online that shows you how to get a list of files in a folder or a list of subfolders in a folder. All the things, basically.

This weekend, I’m adapting that slightly to get a complete list of files within a folder including everything in its subfolders. I know. This is the sort of thing that’s either really interesting or really not, depending on how much you use VBA.

Continue reading

Tagged , , , , ,

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

Tagged , , , , , , , ,

VBA: How to Code a Polygon Coordinates Importer

vba-header

Somehow, a few weeks ago, no doubt as a result of my having enough caffeine in my system to  down a small hippopotamus, I thought it would be a good idea to make a KML to CSV exporter in AutoHotkey. I can’t even claim ignorance this time—I knew VBA was a thing. But the neurons firing in my brain blazed right passed the LOGIC center and crashed head on into the AHK FIXES EVERYTHING node.

Continue reading

Tagged , , , , , , , , ,

VBA: What? Just… What?

whatisthismadness

I know shockingly little about Excel. And that is somewhat unsettling because most of my jobs, which I depend on for food and rent and coffee, require a fuck-ton* of spreadsheets.

In the interest of keeping myself rolling in coffee grounds, I’ve made myself sludge through VBA tutorials this weekend and put together a few lines that’ll transfer a cell from one workbook to another.

*Is fuck-ton hyphenated?

Continue reading

Tagged , , , , , ,