Superscript in a Function

[Nate_]

Hi,
So I wrote/copied a function to convert decimal hours to hours minutes and second and I get my result as a string looking like this: "10h30m30s". How do I superscript the letters in the function?
I managed to do it in a normal sub macro with the code: [ActiveCell.Characters(Start:=4, Length:=1).Font.Superscript = True] but in my function it doesn't work.
Here is my Function:


Function Convert_Into_HMS(Decimal_Deg) As String




Dim hours
Dim minutes
Dim seconds


If Decimal_Deg >= 0 Then
With Application
hours = Int(Decimal_Deg)
minutes = (Decimal_Deg - hours) * 60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
ElseIf Decimal_Deg > -1 Then
With Application
hours = Left(Decimal_Deg, 2)
minutes = (Decimal_Deg + hours) * -60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
Else
With Application
hours = Left(Decimal_Deg, 3)
minutes = (Decimal_Deg - Int(hours)) * -60
seconds = Format(((minutes - Int(minutes)) * 60), "00.0")
Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s"
End With
End If


Dim Colin As String
Colin = Convert_Into_HMS


Dim this As Integer
this = InStr(1, Colin, "h")


Dim should As Integer
should = InStr(1, Colin, "m")


Dim work As Integer
work = InStr(1, Colin, "s")


ActiveCell.Characters(Start:=this, Length:=1).Font.Superscript = True
ActiveCell.Characters(Start:=should, Length:=1).Font.Superscript = True
ActiveCell.Characters(Start:=work, Length:=1).Font.Superscript = True






End Function



As you can see I'm trying to superscript at the end of the function, is there a way to do it right away in the code that defines the function? ( Convert_Into_HMS = " " & hours & "h" & Format(Int(minutes), "00") & "m" & seconds & "s")

And also I Apologize if my coding is a bit awkward, this is my first encounter with VBA.
Thanks and Cheers.

[Paul_Hossler]

Good first effort :clap::clap:


User defined functions cannot change the formatting of a cell, only plug in a Value. So no subscripting
Some fonts do have a subscript h character, but I wouldn't count on it

All variables should be Dim-ed and you should use Option Explicit at the top of the modules (see online help).
The above has been a BIG discussion item here with strongly held opinions on both sides. I like my way

The most common DMS uses DDD°MM'SS.SSS", so that's what I did

I think I got the negative input logic correct

There are many links in Google for this, but I followed your approach




Option Explicit


Function Convert_Into_HMS(Decimal_Deg As Double) As String
Dim Degrees As Long, Minutes As Long, Seconds As Double

If Decimal_Deg >= 0# Then
Seconds = 3600# * Decimal_Deg
Degrees = Int(Seconds / 3600#)
Minutes = Int((Seconds - 3600# * Degrees) / 60#)
Seconds = Seconds - 3600# * Degrees - 60# * Minutes
Convert_Into_HMS = Format(Degrees, "##0") & ChrW(176) & Format(Minutes, "00") & "'" & Format(Seconds, "00.000") & """"
Else
Decimal_Deg = Decimal_Deg + 360#
Seconds = 3600# * Decimal_Deg
Degrees = Int(Seconds / 3600#)
Minutes = Int((Seconds - 3600# * Degrees) / 60#)
Seconds = Seconds - 3600# * Degrees - 60# * Minutes
Convert_Into_HMS = Format(Degrees, "##0") & ChrW(176) & Format(Minutes, "00") & "'" & Format(Seconds, "00.000") & """"
End If
End Function

[Nate_]

I like what you did by always keeping the degrees positive, I didn't have that in my DMS converter (I wrote one as well probably just as convoluted as my HMS converter). Is it possible to write a macro that I can just let run over the cell to do the superscription? so basically after I ran the function just run a macro over the cell? In this case I am actually working with time and not angles so I like it in the "##h##m##s" format.

[Paul_Hossler]

You know, I have no idea why I was thinking degrees-minutes-seconds. Must have been another project

You can try something like this.

The superscripting doesn't work on formulas (as far as I can see) so I put the formatted HMS in the next column as a fixed string so that I could superscript

28376



Select the cells and run the macro to see




Option Explicit


Sub FormatTime()
Dim r As Range, r1 As Range
Dim i As Long
Dim s As String

If Not TypeOf Selection Is Range Then Exit Sub


For Each r In Selection.Cells
With r
If Len(.Value) > 0 Then
If IsNumeric(.Value) Then
Set r1 = .Offset(0, 1)
r1.Font.Superscript = False
r1.Value = Format(.Value, "h""h""mm""m""ss""s""")
i = InStr(r1.Value, "h")
r1.Characters(Start:=i, Length:=1).Font.Superscript = True
i = InStr(r1.Value, "m")
r1.Characters(Start:=i, Length:=1).Font.Superscript = True
i = InStr(r1.Value, "s")
r1.Characters(Start:=i, Length:=1).Font.Superscript = True
End If
End If
End With
Next
End Sub



There's probably more elegant ways to do this, so you might get some other / better ideas

[Nate_]

This helped a lot, thank you. I will try to take it from here and tailor it to my needs. Thanks for your help Paul.