How to debug function values

Question

Trying to understan what is wrong in function? but cant find what is wrong

Sub test()
Dim a as double, b as double
    a = "10,31" 
    b = "10,31"
Msgbox check(a,b)
End Sub

Function check(a as double, b as double)
    ff = TypeName(a) ' double
    gg = TypeName(b) ' double
    If a= b Then GoodExVat = True Else GoodExVat = False: diffExVAt = b - a ' here a = 10,31 b=10,31 diffExVAt = -3.5527136788005-15
check = diffExVAt 
End Function

if a and b seted as 10,31 everything is ok, but when 10,31 comes from other function or expression then i got -3.5527136788005-15

What is that? It is not first time i got same thing. What is wrong with Excel?

`"10,31"` will be stored as `1031` which is not what you want :) — Siddharth Rout, Apr 16 '18 at 09:22
BTW, on my computer, even with Decimal Separator to `.` I got `?cdbl("10,31")` returning `10.31`, probably because I don't have `,` as thousand separator and have `,` as currency decimal separator (?) — Vincent G, Apr 16 '18 at 09:32

Vityata · Accepted Answer · 2018-04-16T09:22:30.017

2

Do not use : in It-Else, it is a bit confusing. If you write it normally, it should work ok:

Sub Test()
    Dim a As Double, b As Double
    a = "10,31"
    b = "10,31"
    MsgBox check(a, b)
End Sub

Function check(a As Double, b As Double)
    ff = TypeName(a)    ' double
    gg = TypeName(b)    ' double
    If a = b Then
        GoodExVat = True
    Else
        GoodExVat = False
    End If

    diffExVAt = b - a    ' here a = 10,31 b=10,31 diffExVAt = -3.5527136788005-15
    check = diffExVAt

End Function

And concerning debugging - simply use F8 and the Step-by-step method.

Concerning the 10,31 coming from other function and expression and getting a -3.55...-15, then the problem is in the floating point mathematics:

edited Apr 16 '18 at 09:22

answered Apr 16 '18 at 09:17

Vityata

42,633
8
55
100

So how to get `-3.5527136788005-15` making 10,31-10,31 ? – Dmitrij Holkin Apr 16 '18 at 09:22
@DmitrijHolkin - there are plenty of answers, depending on the accuracy you need. The easiest one is `If Abs(a - b) < 0.01 Then check = True` or simply round the values up to the second digit. – Vityata Apr 16 '18 at 09:24
@DmitrijHolkin - Can you give an example of the "comes from other function or expression"? Most probably they are not rounded to the second digit, but you think they are... Read the two links in my answer, they are pretty interesting. – Vityata Apr 16 '18 at 09:27
how to see full value digits count in debuger? – Dmitrij Holkin Apr 16 '18 at 09:29
@DmitrijHolkin - select the the variable and press Shift + F9. Or simply write `?NameOfValue` in the immediate window. – Vityata Apr 16 '18 at 09:30
1

++ for following it to the end :) – Siddharth Rout Apr 16 '18 at 09:32
But debuger do not show full digits (( only help Round both values – Dmitrij Holkin Apr 16 '18 at 09:36
@DmitrijHolkin - the `?NameOfValue` in the Immediate window should show all the digits. E.g., write `?a` to see the digits of `a`. – Vityata Apr 16 '18 at 09:40
No showing only 2 digits `10,31` as is. but when i do `round(a,2)` when `GoodExVat = True ` instead of `False` without Round – Dmitrij Holkin Apr 16 '18 at 09:42
@DmitrijHolkin - try with Shift + F9. – Vityata Apr 16 '18 at 09:46
result the same – Dmitrij Holkin Apr 16 '18 at 10:09
@DmitrijHolkin - this is strange. However, the `-3.55...-15` is coming for sure from the double arithmetics. Simply you are missing one of the variables somewhere and it is "poisoning" the whole system. Try writing `debug.print a` for every variable, thus printing them and see their values. – Vityata Apr 16 '18 at 10:12

How to debug function values

1 Answers1