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
Newton Excel Bach, not (just) an Excel Blog 
Reblogged this on Sutoprise Avenue, A SutoCom Source.
LikeLike
Pingback: Dynamic sorting with Excel, VBA, and Python | Newton Excel Bach, not (just) an Excel Blog
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?
LikeLike
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.
LikeLike
Thank You very much for explanations.
LikeLike