VBA Code - Dividing Numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I divide the number 75 by several numbers, however many of the resulting
numbers have a misplaced decimal point. That is, 75 / 999 = 7.507
(Incorrect) . But 75 / 1000 = .075 (Correct). And then 75/ 1001 = 7.492

Here is the code. Any help is appreciated:

Dim dblVal As Double, x as integer
Open "C:\tmp.txt" For Output As #1
For x = 999 To 2000
dblVal = 75 / x
Print #1, dblVal & Chr(9) & x
Next x
Close #1
 
Go to the immediate pane and type
? 75/999
you'll see the result:
7.50750750750751E-02
Note the last four characters -- E-02. Access is using scientific notation
to denote
7.507507... * 10 ^ -2
7.507507 * 0.01
0.07507507...

It's doing the math correctly, but you have an implicit conversion from
double to string that isn't cooperating with you.

Try:

' old code
'Print #1, dblVal & Chr(9) & x
'new code, uses comma to separate outputs with tab character

Print #1, dblVal, x

Another way is to use Format to make an explicit conversion from number to
string:

Print #1, Format(dblVal, "0.000000"), Format(x, "0")

This way will avoid scientific notation in all cases.

HTH,

Kevin
 
That is, 75 / 999 = 7.507 (Incorrect) .
Click to expand...

According to my version:

? cdbl(75/999)
7.50750750750751E-02

which of course is correct. I guess you may be text-truncating your output
somewhere. Just shows the importance of _never_ letting VBA do implicit
type conversions...

HTH


Tim F
 
I divide the number 75 by several numbers, however many of the resulting
numbers have a misplaced decimal point. That is, 75 / 999 = 7.507
(Incorrect) . But 75 / 1000 = .075 (Correct). And then 75/ 1001 = 7.492

Here is the code. Any help is appreciated:

Dim dblVal As Double, x as integer
Open "C:\tmp.txt" For Output As #1
For x = 999 To 2000
dblVal = 75 / x
Print #1, dblVal & Chr(9) & x
Next x
Close #1
Click to expand...

You are mistaken regarding the results of the division.
Here are the full decimal results for the indicated calculations (999
to 1005). Note that the results (except for 0.075) are in scientific
notation (because the .075 result has no decimal remainder).

7.50750750750751E-02 requires you to move the decimal 2 places to the
left, so it equals .07507575 ... etc. as the decimal result (it rounds
up to .0751).
The above is because you are using Double as the Number field size.
Double values are inherently approximations.

999/999 7.50750750750751E-02
1000/999 0.075
1001/999 7.49250749250749E-02
1002/999 7.48502994011976E-02
1003/999 7.47756729810568E-02
1004/999 7.47011952191235E-02
1005/999 7.46268656716418E-02

Change Dim dblVal As Double
to
Dim dblVal As Currency
and this is the result.
(Note: Currency datatypes are 4 decimals.)

999/999 0.0751
1000/999 0.075
1001/999 0.0749
1002/999 0.0749
1003/999 0.0748
1004/999 0.0747
1005/999 0.0746

Hope this clarifies your question.
 
Back
Top