如何在VBA中按空白字符（含制表符、换行符）拆分字符串？

我需要拆分包含文字和数字的字符串，该字符串由多种空白字符分隔（如Tab、换行等ASCII码小于32的控制字符）。原本计划将所有空白字符标准化为单个空格后用Split()拆分，但找到的例程只能处理多空格，无法覆盖所有ASCII<32的空白字符。运行以下VBA函数时出现Error 13（类型不匹配）：

Public Function SplitRe(Text As String, Pattern As String, Optional IgnoreCase As Boolean) As String()
    Static re As Object

    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        re.Global = True
        re.MultiLine = True
    End If

    re.IgnoreCase = IgnoreCase
    re.Pattern = Pattern
    SplitRe = Strings.Split(re.Replace(Text, ChrW(-1)), ChrW(-1))
End Function

待拆分的示例字符串（<tab>代表Tab字符，<nl>代表换行字符）：

<tab>6219<tab><nl>
Changes to Pilot Ops Dashboard<nl>
Daniel Son<tab>Gatehouse<tab>Port of Collinsville<nl>
Medium<nl>
Support<tab>waiting<tab><nl>
30 minutes ago<nl>
8 seconds ago<nl>
5 minutes<nl>

期望得到的字符串数组：

6219
Changes to Pilot Ops Dashboard
Daniel Son
Gatehouse
Port of Collinsville
Medium
Support
waiting
30 minutes ago
8 seconds ago
5 minutes

问题原因与解决方案 #

Error 13的根源是ChrW(-1)：VBA中ChrW的参数必须是0-65535之间的整数，-1超出有效范围导致类型不匹配。同时原函数未处理开头/结尾空白产生的空数组元素，需要补充过滤逻辑。

修正后的函数如下，使用正则匹配所有ASCII<32的控制字符，替换为合法的特殊分隔符，并过滤空元素：

Public Function SplitRe(Text As String, Pattern As String, Optional IgnoreCase As Boolean) As String()
    Static re As Object
    Dim tempStr As String
    Dim resultArr() As String
    Dim filteredArr() As String
    Dim i As Integer, j As Integer
    
    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        re.Global = True
        re.MultiLine = True
    End If

    re.IgnoreCase = IgnoreCase
    re.Pattern = Pattern
    ' 用ASCII 255（非打印特殊字符）作为临时分隔符，避免与文本内容冲突
    tempStr = re.Replace(Text, ChrW(255))
    resultArr = Split(tempStr, ChrW(255))
    
    ' 过滤空元素（处理开头/结尾空白产生的空字符串）
    j = 0
    ReDim filteredArr(0 To UBound(resultArr))
    For i = 0 To UBound(resultArr)
        If Trim(resultArr(i)) <> "" Then
            filteredArr(j) = resultArr(i)
            j = j + 1
        End If
    Next i
    
    If j > 0 Then
        ReDim Preserve filteredArr(0 To j - 1)
        SplitRe = filteredArr
    Else
        SplitRe = Array()
    End If
End Function

调用示例 #

使用正则表达式[\x00-\x1F]+匹配所有ASCII 0-31的控制字符（包含Tab、换行等空白类字符）：

Dim inputStr As String
Dim outputArr() As String

' 构造包含Tab、换行的输入字符串
inputStr = vbTab & "6219" & vbTab & vbCrLf & _
           "Changes to Pilot Ops Dashboard" & vbCrLf & _
           "Daniel Son" & vbTab & "Gatehouse" & vbTab & "Port of Collinsville" & vbCrLf & _
           "Medium" & vbCrLf & _
           "Support" & vbTab & "waiting" & vbTab & vbCrLf & _
           "30 minutes ago" & vbCrLf & _
           "8 seconds ago" & vbCrLf & _
           "5 minutes" & vbCrLf

' 调用拆分函数
outputArr = SplitRe(inputStr, "[\x00-\x1F]+")

' 遍历输出结果
Dim idx As Integer
For idx = LBound(outputArr) To UBound(outputArr)
    Debug.Print outputArr(idx)
Next idx

运行后将得到符合预期的字符串数组，所有空白类控制字符都会被作为分隔符，且不会产生空元素。

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