# My Latitude and Longitude Subs and Functions

1. Intermediate
Windows 11 Access 2021
Join Date
Dec 2023
Location
Corpus Christi, TX
Posts
53

## My Latitude and Longitude Subs and Functions

For anyone who is interested I'm sharing my latitude and Longitude Subroutines and functions. I wrote this over 20 years ago when I was first learning VBA. Since my occupation is a professional mariner I wanted to build some vba that could be used in navigation applications. I first built a voyage planning Application in Excel and later built a voyage planning application using Access. I wrote string subroutines to validate Latitude and longitude to see that say Latitude Degrees did not exceed 90 degrees, Longitude degrees did not exceed 180 Minutes did not exceed 59.999, and that a valid quadrant indicator was present (N, S, E, W) I wanted users to be able to type in Latitudes and longitudes in the same way that they would write them down. After the string was validated I had string functions to break apart and convert the Lat/Lon string to decimal degrees for use in navigational calculations and making it positive or negative (South = Negative, West = Negative).

Finally there are calculations for determining course and distance by Mercator sailing using the formulas from The American Practical Navigator (Bowditch) This required a lot of logic to determine course angle and difference of Longitude. Angles had to be computed in Radians so I had a couple of functions to convert back and forth. the formula from Bowditch also used Logarithm's. It's a lot of code and I wrote this while studying with the VBA for Dummies as my primary reference. There is a Function for Mercator Distance as well. Everything works too as I tested against several other calculation methods.

Finally I have 2 functions for converting decimal Degrees back to strings formatted as 00-00.000N (include leading and trailing zeros) for latitude, and 000-00.000N (include leading and trailing zeros) for Longitude.

I never got around to adding other calculation methods (such as Great Circle) but I think there is VBA code out there now that does this.

I was just learning when I wrote this so perhaps the code could be more streamlined but since it works I'm not really inclined to do so.

Use or modify as you see fit.

