1.045 - 1 = error 1

[abig99]

Im getting some problems while trying to create my own mod function as the built in one doesnt allow for decimals and rounds the figure.

Function KPMod(vNewMainVal, vNo)
Do Until vNewMainVal < vNo
vNewMainVal = (vNewMainVal - vNo)
Loop
KPMod = vNewMainVal
End Function

I had something put together i though would work but when it gets to this point in the calculation it throws an error.

Below is what i tried in the immediate window

?1.045 = 1

the answer i get to this is

4.49999999999999E-02

rather then the 0.045 i would have expected.

Any help appreciated

 

[abig99]

The figures i was giving the function were 10.045 and 1

so effectively mod 10.045 by 1, but whereas the normal mod would give back 0 i want this to give back 0.045

 

[abig99]

OK, had a play and come up with a compromise, while its not ideal it works for my purpose (rounds to 2 decimals). Reason i say its not ideal is because format has its own round component.

Function VVV(VarVal)
NewVarVal = VarVal
ModBy = 0.01
vRemainder = KPMod(NewVarVal, ModBy)
If vRemainder >= 0.005 Then
VarVal = ((VarVal - vRemainder) + ModBy)
Else
VarVal = (VarVal - vRemainder)
End If
VVV = Format(VarVal, "#,##0.00")
End Function
Function KPMod(vNewMainVal, vNo)
Do Until vNewMainVal < vNo
vNewMainVal = Val(Format((vNewMainVal - vNo), "#,##############0.00000000000000"))
Loop
KPMod = vNewMainVal
End Function

Just slap this behind a form and put the following in the onclick event in any button on the form.

inptval = InputBox("EnterValue")
Wibble = VVV(inptval)
MsgBox Wibble

Oh, and the reason im not using round is because depending on which SP for office you have it seems to differ in where it rounds up from.

 

[MichaelRed]

A 'easier' awpproach to getting the "mod" for a floating point value (at least in many cases) is to "promote" the value(s) by the order magnitude of the desired answer, do the normal Mod, th4en reverse the promotion.

e.g. 10.045 mod 1

? ((10.045 * 10^3) mod (1 * 10 ^3)) / 10^3
 0.045

Of course, this has some limitations and is also subject to some possability of binary math error.

Just for the sake of ?, a "function" can easily be implemented:

Public Function basFloatMod(ValIn As Double, ModVal As Double, OrdMag As Integer) As Double

    basFloatMod = ((ValIn * 10 ^ OrdMag) Mod (ModVal * 10 ^ OrdMag)) / 10 ^ OrdMag

End Function

As usual, the above is NOT recommended for actual use, as it sorely lacks in error trapping and other nicieties.





MichaelRed

 

[abig99]

Thanks for the suggestion MichaelRed.

However if someone passes a rather large value to that say 100000000.0001212121 access will throw an overflow error and the system wont work, did actually try that method originally.

 

[abig99]

Hah, ignore me, just followed your method through and its working nicely, very easy way of doing it.

Thanks again MichaelRed

 

[MichaelRed]

hmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmmm ... mmmmm

Now I'm thoroughly confused. The routine can (will) cause/generate errors (a-la the overflow mentioned), On hte other hand, I tried to couch the response to note that it is NOT a universal answer and to note that it needs some additional work (error checking anyone?).

So the first previous comment is 'correct', but ?specious?





MichaelRed