Simulate VBA arithmetic in .NET

Question

Disclaimer: I know that 0.025 cannot be represented exactly in IEEE floating-point variables and, thus, rounding might not return what one would expect. That is not my question!

---

Is it possible to simulate the behavior of the VBA arithmetic operators in .NET?

For example, in VBA, the following expression yields 3:

Dim myInt32 As Long
myInt32 = CLng(0.025 * 100)      ' yields 3

However, in VB.NET, the following expression yields 2:

Dim myInt32 As Integer
myInt32 = CInt(0.025 * 100)      ' yields 2

According to the specification, both should return the same value:

• Long (VBA) and Integer (VB.NET) are 32-bit integer types.
• According to the VBA specification, CLng performs Let-coercion to Long, and Let-coercion between numeric types uses Banker's rounding. The same is true for VB.NET's CInt.
• 0.025 is a double precision IEEE floating-point constant in both cases.

Thus, some implementation detail of the floating-point multiplication operator or the integer-conversion operator changed. However, for reasons of compatibility with a legacy VBA system, I'd need to replicate the mathematical behavior of VBA (however wrong it might be) in a .NET application.

Is there some way to do that? Did someone write a Microsoft.VBA.Math library? Or is the precise VBA algorithm documented somewhere so I can do that myself?

Answer 1

VBA and VB.NET behave differently because VBA uses 80-bit "extended" precision for intermediate floating-point calculations (even though Double is a 64-bit type), whereas VB.NET always uses 64-bit precision. When using 80-bit precision, the value of 0.025 * 100 is slightly greater than 2.5, so CLng(0.025 * 100) rounds up to 3.

Unfortunately, VB.NET doesn't seem to offer 80-bit precision arithmetic. As a workaround, you can create a native Win32 DLL using Visual C++ and call it via P/Invoke. For example:

#include <cmath>
#include <float.h>

#pragma comment(linker, "/EXPORT:MultiplyAndRound=_MultiplyAndRound@16")

extern "C" __int64 __stdcall MultiplyAndRound(double x, double y)
{
    unsigned int cw = _controlfp(0, 0);
    _controlfp(_PC_64, _MCW_PC); // use 80-bit precision (64-bit significand)
    double result = floor(x * y + 0.5);
    if (result - (x * y + 0.5) == 0 && fmod(result, 2))
        result -= 1.0; // round down to even if halfway between even and odd
    _controlfp(cw, _MCW_PC); // restore original precision
    return (__int64)result;
}

And in VB.NET:

Declare Function MultiplyAndRound Lib "FPLib.dll" (ByVal x As Double, ByVal y As Double) As Long

Console.WriteLine(MultiplyAndRound(2.5, 1))       ' 2
Console.WriteLine(MultiplyAndRound(0.25, 10))     ' 2
Console.WriteLine(MultiplyAndRound(0.025, 100))   ' 3
Console.WriteLine(MultiplyAndRound(0.0025, 1000)) ' 3

Answer 2

Given that the VBA is supposed to use Banker's rounding, it seems clear to me at first glance that the bug is actually in the VBA side of things. Bankers rounding rounds at the midpoint (.5) so the result digit is even. Thus, to do correct Banker's rounding, 2.5 should round to 2, and not to 3. This matches the .Net result, rather than the VBA result.

However, based on information pulled from a currently deleted answer, we can also see this result in VBA:

Dim myInt32 As Integer
myInt32 = CInt(2.5) ' 2
myInt32 = CInt(0.025 * 100) ' 3

This makes it seem like the rounding in VBA is correct, but the multiplication operation produces a result that is somehow greater than 2.5. Since we're no longer at a mid-point, the Banker's rule does not apply, and we round up to 3.

Therefore, to fix this issue, you'll need to figure out what that VBA code is really doing with that multiplication instruction. Regardless of what is documented, the observations prove that VBA is handling this part differently than .Net. Once you figure out exactly what's going on, with luck you'll be able to simulate that behavior.

One possible option is to go back to the old standby for floating point numbers: check whether you're within some small delta of a mid-point and, if so, just use the mid-point. Here's some (untested) naive code to do it:

Dim result As Double = 0.025 * 100
Dim delta As Double = Double.Epsilon
Dim floor As Integer = Math.Floor(result)
If Math.Abs(result - (CDbl(floor) + 0.5)) <= delta Then
   result = floor + 0.5
End

I emphasize the untested, because at this point we're already dealing strange with results from small computer rounding errors. The naive implementation in this situation is unlikely to be good enough. At very least, you may want to use a factor of 3 or 4 epsilons for your delta. Also, the best you could hope for from this code is that it could force the VBA to match the .Net, when what you're really after is the reverse.