LoginSignup
2
2

More than 1 year has passed since last update.

@sasio

VBAでセル指定の高速化

Last updated at Posted at 2021-06-13

3000文字Tipsに参加しています →3000文字超えてた

はじめに

  • 書いてあること
    • Cells vs Range
    • Value vs Value2 vs なし
    • With vs Dim/Set
  • シート全体をVBAの二次元配列へ格納しておくことは考えない
    • あくまでもセル指定の速度について考える
  • おま環かもしれないので，試用してから使用すること
  • ScreenUpdatingなどの対策は，別途適用すること
    • 情報量が多くてオススメの記事↓

考察対象

  • アクティブシートの任意セルの値を取得
    • 「A1」など，何かしらでセル位置の指定をする
    • 対象セルは未使用セルとする
  • 取得した値はVariant型に格納
    • 各自のやりたい処理に合わせて読み替えてください
  • セルの値取得を、複数セルに流用する想定
    • とある1セルだと決め打ちした高速化はしない
    • セルの名前も付けない

計測方法

参考コード↓

  • Moduleに記述して計測した
    • Workbook,Worksheetでは，Runの引数を"ThisWorkbook.test","Sheet1.test"にすべし
  • test1()をコピペしてtest2,test3,...を作ると比較できる
  • 比較個数や処理回数はConstで操作する
  • <反復対象>部分で，A1の値を取得するが，A2,A3,...と値取得が複数ある想定
  • ブック内には，オブジェクト名Sheet1/シート名Sheet1のワークシートが1つだけとする
  • 計測対象内の変数の型を軽くし，影響を小さくした
    • Timerは返り値がSingleなので，変数をSingleにした
    • 整数はIntegerではなく，Longにした
      • 参考↓

計測
'Module1に記述
Option Explicit
Const TEST As Long = 2          '比較する記述の個数
Const COUNT1 As Long = 100000   '1回の計測で実行する処理の回数
Const COUNT2 As Long = 5        '計測回数

Sub TestRun()
    Dim i As Long
    Dim j As Long
    For i = 2 To COUNT2 + 1 Step 1
        For j = 1 To TEST Step 1
            Cells(i, j).Value = Application.Run("test" & j)
        Next j
    Next i
    MsgBox "完了"
End Sub

Function test1() As Single
    Dim i As Long
    Dim v As Variant
    Dim t As Single
    t = Timer
    
    '<変数宣言>
    For i = 1 To COUNT1
        '<反復対象>
    Next
    
    test1 = Timer - t
End Function
計測対象：初期値
'<変数宣言>
'なし
'<反復対象>
v = [A1]

実行環境

PC1: Excel 2016 32bit, VBA 7.1
PC2: Excel 2019 64bit, VBA 7.1
PC3: Excel 2021 64bit, VBA 7.1

高速化方法

  • v = [A1]からの改善過程を順に記載
    • まずは，1度に1セルずつ指定する場合を考える
  • 凡例：速い<遅い
  • 計測時間：100000件を5回実行した平均(秒)
    • そんなに厳密に計ってないので，同表内での参考に

Rangeオブジェクトの指定

反復対象 PC1 PC2
① v = Cells(1,1) 0.21 0.21
② v = Cells(1,1).Offset(0,0) 0.3 0.32
③ v = Range("A1") 0.56 0.43
④ v = Evaluate("A1") 3.42 1.91
⑤ v = [A1] 3.41 2.04
  • ④⑤は文字列をオブジェクトや値に変換するメソッドであり，セル指定のためだけに使うには遅い
  • ③は文字列をセル位置に変換する手間があるので遅い
  • Cells(1, "A"),Range("A"&1)など余計な処理をすると更に遅い
  • ②は文字列処理はないものの，2工程あるため遅い
  • ①のCells(1,1)は，Cells.Item(1,1)の省略形

Sheetオブジェクトの共通化

