vbaunicode

How to use Unicode characters in VBA editor


Having trouble with vba editor. Have looked to many resources, and tried many proper unicodes for letter schwab "ə" and without dot "ı" but none 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


Solution

  • 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