VBA技術解説
Withステートメントの実行速度と注意点

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2019-06-06

Withステートメントの実行速度と注意点


マクロ VBA With

マクロVBAにおいてWithステートメントはとても重要です、
可読性(読みやすさ、理解しやすさ)、実行速度においては、その役割はとても大きいものになります。


本記事では、Withステートメントを使うか使わないかでのマクロ実行速度の差に焦点を絞って検証します。

まれにメモリ云々の話が出ることもあるようですが、
速度に現れないメモリの違いは今日のPCにおいては考える必要はないでしょう。

今回は技術解説というより検証結果の掲載が主になっています。
実務においてのVBAコードを書く際の参考にしていただければよいと思います。

VBAコードの書き方について迷うことがあれば、
今回の結果と、以下のマクロVBA入門を参考にしてください。

Withテートメントについて
第51回.Withステートメント|マクロVBA入門
Withステートメントを使う事で、Withに指定したオブジェクトに対してオブジェクト名を再度記述することなく、プロパティやメソッドを記述することができます。文章で例えて言えば、主語を一度書いたら、その後は主語を省略するような書き方になります。

第52回.オブジェクト変数とSetステートメント|マクロVBA入門
変数のデータ型の説明において、Object…オブジェクト型 というのがあった事を覚えているでしょうか。数値や文字ではなく、オブジェクトを入れる変数がオブジェクト変数です。オブジェクトと言っても、いろいろなものがあります。

マクロVBAのテストコード(Worksheet)

一番よく使うだろうWorksheetで検証します。



Option Explicit

Sub TestRun()
  Dim i As Long
  Dim ary(1 To 10, 1 To 6) As Double
  For i = 1 To 10
    DoEvents
    ary(i, 1) = test1
    DoEvents
    ary(i, 2) = test2
    DoEvents
    ary(i, 3) = test3
    DoEvents
    ary(i, 4) = test4
    DoEvents
    ary(i, 5) = test5
    DoEvents
    ary(i, 6) = test6
  Next
  Range("B2:G11") = ary
  MsgBox "完了"
End Sub

Function test1() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  For i = 1 To 1000000
    v = Worksheets(1).Cells(i, 1).Value
    v = Worksheets(1).Cells(i, 2).Value
    v = Worksheets(1).Cells(i, 3).Value
  Next
  
  test1 = Timer - t
End Function

Function test2() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  With Worksheets(1)
    For i = 1 To 1000000
      v = .Cells(i, 1).Value
      v = .Cells(i, 2).Value
      v = .Cells(i, 3).Value
    Next
  End With
  
  test2 = Timer - t
End Function

Function test3() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  For i = 1 To 1000000
    v = Sheet1.Cells(i, 1).Value
    v = Sheet1.Cells(i, 2).Value
    v = Sheet1.Cells(i, 3).Value
  Next
  
  test3 = Timer - t
End Function

Function test4() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  With Sheet1
    For i = 1 To 1000000
      v = .Cells(i, 1).Value
      v = .Cells(i, 2).Value
      v = .Cells(i, 3).Value
    Next
  End With
  
  test4 = Timer - t
End Function

Function test5() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  Dim ws As Worksheet
  Set ws = Worksheets(1)
  For i = 1 To 1000000
    v = ws.Cells(i, 1).Value
    v = ws.Cells(i, 2).Value
    v = ws.Cells(i, 3).Value
  Next
  
  test5 = Timer - t
End Function

Function test6() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  Dim ws As Worksheet
  Set ws = Worksheets(1)
  With ws
    For i = 1 To 1000000
      v = .Cells(i, 1).Value
      v = .Cells(i, 2).Value
      v = .Cells(i, 3).Value
    Next
  End With
  
  test6 = Timer - t
End Function
※test3,test4のSheet1はWorksheets(1)のオブジェクト名です。

実行速度の実測値

【test1】
Worksheets
【test2】
With Worksheets
【test3】
Sheet1
【test4】
With Sheet1
【test5】
Worksheet変数
【test6】
With Worksheet変数
1 17.29 14.68 9.10 9.20 9.07 9.09
2 17.07 14.67 9.10 9.08 9.07 9.10
3 17.20 14.70 9.09 9.08 9.06 9.08
4 17.89 14.69 9.10 9.05 9.07 9.09
5 17.22 14.69 9.10 9.07 9.05 9.07
6 17.58 14.77 9.19 9.09 9.06 9.07
7 17.20 14.66 9.10 9.07 9.05 9.07
8 17.19 14.66 9.09 9.09 9.07 9.09
9 17.20 14.67 9.09 9.08 9.06 9.10
10 17.19 14.66 9.09 9.06 9.05 9.09
平均 17.30 14.69 9.11 9.09 9.06 9.08
※数値は秒数です。

秒以下は誤差として考えたほうが良いでしょう。
つまり、
test3~test6は同じであるという事です。
ただし、
test1もtest2も、少し時間がかかっているとはいえ、
1,000,000×3回の結果である事を忘れないでください。

test2がtest3以降より遅い理由の合理的説明は・・・
WithステートメントのVBA内部の処理説明は難しく、そもそも推測でしか語ることができません。
ここでは、中途半端に推測で語ることはせずに、この結果をそのままお伝えします。

少なくとも、Withが早いとかWithを使わないと遅いという事ではないという事は結果から明らかです。
Worksheets()の記述が遅いのであって、Withが直接関係しているのではないという事です。

マクロVBAのテストコード(Range)

頻繁に使うRangeでも検証します。

Option Explicit

