 # NavList:

## A Community Devoted to the Preservation and Practice of Celestial Navigation and Other Methods of Traditional Wayfinding

Message:αβγ
Message:abc
 Add Images & Files Posting Code: Name: Email:
Excel functions for DMS - decimal conversion - second try
From: Michael Dorl
Date: 2004 Nov 19, 10:47 -0600

```George pointed out some problems with my previous functions for dms/decimal
conversion functions for Excel so here's another try.  I believe the -0 12
34 problem has been corrected and the DECtoDMS function now does the right
things with negative angles. Also I added some code to prevent nonsense
like 1 60 60 from arising.  They passed my cursory testing so let's see
what George can find now :-)

from excel TOOLS - MACROS - VISUAL BASIC

once Visual basic starts, you may have to add a Module if you don't
INSERT MODULE

2) Set the format of any cell containing a dms angle to text (FORMAT CELLS)
otherwise you will have trouble entering angles starting with a - sign.
Probably you want to set the whole column or row.

3) You can add leading spaces to dms angle BUT only one space may separate
the components; no trailing space are allowed.

4) a dms angle must have exactly three components.  If not, the function
will return an error.

So here are the functions

Function DMStoDEC(s As String) As Double

Dim parts() As String

Dim Degrees As Integer
Dim Minutes As Integer
Dim Seconds As Double
Dim sign    As Integer
Dim zzzz    As String

s = LTrim(s)

parts = Split(s, , , vbBinaryCompare)

If UBound(parts) <> 2 Then  ' 2 because array starts at 0
Error (9999)
End If

Degrees = parts(0)
If Degrees < 0 Then
sign = -1
Degrees = -Degrees
Else
If Left(s, 1) = "-" Then
sign = -1
Else
sign = 1
End If
End If

Minutes = parts(1)
Seconds = parts(2)

DMStoDEC = sign * (Degrees + Minutes / 60# + Seconds / 3600#)

End Function

Function DECtoDMS(A As Double) As String

Dim Degrees As Integer
Dim Minutes As Integer
Dim Seconds As Double
Dim sign As String

If A < 0 Then
A = -A
sign = "-"
Else
sign = ""
End If

Degrees = Int(A)
A = 60 * (A - Degrees)
Minutes = Int(A)
Seconds = 60# * (A - Minutes)
Seconds = Round(Seconds, 3)
Seconds = Int(Seconds * 1000#)
Seconds = Seconds / 1000#

If Seconds >= 60 Then
Seconds = Seconds - 60
Minutes = Minutes + 1
If Minutes >= 60 Then
Minutes = Minutes - 60
Degrees = Degrees + 1
End If
End If

DECtoDMS = sign & Degrees & " " & Minutes & " " & Seconds

End Function

``` Browse Files

Drop Files ### NavList ### What is NavList? ### Join NavList

 Name: (please, no nicknames or handles) Email:
 Do you want to receive all group messages by email? Yes No
You can also join by posting. Your first on-topic post automatically makes you a member. ### Posting Code

Enter the email address associated with your NavList messages. Your posting code will be emailed to you immediately.
 Email: ### Email Settings

 Posting Code: ### Custom Index

 Subject: Author: Start date: (yyyymm dd) End date: (yyyymm dd)