模拟VBA算术.NET [英] Simulate VBA arithmetic in .NET

问题描述

免责声明：我知道，0.025不能重新$ P $的IEEE浮点变量psented完全，因此，取整可能无法返回人们所期望的。那不是我的问题！的

---

是否有可能以模拟.NET的VBA算术运算符的行为？

例如，在VBA，下面前pression收益率 3 ：

 昏暗myInt32只要
myInt32 = CLng函数（0.025 * 100）'产量3
 

然而，在VB.NET中，下前pression收益率 2 ：

 昏暗myInt32作为整数
myInt32 = CINT（0.025 * 100）'产量2
 

根据本说明书中，两者都应该返回相同的值：

• 龙（VBA）和Integer（VB.NET）都是32位整数类型。
• 根据的VBA规范，CLng函数执行令，强制龙，和字母数值类型之间强制使用银行家的舍入。 也是如此以VB.NET的CINT。
• 0.025 是一个双precision IEEE浮点在这两种情况下不断。

因此​​，浮点乘法操作者或所述整数转换运算的一些实现细节改变。但是，出于兼容性与传统VBA系统的原因，我需要复制的.NET应用程序VBA的数学行为（但是错了，它可能是）。

有没有一些方法来做到这一点？是不是有人写了 Microsoft.VBA.Math 库？或者说是的 precise 的VBA算法记录的地方，所以我可以做自己？

解决方案

VBA和VB.NET的行为不同，因为VBA使用的 80位扩展precision 中间浮点运算（即使双击是64位型），而VB.NET总是使用64位precision。当使用80位precision，0.025 * 100的值比2.5稍大，所以 CLng函数（0.025 * 100）向上舍入到3。

不幸的是，VB.NET似乎并没有提供80位precision算术。作为一种变通方法，您可以创建一个本机Win32 DLL使用Visual C ++和通过P / Invoke的调用它。例如：

的#include＆LT; CMATH＆GT; ＃包括＆LT; FLOAT.H＆GT; 的#pragma评论（连接器，/输出：MultiplyAndRound = _MultiplyAndRound @ 16） 为externC__int64 __stdcall MultiplyAndRound（双X，双Y） {     unsigned int类型CW = _controlfp（0，0）;     _controlfp（_PC_64，_MCW_PC）; //使用80位precision（64位有效数字）     双重结果=地板（X * Y + 0.5）;     如果（结果 - （X * Y + 0.5）== 0＆安培;＆安培; FMOD（结果，2））         结果 - = 1.0; //回合下来，即使中途之间的偶数和奇数     _controlfp（CW，_MCW_PC）; //恢复原来的precision     返程（__int64）的结果; }

而在VB.NET：

 声明功能MultiplyAndRound库FPLib.dll（BYVAL X作为双，BYVAL y为双人间）只要

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
 

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 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

这篇关于模拟VBA算术.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！