VBA自编函数对EXCEL中数据进行BASE64加解密处理(可用于淘客链接加密)
BASE64编码可以打乱文本的阅读性，因此可以用于加密一些敏感信息，让别人无法直接阅读。我们在接到一个客户案例，需要对淘宝阿里妈妈后台导出的推广链接进行加密，并合并客户网站的链接，进行加密，防止被搜索引擎发现是淘客链接，详情见附件。
下面进入正题
EXCEL使用VBA自编函数来对数据进行BASE64编码解码的方法如下：
按ALT+F11进入VBA设置
输入以下代码
Option Explicit
Dim sBASE_64_CHARACTERS As String
Public Function strUnicodeLen(ByVal asContents As String) As Long
Dim len1, k, i, asc1 As Long
'计算unicode字符串的Ansi编码的长度
Dim ascontents1 As String
ascontents1 = "a" & asContents
len1 = Len(ascontents1)
k = 0
For i = 1 To len1
asc1 = Asc(Mid(ascontents1, i, 1))
If asc1 < 0 Then asc1 = 65536 + asc1
If asc1 > 255 Then
k = k + 2
Else
k = k + 1
End If
Next
strUnicodeLen = k - 1
End Function
Public Function strUnicode2Ansi(ByVal asContents As String) As String
'将Unicode编码的字符串，转换成Ansi编码的字符串
Dim len1, i As Long
Dim varchar As String
Dim varasc As Long
Dim varhex, varlow, varhigh As String
strUnicode2Ansi = ""
len1 = Len(asContents)
For i = 1 To len1
varchar = Mid(asContents, i, 1)
varasc = Asc(varchar)
If varasc < 0 Then varasc = varasc + 65536
If varasc > 255 Then
varhex = Hex(varasc)
varlow = Left(varhex, 2)
varhigh = Right(varhex, 2)
strUnicode2Ansi = strUnicode2Ansi & ChrB("&H" & varlow) & ChrB("&H" & varhigh)
Else
strUnicode2Ansi = strUnicode2Ansi & ChrB(varasc)
End If
Next
End Function
Public Function strAnsi2Unicode(ByVal asContents As String) As String
'将Ansi编码的字符串，转换成Unicode编码的字符串
Dim len1, i As Long
Dim varchar As String
Dim varasc As Long
strAnsi2Unicode = ""
len1 = LenB(asContents)
If len1 = 0 Then Exit Function
For i = 1 To len1
varchar = MidB(asContents, i, 1)
varasc = AscB(varchar)
If varasc > 127 Then
strAnsi2Unicode = strAnsi2Unicode & Chr(AscW(MidB(asContents, i + 1, 1) & varchar))
i = i + 1
Else
strAnsi2Unicode = strAnsi2Unicode & Chr(varasc)
End If
Next
End Function
Public Function Base64encode(ByVal asContents As String) As String
sBASE_64_CHARACTERS = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/"
sBASE_64_CHARACTERS = strUnicode2Ansi(sBASE_64_CHARACTERS)
'将Ansi编码的字符串进行Base64编码
'asContents应当是ANSI编码的字符串（二进制的字符串也可以）
asContents = strUnicode2Ansi(asContents)
Dim lnPosition As Long
Dim lsResult As String
Dim Char1 As String
Dim Char2 As String
Dim Char3 As String
Dim Char4 As String
Dim Byte1 As Integer
Dim Byte2 As Integer
Dim Byte3 As Integer
Dim SaveBits1 As Integer
Dim SaveBits2 As Integer
Dim lsGroupBinary As String
Dim lsGroup64 As String
Dim M4, len1, len2 As Long
Dim m3 As Integer
len1 = LenB(asContents)
If len1 < 1 Then
Base64encode = ""
Exit Function
End If
m3 = len1 Mod 3
If m3 > 0 Then asContents = asContents & String(3 - m3, ChrW(0))
'补足位数是为了便于计算
If m3 > 0 Then
len1 = len1 + (3 - m3)
len2 = len1 - 3
Else
len2 = len1
End If
lsResult = ""
For lnPosition = 1 To len2 Step 3
lsGroup64 = ""
lsGroupBinary = MidB(asContents, lnPosition, 3)
Byte1 = AscB(MidB(lsGroupBinary, 1, 1)): SaveBits1 = Byte1 And 3
Byte2 = AscB(MidB(lsGroupBinary, 2, 1)): SaveBits2 = Byte2 And 15
Byte3 = AscB(MidB(lsGroupBinary, 3, 1))
Char1 = MidB(sBASE_64_CHARACTERS, ((Byte1 And 252) / 4) + 1, 1)
Char2 = MidB(sBASE_64_CHARACTERS, (((Byte2 And 240) / 16) Or (SaveBits1 * 16) And &HFF) + 1, 1)
Char3 = MidB(sBASE_64_CHARACTERS, (((Byte3 And 192) / 64) Or (SaveBits2 * 4) And &HFF) + 1, 1)
Char4 = MidB(sBASE_64_CHARACTERS, (Byte3 And 63) + 1, 1)
lsGroup64 = Char1 & Char2 & Char3 & Char4
lsResult = lsResult & lsGroup64
Next
'处理最后剩余的几个字符
If m3 > 0 Then
lsGroup64 = ""
lsGroupBinary = MidB(asContents, len2 + 1, 3)
Byte1 = AscB(MidB(lsGroupBinary, 1, 1)): SaveBits1 = Byte1 And 3
Byte2 = AscB(MidB(lsGroupBinary, 2, 1)): SaveBits2 = Byte2 And 15
Byte3 = AscB(MidB(lsGroupBinary, 3, 1))
Char1 = MidB(sBASE_64_CHARACTERS, ((Byte1 And 252) / 4) + 1, 1)
Char2 = MidB(sBASE_64_CHARACTERS, (((Byte2 And 240) / 16) Or (SaveBits1 * 16) And &HFF) + 1, 1)
Char3 = MidB(sBASE_64_CHARACTERS, (((Byte3 And 192) / 64) Or (SaveBits2 * 4) And &HFF) + 1, 1)
If m3 = 1 Then
lsGroup64 = Char1 & Char2 & ChrB(61) & ChrB(61) '用=号补足位数
Else
lsGroup64 = Char1 & Char2 & Char3 & ChrB(61) '用=号补足位数
End If
lsResult = lsResult & lsGroup64
End If
Base64encode = strAnsi2Unicode(lsResult)
End Function
Public Function Base64decode(ByVal asContents As String) As String
'将Base64编码字符串转换成Ansi编码的字符串
'asContents应当也是ANSI编码的字符串（二进制的字符串也可以）
sBASE_64_CHARACTERS = "AzByCxDwEvFuGtHsIrJqKpLoMnNmOlPkQjRiShTgUfVeWdXcYbZa)(*&>%$#@!+/"
sBASE_64_CHARACTERS = strUnicode2Ansi(sBASE_64_CHARACTERS)
asContents = strUnicode2Ansi(asContents)
Dim lsResult As String
Dim lnPosition As Long
Dim lsGroup64, lsGroupBinary As String
Dim Char1, Char2, Char3, Char4 As String
Dim Byte1, Byte2, Byte3 As String
Dim M4, len1, len2 As Long
len1 = LenB(asContents)
M4 = len1 Mod 4
If len1 < 1 Or M4 > 0 Then
'字符串长度应当是4的倍数
Base64decode = ""
Exit Function
End If
'判断最后一位是不是 = 号
'判断倒数第二位是不是 = 号
'这里m4表示最后剩余的需要单独处理的字符个数
If MidB(asContents, len1, 1) = ChrB(61) Then M4 = 3
If MidB(asContents, len1 - 1, 1) = ChrB(61) Then M4 = 2
If M4 = 0 Then
len2 = len1
Else
len2 = len1 - 4
End If
For lnPosition = 1 To len2 Step 4
lsGroupBinary = ""
lsGroup64 = MidB(asContents, lnPosition, 4)
Char1 = InStrB(sBASE_64_CHARACTERS, MidB(lsGroup64, 1, 1)) - 1
Char2 = InStrB(sBASE_64_CHARACTERS, MidB(lsGroup64, 2, 1)) - 1
Char3 = InStrB(sBASE_64_CHARACTERS, MidB(lsGroup64, 3, 1)) - 1
Char4 = InStrB(sBASE_64_CHARACTERS, MidB(lsGroup64, 4, 1)) - 1
Byte1 = ChrB(((Char2 And 48) / 16) Or (Char1 * 4) And &HFF)
Byte2 = lsGroupBinary & ChrB(((Char3 And 60) / 4) Or (Char2 * 16) And &HFF)
Byte3 = ChrB((((Char3 And 3) * 64) And &HFF) Or (Char4 And 63))
lsGroupBinary = Byte1 & Byte2 & Byte3
lsResult = lsResult & lsGroupBinary
Next
'处理最后剩余的几个字符
If M4 > 0 Then
lsGroupBinary = ""
lsGroup64 = MidB(asContents, len2 + 1, M4) & ChrB(65) 'chr(65)=A，转换成值为0
If M4 = 2 Then '补足4位，是为了便于计算
lsGroup64 = lsGroup64 & ChrB(65)
End If
Char1 = InStrB(sBASE_64_CHARACTERS, MidB(lsGroup64, 1, 1)) - 1
Char2 = InStrB(sBASE_64_CHARACTERS, MidB(lsGroup64, 2, 1)) - 1
Char3 = InStrB(sBASE_64_CHARACTERS, MidB(lsGroup64, 3, 1)) - 1
Char4 = InStrB(sBASE_64_CHARACTERS, MidB(lsGroup64, 4, 1)) - 1
Byte1 = ChrB(((Char2 And 48) / 16) Or (Char1 * 4) And &HFF)
Byte2 = lsGroupBinary & ChrB(((Char3 And 60) / 4) Or (Char2 * 16) And &HFF)
Byte3 = ChrB((((Char3 And 3) * 64) And &HFF) Or (Char4 And 63))
If M4 = 2 Then
lsGroupBinary = Byte1
ElseIf M4 = 3 Then
lsGroupBinary = Byte1 & Byte2
End If
lsResult = lsResult & lsGroupBinary
End If
Base64decode = strAnsi2Unicode(lsResult)
End Function调用方式：在需要的地方输入公式 =Base64encode(要编码的单元格) 解码用：=Base64decode(要编码的单元格)
顶(11)
踩(0)
- 最新评论