空腹おやじのログと備忘録

VBA(主にExcel)でいろいろな実験的な事とか、Linuxのコマンドとか設定とかについて忘れないように、あれこれと・・・

【VBA】Excelはアクティブシートが変わると、処理時間が変わる場合があるようだ

いつもの如く、怪しげな事(?)をゴニョゴニョとやっていて、
「あれ、さっきより処理速くね?」となったので・・・

経緯

ワークシートに50万件ほどのデータがあって、そいつにフィルターを掛けて外しての繰り返しをやっていたら、何かの拍子に処理時間が早くなったんですよ。
調べてみたら、フィルター処理を行う時に、

フィルターを適用するワークシートをアクティブにしているより、
フィルターを適用しないワークシートをアクティブにしておいた方が

速かったんですよ。

もちろん

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

は適用した上ですよ。
ブックには、式が1つもないので、下2つは、ほとんど意味がないような気がするけど、お約束なので・・・

結果

とりあえず、というか、いきなり結果から。
速度順に並べたが、「フィルターを適用するシート以外のシート」をアクティブにしたケースが全て上位になっている。率にして、15%以上の差が出ており、誤差と言える値ではなさそうである。
「平均処理時間」は、5回実行した結果の平均値。

アクティブシートScreenUpdatingWindowState平均処理時間(秒)備  考
非フィルター適用シートFalsexlMinimized16.31
非フィルター適用シートFalsexlNormal16.32Worksheet.Visible = False
非フィルター適用シートFalsexlNormal16.35
非フィルター適用シートTruexlMinimized16.46
非フィルター適用シートTruexlNormal16.65Worksheet.Visible = False
非フィルター適用シートTruexlNormal16.67
フィルター適用シートFalsexlNormal18.85
フィルター適用シートFalsexlMinimized18.86
フィルター適用シートTruexlMinimized21.04
フィルター適用シートTruexlNormal25.13

コード

Private Const KEY_COL           As Long = 1

Private Const DATA_SHEET_NAME   As String = "Data"
Private Const KEY_SHEET_NAME    As String = "Key"

Private Const HEADER_ROWS       As Long = 1

Private Const MAX_KEYS_COUNTS   As Long = 50
Private Const MAX_DATAS_COUNTS  As Long = 500000


Public Sub queryByAutoFilter()

    Dim ws          As Worksheet
    Dim r           As Range
    Dim lEndRow     As Long
    Dim sgStart     As Single
    Dim sgStop      As Single
    Dim vKeys()     As Variant
    Dim i           As Long

    Dim lMatchCount As Long

With Application
    .ScreenUpdating = False
'    .ScreenUpdating = True
    .EnableEvents = False
    .Calculation = xlCalculationManual

    .WindowState = xlNormal
'    .WindowState = xlMinimized
End With

sgStart = Timer

    Set ws = Worksheets(DATA_SHEET_NAME)

    lEndRow = MAX_DATAS_COUNTS + HEADER_ROWS

    '検索対象キー値取得
    Call getKeys(vKeys)

    ReDim sKeys(LBound(vKeys) To UBound(vKeys))

    For i = LBound(vKeys) To UBound(vKeys)
        sKeys(i) = CStr(vKeys(i))
    Next i

    With ws
        'Filter対象レンジを設定
        Set r = .Range(.Cells(1, KEY_COL), .Cells(lEndRow, KEY_COL))
    End With

    With r
        For i = LBound(sKeys) To UBound(sKeys)
            'AutoFilterクリア
            .AutoFilter

            'Filter実行
            .AutoFilter Field:=1, Criteria1:=sKeys(i), Operator:=xlFilterValues

            If .SpecialCells(xlCellTypeVisible).Count > HEADER_ROWS Then
                lMatchCount = lMatchCount + .SpecialCells(xlCellTypeVisible).Count - HEADER_ROWS
            End If
        Next i

        'AutoFilter解除
        .AutoFilter
    End With

