Excel VBA提取日期区域日数并求最大值的代码优化需求

需求说明 #

开发月度报表宏时，需从Excel的A1至最后一行的单元格区域提取日期的日数与月数，其中日期采用英国格式（dd/mm/yyyy），核心目标是高效提取日数并获取最大值，月数提取逻辑可直接参照日数实现。现有代码存在固定行数、循环效率低的问题，以下是优化方案。

现有代码 #

Sub TestR1()
    Dim MonthArray(1 To 4) As Variant
    Dim x As Double
    Dim i As Byte
    Dim Rng As Range
    Dim ExDate As Date
    Dim Mx As Long
   
    Set Rng = Range("A1:A4")
             
    For i = 1 To 4
        Set Rng = Range("A" & i)
        ExDate = VBA.CDate(Range("A" & i).Value)
        x = VBA.Day(ExDate)
        MonthArray(i) = x
    Next i
   
    Mx = Excel.WorksheetFunction.Max(MonthArray)
   
End Sub

优化方案 #

方案1：动态区域+批量数组处理（高效稳定）

自动识别数据最后一行，批量读取数据到数组后处理，减少与Excel单元格的频繁交互，大幅提升效率：

Sub GetMaxDayAndMonth()
    Dim lastRow As Long
    Dim dateRange As Range
    Dim dateArr As Variant
    Dim dayArr As Variant
    Dim maxDay As Long
    Dim monthArr As Variant ' 用于存储月数
    
    ' 动态获取A列最后一行有数据的行号
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    If lastRow < 1 Then Exit Sub ' 无数据时直接退出
    
    ' 定义目标区域并读取到数组
    Set dateRange = Range("A1:A" & lastRow)
    dateArr = dateRange.Value
    
    ' 初始化存储日数和月数的数组
    ReDim dayArr(1 To UBound(dateArr, 1))
    ReDim monthArr(1 To UBound(dateArr, 1))
    
    ' 批量提取日数和月数
    Dim i As Long
    For i = 1 To UBound(dateArr, 1)
        ' 确保英国格式日期正确转换（若单元格格式已设为日期可直接用CDate）
        Dim d As Date
        d = DateSerial( _
            Year(CDate(dateArr(i, 1))), _
            Month(CDate(dateArr(i, 1))), _
            Day(CDate(dateArr(i, 1))) _
        )
        dayArr(i) = Day(d)
        monthArr(i) = Month(d)
    Next i
    
    ' 获取日数最大值
    maxDay = WorksheetFunction.Max(dayArr)
    
    ' 可根据需求输出或使用结果，示例：
    MsgBox "日数最大值：" & maxDay
    ' 月数数组可同理进行后续处理
End Sub

方案2：用Evaluate直接调用工作表函数（极简实现）

如果数据格式规范，可直接用工作表函数的数组公式实现，无需循环：

Sub GetMaxDay_Evaluate()
    Dim lastRow As Long
    Dim maxDay As Long
    Dim maxMonth As Long ' 月数最大值示例
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    If lastRow < 1 Then Exit Sub
    
    ' 提取日数并求最大值（利用DAY函数和MAX函数的数组特性）
    maxDay = Evaluate("MAX(DAY(A1:A" & lastRow & "))")
    ' 提取月数并求最大值
    maxMonth = Evaluate("MAX(MONTH(A1:A" & lastRow & "))")
    
    MsgBox "日数最大值：" & maxDay & vbCrLf & "月数最大值：" & maxMonth
End Sub

关键优化点说明 #

• 动态区域：用Cells(Rows.Count, "A").End(xlUp).Row自动获取最后一行，无需固定行数
• 批量数组：将区域数据一次性读取到数组，比逐个读取单元格快数倍
• 日期格式兼容：用DateSerial或CDate确保英国格式（dd/mm/yyyy）的日期正确转换，避免因系统区域设置导致的识别错误
• 极简实现：方案2利用Excel内置函数的数组能力，一行代码完成提取+求最大值，适合数据格式规范的场景

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