Code:
```Option Compare Database
Option Explicit

Public Const Pi As Double = 3.14159265358979
DegreesToRadians = dblDegrees * (Pi / 180)
End Function

End Function

'------------------------------------------------------------
' NAME: ValidateLatitude()
' PORPOSE: Validates Latititude entered as a string to ensure
' it is properly structured for use in navigation calculations
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
'-------------------------------------------------------------
Public Sub ValidateLatitude(strLatValue As String)
On Error GoTo ValidateLatitude_Err

Dim strLatDegrees As String
Dim strLatMinutes As String
Dim strLatDirection As String
Dim strSeparator As String
Dim dblLatDeg As Double
Dim arrDirection As Variant
Dim intNumCount As Integer
Dim i As Integer
Dim booFoundMatch As Boolean
Dim strMsg As String
Dim booNonNumeric As Boolean

' Check for Null value, if so Exit Sub
If strLatValue = "" Then
TempVars("CancelUpdate") = "True"
Exit Sub
End If

' Set CancelUpdate to False as default
TempVars("CancelUpdate") = "False"

'Set nonNumeric to False as default
booNonNumeric = False

' Fill Array
arrDirection = Array("N", "S", "E", "W")

' Initialize Counter
intNumCount = 0

' Get rid of leading or trailing blanks
strLatValue = Trim(strLatValue)
' assign value to direction variable
strLatDirection = Right(strLatValue, 1)

'''''' Get values for degrees and minutes''''''

' searches for "-" or " " (space or dash)
If InStr(strLatValue, "-") > 0 Then
strSeparator = "-"
ElseIf InStr(strLatValue, " ") > 0 Then
strSeparator = " "
Else
'Warn user and cancel Update
MsgBox "No dash separator for Degrees/Minutes" & vbCrLf & "Please try again.", vbOKOnly + vbInformation, "Missing separator"
TempVars("CancelUpdate") = "True"  ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

' Assign Latitude degrees and minutes to variables
strLatDegrees = Left(strLatValue, InStr(strLatValue, strSeparator) - 1)

' Check to see if user entered a non-numeric character in place of a number
If Not IsNumeric(strLatDegrees) Then booNonNumeric = True

strLatMinutes = Left(Right(strLatValue, Len(strLatValue) - InStr(strLatValue, strSeparator)), _
Len(Right(strLatValue, Len(strLatValue) - InStr(strLatValue, strSeparator))))

' strip direction
strLatMinutes = Left(strLatMinutes, Len(strLatMinutes) - 1)

' Check to see if user entered a non-numeric character in place of a number
If Not IsNumeric(strLatMinutes) Then booNonNumeric = True

' Warn user, cancel update, and exit
If booNonNumeric = True Then
MsgBox "You enetered a non-numeric charactor in place of a number" & vbCrLf & "Please Correct", vbOKOnly + vbExclamation, "Invalid entry"
TempVars("CancelUpdate") = "True"
Exit Sub
End If

' ------------------------------------------------------
' ---------------VALIDATION FOR LATITUDES---------------
' ------------------------------------------------------

' Set Initial Match Value and Check for direction sign

booFoundMatch = True

For i = 0 To 1  ' We only need to check the 1st two array elements ("N" or "S")
If strLatDirection = arrDirection(i) Then
booFoundMatch = True
GoTo NEXTCHECK
Else
booFoundMatch = False
End If
Next i

' Display message if no direction sign.

If booFoundMatch = False Then ' Build a message
strMsg = "The latitude you entered must have" & vbCrLf
strMsg = strMsg & "a valid N/S direction indicator "
strMsg = strMsg & vbCrLf & vbCrLf & "Please try again!"
' Warn user and cancel update
MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

NEXTCHECK:

' Validate Degrees
If Val(strLatDegrees) > 90 Then
'     Build a message
strMsg = "The latitude can not exceed 90 degrees "
strMsg = strMsg & vbCrLf & "Please try again!"

' Warn user and Cancel Update
MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

' Validate Minutes
If Val(strLatMinutes) > 59.9999 Then
'     Build a message
strMsg = "The latitude minutes cannot exceed 59.999"
strMsg = strMsg & vbCrLf & "Please try again!"

' Warn user and Cancel Update
MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

' Finally we combine degrees and minutes and
' check that the total does not exceed 90
dblLatDeg = Format(CDbl((Val(strLatDegrees) + Val(strLatMinutes) / 60)), "00.0000")

If dblLatDeg > 90 Then
'     Build a message
strMsg = "The latitude degrees and minuntes" & vbCrLf
strMsg = strMsg & "can not exceed a total of 90 degrees"
strMsg = strMsg & vbCrLf & "Please try again!"
' Warn user
MsgBox strMsg, vbExclamation, "Data Entry Error"
TempVars("CancelUpdate") = "True"  ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

ValidateLatitude_Exit:
Exit Sub

ValidateLatitude_Err:
MsgBox Err.Number & " " & Err.Description
Resume ValidateLatitude_Exit

End Sub

'------------------------------------------------------------
' NAME: ValidateLongitude()
' PORPOSE: Validates Longitude entered as a string to ensure
' it is properly structured for use in navigation calculations
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
'-------------------------------------------------------------
Public Sub ValidateLongitude(strLonValue As String)
On Error GoTo ValidateLongitude_Err

Dim strLonDegrees As String
Dim strLonMinutes As String
Dim strLonDirection As String
Dim strSeparator As String
Dim dblLonDeg As Double
Dim arrDirection As Variant
Dim intNumCount As Integer
Dim i As Integer
Dim booFoundMatch As Boolean
Dim strMsg As String
Dim f As String
Dim booNonNumeric As Boolean

' Check for Null value, if so Exit Sub
If strLonValue = "" Then
TempVars("CancelUpdate") = "True"
Exit Sub
End If

' Set CancelUpdate to False as default
TempVars("CancelUpdate") = "False"

' Fill Array
arrDirection = Array("N", "S", "E", "W")

' Initialize Counter
intNumCount = 0

' Get rid of leading or trailing blanks
strLonValue = Trim(strLonValue)
' assign value to direction variable
strLonDirection = Right(strLonValue, 1)

'''''' Get values for degrees and minutes''''''

' searches for "-" or " " (space or dash)
If InStr(strLonValue, "-") > 0 Then
strSeparator = "-"
ElseIf InStr(strLonValue, " ") > 0 Then
strSeparator = " "
Else
'Warn user and cancel Update
MsgBox "No dash separator for Degrees/Minutes" & vbCrLf & "Please try again.", vbOKOnly + vbExclamation, "Missing Separator"
TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

' Assign Longitude degrees and minutes to variables
strLonDegrees = Left(strLonValue, InStr(strLonValue, strSeparator) - 1)

'Check to see if user entered a non-numeric character in place of a number
If Not IsNumeric(strLonDegrees) Then booNonNumeric = True

strLonMinutes = Left(Right(strLonValue, Len(strLonValue) - InStr(strLonValue, strSeparator)), _
Len(Right(strLonValue, Len(strLonValue) - InStr(strLonValue, strSeparator))))

' strip direction
strLonMinutes = Left(strLonMinutes, Len(strLonMinutes) - 1)

' Check to see if user entered a non-numeric character in place of a number
If Not IsNumeric(strLonMinutes) Then booNonNumeric = True

' Warn user, cancel update, and exit
If booNonNumeric = True Then
MsgBox "You enetered a non-numeric charactor in place of a number" & vbCrLf & "Please Correct", vbOKOnly + vbExclamation, "Invalid entry"
TempVars("CancelUpdate") = "True"
Exit Sub
End If

' ------------------------------------------------------
' ---------------VALIDATION FOR LONGITUDE---------------
' ------------------------------------------------------

' Set Initial Match Value and Check for direction sign

booFoundMatch = True

For i = 2 To 3  ' We only need to check the 1st two array elements ("E" or "W")
If strLonDirection = arrDirection(i) Then
booFoundMatch = True
GoTo NEXTCHECK
Else
booFoundMatch = False
End If
Next i

' Display message if no direction sign.

If booFoundMatch = False Then ' Build a message
strMsg = "The Longitude you entered must have" & vbCrLf
strMsg = strMsg & "a valid E/W direction indicator "
strMsg = strMsg & vbCrLf & vbCrLf & "Please try again!"
' Warn user and cancel update
MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
TempVars("CancelUpdate") = "True"  ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

NEXTCHECK:

' Validate Degrees
If Val(strLonDegrees) > 180 Then
'     Build a message
strMsg = "The longitude cannot exceed 180 degrees"
strMsg = strMsg & vbCrLf & "Please try again!"

' Warn user and Cancel Update
MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

' Validate Minutes
If Val(strLonMinutes) > 59.9999 Then
'     Build a message
strMsg = "The longitude minutes cannot exceed 59.999"
strMsg = strMsg & vbCrLf & vbCrLf & "Please try again!"

' Warn user and Cancel Update
MsgBox strMsg, vbOKOnly + vbExclamation, "Data Entry Error"
TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

' Finally we combine degrees and minutes and
' check that the total does not exceed 90
dblLonDeg = Format(CDbl((Val(strLonDegrees) + Val(strLonMinutes) / 60)), "00.0000")

If dblLonDeg > 180 Then
'     Build a message
strMsg = "The longitude degrees and minuntes " & vbCrLf
strMsg = "Cannot exceed a total of 90 degrees"
strMsg = strMsg & vbCrLf & "Please try again!"
' Warn user
MsgBox strMsg, vbExclamation, "Data Entry Error"
TempVars("CancelUpdate") = "True"   ' Check this TempVars in the BeforeUpdate event that called this subroutine
Exit Sub
End If

ValidateLongitude_Exit:
Exit Sub

ValidateLongitude_Err:
MsgBox Err.Number & " " & Err.Description
Resume ValidateLongitude_Exit

End Sub

'------------------------------------------------------------
' NAME: Function LatLonToDegs()
' PORPOSE: Convert latitude or longitude entered as a string
' into decimal degrees for use in navigational calculations
' Depends on the ValidateLatitude or ValidateLogitude subroutine
' to insure that the string is entered in the proper format
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
'-------------------------------------------------------------
Public Function LatLonToDegs(strCoordinate As String) As Double
On Error GoTo LatLonToDegs_Err

Dim strCoords As String
Dim strDirec As String
Dim intNegativeDirection As Integer
Dim dblHaveDegrees As Double
Dim dblHaveMinutes As Double
Dim dblMyVal As Double
Dim strMyCell As String
Dim booNonNumeric As Boolean

TempVars("CancelUpdate") = "False"

' Multiplier variable is positive for North Latitude and East Longitude
intNegativeDirection = 1

' If string is null then set TempVars("CancelUpdate") to False and Exit
If IsNull(strCoordinate) Then
MsgBox "No Coodinate provided." & vbCrLf & "Exiting...", vbOKOnly + vbCritical, "Missing Coordinates"
TempVars("CancelUpdate") = "True"
Exit Function
End If

' Get rid of leading or trailing blanks and asign a N/S/E/W direction to variable
strCoords = Trim(strCoordinate)
strDirec = Right(strCoords, 1)

' Multiplier variable for Latitude/Longitude sign
' Latitude is South (-1)
If strDirec = "S" Then
intNegativeDirection = -1
End If

'Longitude is West (-1)
If strDirec = "W" Then
intNegativeDirection = -1
End If

'strip direction
strCoords = Left(strCoords, Len(strCoords) - 1)

'searches for "-"
If InStr(strCoords, "-") > 0 Then

dblHaveDegrees = Left(strCoords, InStr(strCoords, "-") - 1)
dblHaveMinutes = Left(Right(strCoords, Len(strCoords) - InStr(strCoords, "-")), _
Len(Right(strCoords, Len(strCoords) - InStr(strCoords, "-"))))

'searches for " " Space
ElseIf InStr(strCoords, " ") > 0 Then

dblHaveDegrees = Left(strCoords, InStr(strCoords, " ") - 1)
dblHaveMinutes = Left(Right(strCoords, Len(strCoords) - InStr(strCoords, " ")), _
Len(Right(strCoords, Len(strCoords) - InStr(strCoords, " "))))

End If

' Finally add Degrees and Decimal Portion and output result
LatLonToDegs = (dblHaveDegrees + dblHaveMinutes / 60) * intNegativeDirection

LatLonToDegs_Exit:
Exit Function

LatLonToDegs_Err:
MsgBox Err.Number & " " & Err.Description
Resume LatLonToDegs_Exit

End Function

'-------------------------------------------------------------------
' NAME: Function MercatorCse()
' PORPOSE: Returns course in degress by Mercator Sailing
' Lat1, Lon1 - lat and lon for position 1
' Lat2, Lon2 - lat and lon for position 2
' Assumes input is North = + , East = +
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
' original formulas from The American Practical Navigator (Bowditch)
'--------------------------------------------------------------------
Public Function MercatorCse(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double)
On Error GoTo MercatorCse_Err

Dim l As Double
Dim Dlo As Double
Dim NS As String
Dim EW As String
Dim m1 As Double
Dim m2 As Double
Dim m As Double
Dim Lon1Sin As String
Dim Lon2Sin As String
Dim LongIndicator As String
Dim Bearing As Double
Dim CseAngle As Double

' Determine if Cse is due north or due south
If (Lon1 = Lon2) Then
If (Lat1 < Lat2) Then
Bearing = 0
Else
Bearing = 180
End If

' If Bearing is either due N or due S then go straight to end
GoTo SkipCalcs

' If not then we continue on with calculations
Else

' Assign E/W Direction Sign for Lon1 and Lon2

If Lon1 < 0 Then
Lon1Sin = "W"
Else
Lon1Sin = "E"
End If

If Lon2 < 0 Then
Lon2Sin = "W"
Else
Lon2Sin = "E"
End If

' Convert Longitudes to absolute numbers

Lon1 = Abs(Lon1)
Lon2 = Abs(Lon2)

'--------------------------------------------------------
' DETERMINE which of 4 cases we have for longitude, and
' CALCULATE Dlo and DETERMINE E/W componant of Cse Angle
'--------------------------------------------------------
LongIndicator = Lon1Sin & Lon2Sin

Select Case LongIndicator

Case "EW"   ' Going from East to West Longitude

Dlo = Lon1 + Lon2

If Dlo > 180 Then   ' If Dlo exceeds 180 degrees it is shorter to go East
Dlo = 360 - Dlo
EW = "E"
Else
EW = "W"    'Otherwise we go west
End If

Case "WE"   'Going from West to East Longitude

Dlo = Lon1 + Lon2

If Dlo > 180 Then   ' If Dlo exceeds 180 degrees it is shorter to go West
Dlo = 360 - Dlo
EW = "W"
Else
EW = "E"    'Otherwise we go west
End If

Case "WW"   ' We stay in West Longitude
If Lon2 > Lon1 Then
Dlo = Lon2 - Lon1
EW = "W"    ' We are proceeding in a Westerly direction
Else
Dlo = Lon1 - Lon2
EW = "E"    ' We are proceeding in a Easterly direction
End If

Case "EE"   ' We stay in East Longitude
If Lon2 > Lon1 Then
Dlo = Lon2 - Lon1
EW = "E"    ' We are proceeding in a Easterly direction
Else
Dlo = Lon1 - Lon2
EW = "W"    ' We are proceeding in a Westerly direction
End If

End Select

' Multiply Dlo by 60 for use in further Calculations

Dlo = Dlo * 60

'----------------------------------------------------------------------
' CALCULATE MERIDIONAL PARTS:

' CALCULATION NOTES..........
' Formula for meridional parts is
' m = 7915.7 * Log Tan (45 + (Lat/2) - (Sin(Lat) * 23)
' Used a constant value of 0.017453293 for converting degrees to Radians.
' Used a constant of 0.785398 for 45 in Radians
' Must divide Log of number by Log(10).
' Example: Log(Number) / Log(10)
'-------------------------------------------------------------------------

m1 = 7915.7 * Log(Tan(0.785398163 + (Lat1 / 2) * 0.017453293)) / Log(10) - (Sin(Lat1 * 0.017453293) * 23)
m2 = 7915.7 * Log(Tan(0.785398163 + (Lat2 / 2) * 0.017453293)) / Log(10) - (Sin(Lat2 * 0.017453293) * 23)
m = m1 - m2
m = Abs(m)

' CALCULATE COURSE ANGLE:
' The formula from Bowditch is ... Tan C = Dlo/m

If Lat1 = Lat2 Then
CseAngle = 90
GoTo Skip3
End If

CseAngle = (Dlo * 0.07453) / (m * 0.07453)
CseAngle = Atn(CseAngle)
' Convert to degrees
Skip3:
' Set Decimal places to 1
CseAngle = Round(CseAngle, 1)
' Make it a postive number so that the math works properly
CseAngle = Abs(CseAngle)

' CALCULATE THE COURSE ANGLE:

' Determine if North or South
If Lat2 > Lat1 Then
NS = "N"
Else
NS = "S"
End If

' Determine how to apply the course angle

If NS = "N" And EW = "E" Then
Bearing = 0 + CseAngle
ElseIf NS = "S" And EW = "E" Then
Bearing = 180 - CseAngle
ElseIf NS = "N" And EW = "W" Then
Bearing = 360 - CseAngle
ElseIf NS = "S" And EW = "W" Then
Bearing = 180 + CseAngle
End If

' FINALLY WE OUTPUT THE RESULT:

End If
SkipCalcs:
MercatorCse = Bearing

MercatorCse_Exit:
Exit Function

MercatorCse_Err:
MsgBox Err.Number & " " & Err.Description
Resume MercatorCse_Exit

End Function
'-------------------------------------------------------------------
' NAME: Function MercatorDist()
' PORPOSE: Returns Distance in nautical miles by Mercator Sailing
' Lat1, Lon1 - lat and lon for position 1
' Lat2, Lon2 - lat and lon for position 2
' Course as calculated prior in Function MercatoCse()
' Assumes input is North = + , East = +
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: Feb 2002 (Excel) Revised for Access August 2023
' original formulas from The American Practical Navigator (Bowditch)
'--------------------------------------------------------------------
Public Function MercatorDist(Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double, Cse As Double) As Double
On Error GoTo MercatorDist_Err

Dim Lon1Sin As String
Dim Lon2Sin As String
Dim LongIndicator As String
Dim Dlo As Double
Dim l As Double
Dim Dist As Double

' Assign E/W Direction Sign for Lon1 and Lon2

If Lon1 < 0 Then
Lon1Sin = "W"
Else
Lon1Sin = "E"
End If

If Lon2 < 0 Then
Lon2Sin = "W"
Else
Lon2Sin = "E"
End If

' Convert Longitudes to absolute numbers

Lon1 = Abs(Lon1)
Lon2 = Abs(Lon2)

' DETERMINE which of 4 cases we have for longitude, and
' CALCULATE Dlo and DETERMINE E/W componant of Cse Angle

LongIndicator = Lon1Sin & Lon2Sin

Select Case LongIndicator

Case "EW"

Dlo = Lon1 + Lon2

If Dlo > 180 Then
Dlo = 360 - Dlo
End If

Case "WE"

Dlo = Lon1 + Lon2

If Dlo > 180 Then
Dlo = 360 - Dlo
End If

Case "WW"
If Lon2 > Lon1 Then
Dlo = Lon2 - Lon1
Else
Dlo = Lon1 - Lon2
End If

Case "EE"
If Lon2 > Lon1 Then
Dlo = Lon2 - Lon1
Else
Dlo = Lon1 - Lon2
End If

End Select

Select Case Cse ' Used a constant value of 0.017453293 for converting degrees to Radians

Case 90
Dist = Dlo * 60 * Cos(Lat1 * 0.017453293)

Case 270
Dist = Dlo * 60 * Cos(Lat1 * 0.017453293)

Case 0
Dist = Abs((Lat1 - Lat2) * 60)

Case 180
Dist = Abs((Lat1 - Lat2) * 60)

Case Else

l = Abs((Lat1 - Lat2) * 0.017453293)    ' Get Difference of Latitude in Radians

Dist = l / Cos(RadCse)              ' Divide Differnce of latitude by Cos of course in Radians
Dist = RadiansToDegrees(Dist)       ' Convert back to Deerees for angular distance on earths surface
Dist = Abs(Dist) * 60               ' Multiply by 60 to get Nautical Miles

End Select

' display result

MercatorDist = Dist

MercatorDist_Exit:
Exit Function

MercatorDist_Err:
MsgBox Err.Number & " " & Err.Description
Resume MercatorDist_Exit

End Function
'------------------------------------------------------------
' NAME: Function FormatLatitude()
' PORPOSE: Format a latitude string as 00-00.000N (include leading and trailing zeroa)
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: August 2023
'-------------------------------------------------------------
Public Function FormatLatitude(strLatValue As String)
On Error GoTo FormatLatitude_Err

Dim strLatDirection As String
Dim strLatDegrees As String
Dim strLatMinutes As String
Dim strSeparator As String
Dim booNonNumeric As Boolean

' If we are passed an empty string then exit
If strLatValue = "" Then
Exit Function
End If

' Get rid of leading or trailing blanks
strLatValue = Trim(strLatValue)
' assign value to direction variable
strLatDirection = Right(strLatValue, 1)

'''''' Get values for degrees and minutes''''''

' searches for "-" or " " (space or dash)
If InStr(strLatValue, "-") > 0 Then
strSeparator = "-"
ElseIf InStr(strLatValue, " ") > 0 Then
strSeparator = " "
Else
'Warn user and cancel Update
MsgBox "No dash separator for Degrees/Minutes" & vbCrLf & "Please try again.", vbOKOnly + vbInformation, "Missing separator"
Exit Function
End If

' Assign Latitude degrees and minutes to variables and format
strLatDegrees = Left(strLatValue, InStr(strLatValue, strSeparator) - 1)
strLatDegrees = Format(strLatDegrees, "00")

strLatMinutes = Left(Right(strLatValue, Len(strLatValue) - InStr(strLatValue, strSeparator)), _
Len(Right(strLatValue, Len(strLatValue) - InStr(strLatValue, strSeparator))))

' strip direction and format minutes
strLatMinutes = Left(strLatMinutes, Len(strLatMinutes) - 1)
strLatMinutes = Format(strLatMinutes, "00.000")

FormatLatitude = strLatDegrees & "-" & strLatMinutes & strLatDirection

FormatLatitude_Exit:
Exit Function

FormatLatitude_Err:
MsgBox Err.Number & " " & Err.Description
Resume FormatLatitude_Exit
End Function
'------------------------------------------------------------
' NAME: Function FormatLongitude()
' PORPOSE: Format a longitude string as 000-00.000N (include leading and trailing zeroa)
' CREATED BY: Andrew Dulavitz   (adulavitz@hotmail.com)
' DATE: August 2023
'-------------------------------------------------------------
Public Function FormatLongitude(strLonValue As String)
On Error GoTo FormatLongitude_Err

Dim strLonDirection As String
Dim strLonDegrees As String
Dim strLonMinutes As String
Dim strSeparator As String

' If we are passed an empty string then exit
If strLonValue = "" Then
Exit Function
End If

' Get rid of leading or trailing blanks
strLonValue = Trim(strLonValue)
' assign value to direction variable
strLonDirection = Right(strLonValue, 1)

'''''' Get values for degrees and minutes''''''

' searches for "-" or " " (space or dash)
If InStr(strLonValue, "-") > 0 Then
strSeparator = "-"
ElseIf InStr(strLonValue, " ") > 0 Then
strSeparator = " "
Else
'Warn user and cancel Update
MsgBox "No dash separator for Degrees/Minutes" & vbCrLf & "Please try again.", vbOKOnly + vbExclamation, "Missing Separator"
Exit Function
End If

' Assign Longitude degrees and minutes to variables and format
strLonDegrees = Left(strLonValue, InStr(strLonValue, strSeparator) - 1)
strLonDegrees = Format(strLonDegrees, "000")

strLonMinutes = Left(Right(strLonValue, Len(strLonValue) - InStr(strLonValue, strSeparator)), _
Len(Right(strLonValue, Len(strLonValue) - InStr(strLonValue, strSeparator))))
' strip direction
strLonMinutes = Left(strLonMinutes, Len(strLonMinutes) - 1)
strLonMinutes = Format(strLonMinutes, "00.000")

FormatLongitude = strLonDegrees & "-" & strLonMinutes & strLonDirection

FormatLongitude_Exit:
Exit Function

FormatLongitude_Err:
MsgBox Err.Number & " " & Err.Description
Resume FormatLongitude_Exit
End Function```

