A Sort Function

Posted on 23/03/2009 by dougaj4

There is often a need to sort data within a VBA routine, but VBA does not have a built in sort function, so I recently had a look for what is on offer on the Internet.

Chip Pearson has an article on Sorting Array in VBA, which provides a method to transfer data to a worksheet, sort it,  and bring it back, which has some disadvantages which he discusses in the article.  It also has an implementation of the Quick Sort method which I will have a closer look at when time allows.

John Walkenbach has an article at Daily-Dose-of Excel on Dynamic Sorting with a UDF, which whilst interesting has the disadvantages that it doesn’t work in Excel 2007, and is not supposed to work (but does) in earlier versions.

The procedure I ended up adapting comes from Xtreme Visual Basic Talk, and is an implementation of a comb sort.  It was written in VB for arrays of longs, but was easily adapted to VBA, with the additional features:

  • It can be used as a VBA function to sort an array, or on a worksheet as a User Defined Function (UDF).
  • It will sort multi-column ranges or arrays, with a selected column as the sort key.
  • The sort can be ascending or descending.
  • The data can be numbers, text, or mixtures of both.

When used as a UDF the function must be entered as an array function:

  •  select the range for the sorted data
  • enter the function
  • Press ctrl-shift enter

When used with a VBA array the array must be specified with 2 dimensions; i.e a vector array with n entries must be converted to an nx1 array .

The comb sort was chosen because it is reasonably fast, was simple to convert to variant arrays, and was found to be stable with large data sets.  Sorting a single column array of 1 million random numbers takes about 20 seconds.  For best performance on very large data sets the Radix method is superior.  If anyone knows of an implementation of this method that will work on a mixed list of floating point numbers, longs,  and strings I would be interested to see it.

A sample worksheet with open source code can be downloaded from:  Sort Func.zip

Screen shot of the function used as a UDF (click image for full size view):

sortfunc

Posted in Arrays, Excel, UDFs, VBA | Tagged , , , | 21 Comments

By Jingo

Posted on 18/03/2009 by dougaj4

Dick Kusleika at Daily-Dose-of-Excel recently posted a video of an animated bingo spreadsheet, using the Jing screen capture program.

I had previously posted a still image of my Newton’s Cradle animation, which rather spoils the point, so here it is in glorious movie-colour:
(For a high resolution version, visit screencast.com)

Vodpod videos no longer available.
more about “By Jingo“, posted with vodpod
Posted in Animation, Excel, Newton, VBA | 1 Comment

Gravity Calling

Posted on 17/03/2009 by dougaj4

Flipron are a four-piece band that produce an otherworldly collision of bittersweet melody and vaudeville panache.

At least that’s what it say’s here

This YouTube clip features a video of the title track from thier latest album, Gravity Calling

The video was directed by up and coming young London based film director Kerinne Jenkins, who apart from being young and up and coming, is also my daughter.

Posted in Bach | Tagged , | Leave a comment

Writing an array to a worksheet range – correction

Posted on 14/03/2009 by dougaj4

The Microsoft Excel blog has some tips for speeding up VBA performance in Excel 2007, which apply equally to earlier versions.

The number 2 tip (which in my opinion should have been the number 1 tip, and probably the number 3 to 10 tip as well) was “Read/Write Large Blocks of Cells in a Single Operation”.   This could be expressed more generally as “minimise the number of data transfer operations between the worksheet and VBA”, which then also covers the benefits of writing VBA code rather than using a .worksheetfunction call for functions that are found in Excel but not VBA.

I covered this subject in one my first posts to this blog Ranges and Arrays-2, but looking back at this post I see that the code did not work as intended.  What I wrote was:

With Range("MyNamedRange")
.Resize(NumArrayRows, NumArrayColumns).Name = "MyNamedRange"
.Value = MyArray
End With

This is intended to resize the worksheet range to the same size as the VBA array, then transfer the contents of the array to the worksheet.  The code does resize the range, but because we are still inside the “with” statement the array is transferred to the original range size. If this is smaller than the array the array will be truncated, and if it is larger the cells outside the array dimensions will be filled with “#/NA” symbols.

What we need to do is close the “with” statement, then transfer the data to the now re-sized array.  While we are at it we will normally want to clear old data from the worksheet range, which can be done with a .ClearContents statement within the with block.

Finally for large blocks of data, and where you don’t need the date or currency data types, there is a speed advantage in using .Value2, rather than .Value.  Look here: Transferring information from Excel Ranges to the UDF for the reason why.

So the final code is:

ReDim myarray(1 To NumArrayRows, 1 To NumArrayColumns)

‘ Fill array

With Range(“MyNamedRange”)
.ClearContents
.Resize(NumArrayRows, NumArrayColumns).Name = “MyNamedRange”
End With
Range(“MyNamedRange”).Value2 = myarray

As usual, if this code is copied and pasted into the VBE you will nead to replace all the “smart-quotes” with proper quotes, and put a proper apostrophe before the comment line.

Posted in Excel, VBA | 6 Comments

Evaluate integrals to a specified tolerance

Posted on 10/03/2009 by dougaj4

A previous post presented a user defined function (UDF) to perform a numerical integration of a function specified as a text string.  The UDF allowed the number of subdivisions of the integration range to be specified, but the number of subdivisions required to reach the required precision of the result had to be found by trial and error.

The Numerical Methods Guy has recently posted a method of performing the integration with the number of subdivisions doubled at each successive stage, until the required precision is reached.  The beauty of this approach is that all the calculations used in the earlier stages are used in the final solution, so the calculation time is virtually the same as if the minimum required number of subdivisions had been known at the outset.  I have used this approach to adapt the Excel UDF EvalInt(), the new function being EvalIntT, which is included in Eval.zip.   As usual, the download includes full open source code.

 The Numerical Methods Guy’s posts cover only the trapezoidal rule, but the same approach has been applied using Simpson’s Rule, which will normally give a much faster solution.  Examples of the output of EvalIntT are shown below:

EvalIntT Function Results

EvalIntT Function Results

The results shown are for the example given at The Numerical Methods Guy blog, and are virtually identical to the results given by the Matlab function listed there.  Note that using Simpson’s Rule the function has given a result to the specified precision with 512 subdivisions, compared with 32768 for the trapezoidal rule, and the solution time is over 60 times faster.

When entering the function data take care when the function text includes any of the paramaters, as in this case x is included in “exp”.  This problem is avoided by entering the function in lower case and the paramaters in upper case, so the correct substitutions are made when the function is evaluated.

Posted in Excel, Maths, Newton, UDFs | Tagged , , , , , | 2 Comments