Excel中使用VB实现Base64编解码

首先Excel使用VB编写公式参考这篇文章

EXCEL自定义函数_excel自定义函数编写_hongweigg的博客-CSDN博客

然后修改公式：

'base64解码输出hex公式:
Function Base64DecodeToHex(B64 As String) As String					'Base64 解码
    On Error GoTo over                                      '排错
    Dim OutByteDate() As Byte, i As Long, j As Long
    Const B64_CHAR_DICT = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/="
    If InStr(1, B64, "=") <> 0 Then B64 = Left(B64, InStr(1, B64, "=") - 1)     '判断Base64真实长度,除去补位
    Dim length As Long, mods As Long
    mods = Len(B64) Mod 4
    length = Len(B64) - mods
    ReDim OutByteDate(length / 4 * 3 - 1 + Switch(mods = 0, 0, mods = 2, 1, mods = 3, 2))
    Dim OutByteDateIndex As Long
    OutByteDateIndex = 0
    For i = 1 To length Step 4
        Dim buf(3) As Byte
        For j = 0 To 3
            buf(j) = InStr(1, B64_CHAR_DICT, Mid(B64, i + j, 1)) - 1            '根据字符的位置取得索引值
        Next
        OutByteDate(OutByteDateIndex) = buf(0) * &H4 + (buf(1) And &H30) / &H10 'byte0向左移动2bit后为高6bit  byte1取bit5和bit6后向右移动4bit后作为低2bit
        OutByteDate(OutByteDateIndex + 1) = (buf(1) And &HF) * &H10 + (buf(2) And &H3C) / &H4 '取byte1低4bit为高4位  byte2取高4bit做为低4bit
        OutByteDate(OutByteDateIndex + 2) = (buf(2) And &H3) * &H40 + buf(3)        'byte2低2bit为高2bit byte3为低6bit
        OutByteDateIndex = OutByteDateIndex + 3
    Next
    If mods = 2 Then
            Dim buf1(2) As Byte
            buf1(0) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 1, 1)) - 1
            buf1(1) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 2, 1)) - 1
        OutByteDate(OutByteDateIndex) = buf1(0) * &H4 + (buf1(1) And &H30) / 16
        OutByteDateIndex = OutByteDateIndex + 1
    ElseIf mods = 3 Then
            Dim buf2(3) As Byte
            buf2(0) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 1, 1)) - 1
            buf2(1) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 2, 1)) - 1
            buf2(2) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 3, 1)) - 1
        OutByteDate(OutByteDateIndex) = buf2(0) * &H4 + (buf2(1) And &H30) / 16
        OutByteDate(OutByteDateIndex + 1) = (buf2(1) And &HF) * &H10 + (buf2(2) And &H3C) / &H4
        OutByteDateIndex = OutByteDateIndex + 2
    End If
    For i = 0 To OutByteDateIndex Step 1
       Base64DecodeToHex = Base64DecodeToHex + String(2 - Len(Hex(OutByteDate(i))), "0") + Hex(OutByteDate(i)) + " " '输出结果
    Next
over:
End Function

