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?