2. VIP
Windows 10 Access 2007
Join Date
Jan 2017
Location
Swansea,South Wales,UK
Posts
4,772
Nicely commented.

3. This obviously took a lot of time, patience and knowledge to complete. I think it's admirable when people share their work after such efforts.

4. Many thanks for sharing this information which I have downloaded for future use.
Out of interest, do your think your MercatorCse & MercatorDist procedures could be of use to the OP in this thread at: UtterAccess.com

5. Intermediate
Windows 11 Access 2021
Join Date
Dec 2023
Location
Corpus Christi, TX
Posts
53
Many thanks for sharing this information which I have downloaded for future use.
Out of interest, do your think your MercatorCse & MercatorDist procedures could be of use to the OP in this thread at: UtterAccess.com
Good Day Colin,

It's hard to say what he has in mind but since we are talking straight line distances and terrestrial navigation has so many twists and turns I think that google maps as you suggested are quite likely a better alternative.

All that being said it is also quite possible to export .Kml files from google maps and extract the data from them. I worked 20 years installing and repairing submarine cables and have used Excel with Google maps to take survey data and display on Google maps. I never did it the other way around but in doing this I discovered it's possible and there are a number of software tools that can assist you in exporting/importing google maps data. It should be entirely possible to devise a way to export a 'kml file from Google maps, use one of these utilities to read/extract the geo coordinates and then import into an access table.

