Welcome to the NavList Message Boards.

NavList:

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

Compose Your Message

Message:αβγ
Message:abc
Add Images & Files
    or...
       
    Reply
    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
       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
    
    
    

       
    Reply
    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)

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