How to use in vba unicode characters

Question

Having trouble with vba editor. Have looked to many resources, and tried many proper unicodes for letter schwab "ə" and without dot "ı" but non of them displays correctly this letters. Tried to change font shrifts but it gives an error with russian letters. So I need both them displayed on editor as path and there is no way to change the path of folder. I even tried character wide in vba of u+01DD, u+0259 like &H259,1DD but the result is or question mark or different character. u+0131 gives an exact i with dot, not what I need. Anyone knows how to display these characters in vba?

FolderName = "C...ə(?)lsheth/folders/"
FileName =Dir(FolderName & "*.xls*")
Do While FileName <> ""
Debug.Print FileName
FileName = Dir()
Loop

gives an error 52, Bad name or number I think problems lay here that, there are many versions of schwa latin letter, and I need to use(try) all of them as I think the solution must be one of them. Does anyone know how to convert them to use in chrW() for VBA? List is here; I have taken it from unicode page 0259 ə LATIN SMALL LETTER SCHWA • mid-central unrounded vowel • uppercase is 018F Ə • variant uppercase form 018E Ǝ is associated with 01DD ǝ → 01DD ǝ latin small letter turned e → 04D9 ә cyrillic small letter schwa

user_6585761 · Answer

As already mentioned by @Toddleson, the Visual Basic Editor doesn't support Unicode characters. And the Dir function doesn't support it either. However, the FileSystemObject object does support it.

Here's an example that uses the ChrW function to return the desired unicode character, and then uses the FileSystembObject object to loop through each file within the specified folder, filter for .xls files, opens the file, and then saves and closes it.

Sub test()

    Dim folderName As String
    folderName = "C:\Users\Domenic\Desktop\D" & ChrW(&H259) & "nmark\" 'change the path accordingly

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim folder As Object
    Set folder = fso.getfolder(folderName)
    
    Dim file As Object
    Dim wb As Workbook
    For Each file In folder.Files
        If InStr(1, file.Name, ".xls", vbTextCompare) > 0 Then
            Set wb = Workbooks.Open(file.Path)
            'etc
            '
            '
            With wb
                .Save
                .Close
            End With
        End If
    Next file
    
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing

End Sub

user_14608750 · Answer

ChrW(399) = Ə

ChrW(305) = ı

These will not be displayed in VBE but when you assign the value into a cell, Excel will display these characters.

Alternatives:

ChrW(601) = ə

ChrW(618) = ɪ (it looks like the dotless i when printed in Calibri in Excel)

How to use in vba unicode characters

2 Answers