Sorting with Python

A previous post provided a VBA Sort function that could be used as a User Defined Function (UDF) to provide a sorted list on the spreadsheet, or called from another VBA routine to sort an array.

I have just written a Python/PyXll routine to provide the same functionality, including sorting on any specified column, and sorting in ascending or descending order.  Here is the code in full:

from operator import itemgetter
@xl_func("var srange, int sortcol, bool rev : var")
def py_Sort(srange, sortcol, rev):
    return sorted(srange, key = itemgetter(sortcol), reverse = rev)

The py_Sort function requires Python and PyXll to be installed, but otherwise works the same as the VBA function, and a quick test indicates that it is about twice as fast.  The screen-shot below shows output from the VBA and Python function:

VBA and Python Sort Functions

VBA and Python Sort Functions

This entry was posted in Arrays, Excel, Link to Python, UDFs and tagged , , , , , . Bookmark the permalink.

5 Responses to Sorting with Python

  1. Pingback: Dynamic sorting with Excel, VBA, and Python | Newton Excel Bach, not (just) an Excel Blog

  2. Chris says:

    We can manipulate values or anything else in Excel spreadsheets using VBA. So why shoud we use Python instead VBA? I have MS Office with Excel, and I can write some simple macros in VBA, so I see no need to learn Python for making scripts/macros. What is the advantage of using Python instead of VBA?


    • dougaj4 says:

      The main advantage for my purposes is that it gives easy access to fast compiled matrix arithmetic routines, that speed up structural analysis programs by a factor of up to 100, and it also has built-in routines for things like curve fitting, solving polynomial equations etc. The sort routine is an example where programming in Python is much simpler than doing the same thing in VBA, and also gives better functionality.

      That said, for most purposes using VBA alone will be just as good as going through Python, and for someone who isn’t familiar with Python it is much easier and quicker to code, and the end result will sometimes give better performance as well. Most of what is available here is in VBA, and where compiled routines give a significant advantage there are also versions linking to C++, C# and Fortran.

      If you are specifically interested in a VBA sort routine, see the pingback just above your comment. Also look at the Downloads by Category page for related topics.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s