VBA中判断工作表名是否在数组时出现类型不匹配错误求助

问题说明 #

编写AdjustTableSize过程与IsInArray函数，用于检查工作表名称是否匹配Excel区域rng_Target中输入的名称数组。错误触发在IsInArray函数的If element = valueToFind Then行，尝试将变量类型从Variant改为String后仍报错，提示元素和查找值需为Variant或Object类型。

错误核心原因 #

问题并非变量类型声明错误，而是数组构建逻辑错误：

• Range("rng_Target").Value 返回的是二维数组（即使区域是单行或单列）
• specificNames = Array(myArray()) 会把二维数组嵌套进新数组，导致遍历specificNames时，每个element是一个一维数组，而非字符串，和ws.Name（字符串）直接比较就会触发类型不匹配错误

修复后的完整代码 #

Sub AdjustTableSize()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim lastRow As Long
    Dim specificNames As Variant
    Dim myArray As Variant
    Dim i As Long
    
    ' 获取目标区域的值，返回二维数组
    myArray = Range("rng_Target").Value
    ' 将二维数组转换为一维字符串数组
    ReDim specificNames(1 To UBound(myArray, 1))
    For i = 1 To UBound(myArray, 1)
        specificNames(i) = CStr(myArray(i, 1))
    Next i
    
    ' 遍历所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 检查工作表名称是否在目标数组中
        If IsInArray(ws.Name, specificNames) Then
            ' 检查工作表是否存在表格
            If ws.ListObjects.Count > 0 Then
                Set tbl = ws.ListObjects(1)
                ' 获取表格数据行总数（含表头）
                lastRow = tbl.ListRows.Count + 1
                
                ' 若有数据行，删除表头以下的空行
                If lastRow > 1 Then
                    tbl.Range.Resize(lastRow).Offset(1).EntireRow.Delete
                End If
            End If
        End If
    Next ws
End Sub

' 检查值是否在数组中的函数
Function IsInArray(valueToFind As Variant, arr As Variant) As Boolean
    Dim element As Variant
    For Each element In arr
        ' 转为字符串后比较，避免类型差异问题
        If CStr(element) = CStr(valueToFind) Then
            IsInArray = True
            Exit Function
        End If
    Next element
    IsInArray = False
End Function

关键修复点说明 #

1. 数组转换处理：将Range返回的二维数组转为一维字符串数组，确保每个元素是可直接比较的字符串
2. 类型安全比较：在函数中用CStr()强制转换为字符串后再比较，彻底避免类型不匹配问题
3. 增加容错判断：添加ws.ListObjects.Count > 0的判断，避免工作表无表格时触发运行时错误
4. 修正表格行计数：用tbl.ListRows.Count + 1更准确获取包含表头的总行数，原tbl.Range.Rows.Count可能包含表格外的空行

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