Change format of ConvertDegrees function

[Aussiebear]

After much frustration its been decided that I need to change the results from DMS (Degrees Minutes Seconds) to simply DM (Degrees Minutes). The following code delivers DMS results where the seconds are formatted as 00.0000 whereas I now require the minutes to be formatted as 00.000 and no seconds showing.

' Converts decimal latitude, longitude or azimuth value to degrees/minutes/seconds string formatPublic Function ConvertDegrees(ByVal decimalDeg As Double, Optional isLongitude As Variant) As String
If Not IsMissing(isLongitude) And CBool(isLongitude) Then
decimalDeg = NormalizeLon(decimalDeg)
ElseIf Not IsMissing(isLongitude) And Not CBool(isLongitude) Then
decimalDeg = NormalizeLat(decimalDeg)
Else
decimalDeg = NormalizeAzimuth(decimalDeg, False)
End If

Dim s As Integer: s = Sign(decimalDeg)
decimalDeg = Abs(decimalDeg)
Dim degrees As Integer: degrees = Fix(decimalDeg)
Dim minutes As Integer: minutes = Fix((decimalDeg - degrees) * 60)
'Dim seconds As Double: seconds = Round((decimalDeg - degrees - (minutes / 60)) * 60 * 60, 4) ' 4 digit precision corresponds to ~3mm

If Not IsMissing(isLongitude) And Not CBool(isLongitude) Then
ConvertDegrees = Format$(degrees, "00") & Chr$(176) & Format$(minutes, "00") & "'" & Format$(seconds, "00.0000") + Chr$(34)
Else
ConvertDegrees = Format$(degrees, "000") & Chr$(176) & Format$(minutes, "00") & "'" & Format$(seconds, "00.0000") + Chr$(34)
If decimalDeg = 0 Then
' do nothing
ElseIf IsMissing(isLongitude) Then
If s = -1 Then ConvertDegrees = "-" + ConvertDegrees
ElseIf isLongitude Then
If s = 1 Then
ConvertDegrees = ConvertDegrees + "E"
ElseIf s = -1 Then
ConvertDegrees = ConvertDegrees + "W"
End If
Else
If s = 1 Then
ConvertDegrees = ConvertDegrees + "N"
ElseIf s = -1 Then
ConvertDegrees = ConvertDegrees + "S"
End If
End If
End Function

I had thought it would be a simple case of editing the following lines

ConvertDegrees = Format$(degrees, "00") & Chr$(176) & Format$(minutes, "00.000") & "'"
Else
ConvertDegrees = Format$(degrees, "000") & Chr$(176) & Format$(minutes, "00.000") & "'"

but the code fails to run. What have I done wrong?

[Paul_Hossler]

G'day

The normalize functions were commented out since they weren't included

I did fiddle your original first so I could see how you were doing in, but the second version seems to work
Also changed the output format a little too so's I could read it better

I wasn't sure about + being E and N, - being W and S so I may have reversed them

I'm sure an old salt can figure it out :yes

minutes was originally Dim-ed as an Integer, should be Double

Don't know what "fails to run" means specifically


28390





Option Explicit


' Converts decimal latitude, longitude or azimuth value to degrees/minutes/seconds string format
Public Function ConvertDegrees(decimalDeg As Double, Optional isLongitude As Boolean = False) As String


' If Not IsMissing(isLongitude) And CBool(isLongitude) Then
' decimalDeg = NormalizeLon(decimalDeg)
' ElseIf Not IsMissing(isLongitude) And Not CBool(isLongitude) Then
' decimalDeg = NormalizeLat(decimalDeg)
' Else
' decimalDeg = NormalizeAzimuth(decimalDeg, False)
' End If

Dim s As Integer: s = Sgn(decimalDeg)

decimalDeg = Abs(decimalDeg)

Dim degrees As Integer: degrees = Fix(decimalDeg)
Dim minutes As Integer: minutes = Fix((decimalDeg - degrees) * 60)
Dim seconds As Double: seconds = Round((decimalDeg - degrees - (minutes / 60)) * 60 * 60, 4) ' 4 digit precision corresponds to ~3mm