As a side note around 20 years ago when I created my voyage planning application I created a utility for importing/exporting routes to/from Winfrog which was a Survey program that we used on the cable ships but it was rather simple because the survey program wrote almost everything to CSV text files. It was all a great learning experience for me as I was applying my job knowledge to doing things in software. Although it was a great learning experience I realized I was never going to compete with many of the excellent marine navigation products that have teams of professional developers working on them.

6. Hi again
I checked my own code and found this function which I've just posted to the UtterAccess thread:

Code:
`Function GetDistanceMetres(lat1Degrees As Single, lon1Degrees As Single, lat2Degrees As Single, lon2Degrees As Single)    Dim EarthSphereRadiusMetres As Double   ' Dim kilometerConversionToMilesFactor As Double    Dim lat1Radians As Single    Dim lon1Radians As Single    Dim lat2Radians As Single    Dim lon2Radians As Single    Dim AsinBase As Single    Dim DerivedAsin As Single    Dim Pi As Double    Pi = 3.14159265359    'Mean radius of the earth (replace with 3443.89849 to get nautical miles)    EarthSphereRadiusMetres = 6371000    'Convert kilometers into miles   ' kilometerConversionToMilesFactor = 0.621371    'Convert each decimal degree to radians    lat1Radians = (lat1Degrees / 180) * Pi    lon1Radians = (lon1Degrees / 180) * Pi    lat2Radians = (lat2Degrees / 180) * Pi    lon2Radians = (lon2Degrees / 180) * Pi    AsinBase = Sin(Sqr(Sin((lat1Radians - lat2Radians) / 2) ^ 2 + Cos(lat1Radians) * Cos(lat2Radians) * Sin((lon1Radians - lon2Radians) / 2) ^ 2))    DerivedAsin = (AsinBase / Sqr(-AsinBase * AsinBase + 1))    'Get distance from [lat1,lon1] to [lat2,lon2]   ' GetDistanceMetres = Round(2 * DerivedAsin * EarthSphereRadiusMetres, 2)    GetDistanceMetres = Round(2 * DerivedAsin * EarthSphereRadiusMetres, 0)    'Miles: = Round(2 * DerivedAsin * (earthSphereRadiusKilometers * kilometerConversionToMilesFactor), 2)    'Debug.Print GetDistanceMetres    End Function`
I never got around to using it at the time but I've just tested using a number of UK postcodes for which I have all co-ordinate data and it seems fine

