Tag Archives: Excel

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 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 , , , , , ,