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.
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
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
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
Curious about the Sqr function, as the argument would appear to be negative, which I do not believe Excel can handle.
No file attached.
»bp
»bp
ASKER
File now attached
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Interestingly, the IF checking for dblTemp = 1 fails, even though both values appear to be 1.
Perhaps try:
If dblTemp = 1# Then
»bp
If dblTemp = 1# Then
»bp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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