数组vba求和

使用 ParamArray ,它允许您传递任意数量的参数:

Function SumVisible(ParamArray WorkRng() As Variant) As Double
    Dim i as Integer
    Dim rng As Range
    Dim c As Range
    Dim total As Double

    For i = LBound(WorkRng) To UBound(WorkRng)
        Set rng = WorkRng(i)
        For Each c In rng
            If c.Rows.Hidden = False And c.Columns.Hidden = False Then
                total = total + c.Value
            End If
        Next c
    Next i
    SumVisible = total
End Function

您可以使用以下内容:

Public Function SumVisible(ByVal WorkRng As Range) As Double
    Dim col As Range
    For Each col In WorkRng.Columns
        If Not col.EntireColumn.Hidden Then SumVisible = SumVisible + Application.WorksheetFunction.Aggregate(9, 7, col)
    Next col
End Function

聚合:首先为sum指定sum参数,例如9作为sum,然后其他条件参数(例如7)为ignore hidden rows和error values。

SUBTOTAL函数也可以用于自动忽略隐藏行。

您可以在此处查看参数列表:

1. AGGREGATE
2. SUBTOTAL