0

I have a .Net class library that calculates following

double d1 = 114585.8713458709;
double d2 = 3992.809280799137;
double d3 = 563.12764243145182;
d1 += d2 - d3;
return d1.ToString("R");

When I call from .Net console, I get value 118015.55298423857 When I call from excel VBA using interop I get value 118015.55298423859

The value differs by 0.00000000002. What can be the reason for different behavior by same method?

Pary
  • 1
  • In short, VBA is not a precision language. It is not based on the .Net frameworks Double precision but is close to it. For the most part, think of VBA, especially in Excel, as a Monetary precision. So anything past about 5 decimal places, is not guaranteed to be precise without explicitly defining the number of precision points. I would suggest you never compare VBA to .Net, cause you will go prematurely bald during the process. – GoldBishop Sep 23 '17 at 02:44
  • 1
    https://stackoverflow.com/questions/16916073/the-microsoft-ace-driver-changes-the-floating-point-precision-in-the-rest-of-my – Hans Passant Sep 23 '17 at 04:36
  • @GoldBishop - VB/VBA's double is the same as .NET, they are standard IEEE : https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/data-types/double-data-type you probably confuse with the decimal type (which is also the same in VB/VBA and in .NET) but is indeed more a monetary thing – Simon Mourier Sep 23 '17 at 06:13
  • @SimonMourier I have been working in and out of VBA since 96. When .Net came out in early 2000, I started noticing small precision issues, like the OP. So documentation can say one thing....but experience and field knowledge states that they are not evaluated and utilized in the same way. As well, VBA is nothing like .Net, it is more akin to VB6 more than VB.Net. Either way, Excel is not an Engineering or Science's platform and is more monetary oriented. – GoldBishop Sep 25 '17 at 12:01
  • @GoldBishop - You're wrong, or you're not talking of the same thing. COM Automation (VB/VBA/VBScript, etc.) and .NET are using the same exact binary *format*. Why results of computation are sometimes different is perfectly explained by Hans. PS: I've been working with VB since 1991, I win :-) – Simon Mourier Sep 25 '17 at 16:40
  • @SimonMourier I have been working with Basic since 87 (BasicA)...you lose....and I was only 11 yrs old. Basic has never followed the same rules as any other language, until .Net came into scope. At which point, the rules were made available but always with a twist. Basic has never followed the precision like C, ForTran, or even Ada. This is going to be a philosophical debate on semantics but the fact remains that VBA is not an Managed language like .Net. Therefore, it follows its own rules to "Try" to abide by the Managed parameters. – GoldBishop Sep 26 '17 at 02:52

0 Answers0