VBA如何使用ReDim Preserve？筛选单元格存数组的代码报错怎么解决

错误点排查 #

• 变量未声明问题

  代码中C_Count、lngV均未提前声明，如果未开启强制变量声明规则，两个变量会被默认初始化为0：要么循环条件K=7 To 0直接不执行，要么第一次执行lngV = lngV +1时因初始值为空触发类型不匹配错误。同时未初始化的变体类型V，首次执行ReDim Preserve也可能触发下标错误。

• 单元格值合法性校验缺失

  如果Cells(9,K)为文本、#N/A/#VALUE!等错误值，直接做数值比较会触发类型不匹配错误。

• 数组边界问题

  VBA数组默认从0开始索引，原写法数组从索引1开始存储，后续遍历如果按0起始取值会出现下标越界；如果没有符合条件的取值，V始终是未初始化的变体，调用时也会报错。

• 工作表引用不明确

  Cells默认指向当前活动工作表，代码运行时如果活动工作表不是目标表，会读取到错误值甚至空值。

修复后的代码 #

Option Explicit ' 放在模块最顶部，强制变量声明

Sub CollectValidValues()
    Dim V As Variant
    Dim lngV As Long
    Dim K As Long
    Dim C_Count As Long
    Dim curVal As Variant
    
    ' 可根据实际需求修改C_Count赋值逻辑，示例为取第9行的最大有效列数
    C_Count = Cells(9, Columns.Count).End(xlToLeft).Column
    
    ' 初始化数组避免后续ReDim Preserve报错
    ReDim V(1 To 0)
    lngV = 0
    
    For K = 7 To C_Count
        curVal = Cells(9, K).Value
        ' 先校验单元格值为合法数值再做大小比较
        If IsNumeric(curVal) Then
            If curVal > 0 And curVal < 100 Then
                lngV = lngV + 1
                ReDim Preserve V(1 To lngV)
                V(lngV) = curVal
            End If
        End If
    Next
    
    ' 无符合条件值时清空数组，避免后续调用出错
    If lngV = 0 Then Erase V
End Sub

调试技巧 #

• 运行前按F8逐行执行，查看本地窗口中的变量取值，可快速定位是C_Count赋值错误、单元格值不符合要求还是数组下标出错
• 如果需要固定操作某张工作表，把Cells(9,K)替换为Sheets("目标工作表名称").Cells(9,K)，避免活动工作表切换导致的取值错误

内容的提问来源于stack exchange，提问作者김다영