VBA Trick of the Week :: Byte Array in VBA

Posted: June 6, 2013 by Transformer in VBA
Tags: , , ,

VBA Trick HatIn VBA, Byte Arrays are special because, unlike arrays of other datatypes, 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.

e.g.


Sub Test()

        Dim bytArrNames()           As Byte

        bytArrNames() = "UsefulGyaan"

End Sub

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

byrArrNames(0) = 85     ‘ ASCII value for ‘U’
byrArrNames(1) = 0
byrArrNames(2) = 115    ‘ ASCII value for ‘s’
byrArrNames(3) = 0
:
:
:

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

bytArrNames() = StrConv(“UsefulGyaan”, 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 bytArrNames to a string then it will store the same value that has been assigned to the array.


Sub Test()

        Dim bytArrNames()           As Byte
        Dim strName                 As String

        bytArrNames() = "UsefulGyaan"
        strName = bytArrNames   ‘Here UsefulGyaan will be stored in the string

End Sub
Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s