Link to home
Start Free TrialLog in
Avatar of Conor Wassmuth
Conor Wassmuth

asked on

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
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Curious about the Sqr function, as the argument would appear to be negative, which I do not believe Excel can handle.
Avatar of Bill Prew
Bill Prew

No file attached.


»bp
Avatar of Conor Wassmuth

ASKER

File now attached
SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Interestingly, the IF checking for dblTemp = 1 fails, even though both values appear to be 1.
Perhaps try:

If dblTemp = 1# Then


»bp
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Instead of checking dblTemp, you may want to consider checking when i = j.
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