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 Public Function DegreesToRadians(dblDegrees As Double) DegreesToRadians = dblDegrees * (Pi / 180) End Function Public Function RadiansToDegrees(dblRadians As Double) RadiansToDegrees = dblRadians * (180 / Pi) 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 CseAngle = RadiansToDegrees(CseAngle) 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 RadCse 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 RadCse = Cse * 0.017453293 ' Course in Radians 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