#Div/0! front end but not in VBA

[James_Latimer]

Hello,

I have an issue that i cant get my head around. I have a formula on a sheet: =AD504/AD503 which returns #Div/0!

However if i use the following vba i get an answer...

Sub hjklasdfasdfad()
Dim V1 As Double, V2 As Double


V1 = ActiveSheet.Range("AD503").Value
V2 = ActiveSheet.Range("AD504").Value

MsgBox(V1)
MsgBox(V2)
MsgBox (V2 / V1)


End Sub

The values showing are:
V1: 6.706849E-312
V2: 6.022279E-316

Result: 8.979298E-05

Can anyone throw me a bone please? I'm confused as to why the front end is throwing the error. The cell type is set to 'Number' with 4 decimal positions.

Thanks in advance

 

[Joe4]

You are most likely running into this situation explained here: https://www.microsoft.com/en-us/mic...y-does-excel-give-me-seemingly-wrong-answers/

Note that the number of decimal places you are rounding to in your number format has no impact. Formatting just displays the view, not the actual underlying value that Excel uses.
You can use the ROUND function in your formulas to get around that.

So if you round your values, you will get the expected error, i.e.

Sub hjklasdfasdfad()
Dim V1 As Double, V2 As Double

V1 = Round(Range("AD503"), 4)
V2 = Round(Range("AD504"), 4)

MsgBox (V1)
MsgBox (V2)
MsgBox (V2 / V1)

End Sub

 

[James_Latimer]

Thank you for your response, much appreciated.

Hmmm, the only way i've managed to get around this is by creating the following function

Public Function PrecisionPointIssue(ByRef Val1 As Double, ByRef Val2 As Double) As Double
     PrecisionPointIssue = Val2 / Val1
End Function

Then i use the formula =PrecisionPointIssue(AD503, AD504) and i get a valid answer.

If someone knows of a better solution then please let me know. Thanks again.

 

[Joe4]

Did you try using ROUND, like I suggested?
If it didn't work for you, please provide an example (with the values) that didn't work.

Also, are the values in AD503 and AD504 hard-coded or formulas?
If formulas, the easiest thing to do may be to use the ROUND formula directly in those formulas.

 

[James_Latimer]

Apologies, i forgot to include reference to that.

I'm not in a position to round the values being used. I would be getting incorrect results if i were to do that.

 

[Joe4]

I'm not in a position to round the values being used. I would be getting incorrect results if i were to do that.

Please explain further. In your original question, you mentioned using 4 decimal places.
Just to clarify, rounding does not necessarily mean "whole numbers". You can round out to a set number of decimal places too.
Exactly how many decimal places do these values in AD503 and AD504 have?

 

[James_Latimer]

The values are:
6.706849E-312
and
6.022279E-316

The mention of the 4dp was irrelevant and misleading, i shouldn't have mentioned it. That was merely the cell formatting and not the actual value within the cell.

Thanks again for taking the time to look at this with me. I do genuinely appreciate it. I apologise if i'm not being clear (please see my avatar).

 

[Joe4]

I see. So you are really dealing with miniscule values.

I misunderstood your issue. I thought you meant for those values to be zero, and they were returning tiny values, which sometimes happens with the issue I linked to.
I didn't realize that you actually are working in very small numbers.

It looks like the issue is that Excel cannot track numbers smaller than [FONT=&quot]2.22507385850721E-308, and your numbers are smaller than that (see: [/FONT][FONT=&quot]2.22507385850721E-308).
[/FONT]However, VBA can track smaller ones. So Excel is treating your numbers as zero, while VBA is not.

So I think your best is to probably go with the VBA solution that you have come up with.

 

[shg]

I think the issue is that Excel doesn't use denormalized floating point numbers.

The smallest number a normalized Double can contain is 1 x 2^-1022, which is 10^-307.65

Denormalized Doubles can go down to 1 x 2^-1074, which is ~ 10^-323.31

Excel (but apparently not VBA) whacks denormalized numbers to zero.

EDIT: Which is what Joe said