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 1/10th 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: