Hello,
I'm quite unfamiliar with access vba and i'm actually facing some trouble:
I got a VBA code on Excel that help me find the shortest road between sets of coordinates, but since I'm going to have hundred of thousand of data I wish to do it on Access, the problem is I don't know how to translate the code I got on Excel to Access.
Here's the code I use in Excel:
Thanks for your time
I'm quite unfamiliar with access vba and i'm actually facing some trouble:
I got a VBA code on Excel that help me find the shortest road between sets of coordinates, but since I'm going to have hundred of thousand of data I wish to do it on Access, the problem is I don't know how to translate the code I got on Excel to Access.
Here's the code I use in Excel:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | Function distance(A As Range, listeB As Range) Dim datas, ptA, lig As Long Dim b As Double, c As Double, d As Double ptA = A.Value datas = listeB.Value distance = 99999 '=SI((ACOS(SIN(RADIANS(A2))*SIN(RADIANS($D$2))+COS(RADIANS(A2))*COS(RADIANS($D$2))*COS(RADIANS(B2-$E$2)))*6371)>1;0;-1) With Application.WorksheetFunction b = Sin(.Radians(ptA(1, 1))) c = Cos(.Radians(ptA(1, 1))) For lig = 1 To UBound(datas) d = (.Acos(b * Sin(.Radians(datas(lig, 1))) + c * Cos(.Radians(datas(lig, 1))) * Cos(.Radians(ptA(1, 2) - datas(lig, 2)))) * 6371) If d < distance Then distance = d Next lig End With End Function |
Thanks for your time