使用内置函数计算Range内的数据

Max,Min,Average, Sum

Option Explicit

Sub function_test()
    Dim i As Long, j As Long, num As Long, r As Long, c As Long
    Dim myArray()
    Dim myRange As Range
    '声明
    
    '遍历Range，将符合要求的数据存入数组中
    Set myRange = ActiveSheet.UsedRange
    r = myRange.Rows.Count
    c = myRange.Columns.Count
    num = 1
    
    ReDim myArray(1 To r * c)
    
    For i = 1 To r
        For j = 1 To c
            If IsNumeric(myRange.Cells(i, j)) Then
                myArray(num) = myRange.Cells(i, j)
                num = num + 1
            End If
        Next
    Next
    Debug.Print Application.WorksheetFunction.Sum(myArray)
    Debug.Print Application.WorksheetFunction.Average(myArray)
    Debug.Print Application.WorksheetFunction.Max(myArray)
    Debug.Print Application.WorksheetFunction.Min(myArray)
    
End Sub