VBA函数参数类型变更引发类型不匹配(Error 13)问题排查

问题背景 #

你有一个RemoveEmptyArrRowCol函数，当第二个参数是Long类型时（Test1）运行正常，但改为String类型（Test2）后出现类型不匹配错误；最终还要适配从字典ArrayDict获取数组作为第一个参数的场景。

1) 为何Test1和Test2行为不同？ #

核心原因有两个：

• Case分支匹配失败：Test2中传递的tempStr是"Response Times"（带空格），但函数内部的Select Case判断的是"ResponseTimes"（无空格），导致无法进入对应分支，函数默认返回Empty值。而tempArr声明为动态数组，将Empty赋值给数组变量就会触发类型不匹配(Error 13)。
• 参数传递与逻辑衔接的差异：Test1直接传递字典返回的Long值，函数不需要额外处理键拼接；Test2传递的是字符串参数，函数内部需要正确拼接键名并匹配Case分支，这里的空格遗漏直接导致逻辑中断。

2) 如何修复Test2使其与Test1表现一致？ #

针对上述问题，做以下关键修改：

步骤1：修正Case分支的字符串匹配 #

把函数里的判断字符串和传递的tempStr保持一致，补上空格：

Select Case tempStr
Case "Response Times", "NoCCPR"
    ' 原有逻辑代码
Case "Incidents"
End Select

步骤2：修正tempArr声明并统一函数名 #

将Test2中的无效声明Dim tempArr() Init改为合法的Variant类型（更灵活接收数组），同时确保函数名统一：

Public Sub Test2()
 Dim tempArr As Variant ' 改为Variant，避免数组赋值的类型冲突
 Dim tempStr As String: tempStr = "Response Times"
 ' 确保函数名和实际定义的RemoveEmptyArrRowCol一致
 tempArr = RemoveEmptyArrRowCol(ws.Range("C4:I129").Value, tempStr)
End Sub

步骤3：添加容错判断（可选） #

为避免字典键不存在导致的错误，在函数开头添加检查：

If Not categoryDict.Exists(tempStr & "Cols") Or Not categoryDict.Exists(tempStr & "ColsAdj") Then
    RemoveEmptyArrRowCol = tempArr ' 返回原数组，避免空值赋值错误
    Exit Function
End If
totCol = categoryDict(tempStr & "Cols")
adjColTotal = categoryDict(tempStr & "ColsAdj")

3) 当第一个参数改为从字典ArrayDict获取的数组时，如何提前适配？ #

从字典获取的数组和工作表Range返回的数组都是Variant类型，但需要注意以下适配点：

• 确认字典存储的数组结构：确保ArrayDict中存储的是1-based二维数组（和Range返回的数组索引格式一致：1 To rows, 1 To cols），避免存储一维数组或单个值导致函数循环逻辑出错。
• 添加数组合法性校验：在函数开头加入校验，确保传入的参数是合法二维数组：

Private Function RemoveEmptyArrRowCol(ByRef tempArr As Variant, ByVal tempStr As String) As Variant
    ' 校验是否为数组且是二维结构
    If Not IsArray(tempArr) Then
        RemoveEmptyArrRowCol = tempArr
        Exit Function
    End If
    On Error Resume Next
    Dim colCount As Long
    colCount = UBound(tempArr, 2)
    If Err.Number <> 0 Then ' 不是二维数组
        RemoveEmptyArrRowCol = tempArr
        Exit Function
    End If
    On Error GoTo 0
    ' 原有逻辑代码...
End Function

• 统一数组索引格式：如果字典中的数组是0-based索引（比如自定义生成的数组），需要转换为1-based以匹配函数内的循环逻辑：

' 转换0-based数组为1-based
If LBound(tempArr, 1) = 0 Then
    tempArr = Application.Transpose(Application.Transpose(tempArr))
End If

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