对列求和

• 正如chris neilsen在你的评论中提到的，循环一个数组可能比使用Sum和Index更有效。
  取列和
• 删除数字检查不会带来太多好处，但反过来，如果有错误值，它也不会失败。

Function GetColumnSum( _
    ByVal Data As Variant, _
    ByVal ColumnIndex As Long) _
As Double

    Dim Value, r As Long, Sum As Double
    
    For r = LBound(Data, 1) To UBound(Data, 1)
        Value = Data(r, ColumnIndex)
        If VarType(Value) = vbDouble Then
            Sum = Sum + Value
        End If
    Next r
    
    GetColumnSum = Sum

End Function

证明

Option Explicit

Private Const rCount As Long = 1000000
Private Const cCount As Long = 10
Private Const ColumnIndex As Long = 7
Private Const MaxNumber As Long = 1000
Private Data As Variant

Sub TestSum()
    
    'Data = Empty
    
    Dim t As Double
    
    If IsEmpty(Data) Then
t = Timer
        ReDim Data(1 To rCount, 1 To cCount)
        Dim r As Long, c As Long
        For r = 1 To rCount
            For c = 1 To cCount
                Data(r, c) = Int(MaxNumber * Rnd + 1)
            Next c
        Next r
Debug.Print "Populated: ", Timer - t
    End If
    
    ' Using the function:
t = Timer
    Debug.Print GetColumnSum(Data, ColumnIndex), Timer - t

    ' Using 'Sum' and 'Index'
t = Timer
    With Application '.WorksheetFunction
        Debug.Print .Sum(.Index(Data, 0, ColumnIndex)), Timer - t
    End With

End Sub

测试结果

Populated:     1.171875 
 500770415     0.328125 
 500770415     0.9296875

示例：

Sub SumOfArray()
    Dim dataArr() As Variant
    Dim totalSum As Double
    
   dataArr = Range("A1:K10").Value
    
    ' Calculate the sum
    totalSum = Application.WorksheetFunction.Sum(Application.WorksheetFunction.Index(dataArr, 0, 11))
    
    ' Display the sum
    MsgBox "The sum of column 11 is: " & totalSum
End Sub