June 10, 2008 2:03 PM Subscribe

Microsoft Excel VBA question: Converting decimal degrees to degrees-minutes-seconds (DMS), with rounding customization.

I'm pretty proficient with Excel but am a complete n00b when it comes to programming even Visual Basic. I needed to convert some decimal degrees into DMS format, and failing to find a function that did so, I went to Google and found this page. Surprisingly enough, both of those modules worked successfully, allowing me to access them through the Function menu. I was even able to figure out how to change the function name to something I find clearer.

My one gripe about the Decimal-to-DMS conversion is that it doesn't allow for any numbers after the decimal point in the Seconds part of the output. I wouldn't usually mind (I know how tiny a second is), but I'm using this for a mapping application where meters matter, and the rounding is throwing me off. Can the hive mind help me get decimal seconds?
posted by dondiego87 to Computers & Internet (5 answers total) 1 user marked this as a favorite

I'm pretty proficient with Excel but am a complete n00b when it comes to programming even Visual Basic. I needed to convert some decimal degrees into DMS format, and failing to find a function that did so, I went to Google and found this page. Surprisingly enough, both of those modules worked successfully, allowing me to access them through the Function menu. I was even able to figure out how to change the function name to something I find clearer.

My one gripe about the Decimal-to-DMS conversion is that it doesn't allow for any numbers after the decimal point in the Seconds part of the output. I wouldn't usually mind (I know how tiny a second is), but I'm using this for a mapping application where meters matter, and the rounding is throwing me off. Can the hive mind help me get decimal seconds?

Perfect! Thanks so much.

posted by dondiego87 at 5:04 PM on June 10, 2008

posted by dondiego87 at 5:04 PM on June 10, 2008

Hah... was their bug not mine. In fact, I'm amazed their function works at all. That '+' on the third last line should be a '&' as far as I can tell.

So, anyway, the correct answer for arbitrary precision (up to the limit of the data type) is:

(with changed lines in bold)

posted by pompomtom at 7:10 PM on June 10, 2008

So, anyway, the correct answer for arbitrary precision (up to the limit of the data type) is:

(with changed lines in bold)

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 Minuteseconds = ((minutes - Int(minutes)) * 60)'Returns the Result of degree conversion '(for example, 10.46 = 10~ 27 ' 36") Convert_Degree = " " & degrees & "° " & Int(minutes) & "' " _& seconds & "End With End Function

posted by pompomtom at 7:10 PM on June 10, 2008

erm.. that didn't quite work. the last double quotes should be the chr() code as in the example (or simply four double quotes in a row will achieve the same).

posted by pompomtom at 7:13 PM on June 10, 2008

posted by pompomtom at 7:13 PM on June 10, 2008

Oh, OK. When I made the change you suggested, the function worked for some old instances of it I had on the sheet, but when I tried to make new ones it didn't. Weird. Thanks again!

posted by dondiego87 at 2:10 PM on June 12, 2008

posted by dondiego87 at 2:10 PM on June 12, 2008

This thread is closed to new comments.

Seconds = Format(((Minutes - Int(Minutes)) * 60), "0")

to:

Seconds = Format(((Minutes - Int(Minutes)) * 60), "0.

00000")(my emph...)

That'll give you five decimal places of seconds.

posted by pompomtom at 4:07 PM on June 10, 2008