VBA数组处理日期戳去时报错（运行时错误424）求排查

问题背景 #

原本用于移除单元格区域日期时间戳时间部分的VBA代码，在数据量较大时运行缓慢，尝试改用数组优化后，触发运行时错误424（要求对象）。

原低效代码（逐个单元格处理） #

Dim LR As Long
Dim i As Long

LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With shRawData.Range("B" & i)
        .NumberFormat = "dd/mm/yy"
        .Value = DateValue(.Value)
    End With
Next i
End Sub

报错的数组优化代码 #

Dim i As Long
    Dim LastRow As Long
    Dim Temp As Date
    LastRow = Range("A" & Rows.Count).End(xlUp).Row  ' Last row of source worksheet
    
    Dim DateTime As Variant
    DateTime = Application.Transpose(shRawData.Range("B2:B" & LastRow))
    
    For i = LBound(DateTime) To UBound(DateTime)
        DateTime(i).Value = DateValue(DateTime(i).Value)
    Next i
    
    ' Write data to Sheet
    shRawData.Range("B2:B" & LastRow).Value = Application.Transpose(DateTime)

End Sub

错误原因分析 #

1. 数组元素不是对象，误用.Value属性：
   当把单元格区域赋值给Variant数组时，数组存储的是单元格的实际值（比如日期型数据），而非单元格对象。因此DateTime(i).Value这种写法会触发“要求对象”错误，直接访问DateTime(i)即可获取对应值。

2. 处理范围可能不匹配：
   报错代码中LastRow通过A列获取最后一行，若A列和B列的有效数据行数不一致，会导致处理范围出错，应该直接用目标列B获取最后一行。

修正后的高效代码 #

Sub RemoveTimeFromDates()
    Dim i As Long
    Dim LastRow As Long
    Dim DateTime As Variant
    
    ' 获取目标列B的最后一行，确保处理范围准确
    LastRow = shRawData.Range("B" & Rows.Count).End(xlUp).Row
    If LastRow < 2 Then Exit Sub ' 无有效数据时直接退出
    
    ' 将B列数据读入二维数组（单元格区域默认返回二维数组）
    DateTime = shRawData.Range("B2:B" & LastRow).Value
    
    ' 遍历数组处理日期，提取日期部分
    For i = LBound(DateTime, 1) To UBound(DateTime, 1)
        DateTime(i, 1) = DateValue(DateTime(i, 1))
    Next i
    
    ' 一次性将处理后的数据写回单元格
    shRawData.Range("B2:B" & LastRow).Value = DateTime
    ' 统一设置单元格日期格式
    shRawData.Range("B2:B" & LastRow).NumberFormat = "dd/mm/yy"
End Sub

额外优化说明 #

• 直接使用二维数组：避免Application.Transpose的额外开销，单元格区域赋值给数组默认是行×列的二维结构，遍历指定维度即可。
• 批量设置格式：处理完数据后一次性设置单元格格式，比逐个单元格设置效率更高。
• 增加空数据判断：避免无数据时执行无效循环，提升代码健壮性。

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