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.
Latest posts by Thomas Hunter II (see all)
- Creating and Integrating a Node Lambda function with API Gateway - 2018-04-02
- Developing Cobalt Dungeon using Phaser and Cordova - 2018-03-27
- Cobalt Dungeon for iOS is now available - 2018-03-12