Pros: I know this is a shot in the dark, but my math skills are just not up to debugging this function. Thank you in advance for any thoughts/comments/suggestions:
We have a database of property locations which are geocoded and when a property is selected, one of the things the database does is it provides a list of neighboring properties, sorted by distance. Occasionally the database will throw an error: "Run-time error '5': Invalid procedure call or argument".
I choose to debug and it takes me to the function below (I used apostrophes to break out the function where the error is)
I know it is a mathematical error in that my function is generating a value that is out of range, but I can't figure out why one property (one set of coordinates) throws the error and why another does not.
For example a property with the coordinates of 40.9454923 , -72.8867217 throws the error while 40.9567969 , -72.9755829 does not. I can't figure out how to do the math in my head. I know there is a way to let the VBA editor go through the code step by step and display the values in the immediate window, but I can't get it to go through all the steps.
Thanks,
Isaac Richter
______________________________________
Option Compare Database
Option Explicit
' This routine calculates the distance between two points (given the latitude/longitude of those points). It is being
'used to calculate distance between two ZIP Codes or Postal Codes
'Definitions
' South latitudes are negative, east longitudes are positive
'Passed to function
'lat1, lon1 = Latitude and Longitude of point 1 (in decimal degrees)
'lat2, lon2 = Latitude and Longitude of point 2 (in decimal degrees)
'unit = the unit you desire for results where 'M' is statute miles (default)
' 'K' is kilometers
' 'N' is nautical miles
Const pi = 3.14159265358979
Global PropertyLat
Global PropertyLong
Public lngMap As Long
Global lngComparablesDistance As Long
Function Distance(lat1, lon1, lat2, lon2, unit)
Dim theta, dist
theta = lon1 - lon2
dist = Sin(deg2rad(lat1)) * Sin(deg2rad(lat2)) + Cos(deg2rad(lat1)) * Cos(deg2rad(lat2)) * Cos(deg2rad(theta))
dist = acos(dist)
dist = rad2deg(dist)
Distance = dist * 60 * 1.1515
Select Case UCase(unit)
Case "K"
Distance = Distance * 1.609344
Case "N"
Distance = Distance * 0.8684
End Select
End Function
*****************************
Below this is where the error is
*****************************
' This function get the arccos function from arctan function
'
Function acos(Rad)
If Abs(Rad) <> 1 Then
acos = pi / 2 - Atn(Rad / Sqr(1 - Rad * Rad))
ElseIf Rad = -1 Then
acos = pi
End If
End Function
*****************************
Above this is where the error is
*****************************
' This function converts decimal degrees to radians
'
Function deg2rad(Deg)
deg2rad = CDbl(Deg * pi / 180)
End Function
' This function converts radians to decimal degrees
'
Function rad2deg(Rad)
rad2deg = CDbl(Rad * 180 / pi)
End Function
'Demo
'response.Write distance(32.9697, -96.80322, 29.46786, -98.53506, "M") & " Miles<br>"
'response.Write distance(32.9697, -96.80322, 29.46786, -98.53506, "K") & " Kilometers<br>"
'response.Write distance(32.9697, -96.80322, 29.46786, -98.53506, "N") & " Nautical Miles<br>"
Function GetDistance(Latitude, Longitude)
If ((Not IsNull(PropertyLat)) And (Not IsNull(PropertyLong))) Then
GetDistance = Distance(PropertyLat, PropertyLong, Latitude, Longitude, "M")
End If
End Function
Tidak ada komentar:
Posting Komentar