VBA Excel Distance between two points from a list

[Moody89]

Hello, I am sorry for my bad English. I am a student from Germany and I have the following problem: I am doing a Master which is in fact a combination of Humanities and Computer science. I did a Java course but last week we have received several exercises about VBA Excel, without having a lecture or training before. The reason for this mess is that we have to visit another faculty and the staff administration has a lot of problems to coordinate things. But anyway: I would be extremely thankful, if you could help with the following exercise:

"Write a function, that calculates the distance between two points from a list of coordinates. The function receives the start and end point as arguments. The program should search for the given points in the list (which is in my excel sheet) in order to calculate the distance between them.

Tip: The numbers of Points could be Strings as well. The function CStr(number) transforms a value into a String."

I wrote the following function for the distance and it works

Function distance(ya As Double, xa As Double, yb As Double, xB As Double)
distancePoint = Sqr((ya - yb) ^ 2 + (xa - xB) ^ 2)


End Function

Sorry for my lack of knowledge, but as I said I have started to work with VBA-Excel just one week ago.
It would be great if you could help me.
Thanks

 

[ajamess]

A bit confused by some of your wording, but I'm thinking you mean this.

Say you have a tab called coordinates, that is structured like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]XA[/TD]
[TD]YA[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]XB[/TD]
[TD]YB[/TD]
[/TR]
</tbody>[/TABLE]


.... and so on, for a long list of locationslocations.

Function Distance (Coord1 as String, Coord2 as string)
Dim Coord as worksheet

Set Coord = Workbooks("WorkbookName").Worksheets("Coordinates")

Dim XA as double
Dim XB as double
Dim YA as double
Dim YB as double

XA = application.worksheetfunction.index(coord.Range("B:B"),application.worksheetfunction.match(Coord1,Coord.Range("A:A"),0))
XB = application.worksheetfunction.index(coord.Range("B:B"),application.worksheetfunction.match(Coord2,Coord.Range("A:A"),0))
YA = application.worksheetfunction.index(coord.Range("C:C"),application.worksheetfunction.match(Coord1,Coord.Range("A:A"),0))
YB = application.worksheetfunction.index(coord.Range("C:C"),application.worksheetfunction.match(Coord2,Coord.Range("A:A"),0))

'Then your distance equations here.


End Function

If I misunderstood something, let me know. This basically takes two "Name" inputs. And looks up the X and Y coordinates for both of them, and then you put your distance calculation in there.

 

[Moody89]

Hello ajamess, thanks a lot for your Help!
I tried your code but unfortunately it did not work. But I think cause of an misconception from my site.
Here is the link to the exercises - sorry that it is German. The assignment we are talking about is quite at the end of page one. It starts with HA2: ......
On the second Page you see the example how it should work in the excel sheet.

http://unold.net/informatik/Info_VBA_Uebung_WS1718_Ue3.pdf

I try to translate the assignment as best I can:
HA2: Write a function that calculates the distance between two given points from a coordinate list.
The function gets the coordinate range and the start and end point. Then you have to search in the coordinate list for the start and end point and calculate the distance.
Tip: Numbers of points can also be strings. The CStr (number) function converts a value into a string.
Use the following function to calculate the distance:


[COLOR=rgb(0.000000%, 0.000000%, 100.000000%)]Function [/COLOR]distance(yA [COLOR=rgb(0.000000%, 0.000000%, 100.000000%)]As Double[/COLOR], xA [COLOR=rgb(0.000000%, 0.000000%, 100.000000%)]As Double[/COLOR], yB [COLOR=rgb(0.000000%, 0.000000%, 100.000000%)]As Double[/COLOR], xB [COLOR=rgb(0.000000%, 0.000000%, 100.000000%)]AsDouble[/COLOR])
distance = Sqr((yA - yB) ^ [COLOR=rgb(100.000000%, 0.000000%, 0.000000%)]2 [/COLOR]+ (xA - xB) ^ [COLOR=rgb(100.000000%, 0.000000%, 0.000000%)]2[/COLOR])[COLOR=rgb(0.000000%, 0.000000%, 100.000000%)]End Function [/COLOR]






I tried to put add the coordinate list as an argument to the function, but I receive an error.
It would be great if you could me help again
Thanks a lot

 

[Macropod]

Are these distances great circle distances, or something else? For great circle distances, see the formula at: http://www.msofficeforums.com/excel/24577-trying-find-way-show-closest-using-lat.html#post77574. That formula could be converted to an Excel VBA function.