VBA函数TextFileInfoArrayFPP下标越界问题及Sheet参数疑问

Sheet参数说明 #

函数的Sheet参数是调用时传入的工作表名称字符串，作用有两个：

• Sheets(Sheet)：要进行数据对比的目标工作表；
• Sheets("REF_" & Sheet)：对应的参考工作表，用来和目标工作表的数据做差异比对；
  调用时必须确保当前工作簿中同时存在Sheet指定的表，以及前缀为REF_的同名参考表（比如传"FPP"，则需要有"FPP"和"REF_FPP"两个工作表）。

「下标越界」错误的原因及修复 #

1. 最常见原因：工作表不存在 #

如果传入的Sheet参数对应的工作表（或其参考表）不存在，就会触发下标越界。可以在函数开头加入校验逻辑提前拦截：

' 校验Sheet对应的工作表是否存在
On Error Resume Next
Dim targetSheet As Worksheet, refSheet As Worksheet
Set targetSheet = ThisWorkbook.Sheets(Sheet)
Set refSheet = ThisWorkbook.Sheets("REF_" & Sheet)
On Error GoTo 0

If targetSheet Is Nothing Or refSheet Is Nothing Then
    TextFileInfoArrayFPP = "错误：工作表 " & Sheet & " 或 REF_" & Sheet & " 不存在"
    Exit Function
End If

2. 常量使用错误 #

原代码开头Sheets("REF_FPP").Visible = Visible中的Visible未指定具体常量，应该改为xlVisible，否则会导致工作表显示逻辑出错，进而引发后续问题：

Sheets("REF_FPP").Visible = xlVisible
Sheets("REF_UPB").Visible = xlVisible

3. 循环行索引计算错误 #

原代码第一个循环For i = 0 To EndingRow - 1，通过StartingRow + i计算行号，容易出现行号超出工作表范围的情况，建议直接遍历StartingRow到EndingRow：

' 统计差异数据的行数
For i = StartingRow To EndingRow
    For n = 0 To WeeksToLoad - 1
        If targetSheet.Cells(i, 9 + n).Value <> refSheet.Cells(i, 9 + n).Value Then
            count = count + 1
        End If
    Next n
Next i

4. 参数合理性校验 #

调用函数时需确保：

• StartingRow <= EndingRow，且两行号都在工作表有效行范围内；
• WeeksToLoad为正整数，且9 + WeeksToLoad - 1不超过工作表最大列数（Excel最大列数为16384）。

完整修正后的代码 #

Function TextFileInfoArrayFPP(WeeksToLoad As Integer, EndingRow As Integer, StartingRow As Integer, Sheet As String) As Variant

    ' 先校验参数合理性
    If StartingRow > EndingRow Or WeeksToLoad < 1 Then
        TextFileInfoArrayFPP = "错误：起始行不能大于结束行，且加载周数必须大于0"
        Exit Function
    End If
    
    ' 校验Sheet对应的工作表是否存在
    On Error Resume Next
    Dim targetSheet As Worksheet, refSheet As Worksheet
    Set targetSheet = ThisWorkbook.Sheets(Sheet)
    Set refSheet = ThisWorkbook.Sheets("REF_" & Sheet)
    On Error GoTo 0
    
    If targetSheet Is Nothing Or refSheet Is Nothing Then
        TextFileInfoArrayFPP = "错误：工作表 " & Sheet & " 或 REF_" & Sheet & " 不存在"
        Exit Function
    End If
    
    ' 显示参考工作表
    Sheets("REF_FPP").Visible = xlVisible
    Sheets("REF_UPB").Visible = xlVisible

    Dim count As Integer
    count = 0
    ' 统计差异数据的行数
    For i = StartingRow To EndingRow
        For n = 0 To WeeksToLoad - 1
            If targetSheet.Cells(i, 9 + n).Value <> refSheet.Cells(i, 9 + n).Value Then
                count = count + 1
            End If
        Next n
    Next i

    ' 重定维数组：表头1行 + 差异count行，共count+1行；3列（0-2）
    ReDim FPPArray(count, 2) As Variant

    ' 写入表头
    FPPArray(0, 0) = "Date"
    FPPArray(0, 1) = "PPR Line Item"
    FPPArray(0, 2) = "Value"

    count = 1
    ' 遍历周数
    For i = 0 To WeeksToLoad - 1
        ' 遍历行项
        For n = StartingRow To EndingRow
            If targetSheet.Cells(n, 9 + i).Value <> refSheet.Cells(n, 9 + i).Value Then
                ' 写入日期
                FPPArray(count, 0) = targetSheet.Cells(3, 9 + i).Value
                ' 写入PPR行项
                FPPArray(count, 1) = targetSheet.Cells(n, 1).Value
                ' 写入值
                FPPArray(count, 2) = targetSheet.Cells(n, 9 + i).Value
                count = count + 1
            End If
        Next n
    Next i

    TextFileInfoArrayFPP = FPPArray

    ' 隐藏参考工作表
    Sheets("REF_FPP").Visible = xlHidden
    Sheets("REF_UPB").Visible = xlHidden

End Function

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