VBA-レッスン 13.1。配列の使用 (Arrays)

配列 (Arrays) は、Excel VBA などのプログラミングでよく使用されます。

配列は基本的に、値を格納するための多数のセルを備えた単一の変数ですが、一般的な変数には 1 つの値のみを格納できる格納セルが 1 つしかありません。

配列に含まれるすべての値を参照したい場合は配列全体にアクセスすることも、配列の個々の要素を参照することもできます。

同じデータ型の値のセットを扱う配列を宣言できます。これについては、VBA-レッスン 6.2。データ型 (Variables) しかし、ここでさらに深く掘り下げてみましょう...

なぜ配列が使用されるのでしょうか?

最大 500 個の値を保存する必要があるプロシージャを作成しようとしていると想像してください。 500 個の個別の変数を作成する必要がある場合、それは非常に困難になります。配列の中央では、これらの値の保存と操作がはるかに簡単になります。

配列を使用する 2 番目の理由は、その速度です。配列からのデータの読み取りは、Excel ワークシートのテーブル (セルで構成される) からの読み取りよりも大幅に時間がかかりません。

配列の使用例

ここでは、Excel で VBA 配列を使用する明らかな利点を示す例を示します。

最初のワークシート (「DS」) には、5000 行×3 列のデータ セットがあります。

2 番目のシートには、年および顧客ごとのすべての「はい」の回答 (「YES」) を考慮した要約表が表示されます。

この場合、この手順ではループを使用してデータ セットを処理し、各年および各顧客番号の「はい」回答の数を記録し、そのデータを適切なセルに入力します。

配列を使用しない場合、このプロシージャの実行には Excel 131.44 秒かかります。

ただし、最初にデータを (「DS」ワークシートから) 配列に保存し、次に同じ計算を (「DS」 ワークシート データセットの代わりに配列を使用して) 実行すると、かかる時間はわずか 1.74 秒です。

「YES」応答を含むデータのみを配列に格納することで手順を最適化することにした場合 (これは、全データの約 3/4 に相当します)、所要時間はわずか 1.02 秒です。

これは、配列を使用すると手順を 128 倍高速に実行できることを示す良い例です。同時に多くのデータセットを操作すると、最適化の結果はさらに良くなります。

レッスンの最後に例の詳細に戻ります。

配列の宣言

以下に、VBA で配列を宣言する例をいくつか示します (最初の 2 つの例がよくわからない場合は、それをお読みください)。

 '1次元配列の宣言例
 Dim array1(4)

 '2次元配列の宣言例
 Dim array2(6, 1)

 '動的配列の宣言例
 Dim array3()

配列を宣言するときに固定値を入力できない場合 (データセットのサイズなどに依存するため)、括弧は空のままにしてください。

データ型 (string、long など) を宣言する必要はありませんが、多くの場合、プロシージャの実行が遅くなります。

アレイ内のデータストレージ

いくつかのデータを配列に保存してみましょう。

この場合は 11x1 の値を保存したいので、1 次元配列を作成する必要があります。

 '宣言
 Dim array_example(10)

配列内の要素の番号付けは 0 から始まることを忘れないでください (これはプログラミングの標準であるため、すぐにこの習慣を身に付ける価値がありますが、実際には VBA でこのアプローチを変更できます)。

配列内の各要素はその値を受け取ります。

 '値を配列に保存する
 array_example(0)  = Range("A2")
 array_example(1)  = Range("A3")
 array_example(2)  = Range("A4")
 array_example(3)  = Range("A5")
 array_example(4)  = Range("A6")
 array_example(5)  = Range("A7")
 array_example(6)  = Range("A8")
 array_example(7)  = Range("A9")
 array_example(8)  = Range("A10")
 array_example(9)  = Range("A11")
 array_example(10) = Range("A12")

通常の変数であるかのように、各配列要素を操作または変更できます。

次に、array_example(8) を使用する例を示します。

Sub example()
     '宣言
     Dim array_example(10)
     
     '値を配列に保存する
     array_example(0)  = Range("A2")
     array_example(1)  = Range("A3")
     array_example(2)  = Range("A4")
     array_example(3)  = Range("A5")
     array_example(4)  = Range("A6")
     array_example(5)  = Range("A7")
     array_example(6)  = Range("A8")
     array_example(7)  = Range("A9")
     array_example(8)  = Range("A10")
     array_example(9)  = Range("A11")
     array_example(10) = Range("A12")
     
     'テスト1
     MsgBox array_example(8) '=> 戻り値 : 02.04.2016
    
     'いずれかの値を変更する
     array_example(8) = Year(array_example(8))
     
     'テスト2
     MsgBox array_example(8) '=> リターン : 2016
End Sub

For ループは、配列をより速く保存するためのより良いオプションです。

 '宣言
 Dim array_example(10)

 '値を配列に保存する
 For i = 0 To 10
    array_example(i) = Range("A" & i + 2)
 Next