.NET的System.Convert的替代品在VBA中转换 [英] Alternative for .NET's System.Convert within VBA

问题描述

我正在Microsoft Access中编写VBA函数，以使用RSA密钥加密/解密文本数据.

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

当前有以下作品:

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

但这不起作用:

CreateObject("System.Convert")

我做了一些研究，发现了这个stackoverflow问题: 可以给我打电话吗通过COM从VBA获得C#类的静态方法?

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

事实证明， System.Convert 是静态类，因此它对于COM对象不可用，因此对VBA不可用.

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.

我可以将所有.NET内容包装到我自己的类中，然后将其添加到Access中作为引用，但是它超出了我的舒适范围，我宁愿不必为此保留任何自定义引用项目.

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.

因此，我没有考虑System.Convert选项，而是考虑了我的具体需求.我需要能够:

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

• 将Base 64编码的字符串转换为字节数组并传递给.NET方法
• 将.NET调用返回的字节数组转换为以Base 64编码的字符串

我看到的最简单的选择是编写我自己的VBA代码以在Base 64和字节数组之间进行转换-但是我看到了两个陷阱:

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. 编码问题.在字节数组和字符串之间进行转换意味着必须处理编码问题.
2. Base64变体.在我的头顶上，Base64可能装有CR/LF.我也不确定Base64编码的RSA字符串是否将具有/需要标头.如果我编写了自己的Base64函数，则需要确保它与RSACryptoServiceProvider的 FromBase64String 方法兼容.我不想在加密流中引入多余的字符.

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.

考虑到我的担忧-Convert.FromBase64String似乎是最好的选择，因为它将所有内容保存在.NET中，但是由于我无法使用它-我需要一个替代方法.

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.

如果还有其他提供.FromBase64String和ToBase64String功能但可以通过CreateObject实例化的标准.NET类-这可能是最好的选择.我什么都不知道.

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.

推荐答案

有许多 VBA/VBScript/VB6 Base64 实现，它们产生与.NET ToBase64String完全相同的编码和FromBase64String进行解码.

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

您可以在此处VBA/VB6 示例"rel =" nofollow noreferrer> Visual Basic中的Base64编码器/解码器. Base64 VB 模块bas文件可在此处找到 Base64Coder.bas .

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.

以下是C#和VB6中相同测试的一些屏幕截图.您可以清楚地看到两种语言的结果相同:

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#输出

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输出

这篇关于.NET的System.Convert的替代品在VBA中转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！