VBA代码编译报错'Expected array'：DateDiff使用问题求助

错误根源 #

你遇到的"compile error: expected array"是因为变量名和VBA内置函数名重名：你声明了名为dateDiff的Long型变量，但VBA自带的日期差函数也叫DateDiff。编译器会优先把dateDiff识别为你定义的变量，而非内置函数，导致调用时报错。

修正后的代码 #

把变量名改成不冲突的名称（比如diffDays），同时确保调用的是内置DateDiff函数：

Sub Button2_Click() 
Dim ws As Worksheet 
Dim lastRow As Long 
Dim i As Long 
Dim dateAR As Date 
Dim dateAS As Date 
Dim diffDays As Long ' 重命名变量，避免和函数冲突

' Set the worksheet
Set ws = ThisWorkbook.Sheets("Report")

' Find the last row with data in column AR or AS
lastRow = ws.Cells(ws.Rows.count, "AR").End(xlUp).Row
If ws.Cells(ws.Rows.count, "AS").End(xlUp).Row > lastRow Then
    lastRow = ws.Cells(ws.Rows.count, "AS").End(xlUp).Row
End If

' Loop through each row from 10 to lastRow
For i = 10 To lastRow
    If IsDate(ws.Cells(i, "AR").Value) And IsDate(ws.Cells(i, "AS").Value) Then
        dateAR = ws.Cells(i, "AR").Value
        dateAS = ws.Cells(i, "AS").Value
        ' 调用正确的内置DateDiff函数
        diffDays = DateDiff("d", dateAR, dateAS)
        ' Put the date difference in column AV
        ws.Cells(i, "AV").Value = diffDays
    Else
        ' 可选：给非日期的行标记提示
        ws.Cells(i, "AV").Value = "无效日期"
    End If
Next i

End Sub

额外优化建议 #

• 批量计算替代循环：如果数据量较大，直接用公式填充效率更高，比如：

  ws.Range("AV10:AV" & lastRow).Formula = "=IF(AND(ISDATE(AR10),ISDATE(AS10)),DATEDIF(AR10,AS10,""d""),""无效日期"")"
  ' 如需转为值，可添加：ws.Range("AV10:AV" & lastRow).Value = ws.Range("AV10:AV" & lastRow).Value
  

• 关闭屏幕刷新：在代码开头加Application.ScreenUpdating = False，结尾加Application.ScreenUpdating = True，避免循环时屏幕闪烁。

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