Results 1 to 11 of 11

20100620, 17:31 #1
 Join Date
 Jan 2010
 Location
 USA
 Posts
 39
 Thanks
 8
 Thanked 2 Times in 1 Post
I found this userdefined 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?

20100620, 18:57 #2
 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 addin

20100620, 19:36 #3
 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!

20100620, 20:17 #4
 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.)

20100620, 22:49 #5
 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 addin review

20100621, 20:25 #6
 Join Date
 Jan 2010
 Location
 USA
 Posts
 39
 Thanks
 8
 Thanked 2 Times in 1 Post
Got it. Thanks again.

20100624, 09:03 #7
 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 decimaldegree value is negative, take the absolute value of the decimaldegree value, do all the math with that, and then make the whole degree part of the answer negative if appropriate.

20100624, 10:28 #8
 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.

20100624, 10:48 #9
 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

20100624, 20:01 #10
 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

20100624, 20:43 #11
 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 addin