Menu
I need to encode a 100KB+ string as base64 in VBA. Are there any built-in functions or COM objects available which will do this as a pure VBA approach is either complex or doesn't scale well at these volumes (see links from dbb and marxidad)?
336
To do this, the text string to encode is entered into cell A1 and the encoding type is entered into cell A2. In this example, the encoding type is "base64". The function is entered into cell B1 and the formula is =ENCODEURL(A1,A2). The result of the function is displayed in cell B1.
Although Base64 is a relatively efficient way of encoding binary data it will, on average still increase the file size for more than 25%. This not only increases your bandwidth bill, but also increases the download time.
If we were to Base64 encode a string we would follow these steps: Take the ASCII value of each character in the string. Calculate the 8-bit binary equivalent of the ASCII values. Convert the 8-bit chunks into chunks of 6 bits by simply re-grouping the digits.
The difference between Base64 and hex is really just how bytes are represented. Hex is another way of saying "Base16". Hex will take two characters for each byte - Base64 takes 4 characters for every 3 bytes, so it's more efficient than hex.
You can use the MSXML Base64 encoding functionality as described at www.nonhostile.com/howto-encode-decode-base64-vb6.asp:
Function EncodeBase64(text As String) As String Dim arrData() As Byte arrData = StrConv(text, vbFromUnicode) Dim objXML As MSXML2.DOMDocument Dim objNode As MSXML2.IXMLDOMElement Set objXML = New MSXML2.DOMDocument Set objNode = objXML.createElement("b64") objNode.dataType = "bin.base64" objNode.nodeTypedValue = arrData EncodeBase64 = objNode.Text Set objNode = Nothing Set objXML = Nothing End Function If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
