Encoding Strings to Base64 in VBA

A Deep Dive.

This is the first entry in the Deep Dive series where I'll be going in depth on a small topic or useful bit of information. Today we will be looking at Base64 encoding a string in VBA.

What is Base64 encoding?

Base64 is a way to represent binary data (aka. 1s and 0s) as letters, numbers, and symbols. We call this specific encoding scheme Base64 because it is limited to 64 character-symbols, making it a base-64 mathematical system like the base-10 (0-9) system that we are used to.

Encoding is a form of translation, specifically translating some form of data into another form that is easier or more efficient to send or store. Base64 encoding is most used for sending data through URLs and through email because its character-set (the 64 characters used in the scheme) omits many of the special characters (characters with specific meanings) used be those protocols.

To encode something to Base64, first we must convert it to binary. Once we have the binary, we can split it into 24-bit (twenty-four 1s and 0s) chunks. Each 24-bit chunk then gets split into four 6-bit chunks. then we use the table below to replace each 6-bit chunk with the appropriate character, if there is any room left to fill out the 24-bits then use the padding symbol =.

Binary Character-set chart

Why do I need to encode something to Base64 anyway?

As a programmer you will often run into applications for Base64 when communicating over the internet, be it passing data from a user submitted for to the server or communicating with an API. This kind of communication will most often use a subset of Base64 called Base64URL which uses a slightly different character-set designed to be URL and file-name safe.

How do we encode in VBA?

In VBA, like with most programming languages, this functionality already exists but there is a little round-about way of accessing it. But first, let’s create a function called base64Encode that takes a String as input and outputs a String.

Function base64Encode(input as String) As String

End Function

Create a virtual DOM

Next, inside the function, we need to create a Microsoft XML DOM Document Object.

Dim oXML
Set oXML = CreateObject("Msxml2.DOMDocument.3.0")

This is essentially creating a virtual DOM (Document Object Model) like the one that your web browser uses to render HTML webpages.

Create an element on the DOM

Once, we have created the DOM we need to create a base64 element in the document.

Dim oXML, oNode
Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
Set oNode = oXML.createElement("base64")

Now we have a virtual (in memory) XML document that has an element called base64.

<base64></base64>

Next, we need to tell the element what type of data it accepts by setting its datatype property to bin.base64.

Dim oXML, oNode
Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
Set oNode = oXML.createElement("base64")
oNode.DataType = "bin.base64"

This will apply a Microsoft schema to the element and reference the desired datatype in our virtual XML document.

<base64 xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64"></base64>

Add Value to DOM element

After appropriately typing our element we can convert the input into binary and pass it to the nodeTypedValue property of the XML element.

Dim oXML, oNode
Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
Set oNode = oXML.createElement("base64")
oNode.DataType = "bin.base64"
oNode.nodeTypedValue = Stream_StringToBinary(sText)

That's pretty much it. The XML element does the conversion from binary to base64 for us. Now, all that is left is to retrieve the value as text and return our function.

<base64 xmlns:dt="urn:schemas-microsoft-com:datatypes" dt:dt="bin.base64">UmV2ZXJpb3Njb3BpYyE=</base64>

Get DOM element Base64 encoded value

To get the value out of the element, we just need to call the text property of the element. Don't forget to collect your garbage!

'''''''''''''''''''''''''''''''''''''''''''''''
' Convert String to Base64 encoding           '
'''''''''''''''''''''''''''''''''''''''''''''''
' receives input of type String
' outputs same string with base64 encoding applied

Function base64Encode(input as String) As String
        'dimension tools
        Dim oXML, oNode
        Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
        Set oNode = oXML.createElement("base64")

        'set datatype
        oNode.DataType = "bin.base64"

        'encode to base64
        oNode.nodeTypedValue = Stream_StringToBinary(sText)

        'return
        Base64Encode = oNode.Text

        'garbage collection
        Set oNode = Nothing
        Set oXML = 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.

---