Converting Binary Data to a String in VBA

Deep Dive

In our Deep Dive series, I'll be going in depth on a small tidbit on useful information. Today we will be looking at how to convert Binary data into a string in VBA.

Overview.

In this article we will be creating a function that converts binary data to a string. We will start by creating an ADO Stream object writing the binary data to it then reading that data as text.

Note: the code used in this article was originally written by Antonin Foller and published to http://www.motobit.com | 2003

When would this be useful to me?

The quick answer is... not often when doing the day-to-day stuff. However, using binary streams to read/write files, communicate with certain types of networks like email systems, and moving data around your program can be quite powerful. In the event you need to have this data in a human readable form, this function will come in handy.

What is an ADO Stream Object?

ActiveX Data Objects, or ADO for short, is a cross-platform programming model for C++ and Visual Basic for communicating with the Object Linking and Embedding Database that underpins many databases and filesystems. The Stream Object is the small part of the ADO that we will be concerned with today. The Stream Object is used for retrieving or manipulating the binary contents of a file or email.

A Stream object can be obtained in these ways:

• From a URL pointing to an object (typically a file) containing binary or text data. This object can be a simple document, a Record object representing a structured document, or a folder.

• By opening the default Stream object associated with a Record object. You can obtain the default stream associated with a Record object when the Record is opened, to eliminate a round-trip just to open the stream.

• By instantiating a Stream object. These Stream objects can be used to store data for the purposes of your application. Unlike a Stream associated with a URL, or the default Stream of a Record, an instantiated Stream has no association with an underlying source by default.

How do we convert with VBA?

So, let’s get to it. In VBA, create a function called binaryToString that takes a Variant Binary as input and outputs a String.

Function binaryToString(Binary As Variant) As String
End Function

Next, inside the function, let's start by dimensioning some constants, Const adTypeText = 2 and Const adTypeBinary = 1 we will use these tell the Stream Object what type of data we are writing to it.

Const adTypeText = 2
Const adTypeBinary = 1

Now that we have the necessary constants dimensioned, we can create our Stream Object and specify the stream type.

Const adTypeText = 2
Const adTypeBinary = 1
Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")
BinaryStream.Type = adTypeBinary

At this point we have set the type to binary, now open the stream, and write our function's input binary data to the Stream Object.

Const adTypeText = 2
Const adTypeBinary = 1

Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

BinaryStream.Type = adTypeBinary

BinaryStream.Open
BinaryStream.Write Binary

Now, we have a Stream Object that is holding our binary data in memory. One this to note at this point is that the Stream Object keeps track of your location in the stream, kind of like a cursor keeps track or your position in a document when writing. Because we just wrote a bunch of data to the stream our cursor is now at the end of that data, so to read the data back we will have to move the cursor back to the beginning of the stream.

Const adTypeText = 2
Const adTypeBinary = 1

Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

BinaryStream.Type = adTypeBinary

BinaryStream.Open
BinaryStream.Write Binary
    
BinaryStream.Position = 0

For us to read the stream to a String we will have to provide a little more information to the Stream Object. First, we need to tell it that the binary data we wrote is text. We also need to tell it the type of text by specifying the character-set, the most common is us-ascii Unicode.

Const adTypeText = 2
Const adTypeBinary = 1

Dim BinaryStream
Set BinaryStream = CreateObject("ADODB.Stream")

BinaryStream.Type = adTypeBinary

BinaryStream.Open
BinaryStream.Write Binary
    
BinaryStream.Position = 0
    
BinaryStream.Type = adTypeText
BinaryStream.Charset = "us-ascii"

That's it! All that is left it to return the ReadText property of the Stream Object and as always, collect your garbage!

'''''''''''''''''''''''''''''''''''''''''''''''
' Convert Binary To String                    '
'''''''''''''''''''''''''''''''''''''''''''''''
' *** Note: 2003 Antonin Foller, http://www.motobit.com ***
'recieves bianary data (ex. VT_UI1 | VT_ARRAY) as variant
'outputs a string

Function binaryToString(Binary As Variant) As String

        'dimension constants
        Const adTypeText = 2
        Const adTypeBinary = 1

        'create stream object
        Dim BinaryStream 'As New Stream
        Set BinaryStream = CreateObject("ADODB.Stream")

        'specify stream type - we want to save text/string data
        BinaryStream.Type = adTypeBinary

        'open the stream and write text/string data to the object
        BinaryStream.Open
        BinaryStream.Write Binary

        'change stream type To binary
        BinaryStream.Position = 0
        BinaryStream.Type = adTypeText

        'specify charset for the source text (unicode) data
        BinaryStream.Charset = "us-ascii"

        'return - open the stream and get binary data from the object
        binaryToString = BinaryStream.ReadText

        'garbage collection
        Set BinaryStream = Nothing
End Function

The code used in this article can be found on my GitHub at VBA-Common-Library along with many other useful VBA functions.