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.