Mod calculation in VBA-CodePudding

why vba shows answer "0" when Google shows "11.05"?

Sub test2()
Debug.Print 22.15 Mod 11.1
End Sub

Q: Is it possible to get in VBA result the same as Google provide?

UPD2:VBA's Mod operator (not function) differs significantly from Excels MOD function is a few respects. First, it handles negative values differently... second, if you use 1 as the divisor, it will return 0, not the decimal portion of the floating point number... third, it handles floating point number differently (it uses Banker's Rounding to round all floating point number to whole numbers before performing it operation on those numbers whereas Excel doesn't).

CodePudding user response：

As Mod in VBA only deals with integers, you'll have to scale your values, for example with 10 ^ 3 (1000) if you have values of three decimals or less:

DecimalCount = 3
Scaling = 10 ^ DecimalCount

Debug.Print (22.15 * Scaling Mod 11.1 * Scaling) / Scaling
 11.05 

Debug.Print (22.15 * Scaling Mod 11.075 * Scaling) / Scaling
 0

Just adjust DecimalCount to match your expected values.

CodePudding user response：

An alternative to Gustav's answer that doesn't need the scaling: First make an regular division, use the integer part of the result (integer quotient) and subtract the divisor times the (integer) quotient from the dividend.

Function ModDouble(a As Double, b As Double) As Double
    Dim y As Double
    y = (a / b)
    ModDouble = a - (b * Int(y))
End Function

Testing it:

Sub test()
    Debug.Print ModDouble(5, 3)
    Debug.Print ModDouble(22.15, 11.1)
End Sub

> 2
> 11.05