Consulting

Results 1 to 12 of 12

Thread: Change format of ConvertDegrees function

  1. #1
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location

    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.
    ' 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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

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

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


    Capture.JPG



    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by Paul_Hossler View Post

    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.)
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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).
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    So you're good?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Sometimes....
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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 )

    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
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Paul_Hossler; 05-02-2021 at 06:51 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by Paul_Hossler View Post
    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 )
    Well they (GBRMPA representatives) said it couldn't be done.....
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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")) 
    Last edited by snb; 05-04-2021 at 09:03 AM.

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by Aussiebear View Post
    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.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •