1

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?

Sgdva
  • 2,800
  • 3
  • 17
  • 28
Andrius Solopovas
  • 967
  • 11
  • 41
  • `calc = format(num,"0")` is something i tend to do if i'm going to outline the output, otherwise having `num` in the previous line is not really necessary and can just be `calc = `3...). Also, dimensioning `function calc()` may help – Cyril Feb 01 '22 at 16:38
  • 1
    Additional thing, using `round()` may also help to ensure same decimal places presented. – Cyril Feb 01 '22 at 16:42
  • Is there a way to make vba variable of data type that is equal to cell data type ? – Andrius Solopovas Feb 01 '22 at 16:51

2 Answers2

2

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):

enter image description here

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:

enter image description here

Cyril
  • 6,448
  • 1
  • 18
  • 31
1

Explanation
Double Data type seems to have flaws being "precise" after the "nth" digit. This is stated as well in the documentation
Precision. When you work with floating-point numbers, remember that they do not always have a precise representation in memory. This could lead to unexpected results from certain operations, such as value comparison and the Mod operator.
Troubleshooting
It seems that is the case here: I set up the value from the division on a cell and the division as formula in another one, although excel interface says there are not differences, when computing that value again, the formula on the sheet seems to be more precise.


Actual result

enter image description here

enter image description here

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.

Sgdva
  • 2,800
  • 3
  • 17
  • 28
  • 1
    thanks for presenting the documentation for the memory allocation; i had just posted about that in my answer, then was searching for that related link when i saw you answer as well! – Cyril Feb 01 '22 at 16:57
  • how unfortunate, I could setup api its just that I was trying to automate stuff and keep thing working offline. Its a shame that same data type not available from vba. – Andrius Solopovas Feb 01 '22 at 17:55