Insert Unicode character in VBA string

Question

Using MS Access 2003 in Windows 7, I found that the following function separates all the 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 the Latin small letter C with Karon (U-010D) (č) (HTML & # 269;) to a Const accentString function like this,

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

I was unable to start the function. Is there any syntax that will allow me to adapt this function to remove diacritical characters from strings containing characters not contained in the ANSI character set?

Answer 1

You cannot have functions like ChrW() in a constant declaration.

Corrected version

If you can make these public variables instead of constants, then this should take care of this 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