If isLongitude Then
ConvertDegrees = Format$(degrees, "##0") & Chr$(176) & Format$(minutes, " 00") & "'" & Format$(seconds, " 00.0000") + Chr$(34)
If s = 1 Then
ConvertDegrees = ConvertDegrees + " E"
ElseIf s = -1 Then
ConvertDegrees = ConvertDegrees + " W"
End If

Else
ConvertDegrees = Format$(degrees, "#0") & Chr$(176) & Format$(minutes, " 00") & "'" & Format$(seconds, " 00.0000") + Chr$(34)
If s = 1 Then
ConvertDegrees = ConvertDegrees + " N"
ElseIf s = -1 Then
ConvertDegrees = ConvertDegrees + " S"
End If
End If
End Function


Public Function ConvertDegreesDM(decimalDeg As Double, Optional isLongitude As Boolean = False) As String


' If Not IsMissing(isLongitude) And CBool(isLongitude) Then
' decimalDeg = NormalizeLon(decimalDeg)
' ElseIf Not IsMissing(isLongitude) And Not CBool(isLongitude) Then
' decimalDeg = NormalizeLat(decimalDeg)
' Else
' decimalDeg = NormalizeAzimuth(decimalDeg, False)
' End If

Dim s As Integer: s = Sgn(decimalDeg)

decimalDeg = Abs(decimalDeg)

Dim degrees As Integer: degrees = Fix(decimalDeg)
Dim minutes As Double: minutes = Round((decimalDeg - degrees) * 60, 4) ' <<<<<<<<<<<<<<<<<<<

If isLongitude Then
ConvertDegreesDM = Format$(degrees, "##0") & Chr$(176) & Format$(minutes, " 00.0000") & "'"
If s = 1 Then
ConvertDegreesDM = ConvertDegreesDM + " E"
ElseIf s = -1 Then
ConvertDegreesDM = ConvertDegreesDM + " W"
End If

Else
ConvertDegreesDM = Format$(degrees, "#0") & Chr$(176) & Format$(minutes, " 00.0000") & "'"
If s = 1 Then
ConvertDegreesDM = ConvertDegreesDM + " N"
ElseIf s = -1 Then
ConvertDegreesDM = ConvertDegreesDM + " S"
End If
End If
End Function

[Aussiebear]

I did fiddle your original first so I could see how you were doing in, but the second version seems to work
Also changed the output format a little too so's I could read it better

Thank you for this.


I wasn't sure about + being E and N, - being W and S so I may have reversed them

Wiser heads than us defined the Cartesian system for Graphing and Mapping and thankfully it works North being +, South being -, East is + and West is -


minutes was originally Dim-ed as an Integer, should be Double

I saw that after I posted to the forum but then the line turned red


Don't know what "fails to run" means specifically

Didn't work due to the errors, and my latest results were resulting in Zero being installed in the cell as the calculated value.

So with a rum in one hand and your adopted code in the other hand I shall set forth to the excel workbook. Wish me luck for I fear I shall need it (or another rum.)

[Aussiebear]

Paul, we are close..... Just need to find a way to correctly nominate the direction. Currently I am using South (Column 3, Latitude: where data is entered in as a minus decimal value before converting) and East (Column 4,Longitude: where data is entered in as Positive decimal value before converting).

[Aussiebear]

This is what I've ended up with as my effort. Since I'm only using South and East values I cut out everything else as it was simply confusing me.


' Converts decimal latitude, longitude or azimuth value to degrees/minutes string formatPublic Function ConvertDegrees(ByVal decimalDeg As Double, Optional isLongitude As Variant) As String
If Not IsMissing(isLongitude) And CBool(isLongitude) Then
decimalDeg = NormalizeLon(decimalDeg)
ElseIf Not IsMissing(isLongitude) And Not CBool(isLongitude) Then
decimalDeg = NormalizeLat(decimalDeg)
Else
decimalDeg = NormalizeAzimuth(decimalDeg, False)
End If

Dim s As Integer: s = Sign(decimalDeg)
decimalDeg = Abs(decimalDeg)
Dim degrees As Integer: degrees = Fix(decimalDeg)
Dim minutes As Double: minutes = Round((decimalDeg - degrees) * 60, 3)

If s = -1 Then
ConvertDegrees = Format$(degrees, "#0") & "° " & Format$(minutes, "00.000") & "' S"
ElseIf s = 1 Then
ConvertDegrees = Format$(degrees, "##0") & "° " & Format$(minutes, "00.000") & "' E"
End If
End Function