Code:
```Function TestDistanceKilometres(Postcode1 As String, Postcode2 As String) As Single

Dim lat1 As Single
Dim lon1 As Single
Dim lat2 As Single
Dim lon2 As Single

lat1 = DLookup("Latitude", "Postcodes", "Postcode = '" & Postcode1 & "'")
lon1 = DLookup("Longitude", "Postcodes", "Postcode = '" & Postcode1 & "'")
lat2 = DLookup("Latitude", "Postcodes", "Postcode = '" & Postcode2 & "'")
lon2 = DLookup("Longitude", "Postcodes", "Postcode = '" & Postcode2 & "'")

Debug.Print "Postcodes: " & Postcode1 & ", " & Postcode2 & vbTab & " Distance = " & GetDistanceMetres(lat1, lon1, lat2, lon2) / 1000 & " km"

End Function```
Example results:

Code:
```?TestDistanceKilometres ("BS25 5NB", "TA23 0RP")Postcodes: BS25 5NB, TA23 0RP  Distance: 46.93 km

?TestDistanceKilometres ("SE1 7PB", "HA3 0SN")
Postcodes: SE1 7PB, HA3 0SN   Distance: 15.072 km

?TestDistanceKilometres ("SE1 7PB", "BS7 8HP")
Postcodes: SE1 7PB, BS7 8HP   Distance: 171.52 km

?TestDistanceKilometres ("SE1 7PB", "TA23 0RP")
Postcodes: SE1 7PB, TA23 0RP   Distance: 230.867 km```

