首页 > 代码库 > Excel中使用VBA自定义函数对字符串进行Url编码(UTF-8)

Excel中使用VBA自定义函数对字符串进行Url编码(UTF-8)

代码库 , , ,

Excel是个非常强大的工具,工作中因为需要用到他老完成一些表格数据的处理,所以抽空学了一点小技巧。

VBA是Visual Basic Application的全称,我们在Office的产品家族里面会经常看到它的身影。甚至SQL Server里头也有它的足迹,善于使用这些VBA函数或者自定义一些函数能够帮我们实现一些看似很强大的功能而又不用花费太大的精力。

步骤一. 打开Excel 2007中的Visual Basic编辑器

在Excel 2007中的开发工具菜单下,点击Visual Basic,即可弹出Visual Basic的编辑界面。默认情况下,这个开发工具在功能区是不显示的,需要在Excel设置中勾选上,如下图。

image

image

image

步骤二. 编写自定义函数

选择菜单->插入->模块,就会弹出一个编辑窗口,在其中输入下面代码,即可定义一个UrlEncode的函数。

Public Function UrlEncode(ByRef szString As String) As String
       Dim szChar   As String
       Dim szTemp   As String
       Dim szCode   As String
       Dim szHex    As String
       Dim szBin    As String
       Dim iCount1  As Integer
       Dim iCount2  As Integer
       Dim iStrLen1 As Integer
       Dim iStrLen2 As Integer
       Dim lResult  As Long
       Dim lAscVal  As Long
       szString = Trim$(szString)
       iStrLen1 = Len(szString)
       For iCount1 = 1 To iStrLen1
           szChar = Mid$(szString, iCount1, 1)
           lAscVal = AscW(szChar)
           If lAscVal >= &H0 And lAscVal <= &HFF Then
              If (lAscVal >= &H30 And lAscVal <= &H39) Or _
                 (lAscVal >= &H41 And lAscVal <= &H5A) Or _
                 (lAscVal >= &H61 And lAscVal <= &H7A) Then
                 szCode = szCode & szChar
              Else
                
                 szCode = szCode & "%" & Hex(AscW(szChar))
              End If
           Else
              szHex = Hex(AscW(szChar))
              iStrLen2 = Len(szHex)
              For iCount2 = 1 To iStrLen2
                  szChar = Mid$(szHex, iCount2, 1)
                  Select Case szChar
                         Case Is = "0"
                              szBin = szBin & "0000"
                         Case Is = "1"
                              szBin = szBin & "0001"
                         Case Is = "2"
                              szBin = szBin & "0010"
                         Case Is = "3"
                              szBin = szBin & "0011"
                         Case Is = "4"
                              szBin = szBin & "0100"
                         Case Is = "5"
                        szBin = szBin & "0101"
                         Case Is = "6"
                              szBin = szBin & "0110"
                         Case Is = "7"
                              szBin = szBin & "0111"
                         Case Is = "8"
                              szBin = szBin & "1000"
                         Case Is = "9"
                              szBin = szBin & "1001"
                         Case Is = "A"
                              szBin = szBin & "1010"
                         Case Is = "B"
                              szBin = szBin & "1011"
                         Case Is = "C"
                              szBin = szBin & "1100"
                         Case Is = "D"
                              szBin = szBin & "1101"
                         Case Is = "E"
                              szBin = szBin & "1110"
                         Case Is = "F"
                              szBin = szBin & "1111"
                         Case Else
                  End Select
              Next iCount2
              szTemp = "1110" & Left$(szBin, 4) & "10" & Mid$(szBin, 5, 6) & "10" & Right$(szBin, 6)
              For iCount2 = 1 To 24
                  If Mid$(szTemp, iCount2, 1) = "1" Then
                     lResult = lResult + 1 * 2 ^ (24 - iCount2)
                  Else: lResult = lResult + 0 * 2 ^ (24 - iCount2)
                  End If
              Next iCount2
              szTemp = Hex(lResult)
                    szCode = szCode & "%" & Left$(szTemp, 2) & "%" & Mid$(szTemp, 3, 2) & "%" & Right$(szTemp, 2)
           End If
           szBin = vbNullString
           lResult = 0
       Next iCount1
       UrlEncode = szCode
End Function

步骤三:测试

定义完之后,我们选择菜单->文件->关闭并返回Excel。然后我们就可以在Excel中测试刚才定义的这个函数了。如下图所示。

image

注意,使用了VBA函数的excel文件需要在打开时启用宏,否则函数无效。

本博客遵循CC协议2.5,即署名-非商业性使用-相同方式共享
写作很辛苦,转载请注明作者以及原文链接~
如果你喜欢我的文章,你可以订阅我的博客:-D点击订阅我的文章

  1. Ivan
    | #1

    second last column should be "UrlEncode = szCode"

  2. Ivan
    | #3

    nice code anyway =)

  3. joy
    | #4

    Great!

  4. 鬼愿
    | #5

    有URLDecode的函数么?

  5. | #6

    一直在找这个,谢谢~

  6. | #7

    请问怎么便携解码url的函数呢?

    • Kevin Yang
      | #8

      写完一次之后保存此文件?!

  7. | #9

    有两个问题:
    1、如何编写utf-8的url解码函数?
    2、开启宏的excel文件保存后,貌似vb函数自动消失了。。得重新设置。如何解决呢?

    • Kevin Yang
      | #10

      需要将其保存为带宏的excel工作簿,以xlsm为后缀的。对于第一个问题,照着步骤做行不通么??

  8. | #11

    按照上述说的做了,A列示要转到文字,enurlcode在B列中怎么生成呢?

    • Kevin Yang
      | #12

      如果B列想要对A列进行编码,只需要在B列上写=UrlEncode(A1)就行了

  9. Timor
    | #13

    你好,我按照你的方法弄了,但是会跳出这个    发现二义性的名称:UrlEncode          不能用?求解决大神·

  1. 暂时没有trackbacks.