excel - Convert an array of bytes into a string?

Question

I have arrays of integers, each is a ASCII code representing a single byte of a string.

I can generate a string from the array like this:

Sub BytesToString()

    Dim myArr(): myArr = Array(84, 104, 105, 115, 32, _
        105, 115, 32, 97, 32, 116, 101, 115, 116, 33)

    Dim c As Variant, myStr As String

    For Each c In myArr
        myStr = myStr & Chr(c)
    Next c

    MsgBox myStr

End Sub

...but I feel like this isn't "the right way" to do this, especially since repeated conversions may be needed. Array length will vary.

Is there a built-in or more efficient method to produce the string with VBA?

Answer 1

Turns out, this is one of those rare times where the solution was so simple it was overlooked by several people, including myself.

---

?? "Byte Arrays" and Strings are basically interchangeable.

In VBA, Byte Arrays are special because, unlike arrays of other data types, a string can be directly assigned to a byte array.

In VBA, Strings are UNICODE strings, so when one assigns a string to a byte array then it stores two digits for each character. The first digit will be the ASCII value of the character and next will be 0.
_{(Source: VBA Trick of the Week: Byte Arrays in VBA - Useful Gyaan)}

A couple code samples will likely demonstrate better than I can explain:

Sub Demo1()
    Dim myArr() As Byte, myStr As String
    myStr = "Hi!"
    myArr() = myStr

    Debug.Print "myStr length: " & Len(myStr)                       'returns "3"
    Debug.Print "Arr bounds: " & LBound(myArr) &"to"& UBound(myArr) 'returns "0 to 5"
    myStr = myArr
    Debug.Print myStr                                               'returns "Hi!"
End Sub

In the above case the string's length is 3 so the array’s size will be 6. Values will be stored in the following way:

myArr(0) = 72 ' ASCII : code for 'H'
myArr(1) = 0 ' ASCII 'null' character
myArr(2) = 105 ' ASCII : code for 'i'
myArr(3) = 0 ' ASCII 'null' character
...etc...

The StrConv function can be used if one wants to remove these zeros. In this case it will store ASCII values only.

    myByteArr() = StrConv("StackOverflow", vbFromUnicode)

Just like a string can be directly assigned to a byte array, a byte array can also be directly assigned to a string. In above example if one assigns myArr to a string then it will store the same value that has been assigned to the array.

When the array is populated element-by-element - or, in my case, from a speedy file operation (see below) - an extra step of conversion with StrConv is required.

Sub Demo2()
    Dim myArr(0 To 5) As Byte, myStr As String
    myArr(0) = 104: myArr(1) = 101: myArr(2) = 108
    myArr(3) = 108: myArr(4) = 111: myArr(5) = 33

    Debug.Print "myArr bounds: " & LBound(myArr) &"to"& UBound(myArr) 'returns "0 to 5"

    'since the array was loaded byte-by-byte, we can't "just put back":
    myStr = myArr()
    Debug.Print myStr                               'returns "???" (unprintable characters)
    Debug.Print "myStr length: " & Len(myStr)       'returns "3"

    'using `StrConv` to allow for 2-byte unicode character storage
    myStr = StrConv(myArr(), vbUnicode)
    Debug.Print myStr                                'returns "hello!"
    Debug.Print "myStr length: " & Len(myStr)        'returns "6"
End Sub

---

How a Byte Array made my day a little better...

I have large text files that I been wanting parse/analyze with VBA, but couldn't find a method that wasn't painfully slow in either the loading or the character-by-character parsing.

As an example, today I managed to load a quarter-gigabyte file in ¹/_10^th of a second, and parsed it into a second Byte Array:

Dim bytes() As Byte
Open myFileName For Binary Access Read As #1
ReDim bytes(LOF(1) - 1&)
Get #1, , bytes
Close #1

For x = LBound(arrOut) To UBound(arrOut)
    Select Case bytes(x)

        (..and if I want the character)
            bytes2(y) = bytes(x)
            y = y + 1
    End Select
Next x
ReDim Preserve bytes2(LBound(bytes2) To y - 1)
txtIn = StrConv(bytes2, vbUnicode)

...and I had my completed string in under 5 seconds total. (Hooray!)

---

More Information:

• Useful Gyaan : VBA Trick of the Week: Byte Array in VBA
• VS Magazine : VB Corner: Searching Within Byte Arrays
• IBM : Convert strings and arrays of byte
• FastExcel : Writing efficient VBA UDFs – Faster string handling and Byte arrays
• Stack Overflow : How many bytes does one Unicode character take?
• MSDN : Data Type Summary (VBA)
• MSDN : Get Statement (VBA)