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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 PrewIT / Software Engineering ConsultantCommented:
No file attached.


»bp
0
Conor WassmuthReservoir EngineerAuthor Commented:
File now attached
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bill PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
Perhaps try:

If dblTemp = 1# Then


»bp
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shaun KlineLead Software EngineerCommented:
Instead of checking dblTemp, you may want to consider checking when i = j.
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.