insert unicode character into a VBA string
10,214
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
Author by
Commata
Updated on June 24, 2022Comments
-
Commata almost 2 years
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?