VBA代码Split函数报错：参数数量错误或属性赋值无效

你的VBA宏过程名split和VBA内置的字符串分割函数Split重名了。当你在代码里调用split(cell.Value, ",")时，VBA会优先把它识别为你自己定义的split过程，而这个过程没有参数，所以就会弹出“参数数量错误或属性赋值无效”的报错。这个问题和lastRow变量无关，只是刚好在添加变量后触发了调用Split的代码分支而已。

有两种简单的修复方式：

方式一：修改宏过程名 #

把Sub split()改成一个和内置函数不冲突的名字，比如Sub SplitCellContents()，这样代码里的Split就会正常调用内置函数：

Sub SplitCellContents()
    Dim rng As Range
    Dim cell As Range
    Dim arr() As String
    Dim i As Long, lastRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("A1:A" & lastRow) ' Range of cells to check
    
    For Each cell In rng
        If cell.Value <> "" Then
            If InStr(1, cell.Value, ",") = 0 Then
                Cells(cell.Row, 3) = cell.Value
                Cells(cell.Row, 1) = ""
            Else
                arr = Split(cell.Value, ",") ' Split cell value by comma and add to array
            
                For i = UBound(arr) To 0 Step -1
                     If UBound(arr) = 2 Then
                         Cells(cell.Row, i + 1) = arr(i)
                     Else
                         Cells(cell.Row, i + 2) = arr(i)
                         Cells(cell.Row, 1) = ""
                     End If
                Next i
            End If
        End If
    Next cell
End Sub

方式二：明确指定调用内置函数 #

如果不想修改过程名，在调用Split时加上VBA.前缀，强制指定使用VBA内置的Split函数：

Sub split()
    Dim rng As Range
    Dim cell As Range
    Dim arr() As String
    Dim i As Long, lastRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("A1:A" & lastRow) ' Range of cells to check
    
    For Each cell In rng
        If cell.Value <> "" Then
            If InStr(1, cell.Value, ",") = 0 Then
                Cells(cell.Row, 3) = cell.Value
                Cells(cell.Row, 1) = ""
            Else
                arr = VBA.Split(cell.Value, ",") ' 明确调用VBA内置Split函数
            
                For i = UBound(arr) To 0 Step -1
                     If UBound(arr) = 2 Then
                         Cells(cell.Row, i + 1) = arr(i)
                     Else
                         Cells(cell.Row, i + 2) = arr(i)
                         Cells(cell.Row, 1) = ""
                     End If
                Next i
            End If
        End If
    Next cell
End Sub

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