Excel VBAで配列を覚える労力は報われるのか

はじめに

前回は、Excel VBAでマクロが遅くなる3つの原因と解決方法について紹介しました。

lbibouroku.hatenablog.com

今回はExcel VBAの配列を覚えるのが、なぜ大変なのかを中心に紹介したいと思います。

◆目次◆

はじめに
Excel VBAの配列の使い方を覚えるのが大変な理由
- 決まりごとの多いExcel VBAの配列
それでも配列を覚えた方がいい理由
- どれくらい処理速度が速くなるのか
さいごに

Excel VBAの配列の使い方を覚えるのが大変な理由

答えは前回の記事でお伝えしたとおり、「Excel VBAの配列は制約が多いから」です。
では、どんな制約があるのか、簡単に説明します。
(細かい説明は別の記事で行いますので、今回は「どんな制約があるのか」という観点で見ていただければと思います。)

決まりごとの多いExcel VBAの配列

静的配列と動的配列がある
Excel VBAのは配列では、配列のサイズを後から変更できない「静的配列」と、配列のサイズを後から変更できる「動的配列」の2種類があります。
「静的配列」と「動的配列」ではコードの書き方が異なります。（動的配列の方が、決まりごとが多いです）
インデックスのはじまりが可変である
プログラミングで配列を扱うときは、インデックス0からカウントしていくパターンが多いです。しかし、Excel VBAの場合はインデックスの始まりを0以外にすることが可能です。たとえば、Excelのセルに合わせて0以外の数字から始めることが多々あります。（その方が、結果的に可読性は上がるのですが）
動的配列はサイズの変更ごとにReDimステートメントの宣言が必要である以下は動的配列のコードです。

Sub test4()

'変数drinksをインデックスは空白のまま宣言する。 
    Dim drinks() As String

'「ReDim」で変数drinksの配列の数を宣言しなおす。 
    ReDim Preserve drinks(2)     
    drinks(0) = "Coffee"
    drinks(1) = "Tea"
    drinks(2) = "Milk"

'「ReDim」で変数drinksの配列の数を宣言しなおす。 
    ReDim Preserve drinks(3)
    drinks(3) = "Cocoa"
    
'「ReDim」で変数drinksの配列の数を宣言しなおす。 
    ReDim Preserve drinks(4)
    drinks(4) = "Juice"

End Sub

このように、Excel VBAでは一度配列のサイズ（インデックスの最大値）を決めてしまうと、配列のサイズを変更するたびにRedimステートメントを使って、配列のサイズを宣言しなければなりません。(実際は、セル範囲をFor～Next構文のループを使って、宣言を行うことが多いです）

Variant型
Excel VBAでは変数を宣言するときに、型宣言が必要です。配列でも同様で、たとえば配列の中身が「すべて文字列」であれば、String型を宣言します。
しかし、配列の中身の型が一致しない場合も多々あります。そんなときはVariant型を宣言しなければなりません。
PCのスペックによってはメモリ不足になる場合がある
PCのスペックが低いと、大きな表を配列に格納すると、メモリ不足になってエラーが出てしまう可能性があります。その場合は、必要な範囲のみ配列に格納するなど、工夫が必要です。

それでも配列を覚えた方がいい理由

こちらも前回お伝えしたとおり、「処理速度が速くなる」からです。

どれくらい処理速度が速くなるのか

Excel VBAでマクロ処理を早くする方法はいくつかありますが、「セルの選択回数を減らす」という方法は、かなり効果が高いです。

どれくらい効果があるのでしょうか。比較のために、以下の条件でマクロを実行してみます。

処理内容
Sheet1の「セルA1からセルT10000」の範囲に数字（行数）を入れる
ワークシート関数
Sheet1の「セルU1からセルU10000」の範囲に同じ行の、A～T列を足し算するSUM関数が入っている（1行目には「SUM（A1:T1）」が入っている）

f:id:lbibouroku:20210805194146p:plain

結果は以下のとおりです。

条件	処理方法	マクロが終了するまでの秒数
条件1	特になし	6.351563
条件2	ワークシート関数を手動計算に設定する	3.171875
条件3	画面表示の更新を停止する	5.351563
条件4	条件2と条件3を設定する	2.890625
条件5	配列を使う	0.2265625

なお、計測には以下のマクロを使用しました。

条件1～条件4

Sub test5()

'条件1：このまま実行
'条件2：AとCのコメントを解除
'条件3：BとDのコメントを解除
'条件4：A～Dのコメントを解除

'    Application.Calculation = xlCalculationManual　' A
'    Application.ScreenUpdating = False　' B
    
    Dim start As Variant
    start = Timer
    
    Dim i As Long
    Dim j As Long
    
    For i = 1 To 10000
        For j = 1 To 20
            Sheet1.Cells(i, j) = i
        Next j
    Next i
    
    Dim finish As Variant
    finish = Timer
    
'    Application.Calculation = xlCalculationAutomatic　' C
'    Application.ScreenUpdating = True　'D
    Debug.Print finish - start

End Sub

条件5

Sub test6()

Dim start As Variant
start = Timer

Dim num(1 To 10000, 1 To 20) As Long
Dim i As Long
Dim j As Long
    
    For i = 1 To 10000
        For j = 1 To 20
            num(i, j) = i
        Next j
    Next i
         
Sheee1.Range("A1:T10000") = num

Dim finish As Variant
finish = Timer

Debug.Print finish - start

End Sub

さいごに

一見、Excel VBAの配列の使い方を覚えるのが大変な理由の方が多いように見えますが・・・労力に対してのリターンはきちんとあります！

また、Excel VBAで自動化ツールが作れるようになると、だんだん複雑なコードが書けるようになるのですが、そのうちに「処理速度の問題」という壁にぶつかることがあると思います。ここで配列に手をつけざるを得ないというパターンが多いのではないでしょうか。（実は、私もそうでした。）

配列を覚えるのは難易度がやや高いですが、できるだけわかりやすいように説明しますので、お付き合いください。

次回は配列の種類について説明します。

Kの備忘録（仮）

Python、VBA、Excelを中心に記事を投稿