Excel VBA溢出错误排查：处理23.5万行日期计算时出错

我正在编写一个日期比较函数，执行代码时在For ligne = 2 To last_row循环行出现溢出错误。查阅资料得知该错误常因使用Integer而非Long导致，但我认为并非此情况。需处理约23.5万行数据，急于在月底前完成函数开发，恳请告知错误原因。我的代码如下：

Public Function date_dif(sheet As String, number As Integer, colonnetarget As Integer, colonnedate1 As Integer, Optional colonnedate2 As Integer)

Dim last_row As Long
Dim sht, sht2 As Worksheet
Dim ligne As Integer
Dim formule As String
Dim crit1, crit2 As String

Set sht = ThisWorkbook.Worksheets(sheet)

last_row = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

If colonnedate2 <> 0 Then
    For ligne = 2 To last_row
        sht.Cells(ligne, colonnetarget).Value = DateDiff("d", sht.Cells(ligne, colonnedate1).Value, sht.Cells(ligne, colonnedate2).Value)
    Next
Else
    For ligne = number To last_row
        sht.Cells(lignxe, colonnetarget).Value = DateDiff("d", sht.Cells(ligne, colonnedate1).Value, Now())
    Next
End If


End Function

错误原因及修复方案 #

• 核心问题：Integer类型溢出
  VBA中Integer的取值范围是-32768到32767，你要处理23.5万行数据，ligne被定义为Integer变量，当循环到32768行时必然触发溢出错误。这就是问题的根源，之前的判断有误。
• 次要问题：变量拼写错误
  代码分支里的sht.Cells(lignxe, colonnetarget).Value存在拼写错误，lignxe应为ligne，这会导致额外的运行时错误，需要修正。
• 效率优化建议
  23.5万行逐行赋值速度极慢，建议改用数组批量处理：
  1. 先把日期数据读入数组
  2. 在数组内完成日期差计算
  3. 一次性将结果写入目标列

修改后的核心逻辑示例：

Dim dateArr As Variant
Dim resultArr As Variant
dateArr = sht.Range(sht.Cells(2, colonnedate1), sht.Cells(last_row, colonnedate2)).Value
ReDim resultArr(1 To UBound(dateArr, 1), 1 To 1)

If colonnedate2 <> 0 Then
    For ligne = 1 To UBound(dateArr, 1)
        resultArr(ligne, 1) = DateDiff("d", dateArr(ligne, 1), dateArr(ligne, colonnedate2 - colonnedate1 + 1))
    Next
Else
    For ligne = number - 1 To UBound(dateArr, 1)
        resultArr(ligne, 1) = DateDiff("d", dateArr(ligne, 1), Now())
    Next
End If

sht.Cells(2, colonnetarget).Resize(UBound(resultArr, 1), 1).Value = resultArr

同时，建议把所有涉及行号、列号的参数（number、colonnetarget等）都改为Long类型，彻底避免Integer溢出风险。

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