Formatting issues

[Aussiebear]

The following code is formatting Lattitude data with 3 integers before the decimal place rather than 2 integers. Can it be amended? For example when converting -10.77538872 South it responds with -010° 46' 31.3994"
when I'm expecting -10 46' 31.3994".

' 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)
    End If
    
    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

[paulked]

Hey Mr Bear!

A few questions:

What do the 'Normalize' routines do?

Aren't you asking for this with Format$(degrees, "000") ? But if corrected to "00" (which seems to solve the problem) then the two lines would be the same.

Also, shouldn't s = Sign(decimalDeg) be s = Sgn(decimalDeg)?

Please forgive me if I'm being stupid, but I'm not really understanding this properly as I don't know anything about coordinates

[Aussiebear]

G'day Paulked,

This is a piece of code from within the Vincenty.xlsm file (Written by Thaddeus Vincenty) I downloaded from Github.com. It is meant to simply convert Decimal degrees to DMS format with a high degree of accuracy. However it appears to default all output to that which suits Longitude format rather than Latitude format.

Its as if it defaults to this line
ConvertDegrees = Format$(degrees, "000") & Chr$(176) & Format$(minutes, "00") & "'" & Format$(seconds, "00.0000") + Chr$(34)
My current thinking is that I need to test if the Initial string has a negative sign ( Southings will always be negative), then send it to the appropriate formatting process.

Lattitudes = ConvertDegrees = Format$(degrees, "00") & Chr$(176) & Format$(minutes, "00") & "'" & Format$(seconds, "00.0000") + Chr$(34)
Longitudes =ConvertDegrees = Format$(degrees, "000") & Chr$(176) & Format$(minutes, "00") & "'" & Format$(seconds, "00.0000") + Chr$(34)

Paul Hossler very kindly wrote some code over a year ago which converts Decimal degrees to DMS format. The Vincenty function matches the results but with greater precision. Once I can have confidence in the output I can then turn my mind to a distance and heading concept.

[paulked]

