Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2010
    Location
    USA
    Posts
    39
    Thanks
    8
    Thanked 2 Times in 1 Post
    I found this user-defined formula to convert latitude/longitude from decimal form to degrees (from Microsoft Support):

    Function Convert_Degree(Decimal_Deg) As Variant
    With Application
    'Set degree to Integer of Argument Passed
    Degrees = Int(Decimal_Deg)
    'Set minutes to 60 times the number to the right
    'of the decimal for the variable Decimal_Deg
    Minutes = (Decimal_Deg - Degrees) * 60
    'Set seconds to 60 times the number to the right of the
    'decimal for the variable Minute
    Seconds = Format(((Minutes - Int(Minutes)) * 60), "0")
    'Returns the Result of degree conversion
    '(for example, 10.46 = 10~ 27 ' 36")
    Convert_Degree = " " & Degrees & " " & Int(Minutes) & "' " _
    & Seconds + Chr(34)
    End With
    End Function


    It works well, except I need more precision in the conversion. It only returns the seconds in integers (e.g., 57" instead of 56.7148"). Can anyone tell me how to modify the formula to get this result, or another method?

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Change this line...
    Seconds = Format(((Minutes - Int(Minutes)) * 60), "0")
    To...
    Seconds = Format(((Minutes - Int(Minutes)) * 60), "0.0###")
    '--

    Going a little further, I did some cleanup on the code...
    Function Convert_Degree(ByRef Decimal_Deg As Variant) As Variant
    Dim Degrees As Double
    Dim Minutes As Double
    Dim Seconds As Double

    If TypeName(Decimal_Deg) <> "Double" Then
    Convert_Degree = "Number required"
    Exit Function
    End If

    'Set degree to Integer of Argument Passed
    Degrees = Int(Decimal_Deg)
    'Set minutes to 60 times the number to the right
    'of the decimal for the variable Decimal_Deg
    Minutes = (Decimal_Deg - Degrees) * 60
    'Set seconds to 60 times the number to the right of the
    'decimal for the variable Minute
    Seconds = Format(((Minutes - Int(Minutes)) * 60), "0.0###")
    'Returns the Result of degree conversion
    '(for example, 10.46 = 10~ 27 ' 36")
    Convert_Degree = " " & Degrees & " " & Int(Minutes) & "' " _
    & Seconds & Chr(34)
    End Function
    '--
    Jim Cone
    Portland, Oregon USA
    Extras for Excel add-in

  3. #3
    Lounger
    Join Date
    Jan 2010
    Location
    USA
    Posts
    39
    Thanks
    8
    Thanked 2 Times in 1 Post
    Thank you very much for your fix... and the improvements!

  4. #4
    Lounger
    Join Date
    Jan 2010
    Location
    USA
    Posts
    39
    Thanks
    8
    Thanked 2 Times in 1 Post
    Jim,

    When I use your improved code, I can't get past the "TypeName" check, i.e., the result stays as "Number required." Do my input cells have to be formatted some special way to meet the "Double" check?
    (Sorry, I'm not too good at troubleshooting code.)

  5. #5
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    If you are calling the function from a worksheet cell (instead of calling it using code) then
    Replace...
    If TypeName(Decimal_Deg) <> "Double" Then
    Convert_Degree = "Number required"
    Exit Function
    End If

    With...
    If Not IsNumeric(Decimal_Deg) Then
    Convert_Degree = "Number Required"
    Exit Function
    End If

    -or-
    Just remove those 4 lines.
    That code segment is useful only if someone not familiar with your workbook is using it.
    '--
    Jim Cone
    Portland, Oregon USA
    Special Sort add-in review

  6. #6
    Lounger
    Join Date
    Jan 2010
    Location
    USA
    Posts
    39
    Thanks
    8
    Thanked 2 Times in 1 Post
    Got it. Thanks again.

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Palo Alto, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jim - that's great, but I encourage you to think about what happens when you apply that algorithm to a negative value. After all, by most conventions, latitudes in the southern and longitudes in the western hemispheres are negative. I'm not conversant with visual basic so I won't offer a solution, but the usual approach is take note if the decimal-degree value is negative, take the absolute value of the decimal-degree value, do all the math with that, and then make the whole degree part of the answer negative if appropriate.

  8. #8
    New Lounger
    Join Date
    May 2010
    Location
    Burlington, MA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Earlier in this string the need for precision in the seconds reading was mentioned. It should be pointed out that having precision in latitude and longitude numbers doesn't have much meaning unless you know what model of the earth - known as geoid - is being used. I believe you can potentially be off by hundreds of meters using precise lat/long values that are applied to the wrong geoid. The U.S. Army Corps of Engineers provides a program called Corpscon at http://crunch.tec.army.mil/software/.../corpscon.html that goes into conversion between many of the different reference systems. While not addressing the programming issues in this string, using Corpscon you can at least get a good feeling for the issues involved with any lat/long readings.

  9. #9
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Graig,
    Thanks for pointing that out.
    That wasn't my algorithm and my expertise (if any) lies elsewhere.

    The "Int" functions in the code could be changed to "Fix" functions and that would handle the positive/negative issue.
    Int rounds negative numbers down while Fix truncates them.
    The displayed answer, however, would have multiple minus signs.
    Following your suggestion about using the absolute value, I've modified the code using that approach...

    '--
    Function Convert_Degree(ByRef Decimal_Deg As Variant) As Variant
    Dim Degrees As Double
    Dim Minutes As Double
    Dim Seconds As Double
    Dim strSign As String

    If Not IsNumeric(Decimal_Deg) Then
    Convert_Degree = "Number Required"
    Exit Function
    Else
    If Sgn(Decimal_Deg) > -1 Then
    strSign = " "
    Else
    strSign = "-"
    End If
    Decimal_Deg = Abs(Decimal_Deg)
    End If

    'Set degree to Integer of Argument Passed
    Degrees = Int(Decimal_Deg)
    'Set minutes to 60 times the number to the right
    'of the decimal for the variable Decimal_Deg
    Minutes = (Decimal_Deg - Degrees) * 60
    'Set seconds to 60 times the number to the right of the
    'decimal for the variable Minute
    Seconds = Format$(((Minutes - Int(Minutes)) * 60), "0.0###")
    'Returns the Result of degree conversion
    '(for example, 10.46 = 10~ 27 ' 36")
    Convert_Degree = strSign & Degrees & " " & Int(Minutes) & "' " _
    & Seconds & Chr$(34)
    End Function
    '--

    Jim Cone
    Portland, Oregon USA
    Primitive Software Files

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    Palo Alto, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jim - Back in the days when CPU cycles and memory were precious and doing a little extra arithmetic and creating an extra double variable were expensive, I'd agree with your solution. But nowadays, those resources aren't so critical and simplifying the code can take precedence. So I offer up this modification:

    '---
    Function Convert_Degree(ByRef Decimal_Deg As Variant) As Variant
    Dim Abs_Decimal_Deg As Double
    Dim Degrees As Double
    Dim Minutes As Double
    Dim Seconds As Double
    Dim strSign As String

    If Not IsNumeric(Decimal_Deg) Then
    Convert_Degree = "Number Required"
    Exit Function
    Else
    ' Remove any minus sign, we'll add it back later if needed
    Abs_Decimal_Deg = Abs(Decimal_Deg)
    End If

    'Do all the arithmetic with the absolute value
    'Set degree to Integer of Argument Passed
    Degrees = Int(Abs_Decimal_Deg)
    'Set minutes to 60 times the number to the right
    'of the decimal for the variable Decimal_Deg
    Minutes = (Abs_Decimal_Deg - Degrees) * 60
    'Set seconds to 60 times the number to the right of the
    'decimal for the variable Minute
    Seconds = Format$(((Minutes - Int(Minutes)) * 60), "0.0###")
    ' Put the minus back on just the degree value if original was negative
    If Decimal_Deg < 0 Then
    Degrees = -1# * Degrees
    End If
    'Returns the Result of degree conversion
    '(for example, 10.46 = 10 27' 36")
    Convert_Degree = Degrees & " " & Int(Minutes) & "' " _
    & Seconds & Chr$(34)
    End Function
    '---

    Notes: As I've said, I know little about VB, though I have been playing around with this code in Excell2003. I'm not sure about two changes above:

    On the line where I see if the incoming value is negative, I removed the SGN function call. Seems to me that testing the value itself is more straight forward but maybe there's something about VB that makes the use of SGN important.

    On the line where I multiply the whole degree value by negative one, I entered "-1.0" and the editor changed it to "-1#". I don't know what VB is doing there but trust someone will recommend a change if warranted.

    Graig

  11. #11
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    The Sgn function is used so that results will align left. A positive result will have a leading space.
    (note: the Str function provides a leading space but only works with periods as decimal points)

    The "#" is a type declaration character indicating the number is a double.
    '--
    Jim Cone
    Portland, Oregon USA
    XL Companion add-in

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •