Convert Latitude/Long from decimal to degrees

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

Thank you very much for your fix… and the improvements!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Got it. Thanks again.

Reply | Quote

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.

Reply | Quote

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/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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote