累加列

高效

• 一般来说，为了提高效率，如果可能的话，您应该避免任何类型的循环。
• 另一方面，在数组的元素上循环，而不是在范围的单元格上循环，效率要高得多。

Sub AccumulateColumnEfficient()
    
    Const ACC_ADDRESS As String = "I3:I5"
    Const SRC_COLUMN As String = "G"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim arg As Range: Set arg = ws.Range(ACC_ADDRESS)
    Dim srg As Range: Set srg = arg.EntireRow.Columns(SRC_COLUMN)
    
    Dim rCount As Long: rCount = arg.Rows.Count
    
    Dim aData(), sData()
    
    If rCount = 1 Then ' single cell
        ReDim aData(1 To 1, 1 To 1): aData(1, 1) = arg.Value
        ReDim sData(1 To 1, 1 To 1): sData(1, 1) = srg.Value
    Else ' multiple cells
        aData = arg.Value
        sData = srg.Value
    End If
    
    Dim sValue, aValue, r As Long
    
    For r = 1 To rCount
        sValue = sData(r, 1)
        If IsNumeric(sValue) Then
            aValue = aData(r, 1)
            If IsNumeric(aValue) Then
                aData(r, 1) = aValue + sValue
            End If
        End If
    Next r
    
    arg.Value = aData
    
End Sub

• 请注意，如果您确定只有数字，您可以简化循环（没有 If 语句，没有sValue和aValue）

aData(r, 1) = aData(r, 1) + sData(r, 1)

(not建议）

工作表评估

• 在这种情况下，您可以避免一个循环，但它是否会更有效是值得怀疑的（对它进行基准测试（测量时间）并让我们知道），特别是对于成千上万的行。至少你会有更少的代码行（这不应该是选择代码的主要原因）。

Sub AccumulateColumnEvaluate()
    
    Const ACC_ADDRESS As String = "I3:I5"
    Const SRC_COLUMN As String = "G"
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    With ws.Range(ACC_ADDRESS)
        Dim sAddress As String:
        sAddress = .EntireRow.Columns(SRC_COLUMN).Address(0, 0)
        Dim EvalString As String:
        EvalString = "IF(ISNUMBER(" & sAddress & "),IF(ISNUMBER(" _
            & ACC_ADDRESS & ")," & ACC_ADDRESS & "+" & sAddress _
            & "," & ACC_ADDRESS & ")," & ACC_ADDRESS & ")"
        'Debug.Print EvalString
        ' Debug.Print Result:
        ' IF(ISNUMBER(G3:G5),IF(ISNUMBER(I3:I5),I3:I5+G3:G5,I3:I5),I3:I5)
        .Value = .Worksheet.Evaluate(EvalString)
    End With
    
End Sub

• 请注意，如果您确定只有数字，则可以简化（没有 IF 语句）为

EvalString = ACC_ADDRESS & "+" & sAddress

(not建议）