Excel VBA: Is there a way to iterate through characters in a specific range?

Question

Is there a way to iterate through characters in a specific range, like:

...
For each char1 in range1.Characters
    [It does not work the fake code above, because Characters is not a collection]

I mean: is there such a collection?

Answer 1

If you want to loop to iterate through a single-cell Range object containing text, returning single-Characters objects, this is one way:

Sub IterateCharactersObject()
    Dim ch As Characters, n As Long
    With Sheets("Sheet1").Range("A1")
        For n = 1 To .Characters.Count
            Set ch = .Characters(n, 1)
            'print position#, character, font name & size to immediate window
            Debug.Print "#" & n & "=" & ch.Text, ch.Font.Name, ch.Font.Size
        Next n
    End With
End Sub

The Characters method is necessary only when you need to change some of an object's text without affecting the rest (you cannot use the Characters method to format a portion of the text if the object doesn't support rich text). To change all the text at the same time, you can usually apply the appropriate method or property directly to the object.

...or if you didn't actually need to return a Characters object, it may be better to do it using the MID and LEN functions:

Sub IterateCharacters_String()
    Dim n As Long
    With Sheets("Sheet1").Range("A1")
        For n = 1 To Len(.Value)
            'print position# and character to immediate window
            Debug.Print "#" & n & "=" & Mid(.Value, n, 1)
        Next n
    End With
End Sub

This could also be accomplished without VBA using the MID and LEN worksheet functions.

---

More Information:

• MSDN : Characters Object (Excel)

• MSDN : Range.Characters Property (Excel)

• MSDN : Mid Function (VBA)

• MSDN : MID Function (Excel)