[SOLVED] Distance using Longitude and Latitude (Excel vba to Access vba)

Distance using Longitude and Latitude (Excel vba to Access vba)

I have the following VBA function which i used for MS Excel. However, I have imported my entire database into MS Access and I wish to use this function without calling it from MS Excel. Is it possible? And what are the things I must change?

`
'This is to find the GCDm (great circle distance) in miles

Function GCDnm(origin As String, dest As String) As Integer


Dim olat As Double
Dim olong As Double
Dim dlat As Double
Dim dlong As Double
Dim earthradius As Integer

earthradius = 6371

olat = coordlat(origin)
olong = coordlong(origin)
dlat = coordlat(dest)
dlong = coordlong(dest)

GCDnm = Round(WorksheetFunction.Acos(( _
    Sin(WorksheetFunction.Radians(olat)) * _
    Sin(WorksheetFunction.Radians(dlat)) + _
    Cos(WorksheetFunction.Radians(olat)) * _
    Cos(WorksheetFunction.Radians(dlat)) * _
    Cos(WorksheetFunction.Radians(olong - dlong)))) * _
    earthradius, 0) / 1.852

'GDCm = [arccos[sin(origin latitude) * sin(destination latitude) * cos(origin latitude)    * sin(destination latitude) * cos(origin latitude - dest latitude)]*radius=6371]/1.852

 End Function

`

Solution

While you can automate Excel like Makita says, performance is likely to be pretty bad if you were to then use the function in a query or something like that. Personally I'd rewrite the code in pure VBA:

Function ArcCos(Number As Double) As Double
  ArcCos = Atn(-Number / Sqr(-Number * Number + 1)) + 2 * Atn(1)
End Function

Function DegreesToRadians(Number As Double) As Double
  DegreesToRadians = Number / 57.2957795130823
End Function

'...

GCDnm = Round(ArcCos(( _
  Sin(DegreesToRadians(olat)) * _
  Sin(DegreesToRadians(dlat)) + _
  Cos(DegreesToRadians(olat)) * _
  Cos(DegreesToRadians(dlat)) * _
  Cos(DegreesToRadians(olong - dlong)))) * _
  earthradius, 0) / 1.852