insert unicode character into a VBA string

Question

Using MS Access 2003 on Windows 7, I found that the following function strips all accents from ANSI strings, changing (for example) señor to senor:

Public Function RemoveAccents(ByVal inputString As String) As String Const accentString As String = "ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÏÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóóôõöùúûüýÿ" Const nonAccentStr As String = "AAAAAACEEEEIIIIINOOOOOUUUUYaaaaaaceeeeiiiionoooooouuuuyy" Dim i As Integer For i = 1 To Len(accentString) inputString = Replace(inputString, Mid(accentString, i, 1), Mid(nonAccentStr, i, 1), , , vbBinaryCompare) Next i RemoveAccents = inputString End Function

But when I tried to add Latin small letter C with Caron (U-010D)(č)(HTML č) to the function Const accentString, like this,

Const accentString As String = "ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÏÑÒÓÔÕÖÙÚÛÜÝàáâãäåç" & ChrW$(&H10D) & "èéêëìíîïðñòóóôõöùúûüýÿ" Const nonAccentStr As String = "AAAAAACEEEEIIIIINOOOOOUUUUYaaaaaacceeeeiiiionoooooouuuuyy"

I was unable to run the function. Is there a syntax which will allow me to adapt this function to strip the diacriticals from strings containing characters not in the ANSI character set?

Gee, even though I speak only English and live in the generally homogeneous U.S., but I still have to deal with Unicode characters, and this question helped me. — GlennFromIowa
– GlennFromIowa, Commented Jul 6, 2016 at 15:27

David Zemens · Accepted Answer · 2013-03-26 18:26:03Z

You can't have functions like ChrW() in a constant declaration.

Revised

If you can make these Public variables instead of Constants, then this should take care of it for you:

Const cWithCaron As String = &H10D Public accentString As String Public nonAccentStr As String Sub TestStrings() Dim clnString As String accentString = "ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÏÑÒÓÔÕÖÙÚÛÜÝàáâãäåç" & ChrW(cWithCaron) & "èéêëìíîïðñòóóôõöùúûüýÿ" nonAccentStr = "AAAAAACEEEEIIIIINOOOOOUUUUYaaaaaacceeeeiiiionoooooouuuuyy" 'I added this variable to test the function: clnString = RemoveAccents(accentString) 'And a message box to display the results: MsgBox clnString = nonAccentStr End Sub Public Function RemoveAccents(ByVal inputString As String) As String Dim i As Integer For i = 1 To Len(accentString) inputString = Replace(inputString, Mid(accentString, i, 1), Mid(nonAccentStr, i, 1), , , vbBinaryCompare) Next i RemoveAccents = inputString End Function

David, your suggestion enables me to save and run the function, but now the function is not replacing the Slavic &H10D with a plain c. It is leaving the slavic hacek over the c in place.
Any reason why these have to be declared as CONST? I can think of possible workarounds if these were PUBLIC variables set elsewhere (e.g., the subroutine that calls on the RemoveAccents function).
There is something wrong with the function anyway. If it is run in its original form, it returns too many characters. I am going to withdraw this question so that others do not waste time on it. Thanks for your help!

Collectives™ on Stack Overflow

insert unicode character into a VBA string

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related