Beware of the mod

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.

This entry was posted in Excel, UDFs, VBA and tagged , , . Bookmark the permalink.

2 Responses to Beware of the mod

  1. Pingback: Beware of the mod | Newton Excel Bach, not (just) an Excel Blog : Best Mod . com

  2. Georg says:

    MS seem to have changed the behaviour of the MOD function at least in recent versions of VB: , where they claim “47.9 Mod 9.35” to evaluate to “1.15”. I do not know whether or when it will be introduced in VBA (or even has been introduced???).
    This “strange” generalisation of MOD from long to double nearly drove me crazy when trying to port the code found in Jan Meeus’ first Astronomy morsels book to VBA a few years ago… (some nice teethprint of mine can still be seen in my desk top…). And it reminds me of the (in-)famous difference between Excel and VBA concerning dates: in Excel 1900 is erroneously identified as leap year (somewhere on the web you can find that they wanted to mimic the market leader’s application at the time MS invented Excel…), but in VBA (published a few years after Excel) it is not…
    Happy Easter to everybody!


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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.