[Paul_Hossler]

So you're good?

[Aussiebear]

Sometimes....

[Paul_Hossler]

To keep things simple, maybe use 2 functions. You can include North and West in case contrary winds blow you off course


28396


Don't know the correct way to handle something like latitude = 91. Now it comes back as 1N, but maybe it should be 89N??

-91 comes back as 1S. Maybe it should be 89S??

(You always have such fun little projects :clap::clap: )



Option Explicit


Sub drv()
MsgBox Longitude(-180)
End Sub


Public Function Longitude(decimalDeg As Double) As String
Dim s As Integer
Dim degrees As Integer
Dim minutes As Double


s = Sgn(decimalDeg)

decimalDeg = Abs(decimalDeg)

degrees = Fix(decimalDeg)
minutes = (decimalDeg - degrees) * 60
If degrees > 180# Or degrees < -180# Then degrees = degrees Mod 180

Select Case s
Case 1
Longitude = Format$(degrees, "##0" & Chr$(176)) & Format$(minutes, " 00.000' \E") ' need to escape the 'E'
Case 0
Longitude = Format$(degrees, "##0" & Chr$(176)) & Format$(minutes, " 00.000'")
Case -1
Longitude = Format$(degrees, "##0" & Chr$(176)) & Format$(minutes, " 00.000' W")
End Select
End Function


Sub drv2()
MsgBox Latitude(-90)
End Sub




Public Function Latitude(decimalDeg As Double) As String
Dim s As Integer
Dim degrees As Integer
Dim minutes As Double


s = Sgn(decimalDeg)

decimalDeg = Abs(decimalDeg)

degrees = Fix(decimalDeg)
minutes = (decimalDeg - degrees) * 60
If degrees > 90# Or degrees < -90# Then degrees = degrees Mod 90

Select Case s
Case 1
Latitude = Format$(degrees, "#0" & Chr$(176)) & Format$(minutes, " 00.000' N")
Case 0
Latitude = Format$(degrees, "#0" & Chr$(176)) & Format$(minutes, " 00.000'")
Case -1
Latitude = Format$(degrees, "#0" & Chr$(176)) & Format$(minutes, " 00.000' S")
End Select
End Function

[Aussiebear]

To keep things simple, maybe use 2 functions. You can include North and West in case contrary winds blow you off course.

Don't know the correct way to handle something like latitude = 91. Now it comes back as 1N, but maybe it should be 89N??

-91 comes back as 1S. Maybe it should be 89S??

Thank you for the two functions. As to 91.... simply cant happen ( as any good Bear knows).


(You always have such fun little projects :clap::clap: )

Well they (GBRMPA representatives) said it couldn't be done.....

[snb]

In Formulae:

Latitude:


=TEXT(IF(ABS(TRUNC(A7))=90;90;MOD(ABS(TRUNC(A7));90));"0° ") &RIGHT(TEXT(ABS(A7)*60;"00,000' ");8)& IF(A7=0;"";IF(A7>0;"N";"S"))

Longitude


=TEXT(IF(ABS(TRUNC(B7))=180;180;MOD(ABS(TRUNC(B7));90));"0° ") &RIGHT(TEXT(MOD(ABS(B7);1)*60;"00,000' ");8)& IF(B7=0;"";IF(B7>0;"E";"W"))

[Paul_Hossler]

Thank you for the two functions. As to 91.... simply can't happen ( as any good Bear knows).

Well they (GBRMPA representatives) said it couldn't be done.....


1. Hopefully, but I was thinking for general purposes-ness about being at (e.g.) 70N, 160W and then going 30 more North. That's put you at 80N, 160E

2. I had to Google that - at first glance I thought it said GRANDPA (grandkids were here this weekend)

GBRMPA - Home
https://www.gbrmpa.gov.au
Great Barrier Reef Marine Park Authority.

[snb]

Latitude UDF:


Function F_snb(y)
F_snb = ""
If y <> "" Then F_snb = IIf(Abs(y) = 90, 90, Int(Abs(y)) Mod 90) & "° " & Format((Abs(y) - Int(Abs(y))) * 60, "00.000' ") & Switch(y > 0, "N", y < 0, "S")
End Function