Return a string with superscript characters in VBA

Question

i have a macro in Excel which converts a decimal degree value (right ascension) into astronomical hour angle with hours (h), minutes (m) and seconds (s). Is there any way, to return the string with superscript abbreviations h,m,s?

This is my Excel-macro:

Function Convert_Hours(Decimal_Deg) As Variant
    With Application
        hours_dec = Decimal_Deg / 360 * 24
        hours = Int(hours_dec)

        minutes_dec = hours_dec - hours
        minutes = Int(minutes_dec)

        seconds = minutes_dec - minutes

        Convert_Hours = " " & hours & "h " & minutes & "m " & Round(seconds, 2) & "s "
    End With
End Function

So for example in cell A1 i write 176.7854 and in cell B1 =Convert_Hours(A1). That writes 11h 0m 0.79s into cell B1. But what i want is: 11^h 0^m 0.79^s

I do not(!) want to reference some selected cell with VBA and then apply something like c.Font.Superscript = True, which is the standard answer when googling for superscript string in VBA. My function Convert_Hours() should return a formatted string on its own.

Thank you in advance!

Answer 1

Simple suggestion that ALWAYS worked for me: (1) Go to the programming tab and start recording a MACRO; (2) Select a cell with a previously added string; (3) Click on the formula field (upper part of screen), select a section of the string and mark it as Superscript; (4) Stop recording and look at the generated code. That will show you how this is done.

Here is an example I just generated:

    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
        ActiveCell.FormulaR1C1 = "asd sad ads asd asd asd "
        With ActiveCell.Characters(Start:=1, Length:=12).Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        With ActiveCell.Characters(Start:=13, Length:=7).Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 11
            .Strikethrough = False
            .Superscript = True
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        With ActiveCell.Characters(Start:=20, Length:=5).Font
            .Name = "Arial"
            .FontStyle = "Regular"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        Range("D4").Select
    End Sub

Hope this helps you.

Answer 2

Since a string only can contain single characters without formatting, the only way to achieve this without formatting is to find Unicode characters which represents the superscript small letters.

There is not a complete Unicode block for superscript Latin letters. So we need to pick up those from different blocks. An overview we can find in Latin_script_in_Unicode.

The ʰ and ˢ we can find in Spacing Modifier Letters. The ᵐ we can find in Phonetic Extensions.

If we have found the characters, we must know how to concatenate them into the string in VBA. For this the ChrW function can be used. It needs the decimal code of the character.

So

...
Convert_Hours = " " & hours & ChrW(688) & " " & minutes & ChrW(7504) & " " & Round(seconds, 2) & ChrW(738)
...

will nearly get what you want. But the sizes of the superscript letters will be different because they are from different blocks of Unicode. So I would prefer using default letters in the string and formatting them superscript later. Of course this cannot be done within a User Defined Function (UDF).

---

According to the comment from @arcadeprecinct

...
Convert_Hours = " " & hours & ChrW(&H2B0) & " " & minutes & ChrW(&H1D50) & " " & Round(seconds, 2) & ChrW(&H2E2)
...

will also work using hexadecimal.