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?

VBA/VBScript/VB6 did rounding the way you were taught in school, .5 goes up, always. In DotNet, they moved to a true mathmatical rounding where it goes up one time and down the next, leaving you with a more accurate number when coming up with an average. If you want to round the way VBA does, you need to look at using FLOOR or CEILING. — Steve, Oct 21 '13 at 15:44
@Steve: Nope. The VBA specification explicitly mentions that VBA uses Banker's rounding, which is exactly the same thing .NET uses. Try `CLng(0.035 * 100)` and `CLng(0.045 * 100)` in VBA for comparison. — Heinzi, Oct 21 '13 at 15:49
It appears that `*` returns a different result in VBA vs .NET: 2.5 - 0.025*100 = -1.38777878078145E-16 in VBA, 0 in .NET. — Michael Liu, Oct 21 '13 at 15:52
@MichaelLiu: True, but this might the result of a VBA compiler optimization: Note that `2.5 - CDbl(0.025*100)` yields `0` in VBA. (CDbl should be NO-OP for doubles.) — Heinzi, Oct 21 '13 at 15:55
@varocarbas - downvoting is anonymous, you can't say for any certainty *who* downvoted you. It's distinctly possible that two people coming by (unrelated to the OP or any of the answerers) didn't like your answer for some reason. Your answer really didn't address Heinzi's main issue, anyway. — LittleBobbyTables - Au Revoir, Oct 21 '13 at 16:27
@LittleBobbyTables I recognised this point but, as far as what Heinzi wants, is virtually unsolvable; getting a proper source of knowledge sounds quite acceptable. Actually it might have helped others intending to come up with a solution (like tinstaafl who has also got a -1) to understand the reality better; or even given some hints to people moving from VBA to VB.NET. I didn't say anything wrong and wrote a pretty big and comprehensive answer, -2 this kind of approaches (people intending to help you and anyone else writing general enough statements) is highly censorable IMO — varocarbas, Oct 21 '13 at 16:33
@varocarbas: I just returned to my computer. I'm sorry that you got downvoted, it wasn't me. When I wrote that I appreciate your answer, I really meant it. I did not upvote it yet, because I upvote answers that are helpful but don't answer the question only *after* an answer that actually answers the question has been posted (because pre-existing highly upvoted answers can stop people from participating). — Heinzi, Oct 21 '13 at 19:51
Don't be sorry for me, but for this attitude. I didn't say all that because I was the target of the downvotes (I don't really care... if it gets below certain limit, I consider that the community don't want it and I delete my answer), but because the coward attitude of "attacking" (I mean... this is the most aggressive action you can do in SO) someone who intends to help, didn't say anything wrong and, actually, provided a relevant input for others. Anyway... finally you got your answer (I wasn't too optimistic on this front), thanks to the patience.... — varocarbas, Oct 21 '13 at 19:57
... if someone would have downvoted Michael Liu -2 (cowardly and unmotivatedly) like it happened to me, most likely he would have stop worrying about all this or deleted his answer. You wouldn't have got your solution neither a so brilliant answer wouldn't have ever been created. That's why, I complained: fanatics and cowards tend to avoid nice things to happen (I, personally, don't feel like doing anything when this people is present). On the other hand, stimulating attitudes tend to promote them (+1s make you feel like you have to do more). Thanks for you message though. — varocarbas, Oct 21 '13 at 20:00

Michael Liu · Accepted Answer · 2013-10-21T19:53:39.400

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

You might want to try playing with changing the way the compiler handles floating point:see http://msdn.microsoft.com/en-us/library/e7s85ffb.aspx — Charles Williams, Oct 21 '13 at 17:20

Joel Coehoorn · Answer 2 · 2013-10-21T20:20:46.607

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.

Simulate VBA arithmetic in .NET

2 Answers2

Linked

Related