Problem with excel (vba) Division by zero (Error 11) debugging

Question

I am quite new in VBA and I have an Excel sheet in which some macros are running. In one of the tabs, users can input data and by pressing the command button on the sheet they can get the answer. Now I want to enter data via a userform. I've successfully created an userform and it can perfectly read/write data from the Excel sheet. The problem I am facing now is that as soon as I enter the data in the userform, the command button (which is connected to a macro) gives me "Division by zero" error, and it kinda ruins the Excel file and I need to replace it with the backup file I have! I can't understand why entering data via the userform causes this problem.

When I enter data on the Excel sheet (not via userform) everything is fine!!! This is the part I'm getting the error:

Function ATn2(x As Variant, Y As Variant) As Double
    If x = 0 Then
        If Y = 0 Then
            ATn2 = 1 / 0
        ElseIf Y > 0 Then
            ATn2 = Pi / 2
        Else
            ATn2 = -Pi / 2
        End If
    ElseIf x > 0 Then
        If Y = 0 Then
            ATn2 = 0
        Else
            ATn2 = Atn(Y / x)
        End If
    Else
        If Y = 0 Then
            ATn2 = Pi
        Else
            ATn2 = (Pi - Atn(Abs(Y) / Abs(x))) * Sgn(Y)
        End If
    End If 
End Function

And of course I can see that the error is coming from ATn2 = 1 / 0 :)

1/0 cannot work in any circumstance. The code worked only when y <> 0. Anyhow, try changing ATn2(x As Variant, Y As Variant) with ATn2(x As Double, Y As Double). Not for solving the division by zero error... — FaneDuru
And of course I can see that the error is coming from ATn2 = 1 / 0 - so you are aware that it happens because you are calling ATn2(0, 0). So don't do that. — GSerg
FaneDuru and GSerg, thank you for your comments. Yes, I know that is the problem, but I cannot completely follow the code since I haven't written it, and also I cannot understand why It doesn't happen when I enter the data in the excel sheet! only putting data by userform causing this problem — Navid Nikraftar

Pete Uzzell Pete Uzzell · Accepted Answer · 2020-12-03T08:00:15

by allowing the option of ATn2 = 1 / 0 if both x and Y are 0 you are creating the divide by zero error. In the other instances of Y = 0 you have specified either Pi or 0 as the return value.

Perhaps you should change the 1 / 0 to a different value.

Function ATn2(x As Variant, Y As Variant) As Double
If x = 0 Then
    If Y = 0 Then
        ' // BOTH ARGUMENTS ARE ZERO
        MsgBox ("Values are both zero and cannot be calculated")
        Exit Function
    ElseIf Y > 0 Then
        ATn2 = Pi / 2
    Else
        ATn2 = -Pi / 2
    End If
ElseIf x > 0 Then
    If Y = 0 Then
        ATn2 = 0
    Else
        ATn2 = Atn(Y / x)
    End If
Else
    If Y = 0 Then
        ATn2 = Pi
    Else
        ATn2 = (Pi - Atn(Abs(Y) / Abs(x))) * Sgn(Y)
    End If
End If
End Function

Problem with excel (vba) Division by zero (Error 11) debugging

2 Answers