変数への代入処理を高速化したい

Question

下記のようなエクセルVBAを作成しています。for next文について営業日が多くなればなるほど、処理が重くなるのでうまく纏めて高速化を図りたいと考えています。うまくまとめる方法をご教授いただけませんでしょうか？

        '変数をセット
        Dim a0, a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19 As Long
               
        '変数に値を入力
        a0 = ActiveCell.Offset(0, -2).Value '当日の値
        Dim workday As Long
        Dim code As Long
        workday = ActiveCell.Offset(0, -11).Value
        code = ActiveCell.Offset(0, -9).Value
        
        Dim i As Long, B As Variant
        B = Range("A1:J295680")
        For i = 1 To 295680
            If B(i, 1) = workday - 1 And B(i, 3) = code Then a1 = B(i, 10) '1営業日前の値
            If B(i, 1) = workday - 2 And B(i, 3) = code Then a2 = B(i, 10) '2営業日前の値
            If B(i, 1) = workday - 3 And B(i, 3) = code Then a3 = B(i, 10) '3営業日前の値
            If B(i, 1) = workday - 4 And B(i, 3) = code Then a4 = B(i, 10) '4営業日前の値
            If B(i, 1) = workday - 5 And B(i, 3) = code Then a5 = B(i, 10) '5営業日前の値
            If B(i, 1) = workday - 6 And B(i, 3) = code Then a6 = B(i, 10) '6営業日前の値
            If B(i, 1) = workday - 7 And B(i, 3) = code Then a7 = B(i, 10) '7営業日前の値
            If B(i, 1) = workday - 8 And B(i, 3) = code Then a8 = B(i, 10) '8営業日前の値
            If B(i, 1) = workday - 9 And B(i, 3) = code Then a9 = B(i, 10) '9営業日前の値
            If B(i, 1) = workday - 10 And B(i, 3) = code Then a10 = B(i, 10) '10営業日前の値
            If B(i, 1) = workday - 11 And B(i, 3) = code Then a11 = B(i, 10) '11営業日前の値
            If B(i, 1) = workday - 12 And B(i, 3) = code Then a12 = B(i, 10) '12営業日前の値
            If B(i, 1) = workday - 13 And B(i, 3) = code Then a13 = B(i, 10) '13営業日前の値
            If B(i, 1) = workday - 14 And B(i, 3) = code Then a14 = B(i, 10) '14営業日前の値
            If B(i, 1) = workday - 15 And B(i, 3) = code Then a15 = B(i, 10) '15営業日前の値
            If B(i, 1) = workday - 16 And B(i, 3) = code Then a16 = B(i, 10) '16営業日前の値
            If B(i, 1) = workday - 17 And B(i, 3) = code Then a17 = B(i, 10) '17営業日前の値
            If B(i, 1) = workday - 18 And B(i, 3) = code Then a18 = B(i, 10) '18営業日前の値
            If B(i, 1) = workday - 19 And B(i, 3) = code Then a19 = B(i, 10) '19営業日前の値
            
        Next i
　　　　ActiveCell.Offset(0, 2) = Application.WorksheetFunction.Average(a0, a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19) 

Answer 1

当日の値も同じ範囲内にあると勝手に仮定してますが、
選択した行の日付を起点に19営業日前までの平均をとりたいのだとすると、
AVERAGEIFS関数を使ってこんな風に書けるのではないかと想像しました。
（場合によってはVBAにせずともセル上に数式を書くだけで用を足したりするかも、とか）

    Dim workday As Long
    Dim code As Long
    workday = ActiveCell.Offset(0, -11).Value
    code = ActiveCell.Offset(0, -9).Value

    Dim R As Range
    Set R = Range("A1:J295680")
    With Application.WorksheetFunction
        ActiveCell.Offset(0, 2) = .AverageIfs(.Index(R, 0, 10), .Index(R, 0, 3), code, .Index(R, 0, 1), "<=" & workday, .Index(R, 0, 1), ">=" & workday - 19)
    End With

Comments

[K-iwata]

私のコードから実施しようとしている内容の意図をくみ取っていただきありがとうございます。Agebareifs関数にIndexを組み合わせる仕方は大変勉強になり、実際に一番高速化できました。

Answer 2

「for next文について営業日が多くなればなるほど、処理が重くなる」とのことですが、
提示のコードではFor i = 1 To 295680と処理数は固定なので関係ないですね。

入力してある範囲だけを対象にするようにすればいいでしょう。CurrentRegionでデータ範囲を取得できます。これで無駄な処理を省略できます。

        B = Range("A1").CurrentRegion
        For i = 1 To UBound(B)

あとy-waiwaiさんも回答されているように複数の変数を配列にすればコードはシンプルになります。

配列利用サンプルコード

    '配列
    Dim a(0 To 19) As Long
    '配列に値を入力
    a(0) = ActiveCell.Offset(0, -2).Value    '当日の値
    Dim workday As Long
    Dim code As Long
    workday = ActiveCell.Offset(0, -11).Value
    code = ActiveCell.Offset(0, -9).Value

    Dim i As Long, B As Variant
    B = Range("A1").CurrentRegion.Value
    For i = 1 To UBound(B)
        If B(i, 3) = code Then
            Dim diff As Long
            diff = workday - B(i, 1)
            If diff > 0 And diff < 20 Then a(diff) = B(i, 10)
        End If
    Next i
    ActiveCell.Offset(0, 2) = Application.WorksheetFunction.Average(a)

また、営業日がどんだけあろうと、コードを見るかぎりは対象は当日から19営業日前までの20個だけなので、データが営業日の昇順(あるいは降順)になっているなら、Index関数で開始位置を取得してそこから20個分のデータを取得すればそもそもForループ自体が不要になりますね。
データ例の提示がないので推測ですが。

Answer 3

みんな勘違いするのですが、VBAにおいては
Dim a0, a1, a2 As Long
という書き方だと、
Dim a0 As Variant, a1 As Variant, a2 As Long
と書いたのと同じ意味になります。

Answer 4

ぱっと見て思う事は

If B(i, 1) = workday - 1 And B(i, 3) = code Then a1 = B(i, 10) '1営業日前の値

の And B(i, 3) = code の同じ判定がずらっと並んでいますが、ここが成立しないと代入しないなら、最初に別のIfで判定してしまえばよいと思います。
If文をAndで括って判定する場合、If～Thenまで間の全ての条件を判定するのでここで無駄に判定が増えます。

    For i = 1 To 295680
        If B(i, 3) = code Then
            If B(i, 1) = workday - 1 Then a1 = B(i, 10) '1営業日前の値

            …

            If B(i, 1) = workday - 19 Then a19 = B(i, 10) '19営業日前の値
        End If
    Next

これで、If判定の回数が半分程度になります。
データの並びによっては、If B(i, 1) = workday - 1～19 のIf判定も、If B(i, 1) = workday - 1だけでよいかもしれませんし、hatena19さんの回答のようにForの処理自体がそもそも要らなくなる可能性もあります。

Comments

[K-iwata]

シンプルにわかりやすいアドバイスありがとうございました。

Answer 5

変数をたくさん作るんでなくて、配列にしてしまいませんか