7. Intermediate
Windows 11 Access 2021
Join Date
Dec 2023
Location
Corpus Christi, TX
Posts
53
Colin,

That looks good, the formulas use basic spherical trigonometry which over shorter distance produce perfectly acceptable results. There are a plethora of Calculation methods in use which ones are preferable in each circumstance is dependent on a number of different factors but perhaps the most important one is the Datum in use. with the advent of GPS most uses have settled on WGS84. This is mainly because the earth is not a perfect sphere and has some flattening at the poles. Maps and Charts have been produced over the years that use different Datums. when using different chart datums corrections must be applied or errors will result. There are some navigational charts that when using GPS data can produce significant errors if not corrected.

Likewise when measuring angles on a sphere we must keep in mind the we exist on a flat plane (our frame of reference) and that over large areas any course angle other than 0, 90, 180, or 270 degrees is going to be continuously changing as we move across the sphere. This is a Great Circle. In marine navigation we interpret this as a series of straight lines (Rhumb Lines) that change as we move across the ocean. while one can keep the same course angle for long distances (Rumb Line) the shortest distance will be a great circle. These are never usually a problem in the real world as long as we are aware of the limitations and built in errors of whatever calculation method we are using.

You are likely aware of what I just re-iterated but I thought I would throw this in there for the benefit of anyone who is curious and wants to delve into these types of calculations. Almost every calculation method involves some some tradeoffs in accuracy you just need to choose the best one for your application.

