使用VBA拆分Excel单元格多值字符串并解决对象变量未设置报错

你遇到的报错是VBA对象赋值规则问题导致的：

• 你声明的Rng是Range对象类型，VBA中给对象变量赋值必须使用Set关键字，直接用等号赋值不符合对象赋值要求
• 普通InputBox返回的是字符串类型，无法直接赋值给Range对象，需要转成Range对象或使用专用的范围输入方法

Sub SplitSkills()
    Dim Separator As String
    Dim Rng As Range
    Dim myCell As Range
    Dim SplitArr() As String
    Dim NumofElements As Integer
    Dim MyArray() As String
    Dim X As Integer
    Dim i As Integer
    
    '获取分隔符
    Separator = InputBox("Enter the Separator Here: ")
    If Separator = "" Then Exit Sub '用户点击取消直接退出
    
    '获取处理范围，支持框选或手动输入地址
    On Error Resume Next
    Set Rng = Application.InputBox("Enter Range Here: ", Type:=8)
    On Error GoTo 0
    If Rng Is Nothing Then Exit Sub '用户点击取消直接退出
    
    '拆分内容存入数组
    X = 0
    ReDim MyArray(0 To 0)
    For Each myCell In Rng
        If myCell.Value <> "" Then
            SplitArr = Split(myCell.Value, Separator)
            NumofElements = UBound(SplitArr) + 1
            ReDim Preserve MyArray(0 To X + NumofElements - 1)
            For i = 0 To UBound(SplitArr)
                If Trim(SplitArr(i)) <> "" Then '跳过空值
                    MyArray(X) = Trim(SplitArr(i))
                    X = X + 1
                End If
            Next i
            Erase SplitArr
        End If
    Next
    
    '将结果输出到所选范围右侧第一列
    Rng.Offset(0, 1).Resize(UBound(MyArray) + 1, 1) = WorksheetFunction.Transpose(MyArray)
End Sub

• 补充了对象赋值必须的Set关键字，解决原始报错
• 替换普通输入框为Application.InputBox(Type:=8)，支持用户直接鼠标框选需要处理的单元格范围，使用更便捷
• 修正了原始代码的数组下标越界问题：Split返回的数组最大索引为UBound(SplitArr)，原始代码循环到NumofElements会超出数组范围
• 修正了数组索引偏移错误，避免输出结果出现空值或内容错位
• 新增空值过滤逻辑，拆分后自动跳过空白内容，减少后续去重的清理工作量
• 新增用户取消输入的异常处理，避免操作中断时触发报错

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