在VBA函数中返回带有上标字符的字符串 [英] Return a string with superscript characters in VBA function

问题描述

我在Excel中有一个宏，该宏将十进制度值(右升)转换为带有小时(h)，分钟(m)和秒(s)的天文小时角.有什么方法可以返回带有上标缩写h，m，s的字符串?

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?

这是我的Excel宏:

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

例如，在单元格A1中，我编写 176.7854 ，在单元格B1中，我编写 = Convert_Hours(A1).它将 11h 0m 0.79s 写入单元格B1中.但是我想要的是:11 ^h 0 ^m 0.79 ^s

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

我不想(！)使用VBA引用某些选定的单元格，然后应用类似 c.Font.Superscript = True 之类的东西，这是在VBA中搜索上标字符串时的标准答案.我的函数 Convert_Hours()应该自行返回格式化的字符串.

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.

提前谢谢！

推荐答案

由于一个字符串只能包含单个字符而无需格式化，因此实现不格式化的唯一方法是找到代表上标小写字母的Unicode字符.

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.

对于上标拉丁字母没有完整的Unicode块.因此，我们需要从不同的块中提取那些.我们可以在 Latin_script_in_Unicode 中找到概述.

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.

如果找到了字符，我们必须知道如何将它们连接到 VBA 中的字符串中.为此，可以使用 ChrW 函数.它需要字符的十进制代码.

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.

所以

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

几乎可以得到您想要的.但是上标字母的大小将有所不同，因为它们来自Unicode的不同块.因此，我希望在字符串中使用默认字母，然后再将其设置为上标格式.当然，这不能在用户定义功能(UDF)中完成.

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).

根据@arcadeprecinct的评论

According to the comment from @arcadeprecinct

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

也可以使用十六进制.

这篇关于在VBA函数中返回带有上标字符的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！