技术问询：如何在VBA中使用UTF-8？Excel2013代码窗格支持Unicode字符吗？

Great questions! Let's tackle each one clearly:

VBA’s native string type uses UTF-16 under the hood, but there’s no built-in "UTF-8 string" type—so working with UTF-8 (like reading/writing files or handling external data) requires a bit of extra work. Here are two reliable, Excel 2013-compatible approaches:

Using ADODB.Stream (simplest for file operations) #

This is my go-to for reading/writing UTF-8 files, whether you need a BOM or not. Here’s how to use it:

Writing a UTF-8 file #

Sub WriteUTF8File()
    Dim stream As Object
    Set stream = CreateObject("ADODB.Stream")
    
    stream.Charset = "UTF-8"
    stream.Open
    stream.WriteText "Hello, 世界! This is UTF-8 text."
    stream.SaveToFile "C:\your\file\path.txt", 2 ' 2 = overwrite existing file
    stream.Close
End Sub

Reading a UTF-8 file #

Sub ReadUTF8File()
    Dim stream As Object
    Dim fileContent As String
    
    Set stream = CreateObject("ADODB.Stream")
    stream.Charset = "UTF-8"
    stream.Open
    stream.LoadFromFile "C:\your\file\path.txt"
    fileContent = stream.ReadText
    stream.Close
    
    MsgBox fileContent ' Shows the UTF-8 content correctly
End Sub

Using Windows API Functions (for fine-grained control) #

If you need to convert between VBA’s UTF-16 strings and UTF-8 byte arrays (e.g., for network calls or custom file handling), use the WideCharToMultiByte and MultiByteToWideChar Windows APIs. Here’s a quick example of converting a string to UTF-8 bytes:

' For 64-bit Excel
Private Declare PtrSafe Function WideCharToMultiByte Lib "kernel32" ( _
    ByVal CodePage As Long, _
    ByVal dwFlags As Long, _
    ByVal lpWideCharStr As LongPtr, _
    ByVal cchWideChar As Long, _
    ByVal lpMultiByteStr As LongPtr, _
    ByVal cbMultiByte As Long, _
    ByVal lpDefaultChar As LongPtr, _
    ByVal lpUsedDefaultChar As LongPtr _
) As Long

' For 32-bit Excel, use this instead:
' Private Declare Function WideCharToMultiByte Lib "kernel32" ( _
'     ByVal CodePage As Long, _
'     ByVal dwFlags As Long, _
'     ByVal lpWideCharStr As Long, _
'     ByVal cchWideChar As Long, _
'     ByVal lpMultiByteStr As Long, _
'     ByVal cbMultiByte As Long, _
'     ByVal lpDefaultChar As Long, _
'     ByVal lpUsedDefaultChar As Long _
' ) As Long

Function StringToUTF8Bytes(ByVal inputStr As String) As Byte()
    Dim byteCount As Long
    Dim utf8Bytes() As Byte
    
    If inputStr = vbNullString Then
        StringToUTF8Bytes = vbNullString
        Exit Function
    End If
    
    ' Calculate how many bytes we need for the UTF-8 conversion
    byteCount = WideCharToMultiByte(65001, 0, StrPtr(inputStr), -1, 0, 0, 0, 0)
    ReDim utf8Bytes(0 To byteCount - 2) ' Subtract 2 to exclude the null terminator
    
    ' Perform the conversion
    WideCharToMultiByte 65001, 0, StrPtr(inputStr), -1, VarPtr(utf8Bytes(0)), byteCount, 0, 0
    
    StringToUTF8Bytes = utf8Bytes
End Function

Note: 65001 is the Windows code page identifier for UTF-8.

Absolutely—you can input Unicode characters directly into the Excel 2013 VBA editor, including the ◊ symbol in your example code.

Your sample Select Case block should work without issues, as long as two things are true:

• The editor’s font supports the Unicode character. By default, the VBA editor uses "Courier New", which includes most common symbols like ◊. If you see weird boxes instead of the symbol, go to Tools > Options > Editor Format and switch to a font that supports more Unicode characters (e.g., "Segoe UI Symbol" or "Arial Unicode MS").
• The string you’re comparing (Work(str)) contains the exact same Unicode character as your Case values. Since Excel 2013 supports Unicode in cells, pulling the string from a worksheet should work perfectly.

To test this, here’s a quick working version of your code:

Sub TestUnicodeCase()
    Dim testString As String
    testString = "◊ Engineering" ' Type this directly in the code pane
    
    Select Case testString
        Case "◊ Engineering", "◊ Woodworking", "◊ Technician"
            MsgBox "Match found!"
        Case Else
            MsgBox "No match."
    End Select
End Sub

Run this, and you’ll get the "Match found!" message as expected.

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