Beware of the mod

Posted on 22/04/2011 by dougaj4

Playing with a simple VBA user defined function (UDF), inspired by a thread at Daily Dose of Excel, I was finding it was mysteriously giving the wrong answer.

The task was to find the angle between the hands of an analog clock for any given time (given as a time serial number).  I came up with this formula on the spreadsheet:

  • =ABS(MOD(G2,1/2)*2-MOD(G2,1/24)*24)*360

which works, but when transferred to a VBA function:

Function Timeangle(TimeNum As Double) As Double
Timeangle = Abs((TimeNum Mod (1/2))*2 - ((TimeNum Mod (1/24))*24)) * 360
End Function

the UDF returned an error.

It seems that, for some reason best known to Microsoft, the VBA Mod function converts all values to Longs before doing its work, so any double between zero and one will be converted to zero, resulting in a divide by zero error in this case.

The solution to the problem is to not use the Mod function:

Function Timeangle(TimeNum As Double) As Double
Timeangle = Abs((TimeNum * 2 - Int(TimeNum * 2)) - (TimeNum * 24 - Int(TimeNum * 24))) * 360
End Function

An easily spotted error for a trivial application in this case, but this “feature” could cause some really hard to find bugs in a more complex application.

Posted in Excel, UDFs, VBA | Tagged , , | 2 Comments

Calling Add-in functions from VBA

Posted on 16/04/2011 by dougaj4

When an Excel add-in is active any User Defined Functions (UDFs) in the add-in are available to any other open worksheet.  It might be expected that the same would apply to VBA routines defined in other files, but if you try calling an add-in function from VBA (other than from the add-in itself), you will find the function is not recognised.

This is an easy problem to fix.  The add-in needs to be referenced in the Visual Basic Editor.  Select Tools-References and either select the add-in from the list of available references, or if it is not on the list, use the browse button and select the add-in file.

Note that:

  • The add-in must be referenced for each file in which you wish to refer to it from VBA.
  • The add-in is referenced using the VBA Project name, not the file name.  It follows that the project name must be changed from the default (VBAProject), before selecting the reference.

An example of referencing the XNumbers add-in is shown in the screen shot below:

Referencing and add-in

Posted in Excel, UDFs, VBA | Tagged , , , , | 2 Comments

XLW

Posted on 13/04/2011 by dougaj4

Prompted by a thread at The Wilmott Forums that linked here, I was prompted to take a look at XLW- A Wrapper for the Excel API.  This is what they say about themselves:

“XLW is an open source application that wraps the Excel C API in simple C++, C# or VB.NET interfaces which you can use to customize Excel with your own worksheet functions and menu items.

XLW developers include Financial Engineering practitioners with extensive experience of developing quantitative analytics in the finance industry including Mark Joshi the author of The Concepts and Practice of Mathematical Finance and C++ Design Patterns and Derivatives Pricing.”

I have yet to try it, so this isn’t a recommendation, but it does look interesting.

The Wilmott Forum thread is also worth a detailed read for different opinions on the best way to link Excel to compiled code.

Edit 14 Apr 2011:

Found via: http://chriscavanagh.wordpress.com/2008/04/13/excel-add-ins-in-c/

Moving from an Excel xla add-in to a C# add-in – a detailed account of the trials and tribulations of moving from an xla add-in to C#.  Not directly related to XLW, but well worth a read for anyone contemplating the same process.

Posted in Excel, Link to dll | Tagged , , | Leave a comment

Alternative IP and Rotations

Posted on 11/04/2011 by dougaj4

In response to the 3D Intersections and PView Function posts Lori Miller has sent me two very elegant alternative ways of achieving similar results.

The first is an on-spreadsheet solution to the intersection of two lines, using the LinEst function:

Intersection point of two 3D lines

Note that if the lines intersect, Point 1 and Point 2 will have the same coordinates, otherwise they will be different.

The second is a perspective projection of a hypercube (specifically a 4D hypercube, or tesseract):

Tesseract Perspective Projection

 It took me some time to work out how this one worked.  The file is an xlsx, so there is no VBA, but also there is no visible data (other than a single number associated with each of the slider bars), but no hidden rows, columns or sheets either.  The secret is that all the data and calculations are in array formulas in defined names.  Have a look in the name manager and see if you can work out the algorithm.

The files may be downloaded from:

LineIP.xlsx

Hypercube.xlsx

Posted in Arrays, Charts, Charts, Coordinate Geometry, Excel | Tagged , , , | Leave a comment

Tanh-Sinh Quadrature V2.0

Posted on 04/04/2011 by dougaj4

I recently received an update of the Tanh-Sinh Quadrature spreadsheet from Graeme Dennes, which can be downloaded from:  Tanh-Sinh Quadrature (including, as usual, full open source code). – Link fixed 5th April 2011

Here is Graeme’s description of the updates:

The Tanh-Sinh program and the two Double Exponential programs have been revised to improve their robustness when dealing with functions and limits for which overflow can occur. Previously, overflow events were managed by the automatic tolerance feature I introduced, which worked very well – for most situations. This revision takes a different path, in that the abscissas and weights for the interval (-1,1) are generated first, and stored in an array. The abscissas and weights are independent of the integrand and the limits, and are based only on the given tolerance figure (10^-14), Excel’s smallest positive floating point number (10^-305, with just a tad in reserve), and the method itself. The abscissas and weights are different for each program. The programs then access their array data as required. (Previously the abscissas and weights were calculated on the fly by the integration programs themselves.) Thus, overflow should not be possible with this new method.

The core technique (pre-generation of the abscissas and weights and overflow prevention) is by Takuya Ooura.

Consequently, the automatic tolerance feature has been removed from the three programs as being redundant, and all related text has been removed.

As a further change, the optional inputs of tolerance and level/panels/loops have been removed from all five programs, as these are no longer considered necessary or useful. All related text has also been removed.

In summary, overflow should not occur, the results should be at the maximum accuracy possible, and the programs’ use has been simplified.

Previous post on Tahn-Sinh Quadrature.

Posted in Excel, Maths, Newton, Numerical integration, UDFs, VBA | Tagged , , , , | 2 Comments