Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
900 views
in Technique[技术] by (71.8m points)

excel - Convert an array of bytes into a string?

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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:


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...