Is a string an array in VBA that can be iterated through?

Question

Is a string an array in VBA?

For instance, can I iterate through it like I could in C/C++ ?

Do something like this:

char myArray[10]; 

for (int i = 0; i < length; i++)
{
    cout << myArray[i];
}

What would the equivalent be in in VBA? It is not behaving as I would expect. Never actually tried to manipulate many strings before in VBA! :)

Answer 1

It is not an array but what you want can be done using MID

Sub test()
    Dim strSentence As String
    Dim lngCount As Long    

    strSentence = "This is a string"

    For lngCount = 1 To Len(strSentence)
        Debug.Print Mid(strSentence, lngCount, 1)
    Next lngCount             
End Sub

See also Mat's Mug answer for an additional good point.

Edit

Actually there is a second way to iterate through a string, which is to convert the string to an array of single-digit strings and then iterate through them. To this end, we fist have to convert the original string to unicode format so that the Null character can be taken as delimiter. Here is a reproducible example:

Sub test2()
    Dim strSentence As String
    Dim lngCount As Long
    Dim strArray() As String

    strSentence = "This is a string"
    strSentence = StrConv(strSentence, vbUnicode)

    strArray = Split(strSentence, vbNullChar)

    For lngCount = 0 To UBound(strArray)
        Debug.Print strArray(lngCount)
    Next lngCount
End Sub

Out of curiosity, I compared the two approaches (using Mat's version of Mid, which is faster):

Sub test_sub()
    Dim strTest(1 To 5000) As String
    Dim lngStringIter As Long
    Dim lngChars As Long, dblTick As Double

    ' Generate  some long strings first
    For lngStringIter = 1 To 5000
        strTest(lngStringIter) = vbNullChar
        For lngChars = 1 To 10
            strTest(lngStringIter) = strTest(lngStringIter) & _
                Chr(Int((90 - 65 + 1) * Rnd + 65)) & strTest(lngStringIter)
        Next lngChars
    Next lngStringIter

    ' Lets see what happens..
    dblTick = CDbl(Now())
    For lngStringIter = 1 To 5000
      test strTest(lngStringIter)
    Next lngStringIter
    Debug.Print "Time Mid: ", CDbl(Now()) - dblTick

    dblTick = CDbl(Now())
    For lngStringIter = 1 To 5000
      test2 strTest(lngStringIter)
    Next lngStringIter
    Debug.Print "Time Split: ", CDbl(Now()) - dblTick
End Sub

Results:

Time Mid:     4.62962998426519e-05 
Time Split:    1.15740767796524e-05 

So it seems that the Split approach is somewhat faster.

---

A VBA string is implemented as a BSTR datatype. More info for this data type can be found here and here.

Answer 2

A string is an array of bytes, and you can iterate through it if you know your way around two-byte implementations of a string (or Unicode, which is usually 2 bytes per character, but can be more: and it's always going to be 2-Byte if your string originated in VBA).

When I say is, I mean it: there is no need for a type conversion, and this will work just fine:

Public Sub TestByteString()

    Dim strChars As String
    Dim arrBytes() As Byte
    Dim i As Integer


    strChars = "The quick Brown Fox"
    arrBytes = strChars

    Debug.Print strChars
    Debug.Print

    For i = LBound(arrBytes) To UBound(arrBytes) Step 2 

        Debug.Print Chr(arrBytes(i)) & vbTab & "Byte " & i & " = " & arrBytes(i)

    Next i

    arrBytes(0) = Asc("?")
    arrBytes(2) = Asc("!")
    arrBytes(4) = Asc("*")

    strChars = arrBytes

    Debug.Print
    Debug.Print strChars

    Erase arrBytes

End Sub

Your outputs will look like this:

The quick Brown Fox

T   Byte 0 = 84
h   Byte 2 = 104
e   Byte 4 = 101
    Byte 6 = 32
q   Byte 8 = 113
u   Byte 10 = 117
i   Byte 12 = 105
c   Byte 14 = 99
k   Byte 16 = 107
    Byte 18 = 32
B   Byte 20 = 66
r   Byte 22 = 114
o   Byte 24 = 111
w   Byte 26 = 119
n   Byte 28 = 110
    Byte 30 = 32
F   Byte 32 = 70
o   Byte 34 = 111
x   Byte 36 = 120

?!* quick Brown Fox

Note the 'Step 2' in the loop: I'm discarding every other byte, because I know that it's plain-vanilla Latin characters - 'ASCII' text to the uninitiated.

It gets interesting when you have to deal with Arabic and Pinyin text: and you should never assume in a real-world worksheet that you're always going to be dealing with plain-vanilla US ASCII, as I did in that demonstration piece.

For a more comprehensive example, with more detailed explanation, try this from Excellerando:

Writing an Excel range to a csv file: optimisations and unicode compatibility

The Byte-array optimisation is towards the bottom, under this heading:

A VBA implementation of the Adler-32 checksum, running on byte arrays instead of using VBA string-handling.

The underlying nature of a string does not seem to be as widely-known as it should be: it's not a thing that you will use often in your code, but a lot of the problems with Unicode and non-Latin alphabets that people have get easier when they have a deeper understanding of the variables in their code.