# Minimum Distance

1. ## Minimum Distance

Hello,

I am trying to write a vba function that finds the smallest distance between two large lists of gps coordinates. I already have a basic formula for calculating each of these, but I need to find this efficient function for determining the next closest gps coordinate and by how much. I have done a little bit of vba programming, though I have done more java in the past. I know I will need some type of loop where it looks to determine whether the newly calculated value is smaller than the current running minimum and if not, skip to the next one. I have some rough "pseudocode" of what i'm looking for.

minDistance(range,originalCoords)
define min double = 0
begin loop
dist = originalCoords - nextCoords \\The actual calculation is a little more
if dist < min \\complicated but it should work
dist = min
loop
end loop

I know there's probably a lot missing, including some reference that stops the loop when it hits the last set of coordinates to calculate and something to return the min value. The value nextCoords would be my next listed coordinates to calculate the distance between from the original. Also, I have one cell for latitude and one for longitude, if that is any help.
Any feedback and help would be greatly appreciated!
Thanks!

P.S. if this is listed on another forum topic already, let me know and I will go to there for help!  Register To Reply

2. ## Re: Minimum Distance

Welcome to the forum.

How large are the two lists?

All you want to now is the minimum distance, not the nearest point?

You want to calculate great-circle distance?

Post a workbook with some data.  Register To Reply

3. ## Re: Minimum Distance

The lists are about 400 locations in total and i'm trying to find the minimum distance to the next coordinate for each. I am using the great-circle distance formula for each of these but there is 399 calculations for each 400 locations, so that's why I feel some vba programming is necessary. Here is an attached worksheet with a sample of 5 locations, using a rudimentary method to find each of the minimum, but with 400 locations, excel runs out of room and this would take quite a while to accomplish.

test min dist.xls

Update: I ended up making 400 macros (yes, it took me 5 hours, but I got it done) and got my data, but I still want to know how to write a function in vba to do this for me because I will definitely be using this kind of method in future research of my own.

Update 2: I started looking further into VBA and I have come up with some code that seems like it should work. Here it is:

Function FindMinDist(Lat1 As Double, Lon1 As Double)
Dim Index As Integer, Min As Double, Dist As Double
Index = 3
Min = 500
While Index < 404
Index = Index + 1
Dist = WorksheetFunction.Acos(Cos(WorksheetFunction.Radians(90 - Lat1)) * Cos(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) + Sin(WorksheetFunction.Radians(90 - Lat1)) * Sin(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) * Cos(WorksheetFunction.Radians(Lon1 - Worksheet.Cells(Index, 3)))) * 3958.756
If Dist < Min And Dist <> 0 Then
Min = Dist
End If
Wend
FindMinDist = Min
End Function

When I try using the function, it gives me back #VALUE! in the cell. I have my latitude in C4:C403 & longitude in D4:D403. Any ideas on how to make this work properly?  Register To Reply

4. ## Re: Minimum Distance

Figured it out! All good to go! Wrote it myself. Here it is if anyone is interested: ``Please Login or Register  to view this content.``  Register To Reply

5. ## Re: Minimum Distance

Another way: ``Please Login or Register  to view this content.``
By passing the range containing the other lat-longs, Excel sees a dependency and will automatically recompute when the data changes. Example usage:

=FindMinDist(D2, E2, D4:E392)

The value returned is in radians, which means you can multiply by the earth radius in your preferred units (meters, miles, nautical miles, ...) to get linear (great circle) distance.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1