関数のクラスへのまとめ方

Question

あるデータソースの行を列固定で順番に確認していって、
①変数にそれまで確認した値を足していく
②空白が出てきたらそのセルに合計値を足す
③合計値を初期化して再度次の行から確認を始める
④それを最終行から2行目まで行う
という関数を作ったのですが、どうも記述量がおおくなってしまいました。

クラスにして使いまわせればと思っているのですがどのようにコードを書いたらいいでしょうか？
そもそもクラスを使うまでもないでしょうか？
以下実際に書いたコードになります。

Sub NewForm_4() '合計時間の計算

Dim Total1, Total2, Total3, Total4, Total5, Total6 As Long
Set nws = Workbooks("実績集計表作成ver2.21").Worksheets("分析シート(作業者)")
LastRow = nws.Cells(Rows.Count, 1).End(xlUp).Row

For i = LastRow To 2 Step -1 '最終行からスタート

    If nws.Cells(i, 11) <> "" Then  'i行目が空白でなければTotalへ数字を足す
    
        'それぞれ求めたい合計値が異なるので変数を分けている
        Total1 = Total1 + nws.Cells(i, 11)  '作業時間
        Total2 = Total2 + nws.Cells(i, 13)  '前作業
        Total3 = Total3 + nws.Cells(i, 14)  '設備稼働時間
        Total4 = Total4 + nws.Cells(i, 15)  '後作業
        Total5 = Total5 + nws.Cells(i, 16)  'インターバル
        Total6 = Total6 + nws.Cells(i, 20)  '総作業時間
        
    ElseIf nws.Cells(i, 11) = "" Then
    '11行目が空白であればTotalの中身を記述する
        nws.Cells(i, 11) = Total1
        nws.Cells(i, 13) = Total2
        nws.Cells(i, 14) = Total3
        nws.Cells(i, 15) = Total4
        nws.Cells(i, 16) = Total5
        nws.Cells(i, 20) = Total6
      
    '変数の初期化
        Total1 = 0
        Total2 = 0
        Total3 = 0
        Total4 = 0
        Total5 = 0
        Total6 = 0
        
    End If

Next i

End Sub

Comments

[sk.exe]

> ①変数にそれまで確認した値を足していく
> ②空白が出てきたらそのセルに合計値を足す
> ③合計値を初期化して再度次の行から確認を始める

空白セルが 2 行以上連続している範囲が現れた場合は
どのようになさりたいのでしょうか。

> そもそもクラスを使うまでもないでしょうか？

記述されてある通りの処理を行なうだけなら
必要だとは思えません。

[Kyo_Konan]

空白セル自体もとのデータソースにプログラムで追加しているものなので、連続することはありません。

クラスを使うまでもなさそうですね。
何枚か似たような若干違うレイアウトのシートを作るのですが

Total1 = Total1 + nws.Cells(i, 11) '作業時間
Total2 = Total2 + nws.Cells(i, 13) '前作業
Total3 = Total3 + nws.Cells(i, 14) '設備稼働時間
Total4 = Total4 + nws.Cells(i, 15) '後作業
Total5 = Total5 + nws.Cells(i, 16) 'インターバル
Total6 = Total6 + nws.Cells(i, 20) '総作業時間

の部分は変わらないので使いまわせたらと思い、質問させてもらいました。

Answer 1

例えば配列変数を使用する場合。

