Formatting for Degrees Minutes and Seconds

[geofferrington]

Hi there folks.

I am trying to format Degrees expressed as a decimal amount as
Degrees Minutes Seconds

The [h]:mm:ss format given in the custom section of the "Format Cells"
dialogue box does only a tolerable job and I need to divide the Decimal
Degrees by 24 first, in order to achieve that.

So... in order to achieve my goal I have decided to write my own UDF.
Although this does not leave me with an underlying numeric value, (and
that is not a real bad thing) I cannot discover a way to insert a °
(°)character into the output string.

The function code is as follows:

Function formDMS(DecDeg)
If DecDeg < 0 Then DecDeg = DecDeg * (-1)
d = Int(DecDeg)
m = (DecDeg - d) * 60
im = Int(m)
s = Round((m - Int(m)) * 60, 0)
formDMS = d & ":" & im & "'" & s & """"
End Function
---------------^------------------------
it is the character above the "^" that needs to be modified.

any ideas... would be appreciated

Regards
Geoff

 

[Nick Hebb]

formDMS = d & Chr(&HB0) & " " & im & "' " & s & """"

You can get the unicode hex number (B0 in this case) for a symbol from
the Insert > Symbol dialog box. Take the number and prefix it with &H
to put it in VB Hex form. then convert the number to a character with
the Chr() function.

Or, just that little degree symbol that you just inserted in your post
above, copy it, and paste it into your VBA editor. Or just copy the
following line:

formDMS = d & "° " & im & "' " & s & """"

I just tried it. It works too.

 

[geofferrington]

Sure does Nick.... Thanks a heap.