India Gadget Reviews

How To Sort Excel Data Using Formulas And The Dictionary Object In VBA

A common task for Excel users is to sort data, and the sort tool in Excel or VBA's sorting functions are normally the best solution. However, a basic knowledge of how to sort using direct VBA code could be of benefit if you need some flexibility, for example to create your own conditional sorting procedures.

This article shows you how to sort a basic list using the countif function and the dictionary object.

Creating A Sort Procedure

We're going to sort a simple list of numbers in numerical order, but the code will work just as well with a list of names and with a little creativity you should be able to mimic Excel's own sophisticated sorting tool.

Here's our list of numbers:

3
5
0
3
6
1

First, we'll define some objects and variables.

Dim myRange As Range
Dim func As Object
Dim x As Long
Dim curNumb As Long
Dim numbers As New Dictionary

Next, we'll create the range and begin to iterate through the list.

Set myRange = Range("a1").CurrentRegion.Columns(1)
Set func = Application.WorksheetFunction

For x = 1 To myRange.Rows.Count
curNumb = myRange.Rows(x)

With our current number defined, we use the countIf function to find out how many numbers are greater than the number, giving the number a rank. We add 1 to the ranking because, for example countif would return zero for the highest number but we want the rank to be 1.

ranking = func.CountIf(myRange, ">" & curNumb) + 1

Now we'll add the number and the ranking to our dictionary object. One issue is if there are two or more identical numbers they will have the same ranking so we check to see if the ranking exists, and if so increase the rank by 1.

If numbers.Exists(ranking) Then

Do

ranking = ranking + 1

Loop Until Not numbers.Exists(ranking)

End If

The dictionary object is the "key" to this procedure and we can now print out the new list in a sorted format.

For x = 1 To numbers.Count
myRange.Rows(x) = numbers(x)
Next

This gives an output of:

6
5
3
3
1
0

Clearly, either the Excel sort tool or VBA's sort function would do a better job in most situations, but it's good to have a little more control sometimes over what you want your code to do. For example, what if you need some items in a list sorted but not others?

Summary
There are many different ways to sort a list and Excel's tools are the usual way to complete this task. But knowing how to address a solution directly in code can give your greater control and flexibility to get the outcome you need.

Andy L Gibson is a former Web Site programmer rediscovering his interest in Excel applications for small business. His blog at http://solutions4business.wordpress.com/ holds the VBA code for all of his articles on Excel and is available for FREE download. He is developing an Excel application which will contain working examples of VBA methods and solutions.