Created
December 17, 2022 16:57
-
-
Save Jomy10/00da0bb34bcecb880b12933cb7e26a23 to your computer and use it in GitHub Desktop.
VBA Script to convert a coordinate to longitude and latitude in Excel (DMS to degrees)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'Parse a coordinate to an array of numbers containing hours, minutes, seconds (still represented as strings) | |
Private Function ParseCoOne(Co As String) As String() | |
Dim parsed(3) As String | |
Dim tmp() As String | |
tmp = Split(Co, "°") | |
parsed(0) = tmp(0) | |
tmp = Split(tmp(1), "'") | |
parsed(1) = tmp(0) | |
tmp = Split(tmp(1), """") | |
parsed(2) = tmp(0) | |
ParseCoOne = parsed | |
End Function | |
'convert DMS to degrees (CoTy = N or E) | |
Public Function DMSToDegree(Co As String, CoTy As String) As Double | |
'VBA gives wrong erros when using CDbl | |
On Error Resume Next | |
Dim coSplit() As String | |
Dim nor As String | |
Dim eas As String | |
coSplit = Split(Co, " ") | |
nor = coSplit(0) | |
eas = coSplit(1) | |
Dim parsedCo() As String | |
If CoTy = "N" Then | |
'Simple format check | |
If Not nor Like "*°*'*""N" Then | |
MsgBox "Error: Invalid latitude" | |
End If | |
parsedCo = ParseCoOne(nor) | |
ElseIf CoTy = "E" Then | |
If Not eas Like "*°*'*""E" Then | |
MsgBox "Error: Invalid longitude" | |
End If | |
parsedCo = ParseCoOne(eas) | |
Else | |
MsgBox "Error: Invalid CoTy (possible values are ""N"", ""E"")" | |
End If | |
Dim total As Double | |
total = CDbl(parsedCo(0)) + CDbl(parsedCo(1)) / 60 + CDbl(parsedCo(2)) / 3600 | |
DMSToDegree = total | |
End Function | |
'Will output longitude of 50,5058611 | |
Sub test() | |
MsgBox DMSToDegree("50°30'21,1""N 5°35'39,6""E", "N") | |
End Sub |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment