【Excel VBA】 要素数を変更出来る動的配列の設定方法

こんにちは。IMMRです。

例えば、下記のような商品名一覧を配列に入れたい場合は以下のようにセットします。

図1　サンプルデータ1

、

配列の宣言は以下になります。

Dim MyData(n) As String 　※nは配列の要素数です。また要素数は0(ゼロ)から始まります。

　　　　　　　　　そのため、10個の配列を宣言するときは、n=9となります。　MyData（０）～MyData（9）

そして、下記のように配列にセットしていきます。

Dim MyData(9) As String
Dim i As Long
Dim cell_Max As Long

cell_Max = Range("A1000").End(xlUp).Row
For i = 2 To cell_Max
    MyData(i - 2) = Cells(i, "B")
Next

これにより、B列にある商品名を配列にセットすることができます。

では次に下記のデータを使用して、同じ処理を行います。プログラムは上記と同じです。

図2　サンプルデータ2

しかし、実行すると下記のようなエラーとなってしまいます。

図3　実行エラー

これは、MyData(9)までしかセット出来ない配列に対して、MyData(10)に値を入れようとしたからです。

Dim MyData(100) As Stringのように「大は小を兼ねる」のように大きく配列を作るという方法にすればエラーなく、処理を行うことができますが、必要以上に要素数の大きい配列を作成するのは、メモリを使ってしまうのであまり望ましくありません。

入力データにちょうどよい要素数の配列を使用するのがいいのです。

ちょうどよい要素数を使用するには、入力データを確認し、要素数を変更する必要があります。配列の要素数を変更することが出来る、それが動的配列なのです。

1.要素数を変更出来る動的配列の設定方法

要素数を変更出来る動的配列の設定方法ですが

1.動的配列の宣言

2.動的配列の要素数の設定

という順で行っていきます。

1.動的配列の宣言

Dim new_MyData() As String

※普通の配列では、Dim d_Hairetu(1０) As String　のように要素数を記入して宣言しますが。動的配列の場合は要素数を宣言しません。()で宣言します。

2.動的配列の要素数の設定

ReDim new_MyData(n)　　　　　※nは要素数

※処理の中でデータ数を確認して、要素数が決定したらReDimを使用して、要素数の設定をします。

2.要素数を変更出来る動的配列を使用したサンプルコード

要素数を変更出来る動的配列を使用したサンプルコードについて説明しますが。使用するのは、上記の図1と同じデータを使用します。

図4　サンプルデータ1

プログラムは以下の通りです。

Sub TEST36()

Dim new_MyData() As String
Dim cell_Max As Long
Dim i As Long

cell_Max = Range("A1000").End(xlUp).Row

ReDim new_MyData(cell_Max - 2)
For i = 2 To cell_Max
    new_MyData(i - 2) = Cells(i, "B")
Next

For i = 2 To cell_Max
    Cells(i, "G") = new_MyData(i - 2)
Next

End Sub

このプログラムは、B列に記載してある商品名を動的配列にセットし、正しくセットされているかを確認するプログラムです。

そのため、G列に配列にセットした値を出力しています。

For文二つを使ってこのようにロジックを作成するのは、よい書き方ではありませんが、

ここでは配列にセットされているかの確認をしているのでわざとこのような記載をしています。ご了承ください。

では、プログラムの説明をしていきます。

Dim new_MyData() As String

　　配列を宣言しています。　※ここでは「new_MyData」という名前で宣言しています。

Dim cell_Max As Long

　　データが何行目まで格納されているか。行番号を格納する変数を宣言しています。

Dim i As Long

　　for文で使用する変数を宣言しています。

cell_Max = Range("A1000").End(xlUp).Row　

　これは、セルのA1000から「Ctrl」+「↑」を押したのと同じようにセルに記載されているのが何行目かを確認する方法です。別の記事で紹介していますので、ご参照下さい。　【Excel VBA】For Nextで最大値が自動で設定される方法　

　ここでは11行目までにデータが記載されているのでcell_Maxには11がセットされます。

ReDim new_MyData(cell_Max - 2)

　動的配列を宣言したnew_MyDataに要素数を設定しています。ここで-2を使用している理由ですが、　cell_maxは、ヘッダーを含んだ個数になります。(ヘッダー：1件　データ：10件)そのため、-1が必要です。

　そして、配列は０から始まります。10個のデータを格納するには、[9]つまり[0],[1],[2]・・・[8][9]で足ります。

　もし、[10]とすると問題はないですが１０個のデータの場合、[10]の箇所には値が入らないので余分な部分になってしまいます。

For i = 2 To cell_Max

　For文によってセルの値を配列に格納していますが、1行目はヘッダーなので2行目から格納の必要があるからです。そのため、1ではなく、2が設定されています。cell_maxはデータがセットされている最後の行です。今回の場合は11がセットされています。

つまり2～11でループが行われることになります。

new_MyData(i - 2) = Cells(i, "B")

　セルの値を配列に格納します。上記であげたFor文を使用しているのでセル(B2)～セル(B11)をセットしていることになります。

　配列は、0からなので配列(0)～配列(9)に値を格納していきます。

Cells(i, "G") = new_MyData(i - 2)

　配列に格納するといった作業ではないですがここでは、配列に格納した値を今度は、セルのG列に値をセットしていきます。

3.要素数を変更出来る動的配列を使用したサンプルコードの実行

では、実際に上記のプログラムを実行します。

下記が処理実行前のブックです。

図5　処理1_実行前

実行を行います。

↓

↓

↓

図6　処理1_実行後

B列にある商品名を配列にセットして、その後配列からG列に正しくセットされていることが確認できます。

では次に、異なるデータ数で実施します。

図7　処理2_実行前

今度は、さきほどと異なるデータ数を使用して行います。もちろんプログラムは上記と同じです。

では実行を行います。

↓

↓

↓

図8　処理2_実行後

先ほどと同じようにB列にある商品名を配列にセットして、その後配列からG列に正しくセットされていることが確認できます。

これにより、動的配列を使用して値を格納することが出来ました。