I've downloaded the demo workbook which wouldn't load without errors (haven't looked at these yet) but there is a whole load of code to run on Workbook_Open. I tried copying that from GitHub but it didn't work, then read the red bit!

How to use it?

• Vincenty functions can be simply added to any existing Excel workbook. Download Vincenty.bas file, in Excel hit [Alt+F11] to open Visual Basic editor. Next, in the browser panel right-click on VBA Project (your file name), select Import File and choose downloaded Vincenty.bas module. Then save as 'macro-enabled workbook" and you will be able to use added functions in your Excel formulas. In a cell just start typing: =Vincenty.. and you should see the list of added functions. Do NOT simply copy-paste Vincenty.bas file content into a new Excel module - file contains some extra lines not visible in VBA editor.

[paulked]

After re-booting it worked.

I think that they are formatted that way only out of preference so, if you prefer, you can do away with that 'if' block altogether:

    'If Not IsMissing(isLongitude) And Not CBool(isLongitude) Then
        ConvertDegrees = Format$(degrees, "0") & Chr$(176) & Format$(minutes, "0") & "'" & Format$(seconds, "00.0000") + Chr$(34)
    'Else
    '    ConvertDegrees = Format$(degrees, "000") & Chr$(176) & Format$(minutes, "00") & "'" & Format$(seconds, "00.0000") + Chr$(34)
    'End If

The Normalizex() and Sign() functions are included with the file on GitHub (attached)

[Aussiebear]

I also read the red bit which made me a little nervous about downloading any files. Initially I down loaded the .xls file which seemed harmless enough but then decided to try the .xlsm file as I thought about creating a form which would allow me to scroll through all the rows one by one as I enter the details into a Filemaker Pro database. On perusing the different functions within the module I decided to try the ConvertDegrees function to test for accuracy, but ended up with incorrect results hence my initial post. I may yet decide to download the .bas and install.

[paulked]

I haven't looked at its accuracy, but it didn't (hasn't) caused any malfunction with anything on my pc, fingers crossed!

Good luck

[Aussiebear]

Thank you Paulked

[Aussiebear]

Sorry but still looking for an answer here please?

[p45cal]

There is nothing to fear from the lines in the .bas file which don't appear in the VBE. They're there to give the module its correct name and make a nice interface when using the Function Arguments dialogue box when entering these functions in a spreadsheet formula.
paulked gave you a suggestion in msg#5, which you might tweak from:
ConvertDegrees = Format$(degrees, "0") & Chr$(176)…
to:
ConvertDegrees = Format$(degrees, "##0") & Chr$(176)
but is actually unnecessary!

Or have I missed something?

[snb]

What is the difference between

-010° 46' 31.3994"
and
-10 46' 31.3994"

To me it seems the first outcome is the correct one ?

[Aussiebear]

Latitudes only range from -90° to +90° whereas Longitudes range from -180° to +180°

[p45cal]

Sorry but still looking for an answer here please?

So are you sorted?

[Aussiebear]

Both Macs are now slowed down by this software, so I need to come up with another solution.

[Paul_Hossler]

For example when converting -10.77538872 South it responds with -010° 46' 31.3994" when I'm expecting -10 46' 31.3994".

What I'm seeing is that ConvertDegrees() takes a latitude input of -10.77538872 and converts it to 010° 46' 31.3994"S

i.e. no 'South' on the input but a positive DMS with an 'S' for southern latitude

I'm guessing that you want

Latitudes

-10.77538872 to convert to 10°46'31.3994"S

+10.77538872 to convert to 10°46'31.3994"N



Longitudes

-110.77538872 to convert to 10°46'31.3994"W

+110.77538872 to convert to 10°46'31.3994"E


The original logic to select lat or long was a little convoluted so I changed it. The rest is basically the same

Sub test()
    MsgBox ConvertDegrees(10.77538872, -1)  '   E Longitude
    MsgBox ConvertDegrees(-10.77538872, -1) '   W Longitude


    MsgBox ConvertDegrees(179.77538872, -1) '  E Longitude
    MsgBox ConvertDegrees(-179.77538872, -1) '  W Longitude




    MsgBox ConvertDegrees(10.77538872, 1)   '   N latitude
    MsgBox ConvertDegrees(-10.77538872, 1)  '   S latitude


    MsgBox ConvertDegrees(89.77538872, 1)   '   N latitude
    MsgBox ConvertDegrees(-89.77538872, 1)  '   S latitude


    MsgBox ConvertDegrees(45, 0)            '   azumuth
    MsgBox ConvertDegrees(-45, 0)


End Sub


' Converts decimal latitude, longitude or azimuth value to degrees/minutes/seconds string format
'   ConvType          = -1 Longitude,            = 0 Azumuth,           = 1 Latitude
Public Function ConvertDegrees(ByVal decimalDeg As Double, Optional ConvType As Long = -1) As String
    
    Select Case ConvType
        Case -1 '   longitude
            decimalDeg = NormalizeLon(decimalDeg)
        Case 1  '   Latitude
            decimalDeg = NormalizeLat(decimalDeg)
        Case 0  '   azimuth
            decimalDeg = NormalizeAzimuth(decimalDeg, True)     '   0 - 360
    End Select
    
    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
            
    Select Case ConvType
        Case -1
            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
        
        Case 1
            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
        
        Case 0
            ConvertDegrees = Format$(degrees, "##0") & Chr$(176) & Format$(minutes, "00") & "'" & Format$(seconds, "00.0000") + Chr$(34)
            If s = -1 Then ConvertDegrees = "-" + ConvertDegrees
    End Select
            
End Function

[paulked]

Can you strip the formulae out of the code?

Alternatively, having a quick trawl I found this:

Function DD2DMS(Degrees As Variant, Optional Lat As Boolean = True, Optional Ascii As Boolean = True) As String
   
   Dim ArcMins As Variant
   Dim ArcSecs As Variant
   Dim NSEW    As String * 2
   Dim D_mark As String * 1, M_mark As String * 1, S_mark As String * 1
   If Lat Then
      NSEW = IIf(Degrees < 0, " S", " N")
   Else
      NSEW = IIf(Degrees < 0, " W", " E")
   End If
   
   D_mark = ChrW(176)
   If Ascii Then
      M_mark = ChrW(39): S_mark = ChrW(34)
   Else
      M_mark = ChrW(&H2032): S_mark = ChrW(&H2033)
   End If
   
   Degrees = Abs(Degrees)
   ArcMins = 60 * (Degrees - Int(Degrees))
   ArcSecs = 60 * (ArcMins - Int(ArcMins))
   Degrees = Int(Degrees) & D_mark & Chr(32)
   ArcMins = Format(Int(ArcMins), "00") & M_mark & Chr(32)
   ArcSecs = Format(ArcSecs, "00") & S_mark & NSEW
   
   DD2DMS = Degrees & ArcMins & ArcSecs
   
End Function

It may help?

[Aussiebear]

I have for the moment started to recompile my workbooks, and Filemaker Pro layout. Please forgive me if I'm preoccupied with the basics before coming back to this. Hopefully only a day away.... fingers crossed

[paulked]

Can you strip the formulae out of the code?

Alternatively, having a quick trawl I found this:

Function DD2DMS(Degrees As Variant, Optional Lat As Boolean = True, Optional Ascii As Boolean = True) As String
   
   Dim ArcMins As Variant
   Dim ArcSecs As Variant
   Dim NSEW    As String * 2
   Dim D_mark As String * 1, M_mark As String * 1, S_mark As String * 1
   If Lat Then
      NSEW = IIf(Degrees < 0, " S", " N")
   Else
      NSEW = IIf(Degrees < 0, " W", " E")
   End If
   
   D_mark = ChrW(176)
   If Ascii Then
      M_mark = ChrW(39): S_mark = ChrW(34)
   Else
      M_mark = ChrW(&H2032): S_mark = ChrW(&H2033)
   End If
   
   Degrees = Abs(Degrees)
   ArcMins = 60 * (Degrees - Int(Degrees))
   ArcSecs = 60 * (ArcMins - Int(ArcMins))
   Degrees = Int(Degrees) & D_mark & Chr(32)
   ArcMins = Format(Int(ArcMins), "00") & M_mark & Chr(32)
   ArcSecs = Format(ArcSecs, "00") & S_mark & NSEW
   
   DD2DMS = Degrees & ArcMins & ArcSecs
   
End Function

It may help?

Sorry, forgot to put the URL which is here

[Paul_Hossler]

@AussieBear -- The ConvertDegrees() function is not used in the macros, but only as a worksheet function

Attached is my text xlsm

[Aussiebear]

Sorry Paul but your file when used on my data converts South DMS to West and is missing the negative sign in front of the result.