'base64解码输出Str公式:
Function Base64DecodeToStr(B64 As String) As String													'Base64 解码
    On Error GoTo over                                                          '排错
    Dim OutByteDate() As Byte, i As Long, j As Long
    Const B64_CHAR_DICT = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/="
    If InStr(1, B64, "=") <> 0 Then B64 = Left(B64, InStr(1, B64, "=") - 1)     '判断Base64真实长度,除去补位
    Dim length As Long, mods As Long
    mods = Len(B64) Mod 4
    length = Len(B64) - mods
    ReDim OutByteDate(length / 4 * 3 - 1 + Switch(mods = 0, 0, mods = 2, 1, mods = 3, 2))
    Dim OutByteDateIndex As Long
    OutByteDateIndex = 0
    For i = 1 To length Step 4
        Dim buf(3) As Byte
        For j = 0 To 3
            buf(j) = InStr(1, B64_CHAR_DICT, Mid(B64, i + j, 1)) - 1                          '根据字符的位置取得索引值
        Next
        OutByteDate(OutByteDateIndex) = buf(0) * &H4 + (buf(1) And &H30) / &H10               'byte0向左移动2bit后为高6bit  byte1取bit5和bit6后向右移动4bit后作为低2bit
        OutByteDate(OutByteDateIndex + 1) = (buf(1) And &HF) * &H10 + (buf(2) And &H3C) / &H4 '取byte1低4bit为高4位  byte2取高4bit做为低4bit
        OutByteDate(OutByteDateIndex + 2) = (buf(2) And &H3) * &H40 + buf(3)        'byte2低2bit为高2bit byte3为低6bit
        OutByteDateIndex = OutByteDateIndex + 3
    Next
    If mods = 2 Then
            Dim buf1(2) As Byte
            buf1(0) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 1, 1)) - 1
            buf1(1) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 2, 1)) - 1
        OutByteDate(OutByteDateIndex) = buf1(0) * &H4 + (buf1(1) And &H30) / 16
        OutByteDateIndex = OutByteDateIndex + 1
    ElseIf mods = 3 Then
            Dim buf2(3) As Byte
            buf2(0) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 1, 1)) - 1
            buf2(1) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 2, 1)) - 1
            buf2(2) = InStr(1, B64_CHAR_DICT, Mid(B64, length + 3, 1)) - 1
        OutByteDate(OutByteDateIndex) = buf2(0) * &H4 + (buf2(1) And &H30) / 16
        OutByteDate(OutByteDateIndex + 1) = (buf2(1) And &HF) * &H10 + (buf2(2) And &H3C) / &H4
        OutByteDateIndex = OutByteDateIndex + 2
    End If
    For i = 0 To OutByteDateIndex Step 1
       Base64DecodeToStr = Base64DecodeToStr + Chr(OutByteDate(i)) '输出结果
    Next
over:
End Function


'base64编码公式:
Function Base64Encode(StrA As String) As String
    On Error GoTo over
    Dim buf() As Byte, length As Long, mods As Long
    Dim Str() As Byte
    Dim i, kk As Integer
    kk = Len(StrA) - 1
    ReDim Str(kk)
    For i = 0 To kk
        Str(i) = Asc(Mid(StrA, i + 1, 1))
    Next i
    Const B64_CHAR_DICT = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/="
    mods = (UBound(Str) + 1) Mod 3
    length = UBound(Str) + 1 - mods
    ReDim buf(length / 3 * 4 + IIf(mods <> 0, 4, 0) - 1)
    Dim OutByteDateIndex As Long
    OutByteDateIndex = 0
    For i = 0 To length - 1 Step 3
        buf(OutByteDateIndex) = (Str(i) And &HFC) / &H4
        buf(OutByteDateIndex + 1) = (Str(i) And &H3) * &H10 + (Str(i + 1) And &HF0) / &H10
        buf(OutByteDateIndex + 2) = (Str(i + 1) And &HF) * &H4 + (Str(i + 2) And &HC0) / &H40
        buf(OutByteDateIndex + 3) = Str(i + 2) And &H3F
        OutByteDateIndex = OutByteDateIndex + 4
    Next
    If mods = 1 Then
        buf(OutByteDateIndex) = (Str(length) And &HFC) / &H4
        buf(OutByteDateIndex + 1) = (Str(length) And &H3) * &H10
        buf(OutByteDateIndex + 2) = 64
        buf(OutByteDateIndex + 3) = 64
    ElseIf mods = 2 Then
        buf(OutByteDateIndex) = (Str(length) And &HFC) / &H4
        buf(OutByteDateIndex + 1) = (Str(length) And &H3) * &H10 + (Str(length + 1) And &HF0) / &H10
        buf(OutByteDateIndex + 2) = (Str(length + 1) And &HF) * &H4
        buf(OutByteDateIndex + 3) = 64
    End If
    For i = 0 To UBound(buf)
        Base64Encode = Base64Encode + Mid(B64_CHAR_DICT, buf(i) + 1, 1)
    Next
over:
End Function

测试结果：