Link function to Sub
I want to calculate distance between two latitude and longitude coordinate.
I have this coding, it can be run but nothing is happening. Can anyone tell me the problem of my coding?
Option Explicit
Private Const C_RADIUS_EARTH_KM As Double = 6371.1
Private Const C_PI As Double = 3.14159265358979
Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
Latitude2 As Double, Longitude2 As Double) As Double
Dim Lat1 As Double
Dim Lat2 As Double
Dim long1 As Double
Dim long2 As Double
Dim X As Long
Dim Delta As Double
X = 24
' convert to decimal degrees
Lat1 = Latitude1 * X
long1 = Longitude1 * X
Lat2 = Latitude2 * X
long2 = Longitude2 * X
' convert to radians: radians = (degrees/180) * PI
Lat1 = (Lat1 / 180) * C_PI
Lat2 = (Lat2 / 180) * C_PI
long1 = (long1 / 180) * C_PI
long2 = (long2 / 180) * C_PI
' get the central spherical angle
Delta = ((2 * ArcSin(Sqr((Sin((Lat1 - Lat2) / 2) ^ 2) + _
Cos(Lat1) * Cos(Lat2) * (Sin((long1 - long2) / 2) ^ 2)))))
GreatCircleDistance = Delta * C_RADIUS_EARTH_KM
End Function
Function ArcSin(X As Double) As Double
' VBA doesn't have an ArcSin function. Improvise
ArcSin = Atn(X / Sqr(-X * X + 1))
End Function
Sub Checking()
Dim Lat1 As Double
Dim long1 As Double
Dim Lat2 As Double
Dim long2 As Double
Dim results As Double
results = GreatCircleDistance(Lat1, long1, Lat2, long2)
Do While Not IsEmpty(Cells(4, 1))
Lat1 = Cells(4, 1)
long1 = Cells(4, 2)
Lat2 = Cells(4, 3)
long2 = Cells(4, 4)
Cells(4, 5) = results
Loop
End Sub
| Attachment | Size |
|---|---|
| DistanceLatLon.zip | 15.11 KB |
RE: Link Function to Sub
Hi,
The problem is with the Checking subroutine.
1) The code line:
results = GreatCircleDistance(Lat1, long1, Lat2, long2)
always will be evaluate to:
results = 0
because you not initialize your variables and you actually call your function in that way:
GreatCircleDistance(0, 0, 0, 0)
2) I don't understand the purpose of Do While... Loop. Can you explain in more details what you try to achieve. For example, if Cells(4, 1) contain some value this will produce an infinite loop. And this is not good...
If you can, please attach some example file.
Best regards.
RE: Link Function to Sub
Hi Manny,
1) actually I want the *results* part to take value of the GreatCircleDistance function but I don't know how, I think by doing that will do it.
2) for do while loop, what I'm trying to do is I want the loop to continue count the input data that I've already put in the excel sheet.
the reason for cells(4,1) is to show that I have four cells of input data and I want the variable that I've declare in the function to be represent the cells or otherwise.
Please help me correct and show me where I've done wrong - which I believe in most of the explanation that I gave.
Thank you for your time. I'm very appreciate it.
Regards.
RE: Link Function to Sub
Hi,
If I understood you correctly, the following subroutine will works for you:
' ************************* ' ************************* '
Sub Checking()
Dim i As Long
Dim Lat1 As Double
Dim long1 As Double
Dim Lat2 As Double
Dim long2 As Double
Application.ScreenUpdating = False
i = 4
Do While Not IsEmpty(Cells(i, 1))
Lat1 = Cells(i, 1)
long1 = Cells(i, 2)
Lat2 = Cells(i, 3)
long2 = Cells(i, 4)
Cells(i, 5) = GreatCircleDistance(Lat1, long1, Lat2, long2)
i = i + 1
Loop
Application.ScreenUpdating = True
End Sub
' ************************* ' ************************* '
If not - please, give some more details.
Best regards.
RE: Link Function to Sub
The macro turn to have
compile error:
Argument not optional
i think it because of the
Cells(i, 5) = GreatCircleDistance(Lat1, long1, Lat2, long2)
does not declare all argument from the GreatCicrcleDistance function. but when i put all the arguments to the equation, nothing is happen.
Actually how to apply the function so it can be use in the excel sheet.?
Thank you very much.
Compile error
Hi,
I'm sure that the subroutine works fine, because I tested it before I make a comment post here. Check whether you mistype something. I cannot help you further without I see your code. If you still get this error, please attach an example file with your code.
To use your function in Excel worksheet, for example, enter in cell E4 the following formula:
= GreatCircleDistance(A4, B4, C4, D4)
Best regards.
RE : Compile Error
Dear Manny,
I have my best to follow your answer accordingly. However, still it didn't work for me.
Can you tell me how to attach file in this reply, I didn't find ways to do that *noob me.
thank you.
Attach file
To attach a file:
1) Log In;
2) Go to your forum topic -> Edit tab;
3) Into File attachments section attach your file (browse your file and then press Attach button);
4) Save your topic.
File attached
Dear manny,
I've attach the file on the 1st post on this topic.
Hope u can help me to check where have I gone wrong.
Thank you for your time
Hi
Hi,
1) Now I understand why you get a compile error. Your initial requirements are misleading - the function which you are posted here is with four arguments but in your actual code is with six. And this is the first reason the function to not work. To get it to work, you must call it, for example, in this way:
= GreatCircleDistance(A4, B4, C4, D4, TRUE, TRUE)
2) The second reason the funtion to not work is that you pass arguments of type String whilst into the function the arguments are declared as Double. In your example file your data are in this format:
3~ 18" 18'
The proper format for co-ordinates is:
3° 18' 18" (degrees, minute, second)
Now, to get your function to work you must convert your data to decimal or time format (for example 3.305 or 07:19:12). My question is: How are you going to collect your data?
Best regards.
P.S. I don't know how you get your code but I think that its origin is from here:
Latitude, Longitude, And Great Circles
If you not seen this by now, please read it if you need to understand how function work.