Decimal seconds, please.

June 10, 2008 2:03 PM

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)

For some reason I keep getting #VALUE errors trying to make this to arbitrary precision... but for your purposes, if you know how small you need to get, you need to change the line:

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

Response by poster: Perfect! Thanks so much.
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)

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 = ((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

Response by poster: 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