I already have a VBA Script that calculates distances from lat/long of 2
points (a reference point, then other locations). This type of script is
widely available on the net.

My situation now is that I have a list of 7 locations (each with its own
lat/long). I want to find out how close they are to each other.

With my current script, the only way I can figure this out is to copy the
locations into 7 separate columns to run the script 7 times. That is fine
with a small #, but what about if I have 100 locations?

Any suggestions?

Just build a loop to do it, I just today completed that very thing. If you
create a function that does your calculations and just send the numbers it is
very fast. Mine looks at the location of 300 drivers, compares their location
to the lat long I provided, gets the distance and finds the 6 closest, runs
in under 2 seconds. And this machine is junk. If you can build a function and
an array you are set. This is the function we use, i pass in the driver
lat/long and the location lat/long and it returns a result that I do a little
more math on
Public Function calcDistance(destLat, driverLat, destLong, driverLong)
calcDistance = Math.Sqr(((destLat - driverLat) ^ 2) + (destLong -
driverLong) ^ 2)
End Function
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JSpence2003" wrote:

I already have a VBA Script that calculates distances from lat/long of 2
points (a reference point, then other locations). This type of script is
widely available on the net.

My situation now is that I have a list of 7 locations (each with its own
lat/long). I want to find out how close they are to each other.

With my current script, the only way I can figure this out is to copy the
locations into 7 separate columns to run the script 7 times. That is fine
with a small #, but what about if I have 100 locations?

Any suggestions?

That sounds like the script I already have. Where it will plot the distance
between 2 points given you have an origin point and a destination point.

The situation I have now is a list of 7 stores, I want to know how close
they are to each other.

e.g. (store 1 is 5 miles from store 2, 7 miles from store 3, etc)

Jason

"John Bundy" wrote:

Just build a loop to do it, I just today completed that very thing. If you
create a function that does your calculations and just send the numbers it is
very fast. Mine looks at the location of 300 drivers, compares their location
to the lat long I provided, gets the distance and finds the 6 closest, runs
in under 2 seconds. And this machine is junk. If you can build a function and
an array you are set. This is the function we use, i pass in the driver
lat/long and the location lat/long and it returns a result that I do a little
more math on
Public Function calcDistance(destLat, driverLat, destLong, driverLong)
calcDistance = Math.Sqr(((destLat - driverLat) ^ 2) + (destLong -
driverLong) ^ 2)
End Function
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JSpence2003" wrote:

I already have a VBA Script that calculates distances from lat/long of 2
points (a reference point, then other locations). This type of script is
widely available on the net.

My situation now is that I have a list of 7 locations (each with its own
lat/long). I want to find out how close they are to each other.

With my current script, the only way I can figure this out is to copy the
locations into 7 separate columns to run the script 7 times. That is fine
with a small #, but what about if I have 100 locations?

Any suggestions?

Sounds like you are on the right path if you are feeding the information into
an array, if you're not, it will take a LOT longer. What steps are you
needing help with from here or were you just seeing if there was a better way?
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JSpence2003" wrote:

That sounds like the script I already have. Where it will plot the distance
between 2 points given you have an origin point and a destination point.

The situation I have now is a list of 7 stores, I want to know how close
they are to each other.

e.g. (store 1 is 5 miles from store 2, 7 miles from store 3, etc)

Jason

"John Bundy" wrote:

Just build a loop to do it, I just today completed that very thing. If you
create a function that does your calculations and just send the numbers it is
very fast. Mine looks at the location of 300 drivers, compares their location
to the lat long I provided, gets the distance and finds the 6 closest, runs
in under 2 seconds. And this machine is junk. If you can build a function and
an array you are set. This is the function we use, i pass in the driver
lat/long and the location lat/long and it returns a result that I do a little
more math on
Public Function calcDistance(destLat, driverLat, destLong, driverLong)
calcDistance = Math.Sqr(((destLat - driverLat) ^ 2) + (destLong -
driverLong) ^ 2)
End Function
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"JSpence2003" wrote:

I already have a VBA Script that calculates distances from lat/long of 2
points (a reference point, then other locations). This type of script is
widely available on the net.

My situation now is that I have a list of 7 locations (each with its own
lat/long). I want to find out how close they are to each other.

With my current script, the only way I can figure this out is to copy the
locations into 7 separate columns to run the script 7 times. That is fine
with a small #, but what about if I have 100 locations?

Any suggestions?