Sub TestRun2()
  Dim i As Long
  Dim ary(1 To 10, 1 To 4) As Double
  For i = 1 To 10
    DoEvents
    ary(i, 1) = test1
    DoEvents
    ary(i, 2) = test2
    DoEvents
    ary(i, 3) = test3
    DoEvents
    ary(i, 4) = test4
  Next
  Range("B2:E11") = ary
  MsgBox "完了"
End Sub

Function test1() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  For i = 1 To 1000000
    v = Range("A1").Value
    v = Range("A1").Value
    v = Range("A1").Value
  Next
  
  test1 = Timer - t
End Function

Function test2() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  With Range("A1")
    For i = 1 To 1000000
      v = .Value
      v = .Value
      v = .Value
    Next
  End With
  
  test2 = Timer - t
End Function

Function test3() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  Dim rng As Range
  Set rng = Range("A1")
  For i = 1 To 1000000
    v = rng.Value
    v = rng.Value
    v = rng.Value
  Next
  
  test3 = Timer - t
End Function

Function test4() As Double
  Dim i As Long
  Dim v As Variant
  Dim t As Double
  t = Timer
  
  Dim rng As Range
  Set rng = Range("A1")
  With rng
    For i = 1 To 1000000
      v = .Value
      v = .Value
      v = .Value
    Next
  End With
  
  test4 = Timer - t
End Function

実行速度の実測値



【test1】
.Range("A1")
【test2】
With .Range("A1")
【test3】
Range変数
【test4】
With Range変数
1 18.00 4.30 4.29 4.29
2 17.78 4.29 4.29 4.29
3 17.74 4.29 4.31 4.29
4 17.76 4.29 4.29 4.29
5 17.73 4.28 4.29 4.29
6 17.74 4.29 4.30 4.30
7 17.74 4.28 4.29 4.29
8 17.78 4.28 4.29 4.29
9 17.77 4.29 4.29 4.29
10 17.75 4.29 4.29 4.30
平均 17.78 4.29 4.29 4.29
※数値は秒数です。

注目すべき点は、
test2以降ではWorksheetでの結果に比べて短時間で済んでいるという事と、
test1だけが極端に遅いという事です。

ごく簡単に説明すると、
Worksheets()、これはコレクションからインデックスを指定してオブジェクトにアクセスしています。
Rangeでのテストコードでは、これが無い分速いという事です。
そして、Range()やCells()も同様で、コレクションとしてのRangeオブジェクトから特定のセルにアクセスしていますので、この部分に時間がかかります。
これらをWithまたはSetで1回で済ませることで、高速化されているという事になります。

VBA高速化を単純化していえば、
Worksheets()
Range()
Cells
これらの記述が少なければ少ないほど高速化されるという事になります。

Withステートメントの注意点

Forとのネスト
For i = 1 To 1000000
  With Worksheets(1)
    ・・・
  End With
Next

これではWithによる速度効果はほとんどありません。

Withステートメントが1,000,000回実行されるので、これでは実行速度は遅くなってしまいます。

対象オブジェクトのズレ
Sub sample2()
  With Range("A1")
    Debug.Print .Address
    Rows(1).Insert
    Debug.Print .Address
  End With
End Sub

$A$1
$A$2
このようにイミディエイトウインドウに出力されます。
WithでRange("A1")と書かれていても、必ずしもA1セルを参照しているとは限りません。

Withにおいてコレクションの中からインデックスまたは名称でオブジェクトを指定している場合、
Withの中で、オブジェクトの位置をずらすようなVBAコードは書かないほうが良いでしょう。

With内の変数宣言
With Worksheets(1)
  Dim hoge As Long
  ・・・
End With

この変数hogeは、End Withの下でも使用できてしまいまい。
Withに限らず、
VBAではブロック構文内の変数宣言が、ブロック外に対して隠蔽されません。

VBAにおけるブロック内での変数宣言の是非はひとまず置いておくとしても、
ブロック構文内で宣言した変数は、ブロック外では使用しないようにしましょう。

最後に

先にも書きましたが、1,000,000×3回の結果である事を改めて強調しておきます。
数千行から数万行程度なら、どんな書き方をしたとしても体感として速度差を感じることはほとんどないでしょう。
その程度のデータ量で時間がかかっている場合は、他の原因によるものだという事です。

とはいえ、
[Worksheets().]Rangeを並べた書き方はWithステートメントを使うようにした方が良い事は間違いありません。

Withステートメントの使い方の基本的な考え方としては、
可読性や記述の簡便さを主に考えれば良いでしょうという事になります。



同じテーマ「VBA技術解説」の記事

エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
WorksheetFunction.Matchで配列を指定した場合の制限について
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
大量データで処理時間がかかる関数の対処方法(SumIf)
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
Withステートメントの実行速度と注意点
IfステートメントとIIF関数とMax関数の速度比較


新着記事NEW ・・・新着記事一覧を見る

SQL関数と演算子|SQL入門(12月1日)
データの取得:集約集計、並べ替え(DISTINKT,GROUP BY,ORDER BY)|SQL入門(11月30日)
データの取得:条件指定(SELECT,WHERE)|SQL入門(11月29日)
データの挿入:バルクインサート|SQL入門(11月28日)
データの挿入(INSERT)と全削除|SQL入門(11月26日)
テーブル名変更と列追加(ALTER TABLE)とテーブル自動作成|SQL入門(11月25日)
テーブルの作成/削除(CREATE TABLE,DROP TABLE)|SQL入門(11月24日)
データベースに接続/切断|SQL入門(11月23日)
SQLiteのインストール|SQL入門(11月22日)
SQL入門:VBAでデータベースを使う|エクセルの神髄(11月22日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • Withステートメントの実行速度と注意点

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
    掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