Cells(1,1)は，ModuleやWorkbookだと，Excel.Application.ActiveWorkbook.ActiveSheet.Cells(1,1)の，
Worksheetだと，Me.Cells(1,1)の省略形である．
ちなみにRange()だと，前者はExcel.Application.Range()，
後者は同じくMe.Range()の省略形．（後述する）
処理の度にシートをわざわざ指定しているため，これを共通化する．
オブジェクト変数aを使用し，まずはActiveSheetを保存してみる．

変数宣言 反復対象 PC1 PC2
① Dim a As Worksheet
Set a = ActiveSheet		 v = a.Cells(1,1) 0.19 0.16
② Dim a As Object
Set a = ActiveSheet		 v = a.Cells(1,1) 0.37 0.4
③ Dim a As Variant
Set a = ActiveSheet		 v = a.Cells(1,1) 0.37 0.39
  • ②③のような広いオブジェクト変数を使用するより，①のほうが速い

Sheetオブジェクトの指定

Forの外でSetしても僅差なので，
<反復対象>部分にて，オブジェクト変数へのSet単独で時間を計る

変数宣言 反復対象 PC1 PC2
① Dim a As Worksheet Set a = Sheet1 0.01 0.005
② Dim a As Worksheet Set a = Worksheets(1) 0.076 0.043
③ Dim a As Worksheet Set a = Sheets(1) 0.078 0.039
④ Dim a As Worksheet Set a = Worksheets("Sheet1") 0.108 0.075
⑤ Dim a As Worksheet Set a = Sheets("Sheet1") 0.108 0.074
⑥ Dim a As Worksheet Set a = ActiveSheet 0.038 0.013
  • ActiveSheet,Worksheets(),Sheets()は，Excel.Application.ActiveWorkbook.{sheet}の省略
    • Worksheetsは，グラフシートなどを含めずにワークシートだけを検索する
    • ワークシートに限るか，シートの区別をしないかは僅差
  • Sheet1は，VBAProject.Sheet1の省略
    • エクセルブックへの参照ではない
  • シートの検索や⑥のアクティブかどうかより，①のオブジェクト名のほうが速い
    • ①より②のほうが使い勝手は良いが，今回は①を採用

Rangeオブジェクトでの共通化

Worksheet<Object<Variantを見るに，Rangeのほうが更に速いのではないか？

変数宣言 反復対象 PC1 PC2
① Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a(1,1) 0.169 0.137
② Dim a As Range
Set a = Sheet1.Cells		 v = a(1,1) 0.17 0.141
③ Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a.Offset(0,0) 0.184 0.152
④ Dim a As Worksheet
Set a = Sheet1		 v = a.Cells(1, 1) 0.192 0.157
  • Cells()は重いのでFor内では使わない
    • 後述する
  • ④のWorksheet型より，①②③のRange型のほうが速い
  • ③のセル範囲ずらしより，①②のセル位置の取得のほうが速い
    • Offsetはセル結合の影響を受けるので，尚更避けたほうがいい
  • ②の全セルの保存より，①の1セルの保存のほうが速い
    • ①②のa(1,1)は，a.Item(1,1)の省略形

セル範囲指定の高速化

ここからは長方形1つの範囲指定について考える．

変数宣言 反復対象 PC1 PC2 PC3
① Dim b As Worksheet
Set b = Sheet1
Dim a As Range
Set a = b.Cells(1,1)		 v = b.Range(a(1,1),a(1,2)) - - 0.288
② Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = Sheet1.Range(a(1,1),a(1,2)) 0.299 0.269 0.291
③ Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a.Range(a(1,1),a(1,2)) 0.354 0.307 0.338
④ Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a.Range("A1:B1") 0.674 0.458 0.472
⑤ Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a(1,1).Resize(1,2) 0.303 0.277 0.275
  • Sheet1はDim Worksheetより若干速い
    • range.Rangeよりsheet.Rangeよりのほうが速い
  • ③では，range.Rangeが重複して遅い
  • ②は，オブジェクト名を含むが速い
  • ⑤はリサイズ．②よりは遅いが，僅差
    • ちなみに，Resizeはセル結合の影響を受けない
    • A1を含む前提ならば、a.Resize(1,2)が速い

