Welcome to the NavList Message Boards.


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

Compose Your Message

Add Images & Files
    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 :-)
    Some comments first....
    1) To add the functions
       from excel TOOLS - MACROS - VISUAL BASIC
       once Visual basic starts, you may have to add a Module if you don't
    already have one in your sheet
    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
      If Left(s, 1) = "-" Then
        sign = -1
       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 = "-"
      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


    What is NavList?

    Join NavList

    (please, no nicknames or handles)
    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 Settings

    Posting Code:

    Custom Index

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

    Visit this site
    Visit this site
    Visit this site
    Visit this site
    Visit this site
    Visit this site