VBA中非拉丁字符（阿拉伯语、俄语）保留及工作表重命名问题技术咨询

Why This Happens #

VBA Editor relies on your system's default code page (like Windows-1252) instead of UTF-8 by default. That’s why Arabic, Cyrillic, or other non-Latin characters get converted to question marks—they aren’t supported in the default encoding.

Solution 1: Import UTF-8 Encoded Code from a Text Editor #

This is the most reliable way to preserve non-Latin characters:

• Open a UTF-8 capable text editor (like Notepad++), paste your VBA code, and set the encoding to UTF-8 with BOM (the BOM is mandatory—VBA won’t recognize plain UTF-8 files).
• Save the file as a .bas module (e.g., SheetRenamer.bas).
• In Excel, open the VBA Editor (Alt+F11), right-click your workbook in the Project Explorer → Import File, and select the .bas file you saved.
• When you open the imported module, your non-Latin characters should display correctly.

Solution 2: Handle Unicode Directly in VBA (Supports Find/Replace) #

If you don’t want to import files, you can work with Unicode escape sequences or use methods that support Unicode. Here are two practical approaches:

Option A: Use Unicode Escape Sequences (Great for Bulk Replace) #

Convert your non-Latin characters to their Unicode code points and use ChrW() to generate them in code. This works even if the editor can’t display the characters—they’ll still render correctly when the macro runs:

Sub RenameSheetsWithUnicodeReplace()
    Dim ws As Worksheet
    Dim targetArabicName As String
    Dim newName As String
    
    ' Build the Arabic string "نظرة عامة" using Unicode code points
    targetArabicName = ChrW(&H646) & ChrW(&H638) & ChrW(&H631) & ChrW(&H629) & " " & _
                        ChrW(&H639) & ChrW(&H627) & ChrW(&H645) & ChrW(&H629)
    newName = "Overview"
    
    ' Loop through sheets to find and rename
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = targetArabicName Then
            ws.Name = newName
            ws.Protect "Password"
            Exit For ' Stop once we find the target sheet
        End If
    Next ws
End Sub

Pro Tip: To get the Unicode code point for a character, open the VBA Immediate Window (Ctrl+G) and type AscW("ن")—it’ll return the hex value you can use with ChrW().

Option B: Paste Directly (With Encoding Checks) #

If you prefer to paste non-Latin characters directly into VBA, follow these steps to maximize chances of them sticking:

1. Save your Excel file as a .xlsm (Macro-Enabled Workbook)—old .xls files don’t support Unicode.
2. In the VBA Editor, go to Tools → Options → Editor Format and set the font to one that supports non-Latin characters (like Arial Unicode MS).
3. After pasting the characters, save the file immediately, close and reopen Excel to verify the characters are still there. If they turn to question marks, fall back to Solution 1.

Optimized Alternative: Improve Sheet Index Referencing #

If you need to stick with index-based referencing while making the code more maintainable, try this structured approach:

Sub ActivateRenameSheetsByIndex()
    ' Map sheet indices to their target names (supports non-Latin if imported correctly)
    Dim sheetMap As Variant
    sheetMap = Array( _
        Array(2, "نظرة عامة"), _
        Array(3, "Обзор"), _
        Array(4, "Overview") _
    )
    
    Dim i As Integer
    For i = LBound(sheetMap) To UBound(sheetMap)
        With ThisWorkbook.Worksheets(sheetMap(i)(0))
            .Activate
            .Name = sheetMap(i)(1)
            .Protect "Password"
        End With
    Next i
End Sub

Note: If pasting non-Latin names here shows question marks, save this code as a UTF-8 with BOM .bas file and import it as in Solution 1.

Key Notes #

• Always use .xlsm format for workbooks with VBA that uses non-Latin characters—.xls doesn’t support Unicode.
• Ensure anyone using your file has Excel 2007 or later (all modern versions support Unicode).

内容的提问来源于stack exchange，提问作者Clay Campbell