如何使用循环简化我的代码

debugcn 发表于 Dev

用户名

我想获取一组数据的小计（平均值，最小值，最大值...）。我已经使用下面的代码实现了目标。如何使用循环简化它？非常感谢！

Sub AddSubs()
    Worksheets("Summary (3)").Activate
    'http://msdn.microsoft.com/en-us/library/office/ff838166(v=office.15).aspx
    Selection.Subtotal GroupBy:=14, Function:=xlAverage, SummaryBelowData:=False, Replace:=False, PageBreaks:=True, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
    Worksheets("Summary (3)").Activate
    Selection.Subtotal GroupBy:=14, Function:=xlStDev, SummaryBelowData:=False, Replace:=False, PageBreaks:=True, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
    Worksheets("Summary (3)").Activate
    Selection.Subtotal GroupBy:=14, Function:=xlMin, SummaryBelowData:=False, Replace:=False, PageBreaks:=True, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
    Worksheets("Summary (3)").Activate
    Selection.Subtotal GroupBy:=14, Function:=xlMax, SummaryBelowData:=False, Replace:=False, PageBreaks:=True, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
    Worksheets("Summary (3)").Activate
    Selection.Subtotal GroupBy:=14, Function:=xlCount, SummaryBelowData:=False, Replace:=False, PageBreaks:=True, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
End Sub

用户名

进一步我的评论。这是简化代码的一种方法

Sub AddSubs()
    Worksheets("Summary (3)").Activate

    Dim constList As Collection
    Set constList = New Collection

    constList.Add (xlAverage)
    constList.Add (xlStDev)
    constList.Add (xlMin)
    constList.Add (xlMax)
    constList.Add (xlCount)

    Dim cnst
    For Each cnst In constList
        Selection.Subtotal GroupBy:=14, Function:=cnst, SummaryBelowData:=False, Replace:=False, PageBreaks:=True, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
    Next

End Sub

甚至更简单（每个@simocos提示）

Sub Main()

    Dim cnst
    For Each cnst In Array(xlAverage, xlStDev, xlMin, xlMax, xlCount)
        Selection.Subtotal GroupBy:=14, Function:=cnst, SummaryBelowData:=False, Replace:=False, PageBreaks:=True, TotalList:=Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39)
    Next

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。