I'm porting a C program to Access (VBA) and I'm running into some
slight floating point errors, nothing real big, but given enough FP
math they can add up and on some data there is a lot FP math.
The C program runs fine in a C environment (Dev-C++) on my system so
it can't be differences in floating point co-processors.
Does anyone have some experience with porting C floating point over to
VBA?
I need some help, hints, tips and a cocktail.
Thanks,
Curbie
Well, keep in mind that floating point numbers are always an approximation.
Thus, in ms-access we have:
Public Sub TestAdd()
Dim MyNumber As Single
Dim i As Integer
For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub
Here is the actual output of the above:
1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1
You can see that after just 7 additions..already rounding is occurring
And if we add the following line of code to the end of the above:
if MyNumber = 10.1 = True then
msgbox "the number is 10.1"
else
msgbox "the number is something else"
endif
The above will produce:
the number is something else
In other words, the number displayed is NOT 10.1
Doing the same in c++, we get:
//
#include "stdafx.h"
int main(int argc, char* argv[])
{
int i;
float MyNumber = 0;
for (i = 1; i <= 10;++i)
{
MyNumber = MyNumber + 1.01F;
printf ("%f\n", MyNumber);
}
return 0;
}
Output is:
1.010000
2.020000
3.030000
4.040000
5.050000
6.060000
7.070000
8.080001
9.090001
10.100001
Again, you can see the "final" result is NOT 10.1
So, the above is very much near day 1 in any computing science class. Simply
put, decimal representation in a computer are only *approximate* for decimal
numbers. You really can't use decimal numbers in comparisons.
If you need more precision in VBA, use double in place of single...you jump
to 8 bytes, and you should not get much difference in rounding then what you
had going on in your c code.
However:
I would suggest that if you have a fixed number of decimal places (say up to
4), then use the currency data type in VBA. Currency is a scaled integer
value and will not round numbers on you! It is ideal for payroll and the
many business applications that ms-access is typically used for.
It also not clear if you trying to avoid rounding, or you want the "exact"
same rounding in VBA as in your c code? I really do not know the answer for
this. I assume they both use IEEE formats. However, I can be sure that
rounding and results will be 100% identical with c++ code vs that of VBA
code.
so, try bumping up the precision (size) from single to double in VBA. That
should give you ample head room in terms of rounding errors.
And, if you need MORE then 4 decimal places without any rounding then you
can use a scaled integer (packed decimal) in VBA. Scaled integers very cool
and they allow up to about 28 signification digits with NO rounding at all.
So, "Decimal" type in VBA are simply really big integer values with a
'scale' option to place the decimal point into that big integer as a
position.
You might want to expand a bit on what your rounding problem here is.
Usually for financial stuff, we use the currency data type, but I suggest
you use "double" in place of "single" for starts if you need floating types.
However, if this is financial data, then we back to the 1st day of computing
science.....and you have to watch out for rounding errors as both my c++ and
VBA examples show just have a few addtions...things go wonky...
