Posted by & filed under Uncategorized.

Recently, I had the need for a function to properly encode ASCII characters into their UTF-8 equivalent using VBA (Visual Basic for Applications), specifically an Excel document which was making API calls to the Google Maps API. However, to my dismay, no functions existed for this task, and the built in character stream classes converted the characters into a format ignored by the Google API. So, I had to look up the UTF-8 table, along with each characters Hexadecimal equivalent (for both ASCII and UTF-8), and was able to come up with this function.

Now, keep in mind that this does not handle every character known to man. For my use, I had to encode European accented characters used in the German and French languages. For anything else you’ll probably need to make this function more robust.

Public Function tlhAsciiToUtf8(ByRef strText As String) As String
 Dim hexString As Long
 Dim newval As Long
 hexString = Val("&h" & strText)
 If hexString <= 127 Then
   tlhAsciiToUtf8 = "%" & strText
 ElseIf hexString <= 191 Then
   tlhAsciiToUtf8 = "%C2%" & strText
 ElseIf hexString <= 255 Then
   newval = 49856 + hexString
   tlhAsciiToUtf8 = "%" & Mid(Hex(newval), 1, 2) & "%" & Mid(Hex(newval), 3, 2)
 Else
   MsgBox ("NON ASCII CHARACTER: " & hexString)
 End If
End Function

If you’d like to learn more about VBA and Excel checkout the book Excel 2013 Power Programming with VBA.

Thomas Hunter II

Thomas is passionate about technology and building products. A web design business created while attending college slowly evolved into a brick and mortar on Main St. of his small Midwestern hometown. His desire for fame and fortune led to the co-founding of a Y Combinator startup and a life in California.
Hey there! I'm currently writing a book on Microservices which I expect to release in early 2017. If you're interested in getting updates please signup here. More info about the Book
  • Leonardo

    Hi.

    If I pass a value like “ä”, it does not work.
    If I pass a value like ASC(“ä”), putting hexstring = strtext, then works.

    How do you use this function originally (=what parameter do you pass to function)?

    I use it for same way (Google Maps API).

    Regards.
    L

  • I have the feeling the routine is not named properly. Should it not read “ANSI to UTF-8…”, since, by definition… there is no need to encode ASCII?