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


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

Capture.JPG



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.