NavList:

   

Reply

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