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”

AHK: Visualizing ELSE/IF with Arrays

So Hopper’s Amazing Decipherer Script 1.0 was about 472 lines because it included ELSE IF clauses for every letter in the alphabet.

HADS 2.0 cuts it down to 77 lines because arrays, as it turns out, are incredibly useful when paired with loops.

Making a Rotated Alphabet

Deciphering a Caesar cipher requires that the alphabet be moved up by a certain number. Like so.

ROTchart

Notice that this is basically a 2-step process. Turn the letters of the alphabet into numbers 1-26. Then add the rotational value to each of them to find their new positions, in the code below that’s assigned to a variable called indexVar. In ROT1, A =1 and the rotational value is 1, so A = 1+1 = 2.

But what happens when you hit Z? Z=26 and the rotation is 1, so Z =  26+1 = 27.

So, you want to tell the computer IF the number goes above 27, then you want to reset it to 1 and continue.

A Slow Motion Loop

Continue reading “AHK: Visualizing ELSE/IF with Arrays”

AHK: What is an Array?

Arrays are a damn godsend. More specifically, they’re an easy way to deal with large lists. Why is this useful you ask? Imagine you want to run the same code on multiple items. The items could be numbers, letters, words, full sentences—anything.  By putting those things in a list, or an array, you can loop through hundreds, even thousands, of items with just a few lines of code.

ArrayGif1

Continue reading “AHK: What is an Array?”