結論

セル1つ・セル範囲について，以下の通り．

  • 利点：aにA1が保存されていることで，a.Range()とsheet.Range(), a.Cells()とsheet.Cells()が同じ動きになり，使い勝手はいいかも
    • sheet.Cellsは全セルを範囲で指し，range.Cellsはrangeをそのまま指す
    • a.Range("A1:B1")が，Range("A1").Range("A1:B1")となり，Range("A1:B1")である
    • range.Rangeでは，親の左上(例ではA1)の位置をA1だと思い込んだときに，相対的にA1:B1の位置にあるセル(例ではA1:B1)を返す
計測対象：セル1つ
'<変数宣言>
Dim a As Range
Set a = Sheet1.Cells(1, 1)
'<A1の値の取得>
v = a(1, 1)
計測対象：セル範囲
'<変数宣言>
Dim a As Range
Set a = Sheet1.Cells(1, 1)
'<A1:B1の値の取得>
v = Sheet1.Range(a(1, 1), a(1, 2))
計測対象：A1を含むセル範囲
'<変数宣言>
Dim a As Range
Set a = Sheet1.Cells(1, 1)
'<A1:B1の値の取得>
v = a.Resize(1, 2)

今回は考慮しなかったが，1セル決め打ちでいいなら変数に持てばいい．
なお，.Value2をつけたほうが速い．後述する．

計測対象：1セル決め打ち
'<変数宣言>
Dim a As Range
Set a = Sheet1.Cells(1, 1)
'<A1の値の取得>
v = a.Value2

補足

後述するとしていた内容を解説していく．

オブジェクト変数の型

  • Worksheetオブジェクトの型
    • Worksheet型オブジェクト変数は，Worksheet/Sheet1
    • Sheet1は，Sheet1/Sheet1
    • sheets.ItemやActiveSheetの返り値がObjectだから，Sheets()やActiveSheetはObject/Sheet1
  • Rangeオブジェクトの型
    • Range型オブジェクト変数や，それ.Offset()は，Range/Range
    • Cells()やItem()などの，Itemを含むものは，Variant/Object/Range
      • range.Itemの返り値がVariantだから
    • Object/Sheet1から繋げたRange()は，Variant/Object/Range
      • Object/Sheet1の型名だけでは，ワークシートかグラフシートか確定できない
      • エラーとなりEmptyを出力する可能性があるため，Rangeと両立できるVariant型で返す
      • その後実行が成功しても，Variantは継続されるためVariant/Object/Rangeとなる
      • ちなみに，Application.Range("A1")は，ActiveSheet.Rangeに転送されるが，Applicationの時点でワークシートだと分かっているのでVariantにはならない
    • Object/RangeやSheet1/Sheet1から繋げたRange()は，Object/Range
      • sheet.Rangeやrange.Rangeは，application.Rangeと違って，ActiveやMeなど既定以外のシートを指定できない
      • Sheets(1).Range("Sheet2!A1")はエラーで，Application.Range("Sheet2!A1")はセーフ
      • このエラーになることもあるが，グラフシートによるエラーとは違ってVariantにはならない
    • その他は，Object/Range
      • 型のサイズが変わるときは，Object内で昇格する

Object型への.Rangeは，↓がわかりやすい．

行入力時と実行時うんぬんは， ↓が詳しい．

Rangeオブジェクトの取得プロパティの指定

[なし] vs Value vs Value2

