VBAでセル指定の高速化

[3000文字Tips](https://qiita.com/official-events/d523df99d6479293ffa7)に~~参加しています~~ →3000文字超えてた

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

https://qiita.com/kariyaitaru/items/65696800b8f8140f107e

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

# 計測方法
参考コード↓

https://excel-ubara.com/excelvba4/EXCEL_VBA_405.html

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

https://docs.microsoft.com/ja-jp/previous-versions/office/office-10/aa164754(v=office.10)

```vb:計測
'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
```

```vb:計測対象：初期値
'<変数宣言>
'なし
'<反復対象>
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<br>Set a = ActiveSheet | v = a.Cells(1,1) | 0.19 | 0.16 |
| ② Dim a As Object<br>Set a = ActiveSheet    | v = a.Cells(1,1) | 0.37 | 0.4  |
| ③ Dim a As Variant<br>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<br>Set a = Sheet1.Cells(1,1) | v = a(1,1)        | 0.169 | 0.137 |
| ② Dim a As Range<br>Set a = Sheet1.Cells      | v = a(1,1)        | 0.17  | 0.141 |
| ③ Dim a As Range<br>Set a = Sheet1.Cells(1,1) | v = a.Offset(0,0) | 0.184 | 0.152 |
| ④ Dim a As Worksheet<br>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<br>Set b = Sheet1<br>Dim a As Range<br>Set a = b.Cells(1,1) | v = b.Range(a(1,1),a(1,2)) | - | - | 0.288 |
| ② Dim a As Range<br>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<br>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<br>Set a = Sheet1.Cells(1,1) | v = a.Range("A1:B1")       | 0.674 | 0.458 | 0.472 |
| ⑤ Dim a As Range<br>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)を返す

```vb:計測対象：セル1つ
'<変数宣言>
Dim a As Range
Set a = Sheet1.Cells(1, 1)
'<A1の値の取得>
v = a(1, 1)
```

```vb:計測対象：セル範囲
'<変数宣言>
Dim a As Range
Set a = Sheet1.Cells(1, 1)
'<A1:B1の値の取得>
v = Sheet1.Range(a(1, 1), a(1, 2))
```

```vb:計測対象：A1を含むセル範囲
'<変数宣言>
Dim a As Range
Set a = Sheet1.Cells(1, 1)
'<A1:B1の値の取得>
v = a.Resize(1, 2)
```

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

```vb:計測対象：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は，↓がわかりやすい．

https://thom.hateblo.jp/entry/2016/05/28/222452


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

http://officetanaka.net/excel/vba/error/

## 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<br>Set a = Sheet1.Cells(1,1) | v = a.Value2     | 0.080 |
| ② Dim a As Range<br>Set a = Sheet1.Cells(1,1) | v = a.[_Default] | 0.081 |
| ③ Dim a As Range<br>Set a = Sheet1.Cells(1,1) | v = a            | 0.090 |
| ④ Dim a As Range<br>Set a = Sheet1.Cells(1,1) | v = a.Value      | 0.090 |
| ⑤ Dim a As Range<br>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ステートメントの利用

```VB: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のためだけの変数化なら変数を使えばいい

```VB: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のまま
```

# 参考
- 基礎解説をこちらに分割しました↓

https://qiita.com/sasio/items/406aadd21b2b8cd08f63
- Value vs Textなどの解説は多いけど，rangeの型まで考慮してないサイトが多い
    - 中間コードで調べたいなら，[実務であまり役に立たないVBAの内部の話 - Qiita](https://qiita.com/mima_ita/items/ad3adaa9c9db658ecdb7) がオススメ
    - [What is the purpose of the \`With\` statement - StackOverflow](https://stackoverflow.com/questions/63413015/vba-what-is-the-purpose-of-the-with-statement)
 もオススメ
- 公式ドキュメントは正義．分割されてるので注意
    - [Excelオブジェクト系](https://docs.microsoft.com/ja-jp/office/vba/api/overview/excel), [VBA変数系](https://docs.microsoft.com/ja-jp/office/vba/api/overview/language-reference), [VBA言語系](https://docs.microsoft.com/en-us/openspecs/microsoft_general_purpose_programming_languages/ms-vbal/d5418146-0bd2-45eb-9c7a-fd9502722c74)

# 変更履歴
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 全体的に改稿修正