0

I am converting a FORTRAN code that maintains at least 16 decimal precision. I am facing a problem of dividing by zero in VBA excel, but If I try the code below on this online compiler, I do not get a zero. Any help is appreciated. Thanks in advance.

This is the fortran Code

program sum
IMPLICIT DOUBLE PRECISION (A-H,O-Z)
x = 3.14159265358979
y = 1.24325643454325

z = (x*y)/dtan(0.0D0) 
print *, datan(.04D0*z)
end program sum

This is the VBA Code

Public Function dosomething()
Dim X As Double
Dim Y As Double
Dim Z As Double
X = 3.14159265358979
Y = 1.24325643454325
Z = (X * Y) / Tan(0#)
End Function
vestland
  • 55,229
  • 37
  • 187
  • 305
LionsFan
  • 119
  • 1
  • 10
  • This doesn't look like VBA code. Mind sharing the actual VBA code if you want VBA people to help? – Mathieu Guindon Aug 11 '17 at 15:38
  • This is Fortran code, If I try it on the Fortran online compiler it works, but If I try that in VBA it doesn't work. – LionsFan Aug 11 '17 at 15:39
  • I should also add that dtan and datan are intrinsic functions in the Fortran library which equivalent to Tan() and Atn() in VBA. – LionsFan Aug 11 '17 at 15:40
  • I am not asking to translate the code, I am asking how to maintain high precision with numbers the same Fortran does so I do not end up dividing by zero.... – LionsFan Aug 11 '17 at 15:41
  • I edited the code to include the VBA Code. Matt P, you can ignore that print statement, it is irrelevant and I removed it. – LionsFan Aug 11 '17 at 15:47
  • @Mat'sMug I edited the code – LionsFan Aug 11 '17 at 15:48
  • 1
    The tangent of `0` is `0` so you are dividing by `0`, hence the error. – Scott Craner Aug 11 '17 at 15:49
  • @ScottCraner the it should not be zero tho, it should be tan(1E-16) – LionsFan Aug 11 '17 at 15:51
  • 1
    But your code shows `Tan(0)` not `Tan(1E-16)`. So put that in and you stop getting the `divided by 0` error. – Scott Craner Aug 11 '17 at 15:55
  • With `Z = (X * Y) / Tan(1E-16)` I get Z= `3.90580528128931E+16 ` – Scott Craner Aug 11 '17 at 15:57
  • 3
    Note that to keep double precision in your `x` and `y` variables you must append the designator `d0` to the number, as in `x=3.14159265358979d0`. This is a common mistake. If you do not, the compiler usually assumes the variable is single precision and only the first 7 digits are retained from your original assignment. – Matt P Aug 11 '17 at 16:30
  • As @MattP , says, with `print *, x` and `print *, y`, the output is `3.1415927410125732` and `1.2432564496994019` respectively, whereas if `d0` is appended to the assignments, the output is `3.1415926535897900` and `1.2432564345432500` respectively. – ThunderFrame Aug 11 '17 at 22:55

2 Answers2

1
Z = (X * Y) / Tan(0#)

The type hint on the 0 literal is superfluous, Tan function takes a Double and returns a Double. But Tan(0) returns 0, so you are dividing by 0.

Seems your online Fortran compiler is doing something funky.

it should not be zero tho, it should be tan(1E-16)

No. That's mathematically wrong, VBA is doing it right. If you need your VBA code to be just as broken as that Fortran, then you need to handle the situation explicitly:

Z = (X * Y) / Tan(1E-16)

But just know that is mathematically wrong. I've not idea how the Fortran code manages to output 1.5707963267948966. This VBA code outputs 3.90580528128931E+16.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Thank you. I am aware of it mathematically impossible, but I lurked your profile and it seems like you're comfortable with VBA. The code in fortran sets the variable to equal to 0.0D0, when I type that in VBA it changes it to 0#, not sure what that means. I assumed that 0# is equivalent to 0.0D0 in fortran and that's where the error is. – LionsFan Aug 11 '17 at 16:02
  • `0#` is a [type-hinted `Double` literal](https://stackoverflow.com/documentation/vba/877/declaring-variables/2960/type-hints#t=201708111610376144341) with a value of `0`. BASIC originates in FORTRAN (from well before it got "Visual"), that's where the auto-correcting of the `D` came from. I've no idea how `0.0D0` could be anything other than `0` in FORTRAN though. – Mathieu Guindon Aug 11 '17 at 16:08
  • 2
    There are a couple of issues with the Fortran code shown by the OP, but the correct mathematical result really is PI/2 or approx 1.57079632679490 in double precision. This is because `datan(.04D0*z)` is equivalent to arctangent(Infinity). – Matt P Aug 11 '17 at 16:25
  • Evidently, your vba code doesn't match your arbitrary looking mixture of Fortran single and double precision. You show x and y initialized single precision. – tim18 Aug 11 '17 at 16:48
  • @tim18, If i append the variables with D0 it still doesn't change the outcome. I am confused at the part where z = (x*y)/dtan(0.0D0) equals infinity and not zero. – LionsFan Aug 11 '17 at 16:55
1

Without going into too many details, Fortran can support real values (floats) of +/-Infinity and NaN, depending on how the value is calculated. For example, your original post contained two uninitialized variables which you then used to calculate (v1 * v2)/dtan(0.0d0). Since uninitialized vars are often (but not always) set to 0, this calculation becomes 0.0/0.0, which is mathematically undefined and the result is NaN.1

Now, if the numerator is positive, z=(x*y)/dtan(0.0D0) results in z=Infinity, regardless of what x and y are. If your system cannnot represent Infinity, then it uses "a very large number". That is evidently the case with VBA.

Finally, you calculate datan(.04D0*z). Mathematically, this is arctangent(Infinity)=PI/2. And again, the correctly computed Fortran results match this, returning a double-precision value of 1.57079632679490.2

Now, I don't know much about VBA, but it does not seem to support +/-Infinity or NaN. If a "very large number" results in significant error compared to what you are expecting in your final result, then it appears there are workarounds as described at this SO question.

1 Note that in Fortran with double precision you should get dtan(0.0d0) = 0.000000000000000E+000.

2 In order to maintain double-precision in the Fortran x and y variables, you must append d0. Otherwise they will become single-precision values by default and store only the first 7 sig figs from your original assignment, and it's up to the compiler what the remaining digits in the double-precision value take (usually just garbage).

Matt P
  • 2,287
  • 1
  • 11
  • 26