sgStop = Timer

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic

    .WindowState = xlNormal
End With

    Debug.Print Format$(sgStop - sgStart, "0.00")

End Sub


Private Sub getKeys(ByRef vKeys() As Variant)

    Dim lEndRow   As Long
    Dim lItems  As Long
    Dim lValue  As Long
    Dim i       As Long

    ReDim vKeys(MAX_KEYS_COUNTS - 1)

    lEndRow = MAX_KEYS_COUNTS + HEADER_ROWS

    With Worksheets(KEY_SHEET_NAME)
        For i = HEADER_ROWS + 1 To lEndRow
            vKeys(i - HEADER_ROWS - 1) = .Cells(i, 1).Value
        Next i
    End With

End Sub

まとめ(のようなもの)

  • フィルターを適用するワークシートをアクティブにしない方が速かった。
  • ScreenUpdateing = False は、フィルターを適用するワークシートがアクティブな場合には、効果が大きいが、そうでない場合は、効果がほとんど認められなかった。
  • Windowを最小化しても、あまり高速化へのメリットはなかった。

今回のテストでは、ScreenUpdatingの状態に関わらず、フィルターを適用するワークシートをアクティブにしない方が速くなっている。

では、フィルターを適用するワークシートが「表示されていなければ、いいのか?」というと、
「Windowを最小化」により該当ワークシートを非表示状態にして効果が認められるのは、
フィルターを適用するワークシートをアクティブにして、かつ ScreenUpdating = True の場合だけだったので、表示されていなければ、効果があるわけではないようだ。

一方で、「該当ワークシートを非表示にする」というのは、別のワークシートをアクティブ化するという事と同等なので、これは「効果はある」ことになる。


結局の所、ScreenUpdatingがFalseになっていても、フィルターを適用するワークシートがアクティブの場合には、アクティブシートであるが故の何かが、裏で動いていると考えるのが妥当なのかもしれない。
というか、私の頭ではそれしか思い浮かばない。
誰か、わかる方がいらっしゃいましたら教えて下さい。



フィルター以外でも効果があるのかは、要確認。
でも、きっと・・・
忘れて何もしないような気がする。

追記

行削除(2019/5/5)

行の削除処理で比較してみた。

Public Sub deleteRows(ByVal acsh As Long, ByVal doScreenUpdating As Boolean)

    Const END_ROW   As Long = 10000

    Dim ws      As Worksheet
    Dim sgStart As Single
    Dim sgStop  As Single
    Dim i       As Long

sgStart = Timer

    Application.ScreenUpdating = doScreenUpdating

    If acsh = 1 Then
        Worksheets(1).Activate
    Else
        Worksheets(2).Activate
    End If

    Set ws = Worksheets(1)

    With ws
        .Cells.ClearContents

        .Cells(1, 1).Value = 1
        .Cells(2, 1).Value = 2

        .Range("A1:A2").AutoFill Destination:=.Range("A1:A" & CStr(END_ROW)), Type:=xlFillDefault

        For i = END_ROW To 1 Step -2
            .Rows(i).Delete
        Next i
    End With

    Worksheets(1).Activate

    Application.ScreenUpdating = True

sgStop = Timer

    Debug.Print "Active Sheet:" & CStr(acsh), Format$(sgStop - sgStart, "0.00")

End Sub

結果

Active Sheet: 1 ScreenUpdating: True Time[sec]: 3.64
Active Sheet: 1 ScreenUpdating: False Time[sec]: 0.97
Active Sheet: 2 ScreenUpdating: True Time[sec]: 0.89
Active Sheet: 2 ScreenUpdating: False Time[sec]: 0.84

やっぱり、処理するシート以外をアクティブシートにした方が速い。
その場合には、ScreenUpdating = False としても、大きな効果は認められない。(見えない所で処理しているのだから、当たり前といえば当たり前か・・・)
結構使えるかもしれない・・・