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.
Nope. I spent the last two days being bitch slapped with VBA errors.
So in the end, I stole the meat from this script posted by a wonderful human being named John Coleman. Coleman’s VBA is set up to run on a range of cells.
Only difference is that I want to be able to convert anything in my worksheet into a range in a separate cell, so this needs to be a User Defined Function. Since my numbers are in the same cell, they need to be delineated by commas. Luckily my Macros is insanely comma-happy. It puts them everywhere. Sometimes quite unnecessarily at the beginning of a list of numbers, so I need to modify the function to make it work both with and without a leading comma. Also, VBA is really nit-picky about data types. My number list is saved as a string. The first hurdle is just getting Excel to recognize those as integers.
Step 1: Split a String into a String Array
You can take a string and parse it into an array by using Split(). As written below, it’ll take the string textString, find the commas, and chop it up there.
numberArray() = Split(textString, ", ")
To get the lowest and highest index of an array, you can use LBound and UBound. This is really useful for running loops through an array.
Step 2: Convert That String Array into an Integer Array
Say our string array is called numberArray(). You could set a For Loop to run through each index to the highest one and run CInt() to turn the values into integers. Then stash them into an integer array for later reference. Like so:
Dim numberArray() As String Dim intArray() As Integer, size As Integer, a As Integer size = UBound(numberArray) ReDim numbers(size) For a = 1 To UBound(numberArray) intArray(a) = CInt(numberArray(a)) Next a
I know what you’re thinking. What’s that ReDim line for? You can use that to change the size of an array—in this case, we’re changing the integer array (intArray) to be the same size as the string array (numberArray).
You may also be wondering why the conversion is necessary at all? The meat of the script needs to do addition and subtraction on the values. Anytime you need to run math on numbers, they need to be available as some sort of number data type (integers, floats, doubles).
Step 3: Fix the Leading Comma Problem
When I started writing this, I got it to work for cells that start with a comma and a space. Like so.
Then I could not, for the life of me, figure out why it wouldn’t work on a list that doesn’t start with a comma (notice how C3 is missing the number 1?). The Split() function is meant to take the value before the first delimiter too. So I started looping through my list of 10 Most Frequent Lapses In Coding Logic. And I’m pretty sure this is because of #1—Arrays start counting at 0. Meaning when I split an array that start with a comma, it doesn’t store anything at Index 0. But when I split an array that doesn’t have a comma, it stores the first number at Index 0.
And I, not being the sharpest tool, started my conversion loop at a=1. It ignores anything stored at the first index (0).
And I, being lazy, decided that fixing the underlying logic problem would be harder than just adding a comma to the beginning of the string if it doesn’t already have one. That’s why these lines are there:
If Left(textString, 2) <> ", " Then textString = ", " & textString End If
Step 4: Apply The Logic
The IIf line is really key in here. Essentially what that does is tell Excel that if the starting number within the range is less than the ending number, we want it to push both the start and end numbers into the string with a dash. If the start and end number are the same, we need it to just add the end number once with a comma to start off the new range.
The syntax for IIf is like this:
IIf(Expression, Return Value if True, Return Value if False)
You can see that in action here:
The IIf helps Excel avoid duplication for number 7, which is a range all on its own. Otherwise, VBA would return 7-7.
The loop is set to run 13 times in the gif because the highest index is 13. That, however, terminates before the addition of the final range.
This works out well though, because if it ran all the way through, we’d have a trailing comma. Better to just add in one last line after it’s all said and done to process the final range.
LstRng = IIf(startNum < endNum, startNum & "-" & endNum, endNum) strg = strg & LstRng
And that’s about it. Full script is below to be plugged into a module for use inside a spreadsheet.
Function ListToRange(textString As String) 'THIS IS TO CONDENSE THE NUMBERS Dim numberArray() As String Dim strg As String Dim numbers() As Integer, size As Integer, a As Integer Dim newstrng As String If Left(textString, 2) <> ", " Then textString = ", " & textString End If numberArray() = Split(textString, ", ") size = UBound(numberArray) ReDim numbers(size) For a = 1 To UBound(numberArray) numbers(a) = CInt(numberArray(a)) Next a Dim startNum As Integer Dim endNum As Integer Dim Value As String Dim LstRng As String startNum = CStr(numbers(1)) endNum = startNum For X = 2 To UBound(numbers) If numbers(X) <= endNum + 1 Then endNum = numbers(X) Else Value = IIf(startNum < endNum, startNum & "-" & endNum & ", ", endNum & ", ") strg = strg & Value startNum = numbers(X) endNum = startNum End If Next 'To complete the last range LstRng = IIf(startNum < endNum, startNum & "-" & endNum, endNum) strg = strg & LstRng ListToRange = strg End Function
3 Replies to “VBA: How to get a Range of Numbers from a List”
VBA regret :
Bad syntax, still used
dim etc really . . .
LikeLiked by 1 person
Yeah, VBA is clunky alright. But on the upside, it looks so confusing that no one who doesn’t already know it wants to learn it. And if you know it, people think you’re a magician 🙂
LikeLiked by 1 person
like me despite several langs i have yet to learn it !
LikeLiked by 1 person