Simulate VBA arithmetic in .NET

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 LongConsole.WriteLine(MultiplyAndRound(2.5, 1))       ' 2Console.WriteLine(MultiplyAndRound(0.25, 10))     ' 2Console.WriteLine(MultiplyAndRound(0.025, 100))   ' 3Console.WriteLine(MultiplyAndRound(0.0025, 1000)) ' 3

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 IntegermyInt32 = CInt(2.5) ' 2myInt32 = 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 * 100Dim delta As Double = Double.EpsilonDim floor As Integer = Math.Floor(result)If Math.Abs(result - (CDbl(floor) + 0.5)) <= delta Then   result = floor + 0.5End

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.