CODEWITHSUNDEEP
excelvba
user147178
user147178

Reputation: 471

Subscripts (font) in excel vba

I'm trying to populate an array which is composed of greek letters followed by a subscript "1". I already have the greek letters part:

Dim variables(), variables_o
j = 0
For i = 1 To 25
    If i = 13 Or i = 15 Then
    Else
    j = j + 1
    ReDim Preserve variables(j)
    variables(j) = ChrW(944 + i)
    End If
Next

But I'm having trouble with the subscript part. I figure that if I could use the with ... end with feature then I could do it but I'm having trouble figuring out what objects the with ... end with can take. On this website they say:

With...End With Statement (Visual Basic)

The data type of objectExpression can be any class or structure type or even a Visual Basic elementary type such as Integer.

But I don't know what that means. If could do something like this:

dim one as string

one = "1"

with one font.subscript = true end with

Then I could figure out how to do what I want. But the with feature does not seem to act on strings. The problem I'm having is that most of the advice for fonts somewhere along the line use the cell method but I want to populate an array, so I'm having trouble. Again what I would ideally like to do is create some dimension which is simply a subscripted one and then alter my array as follows:

Dim variables(), variables_o
j = 0
For i = 1 To 25
    If i = 13 Or i = 15 Then
    Else
    j = j + 1
    ReDim Preserve variables(j)
    variables(j) = ChrW(944 + i) & subscript_one
    End If
Next

Upvotes: 1

Views: 1205

Answers (1)

Steve S
Steve S

Reputation: 431

To my knowledge, there are no out-of-the-box methods or properties to store the font.Subscript property of a character or series of characters within a string that also contains the characters.

You could use inline tags, like in HTML, to indicate where the subscript begins and ends. For example:

variables(j) = ChrW(944 + i) & "<sub>1</sub>"

Then, when you write out variable, you would parse the string, remove the tags and set the font.Subscript property accordingly.

However, if you're always appending a '1' to each Greek letter, I would just append it to the string, then set the font.Subscript property on the last character of the string when outputting it. For example:

variables(j) = ChrW(944 + i) & "1"

...

For j = 0 to Ubound(variables)
    With Worksheets("Sheet1").Cells(j + 1, 1) 
        .Value = variables(j) 
        .Characters(Len(variables(j)), 1).Font.Subscript = True 
    End With
Next j

If you're writing to something other than a cell in a worksheet, it has to support Rich-Text in order for the subscript to show, e.g. a Rich-Text enabled TextBox on a user form. You should be able to use the .Characters object on those controls in a similar manner.

See MSDN-Characters Object for more information.

Upvotes: 2

Related Questions