How to get equal results when doing arithmetic operations vba/excel [Double variable precision]-CodePudding

I am trying to get equal result of two exact calculations which are computed in a cell formula and the other one with a UDF:

Function calc()
  Dim num as Double
  num = 30000000 * ((1   8 / 100 / 365) ^ 125)
  calc = num
End Function

Result of the calculation is different

A1 = 30000000 * ((1 8 / 100 / 365) ^ 125) not equal to A2 = calc()

We can test it with =if(A1=A2, TRUE, FALSE) which is false. I do understand that it has something to do with data types in vba and executing cell formula. Do you know how to make calculations to from vba function(s) and excel cell field(s) to render same result?

CodePudding user response：

So, the calculation in application excel and the calculation in vba are presenting different outputs (what you've presented, with format displaying 20 decimal places):

As such, you would see false when comparing them. You will need to round() or format() to truncate the calculation at a level that is appropriate. E.g.:

calc = round(num,4)
calc = format(num,"0.###0")

The reason this is occurring is because of the inherent math you're using, specifically, ((1 8 / 100 / 365) ^ 125), and how that is being truncated/rounded in the allocated memory to each part of the calculation, which differs in VBA and in-application Excel.

Edit: Final image with the VBA changes I'd suggested:

CodePudding user response：

Explanation
Double Data type seems to have flaws being

Further thoughts
It seems that is limited by the data type itself, if precision is not an issue, you may try to round it. If it is critical to be as precise as possible, I would suggest you to connect with an API to something that is able to handle more precision. In this scenario, I would use xlwings to use python.