Invalid Procedure or Arguement; Run-time error

I have a set of code that creates an array and populates it with the distance between a set of two coordinates, however using this function it gives a run-time error, invalid procedure or argument. The weird thing is that the code only slips on a select few data points and when these points are deleted the code runs until it hits another set of data that it doesn't like and gives the same message once again.
Option Explicit

Sub test()

    Dim sheetSource As Worksheet
    Dim sheetResults As Worksheet

    Dim intPos As Long
    Dim intMax As Long

    Dim i As Long
    Dim j As Long
    Dim strID As String

    Dim dblDistance As Double
    Dim dblTemp As Double

    Dim Lat1 As Double
    Dim Lat2 As Double
    Dim Long1 As Double
    Dim Long2 As Double

    Const PI As Double = 3.14159265358979

    Set sheetSource = ThisWorkbook.Sheets("Sheet1")
    Set sheetResults = ThisWorkbook.Sheets("Sheet2")

    intPos = 1

    ' 1 Build the matrix
    For i = 2 To sheetSource.Rows.Count

        strID = Trim(sheetSource.Cells(i, 1))

        If strID = "" Then Exit For

        intPos = intPos + 1

        sheetResults.Cells(intPos, 1) = strID
        sheetResults.Cells(1, intPos) = strID

    Next i

    intMax = intPos


    If intMax = 1 Then Exit Sub ' no data


    ' 2 : compute matrix
    For i = 2 To intMax 'looping on lines

        Lat1 = sheetSource.Cells(i, 2)
        Long1 = sheetSource.Cells(i, 3)

        For j = 2 To intMax 'looping on columns

            Lat2 = sheetSource.Cells(j, 2)
            Long2 = sheetSource.Cells(j, 3)

            ' Some hard trigonometry over here
            dblTemp = (Sin((Lat2 * PI) / 180)) * (Sin((Lat1 * PI) / 180)) + (Cos((Lat2 * PI) / 180)) * _
                      ((Cos((Lat1 * PI) / 180))) * (Cos((Long1 - Long2) * (PI / 180)))


            If dblTemp = 1 Then ' If 1, the 2 points are the same. Avoid a division by zero
                 sheetResults.Cells(i, j) = 0
            Else
                    dblDistance = 6371 * (Atn(-dblTemp / Sqr(-dblTemp * dblTemp + 1)) + 2 * Atn(1))
                    sheetResults.Cells(i, j) = dblDistance
            End If

        Next j
    Next i


End Sub

Open in new window


When debugged the debugger points to the line ' dblDistance = 6371 * (Atn(-dblTemp / Sqr(-dblTemp * dblTemp + 1)) + 2 * Atn(1))' as the issue

I have attached the file with the data as well
Sample1.xlsm

Thank you in advance
Conor WassmuthReservoir EngineerAsked:
Who is Participating?
 
Shaun KlineLead Software EngineerCommented:
When it errors out, if I check dblTemp using round(dbltemp, 16) = 1, I get back false, but if I use round(dbltemp, 15) = 1, I get true.

There must be a rounding issue in the way VBA is doing the comparison.

In fact, if I change the if to:
IF round(dbltemp, 15) = 1 Then

Open in new window

the macro runs to completion.
0
 
Shaun KlineLead Software EngineerCommented:
Curious about the Sqr function, as the argument would appear to be negative, which I do not believe Excel can handle.
0
 
Bill PrewCommented:
No file attached.


»bp
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Conor WassmuthReservoir EngineerAuthor Commented:
File now attached
0
 
Bill PrewCommented:
Looks like Shaun may be on to it, when the error is thrown dblTemp is 1, so SQR of a negative fails.

Sqr(-dblTemp * dblTemp + 1)


»bp
0
 
Shaun KlineLead Software EngineerCommented:
Interestingly, the IF checking for dblTemp = 1 fails, even though both values appear to be 1.
0
 
Bill PrewCommented:
Perhaps try:

If dblTemp = 1# Then


»bp
0
 
Shaun KlineLead Software EngineerCommented:
Instead of checking dblTemp, you may want to consider checking when i = j.
0
 
Bill PrewCommented:
Did you look at my solution to your earlier question here?  It probably won't resolve Double rounding issues, those are just part of the deal in VBA and have to be worked around.


I'm going to step back now, it looks like you have an active dialog going on StackOverflow so enough cooks in the kitchen...



»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.