In many cases in can be useful to be able to “sanitize” foreign characters such as accented characters with their equivalent non-accented characters.

For instance, when trying to create a zip file in Windows (SendTo -> Compressed (zipped) folder) as it will complain if files include such characters.

Another use is when creating/naming Folders, such characters have no place (this also includes specials characters and spaces).

For example, I was looking to convert French accented characters into their English counterpart. I didn’t want to use any of the extended ascii characters since they can be the source of potential problems. So I wanted to easily switch

ÀÁÂÃÄÅÇÈÉÊËÌÍÎÏÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðòóôõöùúûüýÿ

to

AAAAAACEEEEIIIIOOOOOUUUUYaaaaaaceeeeiiiioooooouuuuyy

The API Approach

So, how exact could we convert a string?  Well, many years ago (I think back in 2012), I came across the following that I’ve only tweak a little (added error handling and x64 declaration).  Sadly, I don’t remember where I came across it, but I know it can be useful to some.

#If Win64 Then
    'x64 Declarations
    Private Declare PtrSafe Function FoldString Lib "kernel32" Alias "FoldStringA" _
                                                (ByVal dwMapFlags As Byte, _
                                                 ByVal lpSrcStr As LongPtr, _
                                                 ByVal cchSrc As Long, _
                                                 ByVal lpDestStr As LongPtr, _
                                                 ByVal cchdest As Long) As Long
#Else
    'x32 Declaration
    Private Declare Function FoldString Lib "kernel32" Alias "FoldStringA" _
                                        (ByVal dwMapFlags As Byte, _
                                         ByVal lpSrcStr As Long, _
                                         ByVal cchSrc As Long, _
                                         ByVal lpDestStr As Long, _
                                         ByVal cchdest As Long) As Long
#End If

'---------------------------------------------------------------------------------------
' Procedure : SanitizeForeignCharacters
' Author    : Unknown - Found only years ago, contact me should you know who created
'               this code originally
' Modified by:Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Convert a string by replacing foreign/accented characted with standard
'             letters.
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None req'd
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInputString  The string to convert
'
' Usage:
' ~~~~~~
' SanitizeForeignCharacters("LémuçelÇë")    -> LemucelCe
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         Unknown                 Initial Release
' 2         2012-08-28              Code cleanup and error handling added
' 3         2017-08-18              Added x64 bit support
' 4         2017-08-19              Corrected the i declaration as Len returns a long
'                                   *** if used in VS then Len returns an integer ***
'---------------------------------------------------------------------------------------
Function SanitizeForeignCharacters(sInputString As String) As String
    On Error GoTo Error_Handler
    Dim i                     As Long 'Integer -> for VS

    SanitizeForeignCharacters = Space(Len(sInputString))
    For i = 0 To Len(sInputString) * 2 - 2 Step 2
        FoldString &H40, StrPtr(sInputString) + i, 1, _
                   StrPtr(SanitizeForeignCharacters) + i, 1
    Next i

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SanitizeForeignCharacters" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

As such it will convert a string such as:

Par exemple, vous souhaiterez peut-être ouvrir un document qui contient une macro qui le centre de gestion de la confidentialité désactive, car le centre de gestion de la confidentialité estime la macro potentiellement dangereux.

would return

Par exemple, vous souhaiterez peut-etre ouvrir un document qui contient une macro qui le centre de gestion de la confidentialite desactive, car le centre de gestion de la confidentialite estime la macro potentiellement dangereux.

The 100% Pure VBA Approach

I’ve used the above for years and it works beautifully. That said, I also know as a developer, it is best to minimize as much as possible the use of things like ActiveX controls, References, and APIs and wanted to create a 100% VBA function to perform the same function. This is what I created for this purpose.

'---------------------------------------------------------------------------------------
' Procedure : RemoveAccents
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Replace foreign/accented charactes with standard letters within a string
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None req'd
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sInputString  The string to convert
'
' Usage:
' ~~~~~~
' RemoveAccents("LémuçelÇë")    -> LemucelCe
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2017-08-18              Initial Release
'---------------------------------------------------------------------------------------
Public Function RemoveAccents(sInputString As String) As String
    On Error GoTo Error_Handler
    Dim aNotAllowed()         As String
    Dim aReplacements()       As String
    Dim i                     As Long

    aNotAllowed = Split("À,Á,Â,Ã,Ä,Å,Ç,È,É,Ê,Ë,Ì,Í,Î,Ï,Ò,Ó,Ô,Õ,Ö,Ù,Ú,Û,Ü,Ý,à,á,â,ã" & _
                        ",ä,å,ç,è,é,ê,ë,ì,í,î,ï,ð,ò,ó,ô,õ,ö,ù,ú,û,ü,ý,ÿ", ",")
    aReplacements = Split("A,A,A,A,A,A,C,E,E,E,E,I,I,I,I,O,O,O,O,O,U,U,U,U,Y,a,a,a" & _
                          ",a,a,a,c,e,e,e,e,i,i,i,i,o,o,o,o,o,o,u,u,u,u,y,y", ",")

    If UBound(aNotAllowed) <> UBound(aReplacements) Then
        MsgBox "The number of 'Not allowed characters' does not match the number of" & _
               " 'Replacements'.", vbCritical Or vbOKOnly, "Operation Terminated"
        GoTo Error_Handler_Exit
    End If

    RemoveAccents = sInputString
    For i = 0 To UBound(aNotAllowed)
        RemoveAccents = Replace(RemoveAccents, aNotAllowed(i), aReplacements(i))
    Next i

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: RemoveAccents" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Pay particular attention to the note with states

This requires a Option Compare Binary declaration otherwise string case is ignored.

In reality this function is very versatile because it can be used to replace any characters/terms by simply updating the 2 arrays.

Also, performance testing did not reveal any difference in execution time between the 2 techniques making the choice of using the 100% pure VBA function even more appealing!

So, at the end of the day, we clearly see that there is, yet again, more than one way to perform the same task. Which you choose to use is entirely up to you, but at least now you have some options to explore.