Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Sorting Data in Microsoft Excel

When one of the Scripting Sons was six years old he played flag football at the Kirkland Boys and Girls Club. The coach of the team had decided that his son was going to be the star running back, and therefore focused exclusively on showing all the other kids how to block. The only thing the Kirkland team did in the practices preceding their first game was work on blocking; after all, the better the rest of the players blocked the more touchdowns the coach’s son could score.

At the team’s first game Kirkland kicked off; the return man on the opposing team caught the ball and ran untouched into the end zone. Not only did he run untouched, but he also ran without anyone pursuing him: all the Kirkland kids were busy blocking. Turns out that the coach had neglected to fill the players in on one important facet of flag football: when the other team has the ball, you’re supposed to run after them and try to grab the ball carrier’s flag.

Oops.

So what prompted that trip down memory lane? Oh, nothing really…well, except the fact that we’re now five months into the Office Space column and we still haven’t shown you how to sort data in Microsoft Excel. But that wasn’t because we were overlooking such an important and fundamental part of Office scripting, it’s just that – well, OK, maybe it was because we were overlooking such an important and fundamental part of Office scripting. But we’ll make up for that, starting right now.

In order to keep things simple we’re going to assume that we have a basic spreadsheet (C:\Scripts\SortTest.xls) containing the following data:

The actual spreadsheet itself looks something like this:

How do we sort something like this? Here’s one way:

Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = _ 
    objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")

Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("A1")

objRange.Sort objRange2, xlAscending, , , , , , xlYes

Don’t worry; we’ll explain how this all works. We begin by defining a pair of constants – xlAscending and xlYes – and setting the value of each of these to 1; we’ll explain what these constants are for momentarily. We then create an instance of the Excel.Application object and set the Visible property to True; that gives us an instance of Excel that we can see onscreen. Next we use this line of code to open the workbook C:\Scripts\SortTest.xls:

Set objWorkbook = _ 
    objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")

So far so good. With the workbook open we use this line of code to bind to the first worksheet:

Set objWorksheet = objWorkbook.Worksheets(1)

In order to sort the data we need to create a couple of Range objects. First we need to indicate the data that we want to sort. For this sample script that happens to be the entire worksheet; this code provides a quick and easy way to select the range of used cells in the worksheet (note that we give this range the object reference objRange):

Set objRange = objWorksheet.UsedRange

Having identified the data to be sorted we now need to indicate which column we want to sort by (for this first go-round we’ll sort by just one column). To indicate the column to sort by we need to select a cell somewhere (and it could be anywhere) in that column; because we want to sort by column A all we have to do is create a range consisting entirely of cell A1. That’s what we do here, giving this range the object reference objRange2:

Set objRange2 = objExcel.Range("A1")

We’re now ready to sort the data, something that requires just a single line of code:

objRange.Sort objRange2, xlAscending, , , , , , xlYes

As you can see, we simply call the Sort method followed by a bunch of crazy-looking parameters. These parameters, in order (which is very important) consist of the following items:

See? Not too terribly complicated. When we run the script we should get something that looks like this:

As you can see, the data has been sorted alphabetically by Division (that is, by column A). Not bad, not bad at all.

OK, but what if we wanted to sort by Sales Total (column C). No problem:

Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = _ 
    objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")

Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("C1")

objRange.Sort objRange2, xlAscending, , , , , , xlYes

Notice that our object reference objRange2 now points to column C; that’s what this line of code does:

Set objRange2 = objExcel.Range("C1")

Other than that the code remains pretty much unchanged. When we run this script we get back the following:

Which is just what we wanted.

Oh, my: so many questions! OK, so what if we wanted to sort first by column C and then by column A. Admittedly, that won’t have much impact with this simple data set. But here’s what the script would look like:

Const xlAscending = 1
Const xlYes = 1

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = _ 
    objExcel.Workbooks.Open("C:\Scripts\SortTest.xls")

Set objWorksheet = objWorkbook.Worksheets(1)
Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

objRange.Sort objRange2, xlAscending, objRange3, , xlAscending, , , xlYes

As you can see, we defined two sort ranges in this script: objRange2 (which references column C) and objRange3 (which references column A). Note, too that when calling the Sort method we specify objRange2 followed by the sort order (xlAscending). We then specify objRange3 followed by a blank space and then the sort order. Remember, the blank space represents the Type parameter, a parameter used only with PivotTables. Even though we don’t use it, we have to leave a spot for it; otherwise the script will probably fail. If you’re going to make a mistake when sorting data in Excel there’s a good chance this will be where you get mixed up.

You say you want to sort by three columns, the maximum number of columns you can sort by in Excel? Hey, why not? We won’t show you the entire script; we’re confident that you can figure that out for yourself. But the Sort command might look something like this:

objRange.Sort objRange2, xlAscending, objRange3, , xlDescending, _
    objRange4, xlDescending, xlYes

Three object references and three sort orders. That’s pretty much all there is to it.

Today we’ve learned that in flag football you need to grab the opposing ball carrier’s flag and in Excel you need to use the Sort method and a bunch of parameters in order to sort data. There might be more to life than that but, at the moment anyway, we can’t imagine what that could be.