反復対象 PC1 PC2
① v = Cells(1,1) 0.22 0.16
② v = Cells(1,1).Value2 0.24 0.18
③ v = Cells(1,1).Value 0.23 0.19
④ v = Cells(1,1).Text 1.48 0.67
変数宣言 反復対象 PC3
① Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a.Value2 0.080
② Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a.[_Default] 0.081
③ Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a 0.090
④ Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a.Value 0.090
⑤ Dim a As Range
Set a = Sheet1.Cells(1,1)		 v = a.Text 0.469
  • 内部では，Value2の形式で保存されている
  • .Textは￥マークを付けるなど，セルに表示されている書式通りに変換して取得するので遅い
  • .Valueでは，日付を日付形式へ変換し，通貨を通貨型で取得し，他はそのままの値を取得する
  • .Value2では，日付をシリアル値のまま，通貨を浮動小数点型のままで取得する
    • 書式を判定しなくてよいため，.Valueよりも.Value2のほうが速い（今回は逆転したが...）
  • range.[なし]よりもrange.[_Default]のほうが，.Valueへの遷移がないため速い
    • Range/Rangeではないときは，規定プロパティが判断できないため，.[_Default]は使えない
  • [なし]のときの既定プロパティは.Valueに遷移されるが，[なし]と.Valueとでは，Range/Rangeへの.Valueは等しくなるが，Variant/Object/RangeやObject/Rangeへの.Valueは，代入での強制型変換よりも，明示して.Valueを取り出すほうが遅い
    • Cells(1,1).Valueでは，Variant型で保存されたRangeオブジェクトをRange型として読み取り，値をVariant/{String|Double|...}型で返す
      • 以下，推測
      • .Valueが可能なのかのために，中身がRangeかどうかを実行時に確認が必要
      • Variant変数に.ValueするためにRange型かどうか確認するよりも，Variant変数を見てRange型を得た後に既定プロパティで値にするほうが速い
  • .Value2<.Valueであるから，.Valueと[なし]が等しくなるRange/Rangeでは，.Value2が一人勝ちする
    • Value2と[なし]の差よりも，オブジェクト変数やCells()<Range()やItem()<Offset()の差のほうが大きい
      • 基本的にはCells()を使い，[なし]でよいが，Range/Rangeからそのまま取得するときには.Value2をつける

Withステートメントの利用

With
'With
With hoge
    処理
End With

'Dim/Set
Dim obj As 型
Set obj = hoge
処理
Set obj = Nothing
  • Withで変数を1つ宣言し，End WithでNothingするのと同等
  • 自動生成された変数の型は，省略対象と同じ型になる
    • 「Dim obj As 型」の場合だと変数の型を指定しているため，Set時に型変換される
    • というか，Let/Set以外で型が変化することはない
  • よって，Withの省略対象が長いと上述のRangeとObject/Rangeの差が生じる
    • 省略対象はあらかじめ高速にした上で，記述の省略として使用すべし
    • 参照先の呼び出し自体は，DimよりWithのほうが速い
      • とりあえず，Debug.Print .Address()で計測
    • Range型変数が既にあれば有用だが，Withのためだけの変数化なら変数を使えばいい
With使用例
Dim obj As Object
Set obj = sheet.Range("A1") 'Object/Rangeのまま

Dim obj As Range
Set obj = sheet.Range("A1") 'Rangeに変換

With sheet.Range("A1") 'Object/Rangeのまま

Dim obj As Range
Set obj = sheet.Range("A1") 'Rangeに変換
With obj '変換後のRangeのまま

参考

  • 基礎解説をこちらに分割しました↓

変更履歴

2021/6/13 初稿
2021/6/14 プロパティ関連の修正，Itemへの言及の追加
2021/6/15 セル範囲での高速化の追加
2021/6/16 取得プロパティの詳細の追加，Let強制を追加
2021/6/27 Withステートメントを追加
2021/6/28 結論の整理，参考サイトを追加
2021/6/29 オブジェクト変数の型関連を追加
2021/6/30 オブジェクト指向っぽい説明を追加
2021/7/17 基礎解説を別記事に分割
2021/8/12 Application.Runを修正
2022/5/14 全体的に改稿修正

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2