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

5

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

edited Mar 26, 2013 at 18:26

answered Mar 26, 2013 at 17:01

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Commata Over a year ago

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.

David Zemens Over a year ago

Ahhhh I see. Let me take a closer look.

David Zemens Over a year ago

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).

Commata Over a year ago

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!

David Zemens Over a year ago

HOld that thought... I will post a revision momentarily.

Collectives™ on Stack Overflow

insert unicode character into a VBA string

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related