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 :wot

[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 (https://github.com/tdjastrzebski/Vincenty-Excel/raw/master/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 (https://github.com/tdjastrzebski/Vincenty-Excel/raw/master/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 (https://github.com/tdjastrzebski/Vincenty-Excel/raw/master/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 :thumb

[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 (http://dailydoseofexcel.com/archives/2010/11/29/decimal-degrees-to-degrees-mins-secs/)

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

[Aussiebear]

@Paulked, I notice the use of Chrw(176), Chrw(39), Chrw(34), Chrw(&H2032) & Chrw(&H2033). I've not seen that before and was wondering if it should be Chr$ rather than Chrw?

[paulked]

Hey ho, it seemed to be a good article, shame it didn't produce.

[Paul_Hossler]

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.

1. Which Paul?

2. Probably me, since I did not use a minus sign.

But I think the conversion is correct

26376


Did you have the second parameter = 1 since I changed the call format in my sub?


' Converts decimal latitude, longitude or azimuth value to degrees/minutes/seconds string format' ConvType = -1 Longitude, = 0 Azumuth, = 1 Latitude






However, if you have DMS with the trailing "S" do you want the leading "-" also?



The original sub used trailing N/E/S/W so I just followed their approach


Having both seems redundant and possibly ambiguous (since -10 S could be interpreted as +10 N), but if you want the "-" and/or no trailing NESW, it's easy enough to change my function

[Paul_Hossler]

@Paulked, I notice the use of Chrw(176), Chrw(39), Chrw(34), Chrw(&H2032) & Chrw(&H2033). I've not seen that before and was wondering if it should be Chr$ rather than Chrw?

ChrW is for Unicode (2 byte) characters, but since you're on a Mac, I don't think you'd want ChrW


https://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/chr-function




Chr(charcode)
ChrB(charcode)
ChrW(charcode)
The required charcode argument (https://docs.microsoft.com/en-us/office/vba/Language/glossary/vbe-glossary#argument) is a Long (https://docs.microsoft.com/en-us/office/vba/Language/glossary/vbe-glossary) that identifies a character.
Remarks

Numbers from 0–31 are the same as standard, nonprintable ASCII (https://docs.microsoft.com/en-us/office/vba/Language/glossary/vbe-glossary#ascii-character-set) codes. For example, Chr(10) returns a linefeed character. The normal range for charcode is 0–255. However, on DBCS (https://docs.microsoft.com/en-us/office/vba/Language/glossary/vbe-glossary#dbcs) systems, the actual range for charcode is -32768–65535.

Note
The ChrB function is used with byte data contained in a String. Instead of returning a character, which may be one or two bytes, ChrB always returns a single byte.
The ChrW function returns a String containing the Unicode (https://docs.microsoft.com/en-us/office/vba/Language/glossary/vbe-glossary#unicode) character except on platforms where Unicode is not supported, in which case, the behavior is identical to the Chr function.

Note
Visual Basic for the Macintosh does not support Unicode strings. Therefore, ChrW(n) cannot return all Unicode characters for n values in the range of 128–65,535, as it does in the Windows environment. Instead, ChrW(n) attempts a "best guess" for Unicode values n greater than 127. Therefore, you should not use ChrW in the Macintosh environment.

[paulked]

As I said, I found that on the web, I'll go with Paul H said above :yes

[Paul_Hossler]

As I said, I found that on the web, I'll go with Paul H said above :yes


Not me. It was Microsoft what dun it

[paulked]

:rofl:

[Aussiebear]

Thank you to both of you

[Aussiebear]

End result here.... ditched both Mac's ( for calculating this file) and purchased a Dell laptop. It runs the Vincent functions brilliantly. Thanks to all you contributed.

[paulked]

Expensive fix! I don't have a Mac so I can't compare the two, but I would have thought that any computer would calculate a formula to get the same result... isn't that what they are for?

Anyway, good to know you're sorted :thumb:thumb