ナレッジ ベース KB0070

think-cellをアクティブにするとExcelのマクロが鈍化する

VBAマクロでパフォーマンス悪化の原因になりうる一般的な問題は、.Select関数を使用すると発生します。Excelでセルを選択するたびに、それぞれのExcelアドインが(think-cellも含まれます)この選択変更イベントについて通知を受けるため、マクロの速度が顕著に遅くなります。特にマクロ レコーダーを使用して作成されたマクロでは、このタイプの問題が発生しがちです。

Microsoftも、パフォーマンスを改善するためにVBAコードで.Selectステートメントを避けるよう推奨しています。

例:.Selectステートメントの使用を回避する方法

以下のシンプルなマクロAutoFillTableを見てみましょう。

Sub AutoFillTable()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim nCount As Integer
    nCount = iRange.Cells.Count
    For i = 1 To nCount
        Selection.Copy
        If iRange.Cells.Item(i).Value = "" Then
            iRange.Cells.Item(i).Range("A1").Select
            ActiveSheet.Paste
        Else
            iRange.Cells.Item(i).Range("A1").Select
        End If
    Next
End Sub

この関数は、セルの範囲を指定するようユーザーに要請する入力ボックスを開きます。この関数は、範囲内のすべてのセルを移動します。空でないセルが見つかった場合は、このセルのコンテンツをクリップボードにコピーします。この関数はクリップボードのコンテンツをその後の空のセルすべてに貼り付けます。

AutoFillTableではクリップボードを使用して、セルのコンテンツをコピーします。このため、この関数は操作される各セルを選択し、どのセルをコピーし、どのセルに貼り付けるのかExcelが把握できるようにする必要があります。推奨される解決策は以下のAutoFillTable2関数に示されています。

Sub AutoFillTable2()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim iCellMaster As Excel.Range
    For Each iCell In iRange.Cells
        If iCell.Value = "" Then
            If Not iCellMaster Is Nothing Then
                iCellMaster.Copy (iCell)
            End If
        Else
            Set iCellMaster = iCell
        End If
    Next iCell
End Sub

AutoFillTable2は前者とは2つの重要な点で異なります。

  1. 最も重要なのは、セルを選択する必要がないという点です。代わりに、空でないセルが見つかった場合、このセルはiCellMaster変数に保存されます。その後、空のセルが見つかるたびに、iCellMasterのコンテンツはすべてiCellにコピーされます。
  2. Visual Basic言語機能「For Each … Next」を使用して範囲内のすべてのセルにアクセスします。この時点ですでに読みやすさが改善されています。