8. I was aware of most of the details but well worth explaining for the benefit of everyone. Never heard of Rumb lines before
As you say, all calculations involve some degree of approximation (earth isn't spherical, altitude variations not included) but are likely to be good enough for the average user.
In any case, nobody will ever travel the linear distance between 2 locations.

Anyway, I've just published an article with my code and an example app to Calculate Distance Between Locations (isladogs.co.uk)

You might also be interested in my earlier article from 2022 Missing Trigonometric Functions (isladogs.co.uk) which includes code for 6 additional trig functions (ASin / ACos / ACot / ASec / ACsc / Atn2) not provided with Access. I use two of those in the code needed to draw circles on maps (also provided).

9. Intermediate
Windows 11 Access 2021
Join Date
Dec 2023
Location
Corpus Christi, TX
Posts
53
I was aware of most of the details but well worth explaining for the benefit of everyone. Never heard of Rumb lines before
As you say, all calculations involve some degree of approximation (earth isn't spherical, altitude variations not included) but are likely to be good enough for the average user.
In any case, nobody will ever travel the linear distance between 2 locations.

Anyway, I've just published an article with my code and an example app to Calculate Distance Between Locations (isladogs.co.uk)

You might also be interested in my earlier article from 2022 Missing Trigonometric Functions (isladogs.co.uk) which includes code for 6 additional trig functions (ASin / ACos / ACot / ASec / ACsc / Atn2) not provided with Access. I use two of those in the code needed to draw circles on maps (also provided).
Colin,

You do excellent work. I've followed your postings online for a while and you do a great service for others making difficult concepts understandable. I have bookmarked both of these for future reference. Thanks for all you do.

On a personal level I think the better achievement that I achieved with the code posted here was the Lat/Lon validation routines and the code to convert Lat/Lon strings to decimal degrees and back to a string. Most mariners I know do not want to be bothered trying to use decimal degrees and figure out whether to use a positive or a negative number. They work every day in degrees, minutes and decimal minutes of latitude and longitude with a N, S, E, or W indicator. So I felt it was important that whatever I did behind the scenes that the end user is able to work in a way they are accustomed to.

I noticed all your validation code and agree with you on its importance.
Whilst I personally prefer decimal values for lat/long, I agree with you about providing data in the format that users are most comfortable with. But why not degrees, minutes and seconds in your case?

11. Intermediate
Windows 11 Access 2021
Join Date
Dec 2023
Location
Corpus Christi, TX
Posts
53