Very simple to operate. Columns A-C are the ID, Long, and Lat of the
rows in the output array. Columns F-H are the ID, Long, and Lat of
the columns in the output array. The output prints starting in cell
K1 with the row headers from column A and the column headers from
column F.
Enjoy,
Tom Lynch
Option Explicit
Private Sub Calc_Dist()
Dim lRowCount As Long
Dim lColumnCount As Long
Dim lRowCounter As Long
Dim lColumnCounter As Long
Dim SinLat1 As Double
Dim CosLat1 As Double
Dim SinLat2 As Double
Dim CosLat2 As Double
Dim Distrad As Double
Dim CosL1L2 As Double
Dim dblDist As Double
Dim strOutArray(55000, 250) As String
Dim strRowArray(55000, 2) As String
Dim strColumnArray(250, 2) As String
Dim lCount1 As Long
lRowCount = -1
While Range("a1").Offset(lRowCount + 1, 0) <> ""
For lCount1 = 0 To 2
strRowArray(lRowCount + 1, lCount1) = Range("a1").Offset
(lRowCount + 1, lCount1).Cells.Text
Next lCount1
lRowCount = lRowCount + 1
Wend
lColumnCount = -1
While Range("f1").Offset(lColumnCount + 1, 0) <> ""
For lCount1 = 0 To 2
strColumnArray(lColumnCount + 1, lCount1) = Range("f1").Offset
(lColumnCount + 1, lCount1).Cells.Text
Next lCount1
lColumnCount = lColumnCount + 1
Wend
For lRowCounter = 1 To lRowCount
strOutArray(lRowCounter, 0) = strRowArray(lRowCounter, 0)
Next lRowCounter
For lColumnCounter = 1 To lColumnCount
strOutArray(0, lColumnCounter) = strColumnArray(lColumnCounter, 0)
Next lColumnCounter
For lRowCounter = 1 To lRowCount
For lColumnCounter = 1 To lColumnCount
SinLat1 = Sin(Val(strRowArray(lRowCounter, 2)) / 1000000 *
3.1416 / 180)
SinLat2 = Sin(Val(strColumnArray(lColumnCounter, 2)) /
1000000 * 3.1416 / 180)
CosLat1 = Cos(Val(strRowArray(lRowCounter, 2)) / 1000000 *
3.1416 / 180)
CosLat2 = Cos(Val(strColumnArray(lColumnCounter, 2)) /
1000000 * 3.1416 / 180)
CosL1L2 = Cos((Val(strRowArray(lRowCounter, 1)) / 1000000 *
3.1416 / 180) - (Val(strColumnArray(lColumnCounter, 1)) / 1000000 *
3.1416 / 180))
Distrad = Excel.WorksheetFunction.Acos((SinLat1 * SinLat2) +
(CosLat1 * CosLat2 * CosL1L2))
dblDist = Excel.WorksheetFunction.Round(Distrad * 3963.1, 2)
strOutArray(lRowCounter, lColumnCounter) = dblDist
Next lColumnCounter
Next lRowCounter
Columns("k:iv").ClearContents
For lRowCounter = 0 To lRowCount
For lColumnCounter = 0 To lColumnCount
Range("k1").Offset(lRowCounter, lColumnCounter) = strOutArray
(lRowCounter, lColumnCounter)
Next lColumnCounter
Next lRowCounter
End Sub
--- In [email protected], "Hoskins, Richard E."
<[EMAIL PROTECTED]> wrote:
> Would you share this code? Very handy. It would facilitate a
problem I am
> setting up for my spatial epidemiology class.
>
> Thanks -
>
> Dick Hoskins
>
> _____
>
> From: bjs_market_research [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 18, 2005 12:25 PM
> To: [email protected]
> Subject: [Maptitude] Re: measuring distance
>
>
>
> I have an Excel macro that does this. All you would need to do is
> output the Lat/Long of each layer, paste them into Excel, and push
> the button.
>
> --- In [email protected], Tom Shoals <[EMAIL PROTECTED]>
wrote:
> > I have two point layers (10 parks; 200 personal
> > residences). I am trying to find an easy way to
> > measure the distance from each residence to each park.
> > Is there an easy way to do this in Maptitude? Would a
> > script or macro perform this function automatically?
> > If so, does anyone have an example of such a script
> > they could share?
> >
> > Thanks for the help.
> > Tom
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Meet the all-new My Yahoo! - Try it today!
> > http://my.yahoo.com <http://my.yahoo.com>
>
>
>
>
>
> _____
>
> Yahoo! Groups Links
>
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/Maptitude/
> <http://groups.yahoo.com/group/Maptitude/>
>
>
> * To unsubscribe from this group, send an email to:
> [EMAIL PROTECTED]
> <mailto:[EMAIL PROTECTED]>
>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
Service
> <http://docs.yahoo.com/info/terms/> .
------------------------ Yahoo! Groups Sponsor --------------------~-->
In low income neighborhoods, 84% do not own computers.
At Network for Good, help bridge the Digital Divide!
http://us.click.yahoo.com/EA3HyD/3MnJAA/79vVAA/C5grlB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/Maptitude/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
