Alternative for .NET's System.Convert within VBA

Question

I'm writing a VBA function in Microsoft Access to Encrypt / Decrypt text data using RSA keys.

The following works currently:

CreateObject("System.Security.Cryptography.RSACryptoServiceProvider")
CreateObject("System.Text.UTF8Encoding")

but this is not working:

CreateObject("System.Convert")

I did some research and came across this stackoverflow question: Can I call a static method of a C# class from VBA via COM?

So as it turns out, System.Convert is a static class so therefore it is unavailable to the COM object, and therefore unavailable to VBA.

I could wrap all my .NET stuff into my own class and then add it to Access as a reference, but its a little outside my comfort zone and I'd rather stay away from having to maintain custom references anyways for this specific project.

So rather than stay fixated on the System.Convert option, I thought about what my needs are specifically. I need to be able to:

• Convert a Base 64 encoded string to a Byte Array and pass to .NET methods
• Convert a Byte Array, returned from .NET calls, into a Base 64 encoded string

The simplest option I see would be to write my own VBA code to convert between Base 64 and a byte array - however I see two pitfalls:

1. Encoding issues. Converting between byte arrays and strings means having to deal with encoding issues.
2. Base64 variations. Off the top of my head, the Base64 could have CR/LF in it. I am also not sure if Base64 encoded RSA strings would have / need to have a header. If I wrote my own Base64 function, I would need to be sure that it is compatible with the RSACryptoServiceProvider's FromBase64String method. I don't want to introduce extra characters into the encryption stream.

Given my concerns - Convert.FromBase64String seemed the best option since it keeps everything in .NET, but since I can't use it - I need an alternative.

If there were other standard .NET classes that provided the FromBase64String and ToBase64String functionality but were able to be instantiated via CreateObject - that would probably be the best option. I do not know of any however.

Answer 1

There are many VBA/VBScript/VB6 Base64 implementations that generate exactly the same results as .NET ToBase64String for encoding and FromBase64String for decoding.

You can find a VBA/VB6 example with full Base64 Encode and Decode functions here Base64 encoder/decoder in Visual Basic. The Base64 VB module bas file can be found here Base64Coder.bas.

Here are some screenshots from the same tests in C# and VB6. You can clearly see the results are the same for both languages:

C#

static void Main(string[] args)
{
    Check("Aladdin:open sesame", "QWxhZGRpbjpvcGVuIHNlc2FtZQ=="); // example from RFC 2617)
    Check("1", "MQ==");
    Check("22", "MjI=");
    Check("333", "MzMz");
    Check("4444", "NDQ0NA==");
    Check("55555", "NTU1NTU=");
    Check("abc:def", "YWJjOmRlZg==");
    Check("????", "Pz8/Pw==");
    Check("abcdefghijklnmopqrstuvwxyz0123456789???", "YWJjZGVmZ2hpamtsbm1vcHFyc3R1dnd4eXowMTIzNDU2Nzg5Pz8/");
}

private static void Check(string plainText, string base64Text)
{
    string s1 = Base64Encode(plainText);
    string s2 = Base64Decode(base64Text);

    Debug.WriteLine("String: " + s2 + ", to Base64: " + s1);

    if(s1 != base64Text || s2 != plainText)
        Debug.WriteLine("Check failed for \"" + plainText + "\" / \"" + base64Text + "\".");
}
public static string Base64Encode(string plainText)
{
    var plainTextBytes = System.Text.Encoding.UTF8.GetBytes(plainText);
    return System.Convert.ToBase64String(plainTextBytes);
}

public static string Base64Decode(string base64EncodedData)
{
    var base64EncodedBytes = System.Convert.FromBase64String(base64EncodedData);
    return System.Text.Encoding.UTF8.GetString(base64EncodedBytes);
}

C# OUTPUT

VBA/VB6

Public Sub Main()
    Check "Aladdin:open sesame", "QWxhZGRpbjpvcGVuIHNlc2FtZQ==" ' example from RFC 2617
    Check "1", "MQ=="
    Check "22", "MjI="
    Check "333", "MzMz"
    Check "4444", "NDQ0NA=="
    Check "55555", "NTU1NTU="
    Check "abc:def", "YWJjOmRlZg=="
    Check "????", "Pz8/Pw=="
    Check "abcdefghijklnmopqrstuvwxyz0123456789???", "YWJjZGVmZ2hpamtsbm1vcHFyc3R1dnd4eXowMTIzNDU2Nzg5Pz8/"
End Sub

Private Sub Check(ByVal plainText As String, ByVal base64Text As String)
    Dim s1 As String: s1 = Base64EncodeString(plainText)
    Dim s2 As String: s2 = Base64DecodeString(base64Text)

    Debug.Print "String: " & s2 & ", Base64: " & s1

    If s1 <> base64Text Or s2 <> plainText Then _
        Debug.Print "Check failed for """ & plainText & """ / """ & base64Text & """."
End Sub

VBA/VB6 OUTPUT