Change format of ConvertDegrees function

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.

Code:

---

' 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

Code:

---

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?

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


Attachment 28390

Code:

---

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

---

Quote:

---

Originally Posted by Paul_Hossler

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.

Quote:

---

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 -

Quote:

---

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

Quote:

---

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.)

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).

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.

Code:

---

' 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

---

So you're good?

Sometimes....

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


Attachment 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: )

Code:

---

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

---

Quote:

---

Originally Posted by Paul_Hossler

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).

Quote:

---

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

---

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

In Formulae:

Latitude:

PHP Code:

---

=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

PHP Code:

---

=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")) 


---

Quote:

---

Originally Posted by Aussiebear

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.

Latitude UDF:

Code:

---

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

---