Sub TotalFromBottom()

    '集計列の名前のリスト（カンマ区切り）を定数として宣言
    Const TargetColumns As String = "K,L,M,N,O,P,T"

    Dim ColumnNames As Variant
    Dim TotalArray As Variant

    '集計列の名前のリストを1次元配列として取得
    ColumnNames = Split(TargetColumns, ",", , vbBinaryCompare)
    '各集計列の値の合計値を格納するための配列の再定義
    ReDim TotalArray(LBound(ColumnNames) To UBound(ColumnNames))

    Dim nws As Worksheet
    Dim LastRow As Long

    Set nws = Workbooks("実績集計表作成ver2.21").Worksheets("分析シート(作業者)")
    
    With nws
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    
    Dim RowIndex As Long
    Dim ArrayIndex As Long
    
    For RowIndex = LastRow To 2 Step -1
        '現在の行の最初の集計列のセルが空白セルではない場合
        If IsEmpty(nws.Cells(RowIndex, ColumnNames(LBound(ColumnNames))).Value) = False Then
            '現在の行の各列の値を合計値配列の各要素に加算する
            For ArrayIndex = LBound(ColumnNames) To UBound(ColumnNames)
                TotalArray(ArrayIndex) = TotalArray(ArrayIndex) _
                                       + CDec(nws.Cells(RowIndex, ColumnNames(ArrayIndex)).Value)
            Next
        '現在の行の最初の集計列のセルが空白セルである場合
        Else
            '現在の行の各列のセルに対し、合計値配列の各要素の値を代入し、その要素の値を初期化する
            For ArrayIndex = LBound(ColumnNames) To UBound(ColumnNames)
                nws.Cells(RowIndex, ColumnNames(ArrayIndex)).Value = TotalArray(ArrayIndex)
                TotalArray(ArrayIndex) = Empty
            Next
        End If
    Next

    Set nws = Nothing

End Sub

Comments

[sk.exe]

> 何枚か似たような若干違うレイアウトのシートを作る

> の部分は変わらないので使いまわせたらと思い

複数のワークシートに対して同じサブルーチンを実行なさりたいのであれば、
Worksheet オブジェクト型の引数をプロシージャに定義して、
変数 nws によってワークシートを参照している箇所を適宜書き換えればよろしいでしょう。

[Kyo_Konan]

ご回答ありがとうございました！
Subの引数を指定して参照を定義しなおそうと思います。

Answer 2

単純さを重視してみました。
汎用関数として呼び出したい場合、関数内のシート指定を消して
Sub NewForm_4(nws as worksheet)に変えてcall時にシートを指定してあげる形にすればそのまま使いまわせると思います。

Sub NewForm_4() '合計時間の計算


Dim nws As Worksheet
Set nws = Workbooks("実績集計表作成ver2.21").Worksheets("分析シート(作業者)")

Dim LastRow As Long     '最終行
LastRow = nws.Cells(Rows.Count, 1).End(xlUp).Row

'計算用
Dim BaseRow As Long    '合計計算時の基準にするぎょう番号
BaseRow = LastRow '最初の基準は最終行にする(仕様上空白にならないらしいので)

Dim calcCol  '作業時間~総作業時間を穴埋めするときのfor変数
Dim ColList As Variant
ColList = Array(11, 13, 14, 15, 16, 20) '操作したい列番号のリスト

For i = LastRow To 2 Step -1 '最終行からスタート
    
With nws
    If .Cells(i, 11) = "" Then
    '11行目が空白であればTotalの中身を記述する
        
        'colListの要素数だけ処理を回す
        For Each calcCol In ColList
            .Cells(i, calcCol).Value = WorksheetFunction.Sum(.Range(.Cells(i+1, calcCol), .Cells(BaseRow, calcCol))) 'i+1行目～基準行までの合計をi行目に代入
        Next calcCol
        '基準行を再設定(空白は連続しないとのことなので判断処理は入れない)
        BaseRow = i - 1
    End If
    
End With
Next i

End Sub

Comments

[Kyo_Konan]

回答ありがとうございました。
上手く使いまわせそうです。

[pig_vba]

今回はあとから追加する形になりましたが、そもそも空白行自体がプログラムで固定間隔挿入だったりするなら空白行挿入時に同時に代入する処理にしてしまうのも手だったかもしれませんね。（5行ごとに空白とかならその時点で合計数は計算出来るはずなので）