Help for coordinates (1 Viewer)

Newo

New member
Local time
Today, 01:54
Joined
Dec 29, 2020
Messages
1
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:

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
 

Attachments

  • coordonnées.accdb
    600 KB · Views: 426

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:54
Joined
Feb 28, 2001
Messages
27,001
In general, the only difference between Access VBA and Excel VBA is (a) how you trigger it and (b) from where you get/put the data. The math syntax is the same.

Where you have $D$2, for example, that would have to come EITHER from fields in a recordset opened for step-wise operation, or as elements of a query if you were trying to deal with this in bulk.
 

Users who are viewing this thread

Top Bottom