Sorting A List Of Numbers Using VBA and Excel

Sorting A List Of Numbers Using VBA and Excel

Sorting A List Of Numbers Using VBA and Excel

Knowing how to sort a list using VBA code can save a lot of frustration. Our human brains find it easy to identify and arrange items in order, but transferring this into a computer program is surprisingly complex.

Although Excel has excellent sorting tools, it makes sense to know the VBA code for standard sorting. For example, you might be importing a list of numbers and want to sort them before saving them into a range of cells.

A Simple Numerical Sort Procedure

Consider how you might sort a list of numbers in real life.

    Consider the first numberCompare it to every other number of the list and change its positionRepeat the process for each list item

Writing a program to duplicate this process is a little more complicated but we can still follow the same process.

First, we’ll place numbers in an array.


myList="4,2,9,3,10,5"
arr=split(myList,",")

Next, we’ll apply the pseudo code we worked out above. We’ll loop through the array and compare each item to every other list member and rearrange them in the correct order.

Where it gets complicated it that we then repeat the process for every other number in the list.


For i = 0 to ubound(arr)

We begin the loop here and then search the rest of the list before moving to the next number.


For j = i + 1 To mx

If the current number is greater than any of the others then the array items swap places


If CLng(arr(i)) > CLng(arr(j)) Then
tmp = arr(i)
arr(i) = arr(j)
arr(j) = tmp
End If
Next j

With the process completed, we move to the next number.


Next i

The array should be now sorted in order.


for x=0 to ubound(arr)
debug.print arr(x) & ",";
next

'Output
2,3,4,5,9,10

Further Development of the Sort Procedure

The basis of this type of code can be used to sort alphabetical lists as well. Letters of each word could be assigned numbers and sorted on that basis.

It would add complexity to sort by additional letters in each word but simply involves additional looping.


allLetters = "a,b,c,d,e,f... "
firstLetter = Mid("John", 1, 1)
numberOfLetter = InStr(allLetters, LCase(firstLetter))

As with a number of Excel and VBA issues, developers need to decide at what point to utilize standard Excel tools and formulas rather than hard coding to get the same result.