Difference between URLCode UDF and the built-in EncodeURL

[YasserKhalil]

Hello everyone
I am trying to encode url so as the create or build payload and I am confused between the built-in function `ENCODEURL` and a UDF that is supposed to the same (or at least as I think)

Code: Select all

Sub Test_MyEncodeURL_URLEncode_UDFs()
    Dim sText As String
    sText = "Button1=" & Join(Array(Chr(200), Chr(205), Chr(203)), Empty)
    Debug.Print MyEncodeURL(sText)
    Debug.Print URLEncode(sText)
End Sub

Function MyEncodeURL(ByVal sText As String) As String
    MyEncodeURL = Application.WorksheetFunction.EncodeURL(sText)
End Function

Function URLEncode(ByVal Text As String) As String
    Dim Encoded As String, Char As String, CharCode As Integer, i As Long
    Encoded = vbNullString
    For i = 1 To Len(Text)
        CharCode = Asc(Mid(Text, i, 1))
        Select Case CharCode
            Case 48 To 57, 65 To 90, 97 To 122
                Char = Chr(CharCode)
            Case 32
                Char = "+"
            Case Else
                Char = "%" & Hex(CharCode)
        End Select
        Encoded = Encoded & Char
    Next i
    URLEncode = Encoded
End Function

In the immediate window I got two different results. Why is it different and what is the correct between both of them?

Although the results are different but I tried both of them in the payload of the request and both worked well. I am confused a little!!

[HansV]

They produce URL strings for different character sets. The worksheet function ENCODEURL produces UTF-8 encoding, while the UDF produces ASCII.

[YasserKhalil]

Great. So there are the same?
and what if I need the UDF to produce UTF-8 instead of ASCII ?

[HansV]

Why would you want a UDF to produce UTF-8 if you already have a built-in function?

[YasserKhalil]

As I will send the file to some users who uses old versions of excel (2003)

[YasserKhalil]

this worked for me

Code: Select all

Function URLEncode_UTF8(ByVal Text As String) As String
    Dim byteArray() As Byte, sEncoded As String, sHex As String, i As Long, n As Long
    With CreateObject("ADODB.Stream")
        .Type = 2
        .Charset = "utf-8"
        .Open
        .WriteText Text
        .Position = 0
        .Type = 1
        byteArray = .Read
        .Close
    End With
    If UBound(byteArray) >= 2 And byteArray(0) = &HEF And byteArray(1) = &HBB And byteArray(2) = &HBF Then
        byteArray = MidB(byteArray, 4)
    End If
    sEncoded = vbNullString
    For i = LBound(byteArray) To UBound(byteArray)
        n = byteArray(i)
        If n = 32 Then
            sEncoded = sEncoded & "+"
        ElseIf n >= 48 And n <= 57 Or n >= 65 And n <= 90 Or n >= 97 And n <= 122 Then
            sEncoded = sEncoded & Chr(n)
        Else
            sHex = "%" & Right("0" & Hex(n), 2)
            sEncoded = sEncoded & sHex
        End If
    Next i
    URLEncode_UTF8 = sEncoded
End Function

Thank you very much my tutor.

[SpeakEasy]

> users who uses old versions of excel
Excel's URLEncode function is simply a wrapper for a Windows API call - and we can use that call directly ourselves:

Code: Select all

Option Explicit

Private Declare Function UrlEscape Lib "Shlwapi.dll" Alias "UrlEscapeA" (ByVal pszURL As String, ByVal pszEscaped As String, ByRef pcchEscaped As Long, ByVal dwFlags As Long) As Long
Private Const URL_ESCAPE_PERCENT As Long = &H1000
Private Const URL_ESCAPE_SEGMENT_ONLY As Long = &H2000

Public Function EscapeURL(ByVal URL As String, Optional WantLoose As Boolean = True) As String
    Dim LenURL As Long
    Dim EscURL As String
    
    If WantLoose Then URL = "xxxx" & URL
    LenURL = Len(URL) * 3
    EscURL = Space$(LenURL) ' assume worst case that every character is Escaped and thus three chars
    
    If UrlEscape(URL, EscURL , LenURL, URL_ESCAPE_PERCENT + URL_ESCAPE_SEGMENT_ONLY) = 0 Then  'do we get a result?
        EscapeURL = Mid$(EscURL , 1 - 4 * WantLoose, LenURL + 4 * WantLoose)
    End If
End Function

There is one minor difference, however, the API call is much fussier about what it considers legitimate text for encoding. For example passing a single "?" works with the worksheet function, but not the API call (since technically a single question mark does not meet the relevant RFC (RFC1738) defining a URL (e.g neither a # nor a ? can start the of the scheme-specific-part of the URL (which is what we are looking at). Hence the introduction of the WantLoose parameter, which we can set to True if we want a loose interpretation